Wed, Oct
2 New Articles

My Own Private QAQQINI

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


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:


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:


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.


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:


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:


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.


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:


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:


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:


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:


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.


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!


• 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

Cmd1.ActiveConnection = Con1

‘Make a copy of QAQQINI to QTEMP

“ DATA(*YES)’,0000000075.00000)”
Cmd1.CommandText = Stmt

‘Turn On Debug Messages
Cmd1.CommandText = Stmt

‘Tell the job to use the QAQQINI in QTEMP


Cmd1.CommandText = Stmt

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



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: