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

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

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

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

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

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?

My suggestion would be to set this up on a test system, apply the db2updv8 and then see if the performance improves.

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.

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).

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

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.