Hi,
does UDB 8.2.0 Fixpack 7 degrades performance.

I have 7.2.9 UDB on Windows 2003.
steps follwed were as follows,
i) benchmark queries and stored procedures on 7.2.9.
ii) install 8.1.2 UDB.
iii) migrate database, convert indexes, rebind packages.
iv) benchmark queries and sp's.
( at this point - the results of 7.2.9 and 8.1.2 were close enough ) .
v) Apply Fixpack 7
vi) benchmark queries and sp's.
( at this point - the results of 8.2.0 was not even close to the results from 7.2.9 or 8.1.2, the results were showing
a severe performance degardation.) same is the case with Fixpack 7a and 8

IBM Support suggested :
to change DB2_HASH_JOIN to NO cos the default had changed to YES in 8.2.0

tried that, still then, i am not able to acheive the same results as in 7.2.9.


that optimizer is not giving the expected results...
i tried to influence the optimizer by changing the DFT_DEGREE, MAX_QUERYDEGREE, DFT_QUERYOPT parameters

i would like to know, if i am making any mistake in the upgrade process or is there anything else that needs to be followed up inorder to achieve tha same results

Suggestions and help required...

Thanks

Ratish

I would try installing fixpak 8, which is now available. Also, do reorgs and runstats again if possible, then the rebinds. The runstats command has changed from V7 to V8, so make sure you are getting stats on "Indexes all," and "distribution on key columns" (at a minimum).

Hi Marcus,

Thanks for the reply...

yes, i has read thru the Fixpack 8 release notes, applied Fixpack 8, did the runstats (DB2 RUNSTATS ON TABLE <> WITH DISTRIBUTION AND DETAILED INDEXES ALL ) and rebind (DB2RBIND <> ALL -u <> -p <> -l <>).
but the results are still the same with no difference...

i didn't do the reorg, r u suggesting that i do the reorg followed by runstats and rebind ?

I would normally do the reorg, but I can't really tell you that it is the problem.

I think that they only way we can really help on this forum is if you give us details about the problem SQL statement. That would include providing the DDL of tables and indexes with row and distribution counts.

If you could do an explain report on the problem query, that would also be useful.

But the only way to get a definite answer as to what is going wrong would be to have taken explain plans of your benchmark queries on 7.2 then done the migration and then taken explain plans once again. You'd then be able to see if the manner in which the optimiser was accessing the data had changed or not.

It looks like the DB2 memory management principles have been changed significantly between v7 and v8. You'll need to play with agent memory allocation parameters to remedy the situation. Check db2diag.log for memory allocation errors and take snapshots of running applications to see actual memory utilization.

E.g. in one of the cases we had to decrease SORTHEAP, otherwise the optimizer tended to choose plans favouring in-memory sorts as opposed to index access.