Verify the performance of SQL applications with CL commands

You can verify the performance of an SQL application by using the following commands:

CHGQRYA

You can use the Change Query Attribute (CHGQRYA) command to prevent users from running long queries. Set the Query Time Limit (QRYTIMLMT) parameter to prevent a query from starting whose estimated number of elapsed seconds is longer than the specified number of seconds. This stops the building of temporary indexes or other resource intensive query operations.

Also, a query which is canceled because of the query time limit creates performance information messages in the job log, even if the job is not in debug. For information restricting query runtime using this command, see "Control long-running queries with the DB2 UDB for AS/400 Predictive Query Governor".

DSPJOB

You can use the Display Job (DSPJOB) command with the OPTION(*OPNF) parameter to show the indexes and tables being used by an application that is running in a job.

You can also use DSPJOB with the OPTION(*JOBLCK) parameter to analyze object and row lock contention. It displays the objects and rows that are locked and the name of the job holding the lock.

Specify the OPTION(*CMTCTL) parameter on the DSPJOB command to show the isolation level that the program is running, the number of rows being locked during a transaction, and the pending DDL functions. The isolation level displayed is the default isolation level. The actual isolation level, used for any SQL program, is specified on the COMMIT parameter of the CRTSQLxxx command.

PRTSQLINF

The Print SQL Information (PRTSQLINF) command lets you print information about the embedded SQL statements in a program, SQL package, or service program. The information includes the SQL statements, the access plans used during the running of the statement, and a list of the command parameters used to precompile the source member for the object. For more information on printing information about SQL Statements, see the PRTSQLINF section in Gather information about embedded SQL statements with the PRTSQLINF command.

STRDBMON

You can use the Start Database Monitor (STRDBMON) command to capture to a file information about every SQL statement that runs. See Gather statistics about your queries with the database monitor for more information.

TRCJOB

You can use the Trace Job (TRCJOB) command to capture a trace of the program modules that are being run for an SQL application. The trace job output lists the indexes and tables that are used when the initial database open processing occurs for each SQL statement. Also, the processing unit and page resource usage for running each SQL statement can be determined.