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 file and member. If the file or member do not exist, one is created based on the QAQQDBMN file in library QSYS. If the file or member do exist, the record format of the specified file is verified to insure 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 column QQC101 of the QQQ3018 database monitor record.

Restrictions:

1. There are two types of monitors. A private monitor is a monitor over one, specific job (or the current job). A public monitor is a monitor which collects data across multiple jobs. Only one (1) monitor can be started on a specific job at a time (i.e. only one private monitor can be active over any specific job). For example, STRDBMON JOB(*) followed by another STRDBMON JOB(*) within the same job is not allowed. There can be a maximum of ten (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 may have 10 public monitors and 1 private monitor active at the same time for any specific job.
2. If multiple monitors specify the same output file, only one copy of the database statistic records will be written to the specified output file for each job. For example, STRDBMON OUTFILE(LIB/TABLE1) JOB(*) and STRDBMON OUTFILE(LIB/TABLE1) JOB(*ALL) both use the same output file. For the current job, you will not get two copies of the database statistic records, one copy for the private monitor and one copy for the public monitor. You will get only one copy of the database statistic records.
3. QTEMP cannot be specified as the library on the OUTFILE parameter unless JOB(*) was also specified.
4. This command is conditionally threadsafe. For multithreaded jobs, this command is not threadsafe and may fail when the OUTFILE parameter is a distributed file or is a Distributed Data Management (DDM) file.
5. Any public monitor requires the file specified for the OUTFILE parameter to be in a library that resides in the system ASP.
6. When this command is used with a JOB parameter which is generic or applies to other users jobs, you must have job control (*JOBCTL) special authority or be authorized to the SQL Administrator function of IBM i through Application Administration in System i Navigator. The Change Function Usage Information (CHGFCNUSG) command, with a function ID of QIBM_DB_SQLADM, can also be used to change the list of authorized users.

Parameters

Keyword	Description	Choices	Notes
OUTFILE	File to receive output	Qualified object name	Required, Positional 1
	Qualifier 1: File to receive output	Name
	Qualifier 2: Library	Name, *LIBL, *CURLIB
OUTMBR	Output member options	Element list	Optional
	Element 1: Member to receive output	Name, *FIRST
	Element 2: Replace or add records	*REPLACE, *ADD
JOB	Job name	Single values: * Other values: Qualified job name	Optional
	Qualifier 1: Job name	Generic name, name, *ALL
	Qualifier 2: User	Generic name, name, *ALL
	Qualifier 3: Number	000000-999999, *ALL
TYPE	Type of records	*BASIC, *DETAIL, *SUMMARY	Optional
FRCRCD	Force record write	0-32767, *CALC	Optional
INLNBRRCD	Initial number of records	0-2147483646, *NONE	Optional
RUNTHLD	Run time threshold	0-2147483647, *NONE	Optional
STGTHLD	Storage threshold	0-2147483647, *NONE	Optional
INCSYSSQL	Include system SQL	*NO, *YES, *INI	Optional
FTRFILE	Filter by database file	Single values: *NONE Other values (up to 10 repetitions): Qualified object name	Optional
	Qualifier 1: Filter by database file	Generic name, name, *ALL
	Qualifier 2: Library	Generic name, name
FTRUSER	Filter by user profile	Generic name, name, *NONE, *CURRENT	Optional
FTRINTNETA	Filter by internet address	Character value, *NONE, *LOCAL	Optional
FTRLCLPORT	Filter by local port number	0-65535, *NONE	Optional
FTRQRYGOVR	Filter by query governor	*NONE, *ALL, *COND	Optional
FTRCLTACG	Filter by accounting string	Character value, *NONE	Optional
FTRCLTAPP	Filter by application name	Character value, *NONE	Optional
FTRCLTPGM	Filter by program name	Character value, *NONE	Optional
FTRCLTUSR	Filter by client user ID	Character value, *NONE	Optional
FTRCLTWS	Filter by work station	Character value, *NONE	Optional
COMMENT	Comment	Character value, *BLANK	Optional

Top

File to receive output (OUTFILE)

Specifies the file to which the performance statistics are to be written. If the file does not exist, it is created based on model file QAQQDBMN in library QSYS.

This is a required parameter.

Qualifier 1: File to receive output

name

Specify the name of the file.

Qualifier 2: Library

*LIBL

All libraries in the job's library list are searched until the first match is found.

*CURLIB

The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

name

Specify the name of the library to be searched.

Output member options (OUTMBR)

Specifies the name of the database file member that receives the output of the command.

Element 1: Member to receive output

*FIRST

The first member in the file receives the output. If OUTMBR(*FIRST) is specified and the member does not exist, the system creates a member with the name of the file specified for the File to receive output (OUTFILE) parameter. If the member already exists, you have the option to add new records to the end of the existing member or clear the member and then add the new records.

name

Specify the name of the file member that receives the output. If it does not exist, the system creates it.

Element 2: Replace or add records

*REPLACE

The system clears the existing member and adds the new records.

*ADD

The system adds the new records to the end of the existing records.

Job name (JOB)

Specifies the job(s) for which the database monitor is to be started.

Single values

*

The database monitor for the job running the STRDBMON command is to be started.

*ALL

All jobs on the system are monitored, including jobs waiting on job queues.

Qualifier 1: Job name

name

Specify the name of the job whose database monitor is to be started. If no job user name or job number qualifiers are specified, all of the jobs currently in the system are searched for the specified simple job name. If duplicates of the specified job name are found, you need to specify a job user name or job number that uniquely identifies the job to be changed.

generic-name

Specify the generic name of the jobs whose database monitor are to be started. All jobs matching the specified generic name, including jobs waiting on job queues, will be monitored.

Qualifier 2: User

name

Specify the name of the user of the job whose database monitor is to be started.

generic-name

Specify the generic name of the user whose jobs are to be monitored. All jobs matching the specified generic name, including jobs waiting on job queues, will be monitored.

Qualifier 3: Number

000000-999999

Specify the number of the job whose database monitor is to be started.

Type of records (TYPE)

Specifies the type of database records to place in the outfile.

*BASIC

Only the basic database monitor records are collected.

*DETAIL

Both basic and detail database monitor records are collected. The detail database monitor record (QQQ3019) contains a count of the number of synchronous and asynchronous reads and writes to the database, as well as other database counts.

*SUMMARY

Only the basic database monitor records are collected.

Force record write (FRCRCD)

Specifies the number of records to be held in the buffer before forcing the records to be written to the file when running with a private monitor.

*CALC

The system will calculate the number of records to be held in the buffer.

number-of-records

Specify the number of records to be held. Valid values range from 0 through 32767.

Initial number of records (INLNBRRCD)

Specifies the number of records that can be inserted into the monitor file before an automatic extension occurs.

*NONE

A initial number of records was not specified.

number-of-records

Specify the number of records that can be inserted into the monitor file before an automatic extension occurs. Valid values range from 0 to 2147483646.

Run time threshold (RUNTHLD)

Specifies a filtering threshold based on the estimated run time of the SQL statement as calculated by the query optimizer. Monitor records will be created only for those SQL statements whose estimated run time meets or exceeds the specified run time threshold. If the estimated run time of the SQL statement is less than the specified threshold then no monitor records will be created for that SQL statement.

*NONE

A run time threshold is not specified. All monitor records will be created.

runtime-threshold

Specify the run time threshold, in seconds. Monitor records will be created for all SQL statements whose estimated run time meets or exceeds this value.

Storage threshold (STGTHLD)

Specifies a filtering threshold based on the estimated temporary storage usage of the SQL statement. Monitor records will be created only for those SQL statements whose estimated temporary storage meets or exceeds the specified storage threshold. If the estimated temporary storage of the SQL statement is less than the specified threshold then no monitor records will be created for that SQL statement.

*NONE

A storage threshold is not specified. All monitor records will be created.

storage-threshold

Specify the storage threshold, in megabytes. Monitor records will be created for all SQL statements whose estimated temporary storage meets or exceeds this value.

Include system SQL (INCSYSSQL)

Specifies whether or not monitor records will be created for system-generated SQL statements. Monitor records will always be created for user-specified SQL statements. This option determines if monitor records will also be created for SQL statements generated internally by the system.

*NO

No monitor records will be created for system-generated SQL statements. Monitor records will only be created for user-specified SQL statements.

*YES

Monitor records will be created for both user-specified and system-generated SQL statements.

*INI

Monitor records will be generated based on the value of the SQL_DBMON_OUTPUT option in the current INI file. A value of *USER or *DEFAULT creates monitor records for just user-specified SQL statements. A value of *SYSTEM creates monitor records for just system-generated SQL statements. A value of *ALL creates monitor records for both user-specified and system-generated SQL statements. If no INI file exists, then a default value of *NO will be used for the INCSYSSQL option.

Filter by database file (FTRFILE)

Specifies a filter based on the name of the file and library used within the SQL statement. Monitor records will be created only for those SQL statements that use the qualified file. The specified file name can be either the 10-byte short name or the 256-byte long name.

Single values

*NONE

No filtering by file is specified.

Qualifier 1: Data base file

*ALL

Monitor records will be created for any SQL statement that uses any file in the specified library. If none of the files used in the SQL statement come from the specified library, no monitor records will be created for the SQL statement.

name

Monitor records will be created only for those SQL statements that use the specified file. Monitor records will not be created for any SQL statements that do not use the specified file.

generic-name

Monitor records will be created only for those SQL statements that use a file that matches the generic prefix. If none of the files used in the SQL statement match the specified prefix, no monitor records will be created for the SQL statement.

Qualifier 2: Library

name

Monitor records will be created only for those SQL statements that use a file from the specified library. Monitor records will not be created if none of the files used in the SQL statement come from the specified library.

generic-name

Monitor records will be created only for those SQL statements that use a file from a library that matches the generic prefix. If none of the files used in the SQL statement come from the generic library, no monitor records will be created for the SQL statement.

Filter by user profile (FTRUSER)

Specifies a filter based on a user profile name. Monitor records will be created only for those SQL statements that are executed by the specified user. Monitor records will not be created for SQL statements executed by a different user.

*NONE

Filtering by user is not specified.

*CURRENT

Monitor records will be created only for those SQL statements that are executed by the user who is invoking the STRDBMON command. Monitor records will not be created for SQL statements executed by a different user.

user-name

Monitor records will be created only for those SQL statements that are executed by the specified user. Monitor records will not be created for SQL statements executed by a different user.

generic-user-name

Monitor records will be created only for those SQL statements that are executed by a user whose name starts with the specified prefix. Monitor records will not be created for SQL statements executed by a different user.

Filter by internet address (FTRINTNETA)

Specifies filtering based on the client's TCP/IP address or client's TCP/IP host name. Monitor records will be created only for jobs running on behalf of the specified TCP/IP address or host name.

*NONE

Internet address filtering is not specified.

*LOCAL

Monitor records will be created for those jobs that have no TCP/IP client association.

character-value

Monitor records will be created for TCP/IP database server jobs. Jobs named QRWTSRVR and QZDASOINIT are examples of these server jobs.

1. IP version 4 address in dotted decimal form. Specify an internet protocol version 4 address in the form nnn.nnn.nnn.nnn where each nnn is a number in the range 0 through 255.
2. IP version 6 address in colon hexadecimal form. Specify an internet protocol version 6 address in the form xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx where each xxxx is a hex number in the range 0 through FFFF. IP version 6 includes the IPv4-mapped IPv6 address form (for example, ::FFFF:1.2.3.4). For IP version 6, the compressed form of the address is allowed.
3. IP host domain name. Specify an internet host domain name of up to 254 characters in length.

Filter by local port number (FTRLCLPORT)

Specifies filtering based on the local TCP/IP port number. Monitor records will be created for TCP/IP database server jobs running on behalf of the specified local TCP/IP port. Jobs named QRWTSRVR and QZDASOINIT are examples of these server jobs.

*NONE

Port filtering is not specified.

1-65535

Specify the number of the local port for which monitor records are to be created.

Filter by query governor (FTRQRYGOVR)

Specifies filtering based on the query governor limits.

*NONE

Query governor filtering is not specified.

*ALL

Monitor records will be collected when a query governor limit is exceeded, regardless if the query is cancelled or not.

*COND

Monitor records will be collected when a query governor limit is exceeded based on one of the following criteria:

1. Query governor exit program returns 2 indicating that exceeded limit should be ignored, but Database Monitor records should be collected anyway.
2. Query governor exit program returns 3 indicating that the query should be cancelled.
3. Query governor exit program returns 0 indicating that the inquiry message should be issued and the response to the inquiry message is to cancel the query.
4. Query governor exit program fails and the response to the inquiry message is to cancel the query.
5. There is no query governor exit program in effect and the response to the inquiry message is to cancel the query.

   For more information on the query governor exit program, refer to the query governor exit program (QIBM_QQQ_QUERY_GOVR) documentation in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

Filter by accounting string (FTRCLTACG)

Specifies a filter based on the client special register accounting string. Monitor records will be created for SQL statements executed where the client special register accounting string matches this filter value.

The value of the accounting string can be changed through four interfaces. The Set Client Information (SQLESETI) API can change the client special register. The SYSPROC.WLM_SET_CLIENT_INFO() procedure can change the client special register. In CLI, SQLSetConnectAttr() can be used to set the SQL_ATTR_INFO_ACCTSTR connection attribute. In JDBC, the setClientInfo connection method can be used to set the ClientAccounting connection property.

*NONE

Filtering by accounting string is not specified.

accounting string

Monitor records will be created for SQL statements executed where the client special register accounting string matches this filter value.

Filter by application name (FTRCLTAPP)

Specifies a filter based on the client special register application name. Monitor records will be created for SQL statements executed where the client special register application name matches this filter value.

The value of the accounting string can be changed through four interfaces. The Set Client Information (SQLESETI) API can change the client special register. The SYSPROC.WLM_SET_CLIENT_INFO() procedure can change the client special register. In CLI, SQLSetConnectAttr() can be used to set the SQL_ATTR_INFO_APPLNAME connection attribute. In JDBC, the setClientInfo connection method can be used to set the ApplicationName connection property.

*NONE

Filtering by accounting string is not specified.

application name

Monitor records will be created for SQL statements executed where the client special register application name matches this filter value.

Filter by program name (FTRCLTPGM)

Specifies a filter based on the client special register program ID. Monitor records will be created for SQL statements executed where the client special register program ID matches this filter value.

The value of the accounting string can be changed through four interfaces. The Set Client Information (SQLESETI) API can change the client special register. The SYSPROC.WLM_SET_CLIENT_INFO() procedure can change the client special register. In CLI, SQLSetConnectAttr() can be used to set the SQL_ATTR_INFO_PROGRAMID connection attribute. In JDBC, the setClientInfo connection method can be used to set the ClientProgramID connection property.

*NONE

Filtering by accounting string is not specified.

application name

Monitor records will be created for SQL statements executed where the client special register program ID matches this filter value.

Filter by client user ID (FTRCLTUSR)

Specifies a filter based on the client special register user ID. Monitor records will be created for SQL statements executed where the client special register user ID matches this filter value.

The value of the accounting string can be changed through four interfaces. The Set Client Information (SQLESETI) API can change the client special register. The SYSPROC.WLM_SET_CLIENT_INFO() procedure can change the client special register. In CLI, SQLSetConnectAttr() can be used to set the SQL_ATTR_INFO_USERID connection attribute. In JDBC, the setClientInfo connection method can be used to set the ClientUser connection property.

*NONE

Filtering by accounting string is not specified.

application name

Monitor records will be created for SQL statements executed where the client special register user ID matches this filter value.

Filter by work station (FTRCLTWS)

Specifies a filter based on the client special register workstation name. Monitor records will be created for SQL statements executed where the client special register workstation name matches this filter value.

The value of the accounting string can be changed through four interfaces. The Set Client Information (SQLESETI) API can change the client special register. The SYSPROC.WLM_SET_CLIENT_INFO() procedure can change the client special register. In CLI, SQLSetConnectAttr() can be used to set the SQL_ATTR_INFO_WRKSTNNAME connection attribute. In JDBC, the setClientInfo connection method can be used to set the ClientHostName connection property.

*NONE

Filtering by accounting string is not specified.

application name

Monitor records will be created for SQL statements executed where the client special register workstation name matches this filter value.

Comment (COMMENT)

User-specified description that is associated with the database monitor. The description is stored in the monitor record that has a record ID of 3018.

*BLANK

Text is not specified.

character-value

Specify up to 100 characters of text.

Examples

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. Ten records will be held before being written to the file.

Example 2: Starting Private Monitoring

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

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 named MEMBER2 in the file named FILE3. Twenty records will be held before being written to the file.

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 may exist in more than one independent auxiliary storage pool (ASP); the library in the name space of the originator's job will always be 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 may exist in more than one independent auxiliary storage pool (ASP); the library in the name space of the originator's job will always be 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 Megabytes

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

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 of 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 will be collected only if the query is cancelled or a return code of 2 is returned by a query governor exit program. The query can be cancelled by a user response to the inquiry message CPA4259 (that is issued because the query exceeded the query governor limits) or it can be cancelled by the program logic inside the registered query governor exit program.

Example 13: Starting Public Monitoring and Filtering SQL Statements Run From iNav Run SQL Scripts

STRDBMON   OUTFILE(LIB41/DBMONFILE)  JOB(*ALL)
           FTRCLTAPP(System i Navigator - 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 System i Navigator - Run SQL Scripts.

Example 14: Starting Public Monitoring and Filtering SQL Statements Run From iNav 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 15: Starting Public Monitoring and Filtering for the client user dbmusr1. FTRCLTPGM

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.

Error messages

*ESCAPE Messages

CPF1321

Job &1 user &2 job number &3 not found.

CPF222E

&1 special authority is required.

CPF4269

Not authorized to object &1 in &2 type *&3.

CPF436A

Record format for file &1 in &2 does not match model file.

CPF436B

&1 can not be specified on the OUTFILE parameter.

CPF436C

Job &4 is already being monitored.

CPF436E

Job &1 user &2 job number &3 is not active.

CPF43A2

Address specified on FTRINTNETA parameter is not valid.

*STATUS Messages

CPI436A

Database monitor started for job &1, monitor ID &2.