Logo: TUG TORONTO USERS GROUP for Midrange Systems
e -server magazine

November 1995: Volume 11, Number 2

Tips from Jackie

OPNQRYF and SQL Performance Aids

By Jackie Jansen

or anyone interested in response time and performance OPNQRYF has been both a savior and an enemy. You can often influence the length of time a query takes based on the criteria you use and the access paths that exist at the beginning of the query. Little known but excellent tools for developers are the query optimizer messages. These are messages sent to the joblog containing excellent information both on what the query optimizer is doing and what you should do to help it. First you need to place a job in debug mode "STRDBG UPDPROD(*YES)". Remember that you can place a job other than your own in debug mode by first running the STRSRVJOB (Start Service Job) command followed by STRDBG. You then run your normal application that involves either SQL or OPNQRYF statements. Finally you end debug and look at the joblog. The joblog will tell you what access paths were examined and why the optimizer chose the one it did or if it actually built a new access path. Second level text will give you some suggestions on what you can do to optimize your query. This is an excellent tool that is under utilized by most programmers.

V3R1 added two new tools to our tuning kitbag. CHGQRYA and PRTSQLINF. By now many of you have heard about the new Query Governor that comes with V3R1. The query governor lets you stop any query from running that will take longer than a specified number of seconds. For developers this function has some hidden benefits. You specify that a query is not to run if it will take more then 0 seconds. " CHGQRYA QRYTIMLMT(0)". The AS/400 will then give you an error message stating that your query will take x seconds which is longer than your limit. This allows you to evaluate some queries prior to actually running them on a production system.

For SQL programmers V3R1 also incorporates the SQL EXPLAIN function. On the AS/400 this is delivered via the PRTSQLINF command. After compiling a program containing SQL statements, and preferably running it at least once, you can execute the command "PRTSQLINF pgmname". This will give you similar and complimentary information to the debug messages discussed earlier.

Using all these tools gives a programmer powerful tuning aids. As decision support applications become more and more common well performing queries have become essential for AS/400 customers. T < G

Jackie Jansen is an AS/400 Specialist and Consulting SE, providing national technical and marketing support for the AS/400 in Canada. Jackie frequently speaks at AS/400 Technical Conferences and User Group meetings.