Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    36

    Unanswered: Performace issues after applying FP 16

    Hi All,

    We have recently moved our Datawarehouse database from DB2 UDB v8.1 FP5 to FP16. The OS is AIX 5.3 and its a multipartitioned database across 2 servers with 5 partitions.

    We have DataStage jobs scheduled to load data into tables, of which some of them use INSERT or SELECT-INSERT to load the data and not the DB2 LOAD utility. These jobs are now facing a performance problem such that the total execution time has gone up 4-10 times as before.

    Nothing has changed in the structure of the database before or after the fixpak application. Would appreciate any help or pointers which could help me investigate the cause of this behavior.

    Thanks,
    RiTz

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You went from V 8.1 to 8.2 in applying the fixpack. Did you do all of the steps in the post installation process? (dasupdt, db2iupdt, db2updv8, and all of the BIND steps)

    Andy

  3. #3
    Join Date
    Jan 2005
    Posts
    36
    Hi Andy,

    I've performed all the post installation steps mentioned in the readme.txt except for the db2updv8. The main reason for this is that if I need to fallback to the previous version I can do that. The fixpak applied is a regular without commiting.

    Will a db2rbind on the database be helpful. Anything that needs to be done apart from the steps mentioned in readme after applying the fixpak?

    Thanks,
    RiTz

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can rebind all of you packages either with db2rbind or using the REBIND command and see what that does. I cannot think of anything else after applying a fixpack. As V8 still has a few months of IBM support to go, you can open a PMR with IBM.

    Andy

  5. #5
    Join Date
    Jan 2005
    Posts
    36
    I performed a rebind of all the packages in the database. The performance of the INSERTs has not changed though.

    There are a few jobs which use the LOAD utility and they still run in the same time as before the patching. However, the same INSERT jobs execute in the same time in the UAT/Test setup with the production data, on FP16.

    Can't really figure out what could be the problem here! There is not locking or any timeout detected during the execution of these INSERTs too. But the time taken by the INSERTs to load data is pretty high than before.

    Appreciate any kind of suggestions or help.

    RiTz

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I thought (but not sure) that db2updv8 was required when moving from 8.1 to 8.2.

    Since DB2 V8 is out of service at about the end of 1Q 2009, what do propose to do about that?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My suggestion would be to set this up on a test system, apply the db2updv8 and then see if the performance improves.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jan 2005
    Posts
    36
    We do have a test database patched in July 2008 with FP16. The db2updv8 was not executed on both the test as well as production databases, so that we could rollback if there were any major issues.

    However, in the test environment the ETL jobs execute fine with the INSERTs and production data. Its only when these jobs are executing in the production that I am facing problems. SELECTs are not a problem and are running fine as before. Even jobs which use the LOAD are doing as before.

    Had raised a PMR with IBM but not much help as the same works in test environment.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would take a look at your lockwait time. The total amount of lock wait is available in the database snaphost (I believe you need the lock monitor switch on).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jan 2005
    Posts
    36
    I've checked the lock waits using the database snapshot during the execution of the jobs. I have attached the log to the post. Please let me know if you find something useful which I might have missed out on earlier.

    Thanks,
    RiTz
    Attached Files Attached Files

  11. #11
    Join Date
    Jan 2005
    Posts
    36
    The DBM CFG parameter NUM_POOLAGENTS seems to have solved the issue.

    The earlier config parameter values were as follows:

    Priority of agents (AGENTPRI) = SYSTEM
    Max number of existing agents (MAXAGENTS) = 400
    Agent pool size (NUM_POOLAGENTS) = 0
    Initial number of agents in pool (NUM_INITAGENTS) = 0
    Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
    Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
    Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS


    When we changed the NUM_POOLAGENTS to 200 (default calculated) and the INSERTs were executing fine. Setting it back to 0 changes the execution time to twice/thrice the original time.

    That brings me to the question, has the behavior related to assigning agents changed between DB2 8.1 to DB2 8.2 (FP 5 to FP 16)? Can anypne help with any documentation related to such a change in behavior.

Posting Permissions

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