Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2015
    Posts
    10

    Unanswered: Problem in db2 database configuration settings. Facing issue in loading.Config Attach

    Hi All ,

    I am new to db2 database with limited knowledge of database admin...I am working as cognos developer which uses db2 as database source.
    We use db2 import/load tool to load data into our database tables...we have one table with 250 columns and around 40k rows..whenever we try loading data into that table using import/load utility tool from control center ..initially table loading doesn't take much time .
    table gets locked I feel..and sometimes load continues for hours...and sometimes it works fine and data gets loaded in one shot....

    I am attaching the database configuration details.Please have a look on all and suggest if something is screwed up..or we need to change any parameter value.
    Our database is not very big.We have around 15-20 tables with maximum rows as 1 lakh and that too in 2-3 tables only.Rest all table have row count between 20k-40k max.

    Database configuration files attached with name starting from 1 to 4.
    Please suggest if I need to change any db2 config parameters also any way so that I can make that table import faster..

    Many thanks in advance!
    Attached Thumbnails Attached Thumbnails 1.jpg   2.jpg   3.jpg   4.jpg  

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    we would be able to help you more with the import statement used, additional info on the table such as how many indexes, is it in use when you are doing this import? your comment
    around 40k rows..whenever we try loading data into that table using import/load utility tool from control center ..initially table loading doesn't take much time .
    do you mean you have existing data in the table and you are attempting to import another 40K rows?
    Dave

  3. #3
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    It would help if you also included the DB2 version and o/s type - I presume it's 9.7 if you are still using Control Center.
    You should set the locktimeout parm in db cfg to something other than -1 for a start. Are you doing a load or an import? - they are different utilities.
    As dav1m0 said, are you doing a load insert, a load replace, an import insert or an import replace?
    If you are on Linux/Unix you can use db2top to monitor any locking issues
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Looking at the configuration parameters won't help you (or anyone else) to improve performance until you find out what the bottleneck is. Your best bet is to talk to your DBA. Alternatively, use any of the DB2 monitoring tools to determine where the time is spent. For example, you could try running the DBSUMMARY() stored procedure several times during the load when it is slow and look at its output.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    Please post DB2 and OS Version and the exact command that you are using.

    That said - if you are using LOAD command then it needs exclusive lock on the table, so if some select statement is running (even if its WITH UR) still LOAD command will wait and timeout.

    If using IMPORT it should not cause any issue on concurrent reads and writes on the table.
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •