Do you ever see the message, “Unable to retrieve query options file” in an AS/400 job log? The Query options file allows you to set defaults for how your SQL queries process on the AS/400, and it also allows you to set the types of messages you see in your job log. In this article, I will show you a technique for setting the query options without interfering with other programs on your system. These techniques can give you a greater degree of control over the implementation and optimization of your queries and better information from the AS/400 job log with which to analyze performance problems. In addition, these settings can be used to trim the number of open data paths that your ODBC, JDBC and OLE DB programs keep open during operation. The information also applies to controlling OPNQRYF operations and Query/400 processing.

What Is a QAQQINI?

Starting with V4R4, all of the various settings that can affect the query optimizer were placed into the QAQQINI file in the QSYS library. Before that, you had to use CHGQRYA, setting data areas like QQQ-OPTIONS or setting system values like query degree. The QAQQINI file is a physical file with attached triggers that controls how queries are processed on the AS/400. The file is quite an ingenious device; you make entries or updates to the file that describe aspects of how you want your queries to process, and the trigger processes the changes made to the file and propagates the changes to your job or other jobs that are using that query options file. Note that I said “your job or other jobs.” A query options file can be placed in a library, and by issuing a Change Query Attributes command (CHGQRYA), you can tell your job to use that query options file. By default, jobs look for a QAQQINI file in the QUSRSYS library—but you can put a QAQQINI file in any library, even QTEMP.

Using QAQQINI

The copy of the QAQQINI file in the QSYS library should be regarded as a template, and you should not make changes to the entries in that file. You need to make a copy of the file and place it in a user library; then, you can make changes to your heart’s content. Since the QAQQINI file has triggers attached to it, you need to use the Create Duplicate Object (CRTDUPOBJ) command to make a copy. My favorite technique when debugging and playing with performance tuning is to make a copy of the QAQQINI file and place it into

the QTEMP library. In that manner, I am assured that the changes that I am making to the QAQQINI file will not in any way affect other users or jobs running on my system. Here is the syntax for creating a copy of the QAQQINI file into the QTEMP library:

CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(QTEMP) DATA(*YES)

The command above instructs the AS/400 to create a duplicate copy, including data, of the file QAQQINI in the library QSYS and to place that duplicate into the QTEMP library. In order to make your job use the copy of the QAQQINI, you need to issue the Change Query Attributes command (CHGQRYA) to inform your job that it should be using this file. The syntax for the CHGQRYA command is:

CHGQRYA QRYOPTLIB(QTEMP)

The parameter QRYOPTLIB tells the AS/400 which library it should look in to find the query options file that will control how this job is processed.

QAQQINI Anatomy

The QAQQINI file contains three columns, QQPARM, QQVAL, and QQTEXT. The QQPARM field contains the name of the optimizer parameter. The QQVAL field contains the current setting for the parameter named in QQPARM. QQTEXT is a description of the parameter. So, if you want to set the query time limit for queries to 55 seconds, you could issue the following update statement against the QAQQINI file that your job is using:

UPDATE QTEMP.QAQQINI
SET QQVAL=’55’
WHERE QQPARM=’QUERY_TIME_LIMIT’;

What else can you change with the QAQQINI? Well, there are several options you can set, but the one that I use most is the ability to tell the AS/400 to send any query optimizer messages to the job log. This is almost like using the Start Debug command on your job, but your job is not in debug mode. The advantage of using a setting in QAQQINI over throwing the job into debug mode is twofold: You do not have the additional overhead of debug mode, and you do not see any messages in the job log other than messages appropriate to query optimization and performance. I find it much easier to read this way. To turn on query optimizer messages, issue the following update command from SQL:

UPDATE QTEMP.QAQQINI
SET QQVAL=’*YES’ WHERE
QQPARM=’MESSAGES_DEBUG’

Another thing you can control via QAQQINI is whether the AS/400 is allowed to change the order of joins when optimizing a query. This is accomplished by setting the FORCE_JOIN_ORDER parameter value to *DEFAULT, *SQL, *NO, or *YES. By default, the AS/400 tries to choose what it perceives to be the best order for processing files referenced in an SQL statement. You can override this behavior and cause the AS/400 to force the processing of files in the order that they appear in the join clause or from clause by setting this parameter value to *YES. This can be particularly helpful when you are attempting to get the optimizer to choose a specific access path that it just doesn’t seem to want to use. Setting the parameter to *SQL will cause the AS/400 to force the join order only if the SQL statements use the join syntax. Note that this parameter does not ship in the default QAQQINI, you will need to insert a record into QAQQINI before you can adjust the parameter. The following statement inserts a record into QAQQINI telling the optimizer to

force the join order on all SQL statements to the order the statements are referenced in the join or from clause.

