Start Database Monitor (STRDBMON) command

The Start Database Monitor (STRDBMON) command starts the collection of database performance statistics for a specified job, for all jobs on the system or for a selected set of jobs. The statistics are placed in a user-specified database table and member. If the table or member do not exist, one is created based on the QAQQDBMN table in library QSYS, with the public authority for the file the same as the create authority specified for the library in which the file is created. If the table and member do exist, the record format of the specified table is verified to ensure it is the same.

For each monitor started using the STRDBMON command, the system generates a monitor ID that can be used to uniquely identify each individual monitor. The monitor ID can be used on the ENDDBMON command to uniquely identify which monitor is to be ended. The monitor ID is returned in the informational message CPI436A which is generated for each occurrence of the STRDBMON command. The monitor ID can also be found in either the column QQC101 of the QQQ3018 database monitor record or in the QSYS2.DATABASE_MONITOR_INFO catalog.

Informally there are two types of monitors. A private monitor is a monitor over one, specific job (or the current job). Only one (1) monitor can be started on a specific job at a time. For example, STRDBMON JOB(*) followed by another STRDBMON JOB(*) within the same job is not allowed. A public monitor is a monitor which collects data across multiple jobs. There can be a maximum of 10 public monitors active at any one time. For example, STRDBMON JOB(*ALL) followed by another STRDBMON JOB(*ALL) is allowed providing the maximum number of public monitors does not exceed 10. You could have 10 public monitors and 1 private monitor active at the same time for any specific job.

If multiple monitors specify the same output file, only one copy of the database statistic records is written to the file for each job. For example, STRDBMON OUTFILE(LIB/TABLE1) JOB(*) and STRDBMON OUTFILE(LIB/TABLE1) JOB(*ALL) target the same output file. For the current job, there are not two copies of the database statistic records–one copy for the private monitor and one copy for the public monitor. There is only one copy of the database statistic records.

If the monitor is started on all jobs (a public monitor), any jobs waiting on queues or started during the monitoring period are included in the monitor data. If the monitor is started on a specific job (a private monitor) that job must be active in the system when the command is issued. Each job in the system can be monitored concurrently by one private monitor and a maximum of 10 public monitors.

The STRDBMON command allows you to collect statistic records for a specific set or subset of the queries running on any job. This filtering can be performed over the job name, user profile, query table names, query estimated run time, TCP/IP address, or any combination of these filters. Specifying a STRDBMON filter helps minimize the number of statistic records captured for any monitor.

Example 1: Starting Public Monitoring

  STRDBMON   OUTFILE(QGPL/FILE1)  OUTMBR(MEMBER1 *ADD)
   JOB(*ALL)  FRCRCD(10))

This command starts database monitoring for all jobs on the system. The performance statistics are added to the member named MEMBER1 in the file named FILE1 in the QGPL library. 10 records are held before being written to the file.

Example 2: Starting Private Monitoring

  STRDBMON   OUTFILE(*LIBL/FILE3)  OUTMBR(MEMBER2)
   JOB(134543/QPGMR/DSP01)  FRCRCD(20)

Example 3: Starting Private Monitoring to a File in a Library in an Independent ASP

 STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(134543/QPGMR/DSP01)

This command starts database monitoring for job number 134543. The job name is DSP01 and was started by the user named QPGMR. The performance statistics are added to the member name DBMONFILE (since OUTMBR was not specified) in the file named DBMONFILE in the library named LIB41. This library could exist in more than one independent auxiliary storage pool (ASP); the library in the name space of the originator's job is always used.

