IBM Support

How Do I Start an SQL Performance Monitor?

How To


Summary

A Database Engineer (DBE) or IBM i Technical Support Specialist might request an SQL performance monitor to assist with query performance or functional issues.

Environment

Db2 for i

Steps

There are two options available for collecting an SQL performance monitor
  1. Start the monitor from Access Client Solutions (ACS) SQL Performance Center 
  2. Use STRDBMON on the IBM i command line
CAUTION: The monitor can negatively impact system performance if it is allowed to run for long periods of time or allowed to grow in size.  If possible, narrow the scope of the issue and start the monitor shortly before the issue is seen and over specific or generic job names instead of using JOB(*ALL).  For further detail, on the impact of collecting a monitor, see https://www.ibm.com/support/pages/impact-collecting-dbmon-trace-start-database-monitor-strdbmon
Option 1 - IBM i Access Client Solutions
a. Open the SQL Performance Center from IBM i Access Client Solutions
b. From the menu bar, select File -> New -> Performance Monitor
image showing steps a & b, open SQL performance center, from the menu bar select file, new, Performance Monitor
c. Name the monitor and choose filtering options, then click Finish
name your monitor and choose filtering options
d. Monitor the size of your active DBMON by querying the DATABASE_MONITOR_INFO view or right-clicking on the monitor and review the properties. Allowing the monitor to grow beyond 10 - 20GB might cause system-wide performance issues.
             SELECT monitor_id, monitor_library CONCAT '/' CONCAT monitor_file AS dbmon_file, monitor_status, data_size, monitor_job_filter
            FROM QSYS2.DATABASE_MONITOR_INFO
            WHERE monitor_status = 'ACTIVE' ORDER BY data_size DESC
After the monitor reaches about 10GB or after 20 - 60 minutes has lapsed, proceed to step (e) to end the monitor.
image-20220217175423-4
e. Right-click on the monitor to end. 
image-20220217175051-3
Option 2 - IBM i Command Line
a.  On an operating system command line, type the following:  CRTLIB LIB(TSxxxxxx) where xxxxxx is the last six digits of your case number.
b.  Start the database monitor by using either
        STRDBMON OUTFILE(TSxxxxxx/DBMON1) JOB(GenericJobName*) TYPE(*DETAIL) or
        STRDBMON OUTFILE(TSxxxxxx/DBMON1) JOB(SpecificJobName) TYPE(*DETAIL)
c.  Monitor the size of your active DBMON by querying the DATABASE_MONITOR_INFO view, allowing the monitor to grow beyond 10 - 20GB might cause system-wide performance issues.
             SELECT monitor_id, monitor_library CONCAT '/' CONCAT monitor_file AS dbmon_file, monitor_status, data_size, monitor_job_filter
            FROM QSYS2.DATABASE_MONITOR_INFO
            WHERE monitor_status = 'ACTIVE' ORDER BY data_size DESC
d.  After the TSxxxxxx/DBMON1 out file reaches about 10GB or 20 - 60 minutes has elapsed, issue the following command:
        ENDDBMON JOB(GenericJobName*) or ENDDBMON JOB(SpecificJobName)
        If for any reason you are experiencing errors when ending the database monitor, issue the following command:
        ENDDBMON JOB(*ALL/*ALL/*ALL) COMMENT(END_ALL_PUBLIC_MONITORS)
Instructions for sending your data to IBM
a.  CRTSAVF FILE(TSXXXXXX/IBMDATA)
b.  Option 1:  If this option was followed, monitor data is stored in a table that starts with QZG*.  To find the specific name, click the Performance Monitors tab in ACS and the monitor name can be found under the Table column. 
For example, SAVOBJ OBJ(QZG0000833) LIB(QGPL) DEV(*SAVF) SAVF(TSXXXXXX/IBMDATA) DTACPR(*YES)
image showing how to access the table name associated with your sql performance monitor in ACS
c.  Option 2:  If this option was followed, monitor data is stored in the file name you specified on the STRDBMON OUTFILE parameter
For example, SAVOBJ OBJ(DBMON1) LIB(TSXXXXXX) DEV(*SAVF) SAVF(TSXXXXXX/IBMDATA) DTACPR(*YES)                                                                                                                                                    
d.  Upload the IBMDATA save file to your case by using one of the following options

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CI9AAM","label":"Performance->MustGather"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Document Information

Modified date:
18 February 2022

UID

ibm16557122

Page Feedback