TR8 DB2 For i Enhancements, Part 2
December 3, 2014 Michael Sansoterra
In the first part of this series, I introduced several of the DB2 for i enhancements made in IBM i 7.1 Technology refresh 8 (and in IBM i 7.2).
These enhancements were of interest to developers and administrators. Likewise, this second set of enhancements we are about to review will be of interest to developers but may hold even greater interest for those in an administrative role.
This DB2 for i service table function has two new enhancements:
1. The job parameter now accepts the special value ‘*’ to return information about the current job.
2. New column V_SQL_STATEMENT_TEXT is now returned.
-- 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
This first example provides developers with a way to retrieve audit information about the current job. The second example shows how the function can aid administrators to identify a SQL statement that is causing performance issues.
New Columns Added To View QSYS2.USER_INFO
Recall that this relatively new “service” view returns information about user profiles (similar to dumping the output of WRKUSRPRF to an *OUTFILE). Assuming the proper authorization to query the view is in place, this view allows a DB2 query to return users with specific special authorities (like *ALLOBJ), invalid sign-on attempts, recent password changes, etc. This view is useful for providing auditors with real-time information about user profile states.
Some new columns have been added to the view and they’re shown here (and all new columns are NULLABLE).
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:
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.
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:
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.