Example 4: Starting Public Monitoring For All Jobs That Begin With 'QZDA

  STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL/*ALL/QZDA*)

This command starts database monitoring for all jobs that whose job name begins with 'QZDA'. The performance statistics (monitor records) are added to member DBMONFILE (since OUTMBR was not specified) in file DBMONFILE in library LIB41. This library could exist in more than one independent auxiliary storage pool (ASP); the library in the name space of the originator's job is always used.

Example 5: Starting Public Monitoring and Filtering SQL Statements That Run Over 10 Seconds

  STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)  RUNTHLD(10)

This command starts database monitoring for all jobs. Monitor records are created only for those SQL statements whose estimated run time meets or exceeds 10 seconds.

Example 6: Starting Public Monitoring and Filtering SQL Statements That Have an Estimated Temporary Storage Over 200 MB

  STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)  STGTHLD(200)

This command starts database monitoring for all jobs. Monitor records are created only for those SQL statements whose estimated temporary storage meets or exceeds 200 MB.

Example 7: Starting Private Monitoring and Filtering Over a Specific File

  STRDBMON   OUTFILE(LIB41/DBMONFILE) JOB(*)
   FTRFILE(LIB41/TABLE1)

This command starts database monitoring for the current job. Monitor records are created only for those SQL statements that use file LIB41/TABLE1.

Example 8: Starting Private Monitoring for the Current User

  STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*)  FTRUSER(*CURRENT)

This command starts database monitoring for the current job. Monitor records are created only for those SQL statements that are executed by the current user.

Example 9: Starting Public Monitoring For Jobs Beginning With 'QZDA' and Filtering Over Run Time and File

  STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL/*ALL/QZDA*)
   RUNTHLD(10)  FTRUSER(DEVLPR1)  FTRFILE(LIB41/TTT*)

This command starts database monitoring for all jobs whose job name begins with 'QZDA'. Monitor records are created only for those SQL statements that meet all the following conditions:

The estimated run time, as calculated by the query optimizer, meets, or exceeds 10 seconds
Was executed by user 'DEVLPR1'.
Use any file whose name begins with 'TTT' and resides in library LIB41.

Example 10: Starting Public Monitoring and Filtering SQL Statements That Have Internet Address '9.10.111.77'.

  STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)
   FTRINTNETA('9.10.111.77')

This command starts database monitoring for all jobs. Monitor records are created only for TCP/IP database server jobs that are using the client IP version 4 address of '9.10.111.77'.

Example 11: Starting Public Monitoring and Filtering SQL Statements That Have a Port Number of 8471

  STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)  FTRLCLPORT(8471)

This command starts database monitoring for all jobs. Monitor records are created only for TCP/IP database server jobs that are using the local port number 8471.

Example 12: Starting Public Monitoring Based on Feedback from the Query Governor

  CHGSYSVAL QQRYTIMLMT(200)
   STRDBMON   OUTFILE(LIB41/DBMONFILE) JOB(*ALL) FTRQRYGOVR(*COND)

This commands starts database monitoring for all jobs whose estimated run time is expected to exceed 200 seconds, based on the response to the query governor. In this example, data is collected only if the query is canceled or a return code of 2 is returned by a query governor exit program. The query can be canceled by a user response to the inquiry message CPA4259, issued because the query exceeded the query governor limits. It can also be canceled by the program logic inside the registered query governor exit program.

Example 13: Collecting database monitor for Interactive SQL use

STRDBMON OUTFILE(QGPL/STRSQLMON1) OUTMBR(*FIRST *REPLACE)  
   JOB(*ALL/*ALL/*ALL) TYPE(*DETAIL)                       
            FTRCLTPGM(STRSQL)

This command uses the database monitor pre-filter by Client Special Register Program ID to collect monitor records for all the SQL statements executed by Interactive SQL (STRSQL command) usage.

Example 14: Starting Public Monitoring and Filtering SQL Statements Run From IBM i Access Client Solutions Run SQL Scripts

STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)                  
               FTRCLTAPP('IBM i Access Client Solutions - RUN SQL Scripts')

This command starts database monitoring for all jobs. Monitor records are created only for those statements where the client special register CLIENT APPLNAME is IBM® i Access Client Solutions - RUN SQL Scripts.

Example 15: Starting Public Monitoring and Filtering SQL Statements Run From IBM System i® Access for Windows Run SQL Scripts

STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)                  
               FTRCLTPGM('cwbunnav.exe')

This command starts database monitoring for all jobs. Monitor records are created only for those statements where the client special register CLIENT_PROGRAMID is cwbunnav.exe.

Example 16: Starting Public Monitoring and Filtering for the client user dbmusr1

 STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)                  
               FTRCLTUSR(dbmusr1)

This command starts database monitoring for all jobs. Monitor records are created only for those statements where the client special register CLIENT_USERID is dbmusr1.

Example 17: Starting Public Monitoring and Filtering SQL Statements Using FTRUSER

STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)                  
               FTRUSER((STC* *NE) (S*) (TS*) (TSA* *NE))

This command starts database monitoring for all jobs. Monitor records are created only for those statements where the user profile is logically equivalent to: User profile NOT LIKE 'STC%' AND user profile NOT LIKE 'TSA%' AND (user profile LIKE 'S%' OR user profile LIKE 'TS%')