Start Database Monitor (STRDBMON)

Where allowed to run: All environments (*ALL) Threadsafe: Conditional

Parameters Examples Error messages

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 commmand, 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 mulitple 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 of type *SNA.

5. Any public monitor requires the file specified for the OUTFILE parameter to be in a library that resides in the system ASP.

Top

---

 

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
RUNTHLD	Run time 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	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.

Top

 

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.

Top

 

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

Top

 

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.

Top

 

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.

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

Top

 

Run time threshold (RUNTHLD)

Specifies a filtering threshold based on the estimated run time of the SQL statement. 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.

Top

 

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.

Top

 

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 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 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 monitor records will be created for the SQL statement.

Top

 

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.

Top

 

Filter by internet address (FTRINTNETA)

Specifies filtering based on the internet address of a remote system. Monitor records will be created only for those SQL statements executed from the specified remote system.

*NONE

Internet address filtering is not specified.

internet-address

The internet address is specified in the form nnn.nnn.nnn.nnn, where nnn is a decimal number ranging from 0 through 255, without the leading zeros. (An internet address having all binary ones or zeros in the bits of the network or host identifier portions of the address is not valid.)

Top

 

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.

Top

---

 

Examples

Example 1: Starting Database Monitoring For All Jobs

 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 Database Monitoring For a Specific Job

 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 Database Monitoring For a Specific Job 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 Database 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 Database Monitoring For All Jobs 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 Database Monitoring For the Current Job 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 7: Starting Database Monitoring For the Current Job and 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 8: Starting Database 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.

Top

---

 

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.

Top