TR8 DB2 For i Enhancements, Part 2

-- return information about the current job
SELECT * FROM TABLE(QSYS2.GET_JOB_INFO('*')) x

-- Find out what SQL statement Uncle Jed is running 
-- that is killing the system
SELECT V_SQL_STATEMENT_TEXT
FROM TABLE(QSYS2.GET_JOB_INFO('003813/UNCLEJED/QZDASOINIT')) x 

Column Name	System Column	Data Type	Description
USER_OWNER	USER_OWNER	VARCHAR(10)	The user profile that owns this user profile.
USER_CREATOR	CREATOR	VARCHAR(10)	The user profile that created this user profile.
SIZE	SIZE	DECIMAL(15,0)	Size of the user profile, in bytes.
CREATION_ TIMESTAMP	TIMESTAMP	TIMESTAMP	Timestamp of when the user profile was created.
LAST_USED_ TIMESTAMP	LASTUSED	TIMESTAMP	The date the user profile was used last. The time portion of the timestamp will always be 0.
DAYS_USED_ COUNT	DAYSUSED	INTEGER	The number of days the user profile has been used on the system.
LAST_RESET_ TIMESTAMP	LASTRESET	TIMESTAMP	The date when the days used count was last reset to zero. The time portion of the timestamp will always be 0.

Reference: QSYS2.USER_INFO View

Procedures QSYS2.DUMP_PLAN_CACHE_TOPN And QSYS2.EXTRACT_STATEMENTS

If you are responsible for monitoring system performance, then you’ll absolutely love these procedures. The first procedure QSYS2.DUMP_PLAN_CACHE_TOPN dumps the most expensive SQL statements (expensive being defined as the largest cumulative elapsed time) in the active plan cache to a database file of your own choosing:

-- Identify the top 15 most expensive queries and
-- dump them to TRACE/SQLHOGS (i.e. a cache snapshot)
CALL QSYS2.DUMP_PLAN_CACHE_TOPN('TRACE','SQLHOGS',15);

While you can query the output file (TRACE/SQLHOGS) directly, you’ll notice there will probably be more than 15 rows and that it’s difficult to decipher. Its contents are much easier understood by using Visual Explain or a tool such as the EXTRACT_STATEMENTS procedure. EXTRACT_STATEMENTS is used to pull statement info from a “plan cache snapshot” file and put it in an “easy to read” result set:

-- Extract all statements for jobs where the user is MIKE
CALL QSYS2.EXTRACT_STATEMENTS('TRACE', 'SQLHOGS',
ADDITIONAL_PREDICATES=>' AND QQUSER=''MIKE''',
ORDER_BY=>'ORDER BY QQUCNT DESC');

The result set contains quite a few columns.

The documentation for this procedure reveals there are quite a few parameter options. The above example shows that the ADDITIONAL_PREDICATES parameter is passed a string value containing WHERE clause predicates for the purpose of filtering the data in the snapshot.

Here are a few other examples of how to use the procedure (assuming that TRACE/SQLHOGS is the plan cache snapshot):

-- Extract all database server jobs (QZDASOINIT) where 
MIKE is the current user
CALL QSYS2.EXTRACT_STATEMENTS('TRACE', 'SQLHOGS',
ADDITIONAL_PREDICATES=>' AND QQJOB=''QZDASOINIT'' 
AND QVC102=''MIKE''',
ORDER_BY=>'ORDER BY QQUCNT DESC');

-- Return all statements showing "audit" information
CALL QSYS2.EXTRACT_STATEMENTS('TRACE', 'SQLHOGS',
ADDITIONAL_SELECT_COLUMNS=>'*AUDIT',
ORDER_BY=>'ORDER BY QQUCNT DESC');

Of course to use parameters like ORDER_BY, ADDITIONAL_SELECT_COLUMNS and ADDITIONAL_PREDICATES, you have to know what column names to supply. These column names can be identified by querying the plan cache snapshot file directly. Some of the column names are cryptic but in most cases it’s easy to tell what they mean. There’s lots of interesting information available to filter on including:

• QQC183 (host or IP address)
• QQI6 (Statement run time in microseconds)

Additional information about these columns (and their definitions) can be found at this IBM Database Monitor View link. The EXTRACT_STATEMENTS procedure can be used to programmatically pull info from any plan cache snapshot, including those created using System i Navigator.

Of course, be careful when embedding predicates in the criteria of a parameter; all single quotes will need to be doubled up (a.k.a. escaped) so that: QQUSER=’MIKE’ becomes QQUSER=”MIKE” when embedded in the parameter’s value.

Having an easy way to dump the plan cache can help identify the queries that are taking the most time on the system. Further, it can be done with relatively easy programmatically so your system can monitor itself while you have your feet up. Hopefully in the future IBM will tweak it even more so that an admin can select the Top N queries utilizing the highest CPU, I/O, etc.

References: Dump_Plan_Cache_TopN Procedure, QSYS2.EXTRACT_STATEMENTS() procedure

QSYS2.SYSPROGRAMSTMTSTAT And QSYS2.SYSPACKAGESTMTSTAT

These new catalog views contain useful information about the prepared SQL statements that are stored in embedded SQL programs or SQL packages on the IBM i. Check them out as they contain information like:

• Number of times the statement was executed.
• The number of times the access plan was rebuilt.
• The aggregate number of rows returned.
• The number of host variables in each statement.
• The SQL text of each statement.

These views can provide a way to find all stored SQL statements with a certain expression or text, which statements affect the largest number of rows, and which statements are being rebuilt too frequently by DB2.

For example, if you have many embedded SQL programs with the constant ‘DEBTOR’ and you want to find all instances of this constant so you can replace it, run this statement:

SELECT * 
FROM QSYS2.SYSPROGRAMSTMTSTAT 
WHERE STATEMENT_TEXT LIKE '%''DEBTOR''%';

The results will show all prepared statements with the constant and what embedded SQL program they’re found in. Dynamic SQL statements using this constant (run with PREPARE or EXECUTE IMMEDIATE) in an embedded SQL program will not show up in this list.

Likewise, to look through all SQL packages (used by DRDA and ODBC/JDBC/OLE DB/.NET, etc.) for statements containing the constant ‘DEBTOR’, the following query will work:

SELECT * 
FROM QSYS2.SYSPACKAGESTMTSTAT 
WHERE STATEMENT_TEXT LIKE '%''DEBTOR''%';

Keep in mind that packages only store preparable statements and are not guaranteed to contain every statement submitted by, for example, a JDBC client. In my testing, I found that the various numbers (like execution count) returned by these views do persist after an IPL.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORY

TR8 DB2 For i Enhancements, Part 1

---

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot