DATABASE_MONITOR_INFO view

The DATABASE_MONITOR_INFO view returns information about database monitors and plan cache event monitors on the server. Database monitors are started using the Start Database Monitor (STRDBMON) command. The QSYS2.START_PLAN_CACHE_EVENT_MONITOR procedure is used to start a plan cache event monitor. SQL Performance Monitors within IBM® i Access Client Solutions (ACS) are synonymous with database monitors and are included in this view.

Authorization: Start of change The caller must have *ALLOBJ special authority or be authorized to the QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage IDs. End of change

The following table describes the columns in the view. The system name is DBMON_INFO. The schema is QSYS2.

Table 1. DATABASE_MONITOR_INFO view
Column Name	System Column Name	Data Type	Description
MONITOR_ID	MONITOR_ID	CHAR(10)	The system-assigned monitor ID for this monitor.
MONITOR_TYPE	MONTYPE	VARCHAR(7)	Type of monitor. PUBLIC A monitor is considered public when the STRDBMON JOB parameter indicates that jobs other than the current job should be monitored. Public monitors remain active until they are explicitly ended using the End Database Monitor (ENDDBMON) command. PRIVATE A private monitor occurs when the STRDBMON JOB parameter indicates to monitor only the current job. The monitor is ended as part of job termination processing, if needed. Only a private monitor that is active in the current connection will be returned. EVENT An SQL plan cache event monitor intercepts plans as they are moved from the plan cache into a database monitor file.
MONITOR_STATUS	STATUS	VARCHAR(8)	Status of this monitor. ACTIVE Monitor is active. INACTIVE For a PUBLIC or PRIVATE monitor, it is inactive and can become ACTIVE. For an EVENT monitor, entries are no longer being collected. CLOSING The PUBLIC or PRIVATE monitor is not active or is in the processing of ending. It is not known if the entry can be reused for monitoring.
MONITOR_RECORD_TYPE	RCDTYPE	VARCHAR(6)	Type of database records in this monitor. DETAIL Both basic and detail database monitor records. An EVENT monitor always has a value of DETAIL. BASIC Only basic database monitor records
MONITOR_LIBRARY	MONLIB	VARCHAR(10)	Library for this monitor.
MONITOR_FILE	MONFILE	VARCHAR(10)	The file to which the database activity detail is written for this monitor.
MONITOR_MEMBER	MONMBR	VARCHAR(10)	Member for this monitor.
IASP_NUMBER	IASPNUMBER	SMALLINT	The independent auxiliary storage pool (IASP) number for the monitor file. 0 indicates the system ASP.
MONITOR_MEMBER_OPTION	MBROPT	VARCHAR(7) Nullable	Value used for the member replace option the last time this monitor was started. REPLACE ADD Contains the null value for an EVENT monitor.
NUMBER_ROWS	CARD	BIGINT Nullable	The number of rows in the database monitor file. Contains the null value if information is not available.
DATA_SIZE	SIZE	BIGINT Nullable	The total size, in bytes, of the database monitor file. Contains the null value if information is not available.
MONITOR_JOB_FILTER	JOB	VARCHAR(32)	Qualified job name for this monitor. For an EVENT monitor, this is the job that started the monitor. Following the qualified job name is the filter operator that applies to the job name. This is either EQ or NE. The special value of *ALL indicates all jobs on the system are monitored. A generic name is allowed for both the job name and the user name.
HOST_VARIABLE	HOSTVAR	VARCHAR(9) Nullable	How host variables are handled in this database monitor. BASIC Host variables are written in the QQQ3010 database monitor record. SECURE No host variables are captured and no QQQ3010 record is written. CONDENSED Host variable values are captured in the QQQ1000 database monitor record in column QQDBCLOB1. No QQQ3010 record is written. Contains the null value for an EVENT monitor.
FORCE_RECORDS	FRCRCD	SMALLINT Nullable	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. Contains the null value if the system calculates the value or for an EVENT monitor.
RUN_THRESHOLD_FILTER	RUNTHLD	INTEGER Nullable	The filtering threshold, in seconds, based on the estimated run time of SQL statements in this monitor. Contains the null value if a run time threshold is not used for filtering or for an EVENT monitor.
STORAGE_THRESHOLD_FILTER	STGTHLD	INTEGER Nullable	The filtering threshold, in megabytes, based on the estimated temporary storage usage of SQL statements in this monitor. Contains the null value if a temporary threshold is not used for filtering or for an EVENT monitor.
INCLUDE_SYSTEM_SQL	INCSYSSQL	VARCHAR(3)	Monitor includes records for system-generated SQL statements. YES Monitor records are generated for both user-specified and system-generated SQL statements. NO Monitor records are generated for only user-specified SQL statements. INI For a PUBLIC or PRIVATE monitor, records are generated based on the value of the SQL_DBMON_OUTPUT option in the QAQQINI query options.
FILE_FILTER	FTRFILE	VARCHAR(2728) Nullable	A list of up to 10 qualified file references that are used for filtering. Following each file name is the filter operator that applies to the file name. This is either EQ or NE. When more than one file is listed, a comma and a single blank separate the entries. Either the file name or the library name can be a generic name. A special value of *ALL for the file name indicates all files in the library. Contains the null value if no database files are used for filtering.
USER_FILTER	FTRUSER	VARCHAR(158) Nullable	A list of up to 10 user profiles that are used for filtering. Following each user profile name is the filter operator that applies to the user profile. This is either EQ or NE. When more than one profile is listed, a comma and a single blank separate the entries. A profile name can be a generic name. Contains the null value if the user profile is not used for filtering.
TCPIP_FILTER	FTRINTNETA	VARCHAR(254) Nullable	The TCP/IP address or host name is used for filtering. This is an IPv4, IPv6, or IP host domain name, or the special value of *LOCAL. Contains the null value if the TCP/IP address or host name is not used for filtering or for an EVENT monitor.
LOCAL_PORT_FILTER	FTRLCLPORT	INTEGER Nullable	Filtering is 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. The IBM i well defined port numbers are documented here: Port numbers for host servers and server mapper. Contains the null value if the port number is not used for filtering or for an EVENT monitor.
QUERY_GOVERNOR_FILTER	FTRQRYGOVR	VARCHAR(11) Nullable	The query governor is used for filtering. ALL Monitor records will be collected when a query governor limit is exceeded. CONDITIONAL Monitor records will be conditionally collected when a query governor limit is exceeded. Contains the null value if the query governor is not used for filtering or for an EVENT monitor.
CLIENT_ACCTNG_FILTER	FTRCLTACG	VARCHAR(128) Nullable	The CURRENT CLIENT_ACCTNG special register is used for filtering. Contains the null value if the CURRENT CLIENT_ACCTNG special register is not used for filtering or for an EVENT monitor.
CLIENT_APPLNAME_FILTER	FTRCLTAPP	VARCHAR(128) Nullable	The CURRENT CLIENT_APPLNAME special register is used for filtering. Contains the null value if the CURRENT CLIENT_APPLNAME special register is not used for filtering or for an EVENT monitor.
CLIENT_PROGRAMID_FILTER	FTRCLTPGM	VARCHAR(128) Nullable	The CURRENT CLIENT_PROGRAMID special register is used for filtering. Contains the null value if the CURRENT CLIENT_PROGRAMID special register is not used for filtering or for an EVENT monitor.
CLIENT_USERID_FILTER	FTRCLTUSR	VARCHAR(128) Nullable	The CURRENT CLIENT_USERID special register is used for filtering. Contains the null value if the CURRENT CLIENT_USERID special register is not used for filtering or for an EVENT monitor.
CLIENT_WRKSTNNAME_FILTER	FTRCLTWS	VARCHAR(128) Nullable	The CURRENT CLIENT_WRKSTNNAME special register is used for filtering. Contains the null value if the CURRENT CLIENT_WRKSTNNAME special register is not used for filtering or for an EVENT monitor.
SQL_CODE_FILTER	FTRSQLCODE	VARCHAR(7) Nullable	How the SQLCODE result from a statement execution is used for filtering. NONZERO Any SQL statement with an SQLCODE value that is non-zero is included in the monitor. ERROR Any SQL statement with an SQLCODE that is less than zero is collected in the monitor. WARNING Any SQL statement with an SQLCODE that is greater than zero is collected in the monitor. SQLCODE Any SQL statement with an SQLCODE that exactly matches the value in the SQLCODE_VALUE column is collected in the monitor. Contains the null value if the SQLCODE for a statement is not used for filtering or for an EVENT monitor.
SQLCODE_VALUE	SQLCODEVAL	INTEGER Nullable	The positive or negative SQLCODE value to use for filtering. Contains the null value if the SQL_CODE_FILTER column contains a value other than SQLCODE.