INSERT INTO QTEMP.QAQQINI (QQPARM, QQVAL) VALUES (‘FORCE_JOIN_ORDER’,’*YES’)

Another interesting parameter is OPTIMIZATION_GOAL, which allows you to set the goal that the query optimizer is trying to achieve. By default, the query optimizer will attempt to optimize a query for *FIRSTIO if the query is dynamic SQL or for *ALLIO if the query is static SQL. *FIRSTIO implies that the AS/400 will attempt to retrieve and send the user rows that match the query selection requirement as soon as possible, whereas *ALLIO implies that the AS/400 should attempt to run the entire query to its conclusion in the shortest amount of time. Note that if you have OPTIMIZE FOR n ROWS in your query, the query optimizer will ignore this setting. The OPTIMIZATION_GOAL parameter is not in the QAQQINI file that is shipped with V4R4, so you will need to insert a record into the QAQQINI file in order to be able to set this property. Once you have inserted a record, you may use an update statement to change the value of the parameter. The following statement inserts an option record for this parameter and sets its value to *FIRSTIO:

INSERT INTO QTEMP.QAQQINI (QQPARM, QQVAL) VALUES (‘OPTIMIZATION_GOAL’,’*FIRSTIO’)

A really scary option is OPTIMIZE_ STATISTIC_LIMITATION, which controls how deeply the AS/400 will look at logical files that are attached to the physical files your query references. The optimizer will look at the actual key loading in logical files to determine how useful they will be in the resolution of a query. This key loading of an index is called its selectivity. While the AS/400 has some top-level statistics about an index available in the file system, those statistics do not tell the optimizer exactly how the expressions used in your query match up against the actual key values stored in the logical file. When the optimizer is trying to formulate an execution plan, it can elect to open the logical files that it thinks may help the query execute quickly and examine the key values in the logical file. The query optimizer automatically decides how much time that it will spend doing this process, but the OPTIMIZE_STATISTIC_LIMITATION value will let you override the built-in time limits and set your own metrics for the optimizer to use.

The valid settings for OPTIMIZE_ STATISTIC_LIMITATION are *DEFAULT, *NO, *PERCENT n, where n is an integer, and *MAX_NUMBER_OF_RECORDS_ ALLOWED n, where again n is an integer. The interesting options are *PERCENTAGE and *NO. *NO tells the optimizer to not look at records in the index at all and just go with the default metadata that is stored with the index. This can be good or bad, as the optimizer can make some stupid decisions if it has incomplete information. However, this option might help you if the optimizer is taking a stupid pill before optimizing your query, so it is something to keep in your arsenal of weapons. The *PERCENTAGE option is useful, as you can instruct the optimizer on the maximum number of records to read when looking at index statistics. This can be useful when the optimizer is evaluating a large number of similar indexes and trying to determine which will be most suitable for the query. By setting the percentage high, you are instructing the optimizer to read no more than that percentage of entries in the index before finishing its statistical observations. This can help save time in queries against very large tables. The final option, *MAX_NUMBER_OF_RECORDS_ALLOWED, is like setting a threshold for the *NO option. This tells the optimizer not to look at index entries if there are more than N records in the base table. Again, if your queries are spending an inordinate amount of time in optimization due to a large number of logical files created against your physical files, this might be something to play with. The following statement sets the maximum percentage of a logical to read to 45 percent:

UPDATE QTEMP.QAQQINI
SET QQVAL=’*PERCENTAGE 45’ WHERE QQPARM=’OPTIMIZE_STATISTIC_LIMITATION ‘

Lions and Tigers and ODPs...

If you ever look at the open files of a QZDASOINIT job, you might be surprised at how many open data paths (ODPs) that the AS/400 will keep around. ODPs are a good thing, as once the AS/400 has opened the data path, the next time it needs it the machine will just reuse the open one (if possible). The problem with ODPs is that they do take a little RAM, and a large number of ODPs can have a negative impact on performance. The AS/400, by default, will never close these things down, at least not until your job ends. However, never fear! The QAQQINI is coming to your rescue. You can set the parameter OPEN_CURSOR_THRESHOLD to a number. This number is the maximum number of cursors associated with reusable ODPs that the AS/400 should maintain. This parameter works hand-in-hand with the OPEN_CURSOR_CLOSE_COUNT parameter, which specifies how many open cursors to close once the threshold value is met. Note that the AS/400 is not actually closing open cursors; it just closes cursors that you already closed in your application. In the interest of efficiency the AS/400 is retaining them in a sort-of-open state to maintain the reusability of the ODP associated with cursor.

