The overhead that a monitor can place on disk and I/O subsystems is no longer the problem it used to be.

By Kent Milligan

Starting a Database Monitor on your server is quite simple, whether you're using the STRDBMON CL command or the System i Navigator graphical interface. The challenging part of a Database Monitor is minimizing the overhead that a monitor can place on disk and I/O subsystems while it's writing the collected data.

To minimize this overhead, filters were introduced in V5R4 to reduce the amount of data that DB2 for i collects with a Database Monitor. Less data being collected means less data being written to disk by DB2. The filters that are available on V5R4 using the graphical SQL Performance Monitor interface in iSeries Navigator are shown in Figure 1. Let's examine a couple of the filters to make sure the concept is clear. The job name filter provides the ability to have monitor data collected only for a specified job (e.g., QZDASOINIT). The "Queries that access these tables" filter results in the monitor only collecting data for SQL statements that reference a specific object (e.g., MYLIB/BIGTABLE).

Figure 1: In V6R1, System i Navigator offers special additional filters. (Click images to enlarge.)

Additional filters for TCP/IP port number and query governor limits were also added in V6R1 to both the STRDBMON CL command and the System i Navigator interface.

IBM has also recently delivered PTFs to provide additional filter capabilities to systems still running at i5/OS V5R4. These additional filters can be loaded on your server by applying PTF SI32059 or by loading level #17 of the V5R4 Database Group PTF. Since these new filters were delivered outside of a normal IBM release, the filters are available only through the STRDBMON CL command interface. In addition, the off-release delivery vehicle prevented IBM from adding new command parameters; instead, all of the new filters are passed in on the COMMENT parameter.

Two of the new filters delivered with the recent V5R4 PTFs enable customers to more easily determine if they are getting maximum performance benefit from the SQL Query Engine (SQE). IBM delivered the first phase of the SQE way back in V5R2 to improve performance of complex SQL statements and queries (see the Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS Redbook for more details). With each new release, IBM increases the amount of SQL requests that can be processed by SQE. More SQL requests being processed by SQE usually means better performance for applications and end users. Those requests that cannot be routed to SQE are processed by the Classic Query Engine (CQE). Since CQE has a smaller number of performance algorithms and techniques in its toolbox, there's a greater opportunity for slow-running queries, especially for complex SQL requests.

As a result, many customers who have installed a new IBM i release want to know if they have any SQL requests that are still being executed by CQE. That's where the new filters come into play. The Database Monitor output table has always included a field that flags whether an SQL statement was processed by SQE or CQE. Performing this SQE usage analysis, however, required that monitor data be collected for all SQL statements in a workload. The WANT_CQE_ONLY filter simplifies this process by collecting monitor data only for those statements processed by CQE.

As an example, let's take an application that executes 1,000 SQL statements and 950 of the 1,000 SQL statements are processed by the SQL Query Engine. An unfiltered database monitor collection would contain monitor records for all 1,000 SQL statements that are executed by the application. An analyst would then have to sift through the monitor records to find only those statements processed by CQE. In contrast, the WANT_CQE_ONLY filter results in monitor data being collected for only the 50 SQL statements processed by CQE. A database monitor collection will have much lower overhead and disk space requirements when monitor data is being collected for 50 SQL statements instead of 1,000 statements.

Here's an example of how easy it is to specify the new WANT_CQE_ONLY filter:

STRDBMON OUTFILE(MYLIB/APP1) COMMENT('WANT_CQE_ONLY')

As mentioned earlier, there's no CQE command parameter. Instead, the WANT_CQE_ONLY filter is specified as a string value on the COMMENT parameter. If you want the database monitor collection limited to SQL processed by SQE, the filter name is WANT_SQE_ONLY. With the CQE filter specified in this example, the monitor output table (MYLIB/APP1) will contain only SQL statements processed by CQE. The following query can be quickly run against the monitor output to identify the statements being processed by CQE.

SELECT qq1000 "Statement Text" FROM mylib/app1 WHERE qqrid=1000

For my testing, the monitor analysis resulted in the SQL statements being processed by CQE as shown in Figure 2.

Figure 2: In this example, the monitor analysis resulted in CQE processing two SQL statements.

The first SELECT statement is processed by CQE because the FROM clause references a logical file object. The second SELECT statement is processed by CQE because of the reference to the Upper function. With DB2 for i 6.1, SQE supports both the Upper and Lower function; however, SQE still does not support logical file references on the FROM clause.

You must be aware that if a database monitor is not started at the beginning of a job or connection, the collected monitor data may not include the data necessary to determine if the statement was processed by CQE or SQE. As a result, a database monitor collection that is supposed to include all those statements processed by CQE may not include statements that were executed by CQE.

Hopefully, you can see how these new filters can reduce the overhead of the Database Monitor running on V5R4 systems. IBM will eventually formalize these filters as command parameters and options on the graphical System i Navigator interface in future IBM i releases. For more information on the Database Monitor tool and analysis, please reference the IBM Redbook OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4.