Examples

Example 1: Get the MONITOR_ID for all the active PUBLIC monitors and the file names associated with the MONITOR_IDs.

SELECT MONITOR_ID, MONITOR_LIBRARY, MONITOR_FILE 
  FROM QSYS2.DATABASE_MONITOR_INFO 
  WHERE MONITOR_STATUS = 'ACTIVE' AND 
        MONITOR_TYPE = 'PUBLIC'

Example 2: Find the active monitors that have outfiles larger than 1Gig.

SELECT MONITOR_LIBRARY, MONITOR_FILE, NUMBER_ROWS, DATA_SIZE
  FROM QSYS2.DATABASE_MONITOR_INFO 
  WHERE MONITOR_STATUS = 'ACTIVE' AND 
        DATA_SIZE > 1073741824

Example 3: Find any active monitors that are filtering based upon a specific SQLCODE (FTRSQLCODE).

SELECT MONITOR_ID, MONITOR_LIBRARY, MONITOR_FILE, SQLCODE_VALUE  
  FROM QSYS2.DATABASE_MONITOR_INFO   
  WHERE MONITOR_STATUS = 'ACTIVE' AND 
        SQL_CODE_FILTER = 'SQLCODE'

Example 4: Get the MONITOR_ID for a user's SQL plan cache event monitor and use it to end the active event monitor.

CALL QSYS2.END_PLAN_CACHE_EVENT_MONITOR (SELECT MONITOR_ID
                                         FROM QSYS2.DATABASE_MONITOR_INFO
                                         WHERE MONITOR_TYPE = 'EVENT' AND 
	                                           MONITOR_LIBRARY = 'USERLIB')