Purpose
Stores an Event Monitor definition in the database catalogs. When activated (see SET EVENT MONITOR STATE Command and SQL), an event monitor will log monitor data when certain events occur while using the database. You should read "Event Monitors" and Chapter 4. "Event Monitor Output" before using this command.
Context
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
The privileges held by the authorization ID must include either SYSADM or DBADM authority (SQLSTATE 42502).
Format
>>-CREATE--EVENT--MONITOR--event-monitor-name--FOR-------------->
+-,-----------------------------------------------------+
V |
>-----+-+-DATABASE----+-----------------------------------++---->
| +-TABLES------+ |
| +-DEADLOCKS---+ |
| +-TABLESPACES-+ |
| +-BUFFERPOOLS-+ |
+--+-CONNECTIONS--+-+-----------------------------+-+
+-STATEMENTS---+ +-WHERE--| Event Condition |--+
+-TRANSACTIONS-+
+-MANUALSTART-+
>-WRITE--TO--+-PIPE--pipe-name--------------------+-+-------------+>
+-FILE--path-name--| File Options |--+ +-AUTOSTART---+
+-GLOBAL-+
>--+-----------------------+-+--------+------------------------><
+-ON NODE--node-number--+ +-LOCAL--+
Event Condition
+-AND | OR------------------------------------------------------+
V |
|----+-----+---+--+-APPL_ID---+-+-=----------+-comparison-string--++->
+-NOT-+ | +-AUTH_ID---+ +-<>---------+ |
| +-APPL_NAME-+ +->----------+ |
| +->=---------+ |
| +-<----------+ |
| +-<=---------+ |
| +-LIKE-------+ |
| +-NOT--LIKE--+ |
+-(Event Condition)--------------------------------+
>--------------------------------------------------------------|
File Options
|--+-------------------------------+--+------------------------+->
| +-NONE------------+ | | +-pages-+ |
+-MAXFILES--+-number-of-files-+-+ +-MAXFILESIZE--+-NONE--+-+
+-BLOCKED----+ +-APPEND--+
>--+--------------------+--+------------+-+---------+----------|
+-BUFFERSIZE--pages--+ +-NONBLOCKED-+ +-REPLACE-+
|
Parameters
This clause is a special form of the WHERE clause that should not be confused with a standard search condition.
If no WHERE clause is specified then all connections will be monitored.
The naming rules for pipes are platform specific. On UNIX operating systems pipe names are treated like file names. As a result, relative pipe names are permitted, and are treated like relative path-names (see path-name below). However, on OS/2, Windows 95 and Windows NT, there is a special syntax for a pipe name. As a result, on OS/2, Windows 95 and Windows NT absolute pipe names are required.
The existence of the pipe will not be checked at event monitor creation time. It is the responsibility of the user to have created and opened the pipe for reading at the time that the event monitor is activated. If the pipe is not available at this time, then the event monitor will turn itself off, and will log an error. (That is, if the event monitor was activated at database start time as a result of the AUTOSTART option, then the event monitor will log an error in the system error log.) If the event monitor is activated via the SET EVENT MONITOR STATE SQL statement, then that statement will fail (SQLSTATE 58030).
The maximum size of each file can be defined as well as the maximum number of files. An event monitor will never split a single event record across two files. However, an event monitor may write related records in two different files. It is the responsibility of the application that uses this data to keep track of such related information when processing the event files. See "File Event Monitor Target" for more information.
The directory does not have to exist at CREATE EVENT MONITOR time. However, a check is made for the existence of the target path when the event monitor is activated. At that time, if the target path does not exist, an error (SQLSTATE 428A3) is raised.
If an absolute path (a path that starts with the root directory on AIX, or a disk identifier on OS/2, Windows 95 and Windows NT) is specified, then the specified path will be the one used. If a relative path (a path that does not start with the root) is specified, then the path relative to the DB2EVENT directory in the database directory will be used.
When a relative path is specified, the DB2EVENT directory is used to convert it into an absolute path. Thereafter, no distinction is made between absolute and relative paths. The absolute path is stored in the SYSCAT.EVENTMONITORS catalog view.
It is possible to specify two or more event monitors that have the same target path. However, once one of the event monitors has been activated for the first time, and as long as the target directory is not empty, it will be impossible to activate any of the other event monitors.
If an application removes the event files from the directory after they have been written, then the total number of files that an event monitor can produce can exceed number-of-files. This option has been provided to allow a user to guarantee that the event data will not consume more than a specified amount of disk space.
The number of pages must be greater than at least the size of the event buffer in pages. If this requirement is not met, then an error (SQLSTATE 428A4) is raised.
The minimum and default size of each buffer (if this option is not specified) is 1 page (that is, 2 buffers, each 4 K in size). The maximum size of the buffers is limited by the size of the database manager monitor heap (MON_HEAP_SZ) since the buffers are allocated from this heap.
The APPEND option does not apply at CREATE EVENT MONITOR time, if there is existing event data in the directory where the newly created event monitor is to write its event data.
Usage
Comments
Related Information
SET EVENT MONITOR STATE Command and SQL
Purpose
Starts the event analyzer GUI from the command line, allowing the user to view traces produced an event monitor.
Authorization
None, unless connecting to the database (-evm and -db,), then one of the following is required:
Required Connection
None
Format
>>-db2eva------------------------------------------------------->
>--+--path--evmon-target--+-----------------------------------+-+><
| +--conn--+-----------------------+--+ |
| +--db---database-alias--+ |
+--evm--evmon-name---db--database-alias--+--------+----------+
+--conn--+
|
Parameters
Two methods of operation are provided for reading traces with db2eva, you can:
Comments
This tool is only available on OS/2 and Windows platforms.
It does not display database, table space, buffer pool, and table event records, but properly reads traces containing them.
Purpose
Formats the trace produced by file or pipe event monitors, and writes it to standard output. This tool is located in the misc subdirectory of the sqllib directory of the instance.
Authorization
None, unless connecting to the database (-evm and -db are specified), then one of the following is required:
Required Connection
None
Format
>>-db2evmon-----------------------------------------------------> >--+-+-------------------------------------------------+-+----->< | +--db--database-alias---evm--event-monitor-name---+ | +--path--event-monitor-target-------------------------+ |
Parameters
Two methods of operation are provided for formatting a trace:
Comments
If the data is being written to a pipe, the tool formats the output for display using standard output as event records are written occur. In this case, the tool must be started before the monitor is turned on.
See Also
"Programming to Read an Event Monitor Trace"
Purpose
Removes an event monitor definition from the Database catalogs. Whenever an object is deleted, its description is deleted from the catalog and any packages that reference the object are invalidated.
Context
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
The authorization ID of the DROP statement when dropping an event monitor must have SYSADM or DBADM authority
Format
>>-DROP-----EVENT--MONITOR--event-monitor-name----------------->< |
Parameters
If the identified event monitor is ON, an error (SQLSTATE 55034) is raised. Otherwise, the event monitor is deleted.
If there are event files in the target path of the event monitor when the event monitor is dropped, the event files are not deleted.
An event monitor must be stopped or OFF before it can be deleted. Dropping an event monitor does not erase the target directory.
Purpose
The EVENT_MON_STATE function returns the current state of an event monitor.
Context
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Format
>>-EVENT_MON_STATE--(string-expression)------------------------>< |
Parameters
The schema is SYSIBM.
The result is an integer with one of the following values:
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Example:
SELECT EVMONNAME,
CASE
WHEN EVENT_MON_STATE(EVMONNAME) = 0 THEN 'Inactive'
WHEN EVENT_MON_STATE(EVMONNAME) = 1 THEN 'Active'
END
FROM SYSCAT.EVENTMONITORS
Purpose
Displays the status of the database manager monitor switches. Monitor switches instruct the database system manager to collect statistics about its operation and performance and that of the applications using it. A database manager-level switch is on when any monitoring application has turned it on, an active event monitor requires this switch to be on, or it has been set in the database manager configuration. This command is used to determine if the database manager is currently collecting data.
Authorization
One of the following:
Required Connection
Instance. To display the settings for a remote instance, or for a different local instance, it is necessary to first attach to that instance.
>>-GET--+-DATABASE MANAGER-+-MONITOR SWITCHES------------------><
+-DB MANAGER-------+
+-DBM--------------+
|
Parameters
None
Example
The following is sample output from GET DATABASE MANAGER MONITOR SWITCHES:
DBM System Monitor Information Collected
Buffer Pool Activity Information (BUFFERPOOL) = ON 06-11-1997 10:11:01.738377
Lock Information (LOCK) = OFF
Sorting Information (SORT) = ON 06-11-1997 10:11:01.738400
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Unit of Work Information (UOW) = ON 06-11-1997 10:11:01.738353
|
Comments
This command returns the settings for the database manager, indicating whether the database manager is currently collecting monitor data. To see the switch settings for your session issue the GET MONITOR SWITCHES command.
Default switch settings can be set in the database manager configuration file. If switches are set ON in the database manager configuration file, then the database manager will always be collecting monitor data, even if all monitoring applications have turned off their switches.
See Also
Purpose
Displays the status of the database system monitor switches for the current session. Monitor switches instruct the database system manager to collect database activity information. Each application using the database system monitor interface has its own set of monitor switches. This command displays them. To display the database manager-level switches, use GET DATABASE MANAGER MONITOR SWITCHES Command. If a particular switch is on, this command also displays the time stamp for when the switch was turned on.
Authorization
One of the following:
Required Connection
Instance.
>>-GET MONITOR SWITCHES---------------------------------------->< |
Parameters
None
Example
The following is sample output from GET MONITOR SWITCHES:
Monitor Recording Switches Buffer Pool Activity Information (BUFFERPOOL) = ON 02-20-1997 16:04:30.070073 Lock Information (LOCK) = OFF Sorting Information (SORT) = OFF SQL Statement Information (STATEMENT) = ON 02-20-1997 16:04:30.070073 Table Activity Information (TABLE) = OFF Unit of Work Information (UOW) = ON 02-20-1997 16:04:30.070073 |
Comments
When a database system monitor command is first issued the session inherits the switch settings of the database manager configuration. The settings can be overridden using UPDATE MONITOR SWITCHES Command.
See Also
GET DATABASE MANAGER MONITOR SWITCHES Command
Purpose
Collects some of the data that the database manager maintains about its operation and performance. The information returned represents a snapshot of this data at the time the command is issued.
Scope
Returns data only for the node to which the session is attached.
Authorization
One of the following:
Required Connection
Instance. To obtain a snapshot of a remote instance, it is necessary to first attach to that instance.
>>-GET SNAPSHOT FOR--------------------------------------------->
>--+-+-DATABASE MANAGER-+----------------------------+---------><
| +-DB MANAGER-------+ |
| +-DBM--------------+ |
+-ALL DATABASES-----------------------------------+
+-ALL APPLICATIONS--------------------------------+
+-ALL BUFFERPOOLS---------------------------------+
+-APPLICATION--+-APPLID--appl-id-------+----------+
| +-AGENTID--appl-handle--+ |
+-FCM FOR ALL NODES-------------------------------+
+-LOCKS FOR APPLICATION--+-APPLID--appl-id-------++
| +-AGENTID--appl-handle--+|
+--+-ALL-----------+-ON--database-alias-----------+
+-+-DATABASE-+--+
| +-DB-------+ |
+-APPLICATIONS--+
+-TABLES--------+
+-TABLESPACES---+
+-LOCKS---------+
+-BUFFERPOOLS---+
|
Parameters
Parameters are group by the way you would use them.
| Note: | The appl-id and appl-handle can be obtained by issuing a LIST APPLICATIONS
command (see LIST APPLICATIONS - Command).
|
Comments
INSTANCE CONNECTION: If not attached to an instance, issuing this command will automatically attach your session to the instance specified by the DB2INSTANCE environment variable.
To obtain a snapshot from a remote instance (or a different local instance), it is necessary to first attach to that instance. If an alias for a database residing at a different instance is specified, an error message is returned.
DATA COLLECTED UNDER SWITCH CONTROL: Data elements that are collected by the DBMS only if a monitor switch is ON are either not returned, or returned as 'Not Collected'. Check Chapter 3. "Database System Monitor Data Elements" to determine if a switch needs to be turned on for a data element.
See Also
Purpose
Displays the list of databases that are active on this instance. This is a subset of the information listed by the GET SNAPSHOT FOR ALL DATABASES command (see GET SNAPSHOT Command). For each active database, this command displays the following:
Scope
Returns data only for the node to which the session is attached.
Authorization
None
Required Connection
Instance. To obtain a snapshot of a remote instance, it is necessary to first attach to that instance.
Format
>>-LIST ACTIVE DATABASES--------------------------------------->< |
Parameters
None
Examples
Following is sample output from the LIST ACTIVE DATABASES command:
Active Databases Database name = TEST Applications connected currently = 0 Database path = /home/smith/smith/NODE0000/SQL00002/ Database name = SAMPLE Applications connected currently = 1 Database path = /home/smith/smith/NODE0000/SQL00001/ |
See Also
Purpose
Displays the list of applications connected to a database on the instance, including secondary connections established to access a partitioned database.
Scope
This command only returns information for the node on which it is issued.
Authorization
One of the following:
Required Connection
Instance. To list applications for a remote instance, it is necessary to first attach to that instance.
Format
>>-LIST APPLICATIONS---+-----------------------------------+---->
+-FOR--+-DATABASE-+-database-alias--+
+-DB-------+
>-+-------------+----------------------------------------------><
+-SHOW DETAIL-+
|
Parameters
The default application information is comprised of the following:
| Note: | If this option is specified, it is recommended that the output be redirected to a file, and that the report be viewed with the help of an editor. The output lines may wrap around when displayed on the screen. |
Example
The following is sample output from LIST APPLICATIONS:
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- ------------------------------ -------- -----
smith db2bp_32 12 *LOCAL.smith.970220191502 TEST 1
smith db2bp_32 11 *LOCAL.smith.970220191453 SAMPLE 1
|
| Note: | For more information about these fields, see Chapter 3. "Database System Monitor Data Elements". |
Comments
The database administrator can use the output from this command as an aid to problem determination. In addition, this information is required if the database administrator wants to use GET SNAPSHOT Command or FORCE an application.
To list applications at a remote instance (or a different local instance), it is necessary to first attach to that instance. If FOR DATABASE is specified when an attachment exists, and the database resides at an instance which differs from the current attachment, the command will fail.
See Also
Purpose
Displays the contents of the Database Connection Services (DCS) directory to standard output.
Authorization
One of the following:
Required Connection
Instance. To list the DCS applications at a remote instance, it is necessary to first attach to that instance.
Format
>>-LIST DCS APPLICATIONS-+-------------+-----------------------><
+-SHOW DETAIL-+
|
Parameters
Example
The following is sample output from LIST DCS APPLICATIONS:
Auth Id Application Name Agent Id Outbound Application Id -------- -------------------- ---------- -------------------------------- DDCSUS1 db2bp 89330 CAIBMOML.OMXT4H08.A79EAA3C6E29 |
| Note: | For more information about these fields, see Chapter 3. "Database System Monitor Data Elements". |
Comments
The database administrator can use this command to match client application connections to the gateway with corresponding host connections from the gateway.
The database administrator can also use agent ID information to force specified applications off a DDCS server.
Purpose
Resets the counters maintained by the database system monitor for the current session for specified database, or for all active databases, to zero.
Authorization
One of the following:
Required Connection
Instance.
Format
>>-RESET MONITOR---+-ALL-------------------------------+-------><
+-FOR--+-DATABASE-+-database-alias--+
+-DB-------+
|
Parameters
Comments
Each session (instance) has its own private view of the monitor data. If one user resets, other users are not affected.
This resets the data for all monitor switches. To reset data for a single switch turn it OFF, then ON, using UPDATE MONITOR SWITCHES Command.
See Also
Purpose
The SET EVENT MONITOR STATE statement activates or deactivates an event monitor. The SET EVENT MONITOR STATE statement is not under transaction control.
Context
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.
Authorization
The authorization ID of the statement most hold either SYSADM or DBADM authority (SQLSTATE 42815).
Format
>>-SET--EVENT--MONITOR--event-monitor-name--STATE--------------->
+-=-+
>-+---+--+-0-------------+-------------------------------------><
+-1-------------+
+-host-variable-+
|
Parameters
Usage
Comments
The following example activates an event monitor called SMITHPAY.
SET EVENT MONITOR SMITHPAY STATE = 1
The current state of an event monitor (active or inactive) is determined by using the EVENT_MON_STATE built-in function.
Purpose
Selectively turns on or off switches for groups of monitor data to be collected by the database manager. Returns the current state of these switches for the application issuing the call.
To get the current state of the switches at the database manager level use sqlmonss - Get Snapshot API.
Scope
This API only affects the application making the call.
Authorization
One of the following:
Required Connection
Instance.
API Include File
sqlmon.h
C API Syntax
/* File: sqlmon.h */
/* API: Get/Update Monitor Switches */
/* ... */
int SQL_API_FN
sqlmon (
unsigned long version,
_SQLOLDCHAR *reserved,
sqlm_recording_group group_states[],
struct sqlca *sqlca);
/* ... */
|
API Parameters
If requesting data for a version higher than the current server, the database monitor only returns data for its level.
| Note: | If SQLM_DBMON_VERSION1 is specified as the version, the APIs cannot be run remotely. |
Comments
To obtain the status of the switches at the database manager level, call sqlmonss - Get Snapshot API, specifying SQMA_DB2 for OBJ_TYPE (get snapshot for database manager).
See Also
Code Sample
The following example illustrates how to update the monitor switches and print their current settings.
/*
Database Monitor Switch API
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include "sqlca.h"
#include "sqlutil.h" // for using sqlaintp
#include "sqlmon.h"
//------------------------------------------------------------------------------
// Database Monitor Switch API Sample
//------------------------------------------------------------------------------
char* sw_status_string(int val) {
switch (val) {
case SQLM_OFF: return "OFF";
case SQLM_ON: return "ON";
}
return "";
}
void print_sws(sqlm_recording_group group_states[SQLM_NUM_GROUPS]);
void print_sw_set_times(sqlm_recording_group group_states[SQLM_NUM_GROUPS]);
int main() {
//-----------------------------------------------------------------------
// Set Table switch ON, UOW switch OFF, and query the current (default)
// values for the other switches. (see Database Manager Configuration)
//-----------------------------------------------------------------------
sqlm_recording_group group_states[SQLM_NUM_GROUPS];
struct sqlca sqlca;
group_states[SQLM_TABLE_SW].input_state = SQLM_ON;
group_states[SQLM_UOW_SW].input_state = SQLM_OFF;
group_states[SQLM_STATEMENT_SW].input_state = SQLM_HOLD;
group_states[SQLM_BUFFER_POOL_SW].input_state = SQLM_HOLD;
group_states[SQLM_LOCK_SW].input_state = SQLM_HOLD;
group_states[SQLM_SORT_SW].input_state = SQLM_HOLD;
//-----------------------------------------------------------------------
// Perform the call
//-----------------------------------------------------------------------
sqlmon(SQLM_DBMON_VERSION5, NULL, group_states, &sqlca);
if (sqlca.sqlcode<0) {
// get and display a printable error message
char msg[1024];
sqlaintp (msg, sizeof(msg), 60, &sqlca);
printf("%s", msg);
}
//-----------------------------------------------------------------------
// Print the output
//-----------------------------------------------------------------------
print_sws(group_states); // Print the switch values
print_sw_set_times(group_states); // Print their switch set time (if ON)
} // end of Database Monitor Switch API sample
//------------------------------------------------------------------------------
// print switch values
//------------------------------------------------------------------------------
void print_sws(sqlm_recording_group group_states[SQLM_NUM_GROUPS]) {
printf("SQLM_UOW_SW: %s\n",
sw_status_string(group_states[SQLM_UOW_SW].output_state));
printf("SQLM_STATEMENT_SW: %s\n",
sw_status_string(group_states[SQLM_STATEMENT_SW].output_state));
printf("SQLM_TABLE_SW: %s\n",
sw_status_string(group_states[SQLM_TABLE_SW].output_state));
printf("SQLM_BUFFER_POOL_SW: %s\n",
sw_status_string(group_states[SQLM_BUFFER_POOL_SW].output_state));
printf("SQLM_LOCK_SW: %s\n",
sw_status_string(group_states[SQLM_LOCK_SW].output_state));
printf("SQLM_SORT_SW: %s\n",
sw_status_string(group_states[SQLM_SORT_SW].output_state));
} // end print_sws
//------------------------------------------------------------------------------
// print switch set times (if ON)
//------------------------------------------------------------------------------
void print_sw_set_times(
sqlm_recording_group group_states[SQLM_NUM_GROUPS]) {
if (group_states[SQLM_UOW_SW].start_time.seconds) {
printf("SQLM_UOW_SW start_time: %s\n", ctime((time_t *)
&group_states[SQLM_UOW_SW].start_time.seconds));
}
if (group_states[SQLM_STATEMENT_SW].start_time.seconds) {
printf("SQLM_STATEMENT_SW start_time: %s\n", ctime((time_t *)
&group_states[SQLM_STATEMENT_SW].start_time.seconds));
}
if (group_states[SQLM_TABLE_SW].start_time.seconds) {
printf("SQLM_TABLE_SW start_time: %s\n", ctime((time_t *)
&group_states[SQLM_TABLE_SW].start_time.seconds));
}
if (group_states[SQLM_BUFFER_POOL_SW].start_time.seconds) {
printf("SQLM_BUFFER_POOL_SW start_time: %s\n", ctime((time_t *)
&group_states[SQLM_BUFFER_POOL_SW].start_time.seconds));
}
if (group_states[SQLM_LOCK_SW].start_time.seconds) {
printf("SQLM_LOCK_SW start_time: %s\n", ctime((time_t *)
&group_states[SQLM_LOCK_SW].start_time.seconds));
}
if (group_states[SQLM_SORT_SW].start_time.seconds) {
printf("SQLM_SORT_SW start_time: %s\n", ctime((time_t *)
&group_states[SQLM_SORT_SW].start_time.seconds));
}
} // end print_sw_set_times (if ON)
Purpose
Collects database manager monitor information and returns it to a user-allocated data buffer. The information returned represents a snapshot of the database manager operational status at the time the API was called.
Scope
This API returns information only for the instance.
Authorization
One of the following:
Required Connection
Instance. To obtain a snapshot from a remote instance (or a different local instance), it is necessary to first attach to that instance.
API Include File
sqlmon.h
C API Syntax
/* File: sqlmon.h */
/* API: Get Snapshot */
/* ... */
int SQL_API_FN
sqlmonss (
unsigned long version,
_SQLOLDCHAR *reserved,
sqlma *sqlma_ptr,
unsigned long buffer_length,
void *buffer_area,
sqlm_collected *collected,
struct sqlca *sqlca);
/* ... */
|
API Parameters
If requesting data for a version higher than the current server, the database monitor only returns data for its level.
| Note: | If SQLM_DBMON_VERSION1 is specified as the version, the APIs cannot be run remotely. |
Snapshot Requests Supported
The following table lists the Snapshot request types that are supported.
| API request type | Data structures that may be returned (Record type) | Information returned |
|---|---|---|
| SQLMA_APPLINFO_ALL |
sqlm_applinfo (SQLM_APPLINFO_SS) | Application identification information for all applications currently connected to a database that is managed by the DB2 instance on the node where snapshot is taken. |
| SQLMA_DBASE_APPLINFO |
sqlm_applinfo (SQLM_APPLINFO_SS) | Same as SQLMA_APPLINFO_ALL for each application currently connected to the specified database. |
| SQLMA_DCS_APPLINFO_ALL |
sqlm_dcs_applinfo (SQLM_DCS_APPLINFO_SS) | Application identification info for all DCS applications currently connected to a database that is managed by the DB2 instance on the node where snapshot is taken. |
| SQLMA_DB2 |
sqlm_db2 (SQLM_DB2_SS) | Database manager level information, including internal monitor switch settings. |
sqlm_fcm (SQLM_FCM_SS) sqlm_fcm_node (SQLM_NODE_SS) | Fast communication manager information for each node in a partitioned database that this node is communicating with. | |
| SQLMA_DBASE |
sqlm_dbase (SQLM_DBASE_SS) | Database level information and counters for a database. Information is returned only if there is at least one application connected to the database.. |
sqlm_rollfwd_info (SQLM_DBASE_ROLLFWD_SS) sqlm_rollfwd_ts_info (SQLM_DBASE_ROLLFWD_TS_SS) | Rollforward information if a rollforward is in progress. | |
| SQLMA_DBASE_ALL | Same as SQLMA_DBASE for each database active on the node. | |
| SQLMA_APPL |
sqlm_appl (SQLM_APPL_SS) sqlm_lock_wait (SQLM_LOCK_WAIT_SS) | Application level information, includes cumulative counters, status information. Lock information for every agent working for this application that is waiting for a lock. |
sqlm_stmt (SQLM_STMT_SS) | Statement information for each open cursor and the last statement executed. | |
sqlm_subsection (SQLM_SUBSECTION_SS) | Subsection information that immediately follows its parent sqlm_stmt. | |
sqlm_subagent (SQLM_SUBAGENT_SS) | In partitioned databases, an sqlm_subagent always follows its parent sqlm_subsection or sqlm_stmt. An sqlm_subagent is not returned for a coordinator agent. | |
| SQLMA_AGENT_ID | Same as SQLMA_APPL. | |
| SQLMA_DBASE_APPLS | Same as SQLMA_APPL, for each application that is connected to the database on the node. | |
| SQLMA_APPL_ALL | Same as SQLMA_APPL. for each application that is active on the node. | |
| SQLMA_DBASE_TABLES |
sqlm_table_header (SQLM_TABLE_HEADER_SS) sqlm_table (SQLM_TABLE_SS) | Table activity information for each table that was accessed, sqlm_table_header followed by an sqlm_table for each table. |
| SQLMA_APPL_LOCKS |
sqlm_appl_lock (SQLM_APPL_LOCK_SS) sqlm_lock_wait (SQLM_LOCK_WAIT_SS) sqlm_lock (SQLM_LOCK_SS) | List of locks held by the application, and any lock wait information, sqlm_appl_lock is followed by sqlm_lock_wait if an application is in a lock wait, followed by an sqlm_lock for each lock held. |
| SQLMA_APPL_LOCKS_AGENT_ID | Same as SQLMA_APPL_LOCKS. | |
| SQLMA_DBASE_LOCKS |
sqlm_dbase_lock (SQLM_DBASE_LOCK_SS) (SQLMA_APPL_LOCKS) | Lock information at the database level, and application level for each application connected to the database, followed by an SQLMA_APPL_LOCKS for each application. |
| SQLMA_DBASE_TABLESPACES |
sqlm_tablespace_header (SQLM_TABLESPACE_HEADER_SS) sqlm_tablespace (SQLM_TABLESPACE_SS) | Information about table space activity the database level, the application level for each application connected to the database, and the table space level for each table space that has been accessed by an application connected to the database, sqlm_tablespace_header followed by an sqlm_tablespace for each table space. |
| SQLMA_BUFFERPOOLS_ALL |
sqlm_bufferpool (SQLM_BUFFERPOOL_SS) | Bufferpool activity counters. |
| SQLMA_DBASE_BUFFERPOOLS | Same as SQLMA_BUFFERPOOLS_ALL, but for specified database only. |
Specifying the Snapshot Requests
An invocation of sqlmonss() can specify several requests.
The sqlma supplied as input argument to sqlmonss() contains an array of sqlm_obj_struct. Each sqlm_obj_struct is an individual snapshot request.
sqlm_obj_struct is defined as follows:
typedef struct sqlm_obj_struct /* SNAPSHOT REQUEST */
{
unsigned long agent_id; /* used for requests based on agentid */
unsigned long obj_type; /* Snapshot Request Type (SQLMA_XXXX) */
char object[SQLM_OBJECT_SZ];/* used for requests based on object */
/* name, such as 'get snapshot for database' */
}sqlm_obj_struct;
Where agent_id and object are only required if applicable for the request type, and are mutually exclusive. For example: a database name is required when the type is SQLMA_DBASE_LOCKS (get snapshot for locks on database), whereas an agent_id is required when the type is SQLMA_APPL_LOCKS_AGENT_ID. Both agent_id and object are ignored for requests such as SQLMA_APPLINFO_ALL (list applications).
Note that agent_id is the application handle for an application. It does not correspond to any Operating System process Id (it is named this way for source compatibility with older releases of DB2).
Setting up the sqlma and issuing the snapshot call
The following example sets up the sqlma for a call to sqlmonss() that requests two different snapshots. The first request requires an object name, the database alias, the second request requires an agent_id, the application handle:
#include "string.h"
#include "stdlib.h"
#include "stdio.h"
#include "sqlutil.h"
#include "sqlmon.h" // System Monitor interface
main() {
struct sqlca sqlca;
int rc;
#define BUFFER_SZ 4096 // Use a fixed size output buffer
char snap_buffer[BUFFER_SZ]; // Snapshot output buffer
sqlm_collected collected;
//----------------------------------------------------------------------
// Request SQLMA_DBASE, and SQLMA_APPL_LOCKS_AGENT_ID in the sqlma
//----------------------------------------------------------------------
unsigned long agent_id = 0; // STUB: Obtain by issuing 'list application'
// Allocate the variable size sqlma structure
struct sqlma* sqlma = (struct sqlma *) malloc(SQLMASIZE(2));
// Request 2 different snapshots in same call
sqlma->obj_num = 2;
sqlma->obj_var[0].obj_type = SQLMA_DBASE;
strcpy(sqlma->obj_var[0].object, "SAMPLE");
sqlma->obj_var[1].obj_type = SQLMA_APPL_LOCKS_AGENT_ID;
sqlma->obj_var[1].agent_id = agent_id;
//----------------------------------------------------------------------
// Perform the snapshot
//----------------------------------------------------------------------
rc = sqlmonss(SQLM_DBMON_VERSION5, NULL, sqlma,
BUFFER_SZ, snap_buffer,
&collected,
&sqlca);
if (sqlca.sqlcode < 0) {
// get and display a printable error message
char msg[1024];
sqlaintp (msg, sizeof(msg), 60, &sqlca);
printf("%s", msg);
}
free(sqlma);
return rc;
}
Application handles can be retrieved by issuing an SQLMA_APPLINFO_ALL request (list applications). An application connecting to the database can also retrieve its application handle (agent_id) from the sqlca of the CONNECT request (See "Obtaining application handle (AGENT_ID) from the CONNECT request").
Reading the Snapshot Output Buffer
The sqlmonss() routine returns data as contiguous data structures in the user supplied buffer.
The sqlmon.h header file contains the definitions for all records returned by the sqlmonss() routine. It is the first place you should look for record information. It contains comments that explain how records are laid out in the output buffer. You may want to print a copy of this file, and reference it as you read this section.
The data structures returned in the snapshot output buffer are arranged in a two-level hierarchy:
Each record contains a field that specifies its type and a size field that specifies its total size in bytes. The size must be used to read and skip this record in the output buffer. For example, following an SQLMA_DB2 snapshot request (GET SNAPSHOT FOR DATABASE MANAGER) on a parallel system. The buffer could contain:
|
The sqlm_collected output structure indicates the number of top-level structures returned in the buffer. Each top-level structure indicates the number of secondary-level structures that may follow it.
Attention: WARNING: It is imperative that you always use the size field for skipping a record in the output buffer. Never use sizeof() on a snapshot record.
Your application should also always read the record type. For some snapshot requests, the order in which records are returned is not guaranteed, and some record may not be returned when a monitor switch is OFF (see sqlmrset - Reset Monitor API and sqlmon - Get/Update Monitor Switches API).
Loop for reading snapshot output buffer
The following code illustrates how an application should be reading and skipping the records returned in the snapshot output buffer.
#include "stdlib.h"
#include "stdio.h"
#include "sqlutil.h"
#include "string.h"
#include "sqlmon.h" // System Monitor interface
//------------------------------------------------------------------------
// PROCESS EACH RECORD THAT MAY BE RETURNED IN THE SNAPSHOT OUTPUT BUFFER
//------------------------------------------------------------------------
while (collected.num_top_level_structs--) {
// Check the record type, (5th byte of any top-level structure)
switch ((unsigned char) *(snap+sizeof(unsigned long))) {
case SQLM_DB2_SS: {
sqlm_db2 *db2_snap;
db2_snap = (sqlm_db2*) snap;
// Process the database manager snapshot
printf("Processing database manager snapshot\n");
// ...
// Skip all its records in the output buffer
snap += db2_snap->size;
// Skip the secondary level entries
while (db2_snap->num_sec_dbm_structs--) snap+=(*(unsigned long*)snap);
} break;
case SQLM_DBASE_SS: {
sqlm_dbase *db_snap = (sqlm_dbase*) snap;
// Process the snapshot ...
printf("Processing database snapshot\n");
// Skip the database snapshot and any secondary structures
snap += db_snap->size;
while (db_snap->num_sec_dbase_structs--) snap+=(*(unsigned long*)snap);
} break;
case SQLM_APPL_SS: {
sqlm_appl *appl_snap = (sqlm_appl*) snap;
printf("Processing application snapshot\n");
while (appl_snap->num_sec_appl_structs--) snap+=(*(unsigned long*)snap);
} break;
case SQLM_APPLINFO_SS: {
sqlm_applinfo *appinfo_snap = (sqlm_applinfo*) snap;
printf("Processing list application\n");
snap+=appinfo_snap->size;
} break;
case SQLM_DCS_APPLINFO_SS: {
sqlm_dcs_applinfo *dcs_snap = (sqlm_dcs_applinfo*) snap;
printf("Processing list dcs application\n");
snap+=dcs_snap->size;
} break;
case SQLM_TABLE_HEADER_SS: {
sqlm_table_header *tabh_snap = (sqlm_table_header*) snap;
int numtabs = tabh_snap->num_tables;
printf("Processing list tables\n");
// ...
// Skip it in the output buffer
snap += tabh_snap->size;
while (numtabs--) {
sqlm_table *tab_snap= (sqlm_table*) snap;
snap += tab_snap->size;
}
} break;
case SQLM_DBASE_LOCK_SS: {
sqlm_dbase_lock *dbase_lock_snap = (sqlm_dbase_lock*) snap;
printf("Processing snapshot for locks on database\n");
dump_dbase_lock(stdout,, dbase_lock_snap);
// this routine provided the following section Printing Snapshot Output Records
// Skip it in the snapshot output buffer
snap = skip_db_lock_snap(dbase_lock_snap);
} break;
case SQLM_APPL_LOCK_SS: {
sqlm_appl_lock* appl_lock_snap = (sqlm_appl_lock*) snap;
printf("Processing snapshot for locks for application\n");
// Skip it in the snapshot output buffer
snap = skip_appl_lock_snap(appl_lock_snap);
} break;
case SQLM_TABLESPACE_HEADER_SS: {
sqlm_tablespace_header *tspace_snap = (sqlm_tablespace_header*) snap;
printf("Processing snapshot for tablespaces\n");
// Skip it in the snapshot output buffer
snap = skip_tspace_snap(tspace_snap);
} break;
default:
printf("%s:%d: Unexpected record type %d in snapshot output buffer!\n",
__FILE__, __LINE__, (unsigned char) *(snap+sizeof(unsigned long)));
} // end check the current snapshot buffer structure
} // end while there are top-level structures in the snapshot output buffer
Note an anomaly to the interface: some top-level data structures do not have a field that returns the number of all generic secondary structures that follows. Instead, they specify the number of structures returned for a specific secondary-level type. Special treatment is required for these snapshot requests. For example, the following routines, referenced in the main loop above, skip lock and table space snapshot requests in the snapshot output buffer.
Routines to skip specific snapshot requests in the snapshot output buffer
//-------------------------------------------------------------------
// Skip a get snapshot for locks for application (SQLM_APPL_LOCK_SS)
//-------------------------------------------------------------------
char * skip_appl_lock_snap(sqlm_appl_lock * appl_lock_snap) {
char *snap= (char*) appl_lock_snap;
int numlocks=appl_lock_snap->num_locks;
snap+=appl_lock_snap->size;
// Skip the lock entries for this application
while (numlocks--) snap+=(*(unsigned long*)snap);
return snap;
} // end skip_appl_lock_snap
//-------------------------------------------------------------------
// Skip a get snapshot for locks for database (SQLM_DBASE_LOCK_SS)
//-------------------------------------------------------------------
char * skip_db_lock_snap(sqlm_dbase_lock * dbase_lock_snap) {
char *snap= (char*) dbase_lock_snap;
int numaplocks=dbase_lock_snap->num_appls;
snap+=(*(unsigned long*)snap);
// Skip the appl lock entries
while (numaplocks--) {
snap = skip_appl_lock_snap((sqlm_appl_lock*) snap);
}
return snap;
} // end skip_db_lock_snap
//-------------------------------------------------------------------
// Skip a get snapshot for table spaces (SQLM_DBASE_LOCK_SS)
//-------------------------------------------------------------------
char * skip_tspace_snap(sqlm_tablespace_header * ts_header) {
char *snap= (char*) ts_header;
int numtspaces = ts_header->num_tablespaces;
snap+=ts_header->size; // Skip the header
// Skip the sqlm_tablespace entries
while (numtspaces--) snap+=(*(unsigned long*)snap);
return snap;
} // end skip_db_lock_snap
Printing Snapshot Output Records
Attention: No string is NULL-terminated.
All strings returned in snapshot output records are blank-padded up to their maximum length.
The following example illustrates how a GET SNAPSHOT FOR LOCKS on database can be printed.
//------------------------------------------------------------------------------
// Print a Blank Padded String of maximum length SZ
//------------------------------------------------------------------------------
// note: strings returned by sqlmonss are NOT NULL-TERMINATED, they are all
// blank padded up to some maximum length.
//------------------------------------------------------------------------------
#define dump_BPSTRING(fp, str, SZ) \
{ \
int k=0; \
while (str[k]!=' '&&k<SZ) k++; \
if (k<SZ) str[k]='\0'; \
fprintf(fp, #str": %0.*s\n", SZ, str); \
}
//------------------------------------------------------------------------------
// Map Application Status to a printable string
//------------------------------------------------------------------------------
// note: These #define may be found in sqlmon.h
//------------------------------------------------------------------------------
char* appl_status_string(int val) {
switch (val) {
case SQLM_CONNECTPEND: return "SQLM_CONNECTPEND";
case SQLM_CONNECTED: return "SQLM_CONNECTED";
case SQLM_UOWEXEC: return "SQLM_UOWEXEC";
case SQLM_UOWWAIT: return "SQLM_UOWWAIT";
case SQLM_LOCKWAIT: return "SQLM_LOCKWAIT";
case SQLM_COMMIT_ACT: return "SQLM_COMMIT_ACT";
case SQLM_ROLLBACK_ACT: return "SQLM_ROLLBACK_ACT";
case SQLM_RECOMP: return "SQLM_RECOMP";
case SQLM_COMP: return "SQLM_COMP";
case SQLM_INTR: return "SQLM_INTR";
case SQLM_DISCONNECTPEND: return "SQLM_DISCONNECTPEND";
case SQLM_TPREP: return "SQLM_TPREP";
case SQLM_THCOMT: return "SQLM_THCOMT";
case SQLM_THABRT: return "SQLM_THABRT";
case SQLM_TEND: return "SQLM_TEND";
case SQLM_CREATE_DB: return "SQLM_CREATE_DB";
case SQLM_RESTART: return "SQLM_RESTART";
case SQLM_RESTORE: return "SQLM_RESTORE";
case SQLM_BACKUP: return "SQLM_BACKUP";
case SQLM_LOAD: return "SQLM_LOAD";
case SQLM_UNLOAD: return "SQLM_UNLOAD";
case SQLM_IOERROR_WAIT: return "SQLM_IOERROR_WAIT";
case SQLM_QUIESCE_TABLESPACE: return "SQLM_QUIESCE_TABLESPACE";
}
return "";
} // end of appl_status_string
//------------------------------------------------------------------------------
// Map lock_object_type to a printable string
//------------------------------------------------------------------------------
char* lock_object_type_string(int val) {
char *result="";
switch (val) {
case SQLM_TABLE_LOCK: result = "SQLM_TABLE_LOCK";
case SQLM_ROW_LOCK: result = "SQLM_ROW_LOCK";
case SQLM_INTERNAL_LOCK: result = "SQLM_INTERNAL_LOCK";
case SQLM_TABLESPACE_LOCK: result = "SQLM_TABLESPACE_LOCK";
case 0: result = "No lock wait";
}
return result;
} // end of lock_object_type_string
//------------------------------------------------------------------------------
// Map lock_mode to a printable string
//------------------------------------------------------------------------------
char* lock_mode_string(int val) {
char result="";
switch (val) {
case SQLM_LNON: result = "NO";
case SQLM_LOIS: result = "IS";
case SQLM_LOIX: result = "IX";
case SQLM_LOOS: result = "S";
case SQLM_LSIX: result = "SIX";
case SQLM_LOOX: result = "X";
case SQLM_LOIN: result = "IN";
case SQLM_LOOZ: result = "Z";
case SQLM_LOOU: result = "U";
case SQLM_LONS: result = "NS";
case SQLM_LONX: result = "NX";
}
return result;
}
//------------------------------------------------------------------------------
// Map lock_status to a printable string
//------------------------------------------------------------------------------
inline char* lock_status_string(int val) {
switch (val) {
case SQLM_LRBGRNT: result = "Granted";
case SQLM_LRBCONV: result = "Converting";
}
return result;
}
//---------------------------------------------------------------------------
// Print an sqlm_dbase_lock
//---------------------------------------------------------------------------
void dump_sqlm_dbase_lock(FILE* fp, sqlm_dbase_lock* db_lock) {
fprintf(fp,"\nsqlm_dbase_lock contains: \n");
fprintf(fp,"db_lock->info_type: %s\n", "SQLM_DBASE_LOCK_SS");
fprintf(fp,"db_lock->locks_held: %ld\n", db_lock->locks_held);
fprintf(fp,"db_lock->appls_cur_cons: %ld\n", db_lock->appls_cur_cons);
fprintf(fp,"db_lock->num_appls: %ld\n", db_lock->num_appls);
fprintf(fp,"db_lock->locks_waiting: %ld\n", db_lock->locks_waiting);
dump_BPSTRING(fp, db_lock->input_db_alias, SQLM_DBPATH_SZ);
dump_BPSTRING(fp, db_lock->db_name, SQLM_IDENT_SZ);
dump_BPSTRING(fp, db_lock->db_path, SQLM_DBPATH_SZ);
} // end of dump_sqlm_dbase_lock
//------------------------------------------------------------------------------
// Print an sqlm_appl_lock
// routine referred to in earlier section 'Loop for reading snapshot output buffer'
//------------------------------------------------------------------------------
void dump_sqlm_appl_lock(FILE* fp, sqlm_appl_lock* appl_lock) {
fprintf(fp,"\nsqlm_appl_lock contains: \n");
fprintf(fp,"appl_lock->info_type: %s\n", "SQLM_APPL_LOCK_SS");
fprintf(fp,"appl_lock->agent_id: %6.6ld\n", appl_lock->agent_id);
fprintf(fp,"appl_lock->appl_status: %s\n",
appl_status_string(appl_lock->appl_status));
fprintf(fp,"appl_lock->codepage_id: %ld\n", appl_lock->codepage_id);
fprintf(fp,"appl_lock->locks_held: %ld\n", appl_lock->locks_held);
fprintf(fp,"appl_lock->num_locks: %ld\n", appl_lock->num_locks);
// Print the status change time, only if non-zero
if (appl_lock->status_change_time.seconds) {
fprintf(fp,"appl_lock->status_change_time: %s\n",
ctime((time_t*) &appl_lock->status_change_time.seconds));
} // end if status changed
dump_BPSTRING(fp, appl_lock->appl_id, SQLM_APPLID_SZ);
dump_BPSTRING(fp, appl_lock->sequence_no, SQLM_SEQ_SZ);
dump_BPSTRING(fp, appl_lock->appl_name, SQLM_IDENT_SZ);
dump_BPSTRING(fp, appl_lock->auth_id, SQLM_IDENT_SZ);
dump_BPSTRING(fp, appl_lock->client_db_alias, SQLM_IDENT_SZ);
// The following information is returned only if the application is in
// a lock wait (otherwise it is zeroed, or set to blank spaces if a string):
if (appl_lock->appl_status==SQLM_LOCKWAIT) {
fprintf(fp,"appl_lock->lock_object_name: %ld\n",
appl_lock->lock_object_name);
fprintf(fp,"appl_lock->agent_id_holding_lk: %6.6ld\n",
appl_lock->agent_id_holding_lk);
fprintf(fp,"appl_lock->lock_object_type: %s\n",
lock_object_type_string( appl_lock->lock_object_type));
fprintf(fp,"appl_lock->table_file_id: %ld\n", appl_lock->table_file_id);
dump_BPSTRING(fp, appl_lock->appl_id_holding_lk, SQLM_APPLID_SZ);
dump_BPSTRING(fp, appl_lock->sequence_no_holding_lk, SQLM_SEQ_SZ);
dump_BPSTRING(fp, appl_lock->table_name, SQLM_IDENT_SZ);
dump_BPSTRING(fp, appl_lock->table_schema, SQLM_IDENT_SZ);
dump_BPSTRING(fp, appl_lock->tablespace_name, SQLM_IDENT_SZ);
} // end if this application is in a lock_wait
} // end of dump_sqlm_appl_lock
//------------------------------------------------------------------------------
// Print an sqlm_lock
//------------------------------------------------------------------------------
void dump_sqlm_lock(FILE* fp, sqlm_lock* lock) {
fprintf(fp,"\nsqlm_lock contains: \n");
fprintf(fp,"lock->info_type: %s\n", "SQLM_LOCK_SS");
fprintf(fp,"lock->lock_object_type: %s\n",
lock_object_type_string(lock->lock_object_type));
// Print the object of this lock
switch (lock->lock_object_type) {
case SQLM_ROW_LOCK:
case SQLM_TABLE_LOCK:
dump_BPSTRING(fp, lock->table_name, SQLM_IDENT_SZ);
dump_BPSTRING(fp, lock->table_schema, SQLM_IDENT_SZ);
fprintf(fp,"lock->table_file_id: %ld\n", lock->table_file_id);
break;
case SQLM_TABLESPACE_LOCK:
dump_BPSTRING(fp, lock->tablespace_name, SQLM_IDENT_SZ);
break;
case SQLM_INTERNAL_LOCK:
break;
}
fprintf(fp,"lock->lock_mode: %s\n", lock_mode_string(lock->lock_mode));
fprintf(fp,"lock->lock_status: %s\n",lock_status_string(lock->lock_status));
fprintf(fp,"lock->lock_object_name: %ld\n", lock->lock_object_name);
} // end of dump_sqlm_lock
Sample Programs
See Also
sqlmon - Get/Update Monitor Switches API
Purpose
Estimates the buffer size needed by sqlmonss - Get Snapshot API.
Scope
This API only affects the instance to which the calling application is attached.
Authorization
One of the following:
Required Connection
Instance. To obtain information from a remote instance (or a different local instance), it is necessary to first attach to that instance. If an attachment does not exist, an implicit instance attachment is made to the node specified by the DB2INSTANCE environment variable.
API Include File
C API Syntax
/* File: sqlmon.h */
/* API: Estimate Database System Monitor Buffer Size */
/* ... */
int SQL_API_FN
sqlmonsz (
unsigned long version,
_SQLOLDCHAR *reserved,
sqlma *sqlma_ptr,
unsigned long *buff_size,
struct sqlca *sqlca);
/* ... */
|
API Parameters
If requesting data for a version higher than the current server, the database monitor only returns data for its level.
| Note: | If SQLM_DBMON_VERSION1 is specified as the version, the APIs cannot be run remotely. |
Comments
The size returned by sqlmonsz is always a little larger than what is actually required.
This function generates a significant amount of overhead (it basically takes a snapshot that does not return any data). Allocating and freeing memory dynamically for every sqlmonss call is also expensive. If calling sqlmonss repeatedly, for example, when sampling data over a period of time, it may be preferable to allocate a buffer of fixed size (for example 32K), possibly from the stack, and continue reusing that same buffer, rather than call sqlmonsz. If SQLM_RC_BUFFER_FULL is returned then you can call sqlmonsz and allocate a new larger buffer.
If the database system monitor finds no active databases or applications, it may return a buffer size of zero (if, for example, lock information related to a database that is not active is requested). Verify that the estimated buffer size returned by this API is non-zero before calling sqlmonss - Get Snapshot API.
See Also
sqlmon - Get/Update Monitor Switches API
Code Sample
The following example estimates the buffer size required when issuing a snapshot for locks, tables, and database level information.
/*
Database Monitor - Estimate Buffer Size for Snapshot
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlca.h"
#include "sqlutil.h"
#include "sqlmon.h"
main () {
char *dbname = "SAMPLE"; // Name of the database to monitor
int rc;
struct sqlca sqlca;
unsigned long buffer_sz;
//--------------------------------------------------------------------------
// Request SQLMA_DBASE, SQLM_DBASE_TABLES, and SQLMA_DBASE_LOCKS in sqlma
//--------------------------------------------------------------------------
struct sqlma* sqlma = (struct sqlma *) malloc(SQLMASIZE(3));
sqlma->obj_num = 3;
sqlma->obj_var[0].obj_type = SQLMA_DBASE;
strcpy(sqlma->obj_var[0].object, dbname);
sqlma->obj_var[1].obj_type = SQLMA_DBASE_LOCKS;
strcpy(sqlma->obj_var[1].object, dbname);
sqlma->obj_var[2].obj_type = SQLMA_DBASE_TABLES;
strcpy(sqlma->obj_var[2].object, dbname);
//--------------------------------------------------------------------------
// Estimate Buffer size required for this request
//--------------------------------------------------------------------------
sqlmonsz(SQLM_DBMON_VERSION5, NULL, sqlma, &buffer_sz, &sqlca);
if (sqlca.sqlcode) { // note: Positive return codes indicate a Warning
// get and display a printable error message
char msg[1024];
sqlaintp (msg, sizeof(msg), 60, &sqlca);
printf("%s", msg);
}
else printf ("\nBuffer size required for this snapshot is: %d\n",buffer_sz);
//--------------------------------------------------------------------------
// ...Take the Snapshot...
//--------------------------------------------------------------------------
{
char* buffer_ptr;
sqlm_collected collected;
buffer_ptr = (char *) malloc(buffer_sz); // Allocate the buffer
rc = sqlmonss(SQLM_DBMON_VERSION5, NULL, sqlma, buffer_sz, buffer_ptr,
&collected, &sqlca);
// .... Process snapshot output in buffer_ptr ...
delete buffer_ptr; // Free the buffer
}
}
Purpose
Resets the database system monitor data of a specified database, or of all active databases for the application issuing the call.
Scope
This API only affects the application making the call.
Authorization
One of the following:
Required Connection
Instance. To reset the monitor switches for a remote instance (or a different local instance), it is necessary to first attach to that instance.
API Include File
sqlmon.h
C API Syntax
/* File: sqlmon.h */
/* API: Reset Monitor */
/* ... */
int SQL_API_FN
sqlmrset (
unsigned long version,
_SQLOLDCHAR *reserved,
unsigned long reset_all,
_SQLOLDCHAR *db_alias,
struct sqlca *sqlca);
/* ... */
|
API Parameters
Sample Programs
Comments
Each process (attachment) has its own private view of the monitor data. If one user resets, or turns off a monitor switch, other users are not affected. When an application first calls any database monitor function, it inherits the default switch settings from the database manager configuration file.These settings can be overridden with sqlmon - Get/Update Monitor Switches API.
If all active databases are reset, some database manager information is also reset to maintain the consistency of the data that is returned.
This API cannot be used to selectively reset specific data items or specific monitor groups. However a specific group can be reset by turning its switch OFF and then ON, using sqlmon - Get/Update Monitor Switches API.
See Also
sqlmon - Get/Update Monitor Switches API
Code Sample
The following example resets the resettable data elements for a single database.
/*
Database Monitor - Reset Data Elements
*/
#include <stdio.h>
#include "sqlca.h"
#include "sqlutil.h"
#include "sqlmon.h"
//------------------------------------------------------------------------------
// Reset monitored data for a single database
//------------------------------------------------------------------------------
struct sqlca sqlca;
sqlmrset(SQLM_DBMON_VERSION5, NULL, SQLM_OFF, "SAMPLE", &sqlca);
if (sqlca.sqlcode) { // note: Positive return codes indicate a Warning
// get and display a printable error message
char msg[1024];
sqlaintp (msg, sizeof(msg), 60, &sqlca);
printf("%s", msg);
} // end if reset was successful
else printf("Database Monitor Reset for '%s' was successful!\n", argv[1]);
Purpose
Turns one or more database monitor recording switches on or off.
The database manager records a base set of information at all times. Users who require more than this basic information can turn on the appropriate switches, but at a cost to system performance. Switches control information that is expensive to collect. See Chapter 3. "Database System Monitor Data Elements" to determine if a switch is required for a particular data element.
Authorization
One of the following:
Required Connection
Instance.
Format
>>-UPDATE MONITOR SWITCHES USING--switch-name--+-ON--+---------><
+-OFF-+
|
Parameters
Comments
To view the switch settings for your session, use GET MONITOR SWITCHES Command.
To clear the information related to a particular switch, set the switch off, then on.
When the application starts, it inherits the settings in the database manager configuration. See GET DATABASE MANAGER CONFIGURATION in the Command Reference and dft_mon_xxx configuration parameters.
See Also