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

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016