Performance issue on V10 after DB2 upgrade on DSNTIAUL utility for a SQL unload

Aurobinda Pratihari

Performance issue on V10 after DB2 upgrade on DSNTIAUL utility for a SQL unload

Hi All - Last sunday we have migrated DB2 from V8 to V10 conversion mode and we have a major performance issue for a select dynamic peoplesoft SQL which was getting unloaded by DSNTIAUL utility.The SQL was getting unloaded in 4 mins ( IN V8) where as it is taking 5 hours.The SQL is little bit complex and involves nested loop join.is there any issues you are facing on DSNTIAUL after you did V10 upgrade and any solution to that?We have thought of doing a tuning as the last option as the query was running fine in WINSQL and all access path looks good.We did a runstats for all tables after V10 as well.Any suggestions how to fix this.Also I have tried  setting the  register Degree(any).It reduced the SQL unload execution by 1 and half hour less but no success like 4-10 mins run before in V8.Please advise if I can use anything to fix this.


Aurobinda Pratihari

Dana Rothrock

RE: Performance issue on V10 after DB2 upgrade on DSNTIAUL utility for a SQL unload
(in response to Aurobinda Pratihari)

Have you run EXPLAIN?  Check the predicates, path and index usage. There must have been a change in path.  Sometimes DB2 Optimizer will take a new path, different from EXPLAIN, so you may have to get real-time monitoring stats on object access, getpages. Have you tried Multi-Row FETCH option in DSNTIAUL?

Good luck.
Dana Rothrock