The OPEN_CURSOR_CLOSE_COUNT and OPEN_CURSOR_THRESHOLD are not supplied in the QAQQINI file available in QSYS, so you will need to insert the values into your copy of QSYS before you can begin updating and changing the values of these parameters. The following statements will tell the AS/400 not to keep more than 30 ODPs and to release the oldest four when the threshold is reached:

INSERT INTO QTEMP.QAQQINI (QQPARM, QQVAL) VALUES (‘OPEN_CURSOR_THRESHOLD’,’30’);
INSERT INTO QTEMP.QAQQINI (QQPARM, QQVAL) VALUES (‘OPEN_CURSOR_CLOSE_COUNT’,’4’)

QAQQINI in Production and Testing

So far, I’ve told you about QAQQINI and some of its wonderful settings, but how do you use it in a production and testing environment? Figure 1 shows a simple piece of Visual Basic code that uses ActiveX Data Object to copy a QAQQINI to QTEMP and set some options. Of course, since the code uses ADO, you will need to ensure that ADO is referenced in your VB project. After declaring the objects to be used in the program, the first line connects to the AS/400 via an ODBC data source called MY400 with the appropriate user ID and password. Next, the program sets the ActiveConnection property of the command object CMD1 to the connection Con1. At this point, the program is ready to rock and roll.

The first statement that the program will execute is a call to the QSYS.QCMDEXC API. This API allows you to execute any command on the AS/400 as if it is a stored procedure. The API function takes two arguments: a string that represents the command you want to run, and a decimal (15,5) number that informs the AS/400 of the length of the supplied command string. In this case, I am passing the string:

CRTDUPOBJ OBJ(QAQQINI) +
FROMLIB(QSYS) OBJTYPE(*FILE) +
TOLIB(QTEMP) DATA(*YES)

This is the command that will create a new QAQQINI object in the QTEMP library. In Figure 1, note that I am passing the number 75 (the length of the command string) as a decimal 15,5 in the second argument to the QCMDEXC API. Once the call to CMD.Execute is complete, there is a brand-spanking new copy of the QAQQINI object in the QTEMP library, and I am ready to begin setting some options. The next statement that my program executes is to change the value of the MESSAGE_DEBUG entry in the file to *YES. Remember, this causes all of the optimizer messages to be written to the AS/400 job

log. Finally, after I have finished setting the options that I want, my program again uses the QCMDEXC API to execute a call to the CHGQRYA command. This call causes my job to look for the QAQQINI file in the QTEMP library.

Now that my connection is in a happy state and my options are set the way I want them, I am free to execute a bunch of SQL statements and look at my optimizer messages to see how the optimizer is choosing to implement those statements. Or, I could continue to set other options in QAQQINI that control how the SQL in my connection will be processed.

Anyway...

QAQQINI is a much better way to control query processing options on your AS/400. It simplifies the management of performance settings and places most of the values that you want to access in one neat and accessible place. In addition, by using the technique of copying the QAQQINI to the QTEMP library, you can be assured that any changes you make for testing purposes will not interfere with other users. This technique will work with Java, VB or RPG, COBOL, and C on the AS/400. Play with it, learn it, and see what interesting and gross things you can make your AS/400 query optimizer do!

REFERENCES AND RELATED MATERIALS

• AS/400 Database Performance and Query Optimization (Available at http://as400bks.rochester.ibm.com/pubs/html/as400/v4r5/ic2924/info/db2/rzajqmst02.htm)

• Custom Query Options Builder Web site: www.as400.ibm.com/developer/bi/tuner.html

xDim Con1 as new ADODB.Connection
Dim Cmd1 as new adodb.Command
Dim RS as new ADODB.Recordset

Con1.Open “DSN=MY400;UID=HOWARD;PWD=SECRET;”
Cmd1.ActiveConnection = Con1

‘Make a copy of QAQQINI to QTEMP
Stmt = “call QSYS.QCMDEXC(‘CRTDUPOBJ OBJ(QAQQINI)” & _

“ FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(QTEMP)” & _
“ DATA(*YES)’,0000000075.00000)”
Cmd1.CommandText = Stmt
Cmd1.Execute

‘Turn On Debug Messages
Stmt = “UPDATE QTEMP.QAQQINI SET QQVAL=’*YES’” & _
“ WHERE QQPARM=’MESSAGES_DEBUG’”
Cmd1.CommandText = Stmt
Cmd1.Execute

‘Tell the job to use the QAQQINI in QTEMP
STMT=”CALL QSYS.QCMDEXC(‘CHGQRYA QRYOPTLIB(QTEMP)’” & _

“,0000000024.00000)”

Cmd1.CommandText = Stmt
Cmd1.Execute

Figure 1: This Visual Basic program shows how you can create a QAQQINI file in QTEMP and instruct your job to use it.