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.

QSYS2.GET_JOB_INFO enhancement

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.

Sample usage:

-- 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.

Reference: QSYS2.GET_JOB_INFO()

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).

Column Name

System Column

Data Type

Description

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

DECIMAL(15,0)

Size of the user profile, in bytes.

CREATION_
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.

TR8 DB2 For i Enhancements, Part 1

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