130 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
and heavy usage periods. You can use the APIs described previously, in conjunction with job
scheduling (for example), to programmatically perform periodic snapshots.
5.1.8 Query options table (QAQQINI)
The QAQQINI query options table provides a facility to dynamically modify or override some
of the system environment values that were used when you ran your queries. Every System i
installation contains a template QAQQINI table that is located in the QSYS library. We
recommend that you
do not modify this default QAQQINI table. Instead, create a copy of this
table in library QUSRSYS. Then you can modify this table to define system-wide default
values for queries without a unique QAQQINI table assigned.
To create a QAQQINI table in QUSRSYS or in other user libraries (for additional variations on
the query environment), use the Create Duplicate Object (CRTDUPOBJ) CL command or
select Edit Copy/Paste using iSeries Navigator. This duplicates the triggers that are in
place to handle changes to the QAQQINI table to maintain its integrity. The following example
shows the CL command to create a copy of the QAQQINI table:
CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(MYLIB) DATA(*YES)
After you create your own copy of the QAQQINI table, you can direct your queries to use this
table by changing your query runtime attributes.
To change your query runtime attributes, in the Run SQL Scripts window in iSeries Navigator,
select Options Change Query Attributes. Next, specify the name of the library that
contains the QAQQINI table that you want to use for running your query, as shown in
Figure 5-40.
Important: The default QSYS/QAQQINI table is used by certain system processes.
Changing this table may affect these processes. Also objects in library QSYS may
automatically be replaced when applying IBM PTFs or when upgrading your operating
system. This can result in user changes to the QSYS/QAQQINI table being reset to the
IBM-supplied default values.
Chapter 5. Monitoring and tuning SQE 131
You can set various parameters in the QAQQINI table that allow you to monitor the
performance of your queries. When you click the Edit Options button (Figure 5-40), you can
gain access to these parameters.
Figure 5-40 Specifying the location of the QAQQINI table
132 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
Figure 5-41 shows an example of some of these parameters in the QAQQINI table. You can
control the amount of detailed information that you want to see about your query execution by
setting the appropriate parameter values (QQVAL), for example:
򐂰 The MESSAGES_DEBUG parameter controls whether debug messages are written into
your job log.
򐂰 The SQL_SUPPRESS_WARNINGS parameter is used to determine if you want to see
SQL warnings when executing your queries.
Figure 5-41 QAQQINI parameters for monitoring query performance
For more information about valid parameter settings for the QAQQINI table, see Informational
APAR II13320. Also refer to (search on) “Creating the QAQQINI query options file” in the
iSeries Information Center at the following address:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp
Tip: You can also direct your SQL query to use a specific QAQQINI table or temporarily
change certain QAQQINI parameters by including the relevant CL command in your SQL
script as shown in the following examples:
CL: chgqrya degree(*optimize)
CL: chgqrya qryoptlib(library_name)

Get Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.