Improving OPNQRYF performance

In an interactive session, enter STRDBG. Then run a CL containing the OPNQRYF that needs to be improved. What will happen is, the query optimizer will place messages into the job log that explain what actions it took to optimize the query. Also, it makes permanent performance recommendations.

To view the messages generated after running the OPNQRYF with debug turned on, enter:

 WRKJOB
option 10 (display job log)
press PF10 (view detailed messages)
press PF18  (bottom - page up if needed)


Below is a sample:

For this OPNQRYF:

OPNQRYF    FILE((LIBRARY1/FILE1)) QRYSLT('CDSRSF = "Y"')                 


I received the following messages in the job log:

 All access paths were considered for file FILE1
 Additional access path reason codes were used.  
 Arrival sequence access was used for file FILE1
 Access path suggestion for file FDMCDP.    

Place your cursor on the ACCESS PATH SUGGESTION... message above and press PF1. It will give a detailed explanation as to permanent performance improvements, which can be made. Pressing PF1 on the messages above this one will provide you with a detailed explanation of what the query optimizer did. (see message output below)

Some additional hints for improving performance:

Determine if using the CHGQRYA command (in particular the DEGREE parameter) will have an impact on your query. Shops with SMP installed can see great improvements by adjusting this parameter.

Determine if changing the ALWCPYDTA parameter of the OPNQRYF command to see if changing the value will have an impact on your query.

Message ID . . . . . . : CPI432F Severity . . . . . . . : 00 Message type . . . . . : Information Date sent . . . . . . : 06/25/01 Time sent . . . . . . :

Message . . . . : Access path suggestion for file FILE1. Cause . . . . . : To improve performance the query optimizer is suggesting a permanent access path be built with the key fields it is recommending. The access path will access records from member FILE1 of file FILE1 in library LIBRARY1. In the list of key fields that follow, the query optimizer is recommending the first 1 key fields as primary key fields. The remaining key fields are considered secondary key fields and are listed in order of expected selectivity based on this query. Primary key fields are fields that significantly reduce the number of keys selected based on the corresponding selection predicate. Secondary key fields are fields that may or may not significantly reduce the number of keys selected. It is up to the user to determine the true selectivity of secondary key fields and to determine whether those key fields should be used when creating the access path. The query optimizer is able to perform key positioning over any combination of the primary key fields, plus one additional secondary key field. Therefore it is important that the first secondary key field be the most selective secondary key field. The query optimizer will use key selection with any remaining secondary key fields. While key selection is not as fast as key positioning it can still reduce the number of keys selected. Hence, secondary key fields that are fairly selective should be included. When building the access path all primary key fields should be specified first followed by the secondary key fields which are prioritized by selectivity. The following list contains the suggested primary and secondary key fields: CDSRSF. If file FILE1 in library LIBRARY1 is a logical file then the access path should be built over member FILE1 of physical file FILE1 in library LIBRARY1. Recovery . . . : If this query is run frequently, you may want to create the suggested access path for performance reasons. It is possible that the query optimizer will choose not to use the access path just created. For more information, refer to the DB2 for AS/400 SQL Programming book, SC41-5611, or the DB2 for AS/400 SQL Reference book, SC41-5612.