Verifying the performance of SQL applications

The commands that can help you verify performance:

Display Job (DSPJOB)

You can use the Display Job (DSPJOB) command with the OPTION(*OPNF) parameter to show the indexes and tables used by an application 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, the number of rows 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.

Print SQL Information (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, access plans used, and the command parameters used to precompile the source member.

Start Database Monitor (STRDBMON)

You can use the Start Database Monitor (STRDBMON) command to capture to a file information about every SQL statement that runs.

Change Query Attribute (CHGQRYA)

You can use the Change Query Attribute (CHGQRYA) command to change the query attributes for the query optimizer. Among the attributes that can be changed by this command are the predictive query governor, parallelism, and the query options.

Start Debug (STRDBG)

You can use the Start Debug (STRDBG) command to put a job into debug mode, and optionally add as many as 20 programs, 20 class files, and 20 service programs to debug mode. It also specifies certain attributes of the debugging session. For example, it can specify whether database files in production libraries can be updated while in debug mode.