Problem:
WebDocs iSeries searches are slow. You turn on the DEBUGSQL setting:
CHGDTAARA DEBUGSQL = *YES
And when you run a search to display the SQL statements used and then run the SQL statement with interactive SQL, it is very fast.
Solution:
While this looks like a performance problem with the WebDocs iSeries SQL queries, it is actually a PTF-related issue. There are some SQL-related PTFs for V5R2 that cause problems with performance, because the SQL queries do not always select the correct index to use when run in batch mode in programs such as WebDocs iSeries.
Use the following command with the appropriate PTF number to see if you have applied any of the PTFs listed below:
DSPPTF LICPGM(5722999) SELECT(MF31309)
Note: As of 2/21/2005, most of these PTFs are probably superceded by newer ones, but if you are missing any of these PTFs, order them and hopefully the IBM automated PTF system will know they are superceded.
MF31309 V5R2M0 LIC-DB SRC0600A094 following Apply of PTFs
MF31304 V5R2M0 LIC-DB SRCB6005121 DURING DBST VLOG
MF31231 V5R2M0 OSP-DB-SAP400-F/HMDERIVEDHEAP-MSGMCH3203-T/QQQOOOINV QUERY W
MF31301 V5R2M0 LIC-DB JOBS WAITING ON A GATE FROM DBOPPLANSUBCACHE
MF31110 V5R2M0 LIC-DB MAX DATA SPACE SIZE INCREASE
MF31138 V5R2M0 OSP TEMP STORAGE GROWTH AT R520.
MF31074 V5R2M0 OSP-DB PERFORMANCE PROBLEM FOR QUERY WITH LARGE NUMBER OF AN
MF30980 V5R2M0 LIC-DB Read only query for estimate key range is from
MF30960 V5R2M0 OSP TEMP STORAGE GROWTH AT R520.
MF30908 V5R2M0 OSP-DB SQE - POOR PERFORMANCE WITH SIMPLE SQL STATEMENT.
MF30917 V5R2M0 OSP TEMP STORAGE GROWTH AT R520.
MF30872 V5R2M0 LIC-DB-PERFM SQL Peroformance
MF30871 V5R2M0 OSP-DB SQL PERFORMANCE ORDER BY
MF30844 V5R2M0 LIC-DB-PERFM SQL Performance
MF30785 V5R2M0 LIC-DB-MSGMCH3203-F/DbstMdQilPredicateRepConcrete Assert
MF30734 V5R2M0 LIC-DB-MSGMCH3203-F/DbstMdDnfInfo Assert to QQQOOOINV
MF30669 V5R2M0 LIC-DB-PERFM SQL Performance
MF30645 V5R2M0 OSP-DB-MSGLNR7093 SQL GENERATED CODE WITH SQE DOES NOT CREAT
MF30619 V5R2M0 LIC-DB-PERFM SQL Performance
Last Modified On:
No, open a new Support Case