Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS

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)