When you use the STRDBG command, the operation system generates additional information on joblog that can be valuable in analyzing the performance of OPNQRYF commands.
Code
1. Program example:
0001.00 OPNQRYF FILE((MAXPD/FI100U) (MAXPD/FI104U) +
0002.00 (MAXPD/FI114U)) FORMAT(MAXPD/FI100U) +
0003.00 QRYSLT('u100nomlat=u104nomlat & +
0004.00 u104nomlat=u114nomlat') KEYFLD((U100DESPRC))
0005.00 CPYFRMQRYF FROMOPNID(FI100U) TOFILE(SHNTMP/RESULT) +
0006.00 MBROPT(*REPLACE) CRTFILE(*YES)
2. DSPJOBLOG whithout debug
3 > call shntmp/qry1
Member FI100U file FI100U in MAXPD opened.
Physical file RESULT created in library SHNTMP.
Member RESULT added to file RESULT in SHNTMP.
40185 records copied from member FI100U.
3. DSPJOBLOG with STRDBG
3 > strdbg
3 > call shntmp/qry1
Unable to retrieve query options file.
All access paths were considered for file FI100U.
All access paths were considered for file FI114U.
Access path built for file FI100U.
File FI100U processed in join position 1.
File FI104U processed in join position 2.
File FI114U processed in join position 3.
Member FI100U file FI100U in MAXPD opened.
40185 records copied from member FI100U.
4. Use F1 over the messages to analyze command.
Example 1:
Message . . . . : All access paths were considered for file FI100U.
Cause . . . . . : The OS/400 Query optimizer considered all access paths built over member FI100U of file FI100U in library MAXPD.
The list below shows the access paths considered. If file FI100U in
library MAXPD is a logical file then the access paths specified are actually built over member FI100U of physical file FI100U in library MAXPD. Following each access path name in the list is a reason code which explains why the access path was not used. A reason code of 0 indicates that the access path was used to implement the query.
MAXPD/LI100U1 5, MAXPD/LI100U2 5, MAXPD/LI100U3
5. The reason codes and their meanings follow:
1 - Access path was not in a valid state. The system invalidated the access path.
2 - Access path was not in a valid state. The user requested that the
access path be rebuilt.
3 - Access path is a temporary access path (resides in library QTEMP) and was not specified as the file to be queried.
4 - The cost to use this access path, as determined by the optimizer, was higher than the cost associated with the chosen access method.
5 - The keys of the access path did not match the fields specified for the ordering/grouping criteria. For distributed file queries, the access path keys must exactly match the ordering fields if the access path is to be used when ALWCPYDTA(*YES or *NO) is specified.
6 - The keys of the access path did not match the fields specified for the join criteria.
--> In this example the system can't use one of existing logical files (access path). Maybe you can create another logical file or change the sequence in command.
Example 2:
Message ID . . . . . . : CPI4321 Severity . . . . . . . : 00
Message type . . . . . : Information
Date sent . . . . . . : 23/11/01 Time sent . . . . . . : 14:22:00
Message . . . . : Access path built for file FI100U.
Cause . . . . . : A temporary access path was built to access records from member FI100U of file FI100U in library MAXPD for reason code 1. This process took 0 minutes and ,2 seconds. The access path built contains 1188 entries. The access path was built using 0 parallel tasks. A zero for the number of parallel tasks indicates that parallelism was not used. The reason codes and their meanings follow:
1 - Perform specified ordering/grouping criteria.
2 - Perform specified join criteria.
3 - Perform specified record selection to minimize I/O wait time.
The access path was built using the following key fields. The key fields and their corresponding sequence (ASCEND or DESCEND) will be shown:
--> The system had to build a temporary access path. This message has the time it took and the reason.
6. Try it with your queries to improve system performance.
Good Luck!
