SQL access to DB2 monitoring data

Document options Document options requiring JavaScript are not displayed New site feature Check out our new article design and features. Tell us what you think. Rate this page Help us improve this content

Level: Introductory

Dirk deRoos (dderoos@ca.ibm.com), DB2 Information Development, IBM Toronto Lab

08 May 2003

DB2 UDB Version 8.1 features the capability to capture snapshots of database system monitor data using SQL table functions. This article outlines the capabilities and details how to implement this monitoring technique.

Introduction

One means of monitoring the status of an IBM® DB2® Universal Database™ (UDB) instance is by capturing snapshots of database system monitor data. A snapshot of monitor data describes the state of database activity at the particular point in time that a snapshot is taken. This approach of database system monitoring is particularly useful in evaluating the performance of a database system, and in detecting potential problems.

DB2 UDB Version 8.1 for UNIX®, Linux, and Windows® features the capability to capture snapshots using SQL table functions. This represents a significant improvement in the ease with which snapshots can be captured and stored for fast and flexible retrieval.

Back to top

Types of snapshots

There are a number of available snapshot monitor SQL table functions (referred to as snapshot table functions), each of which captures a particular type of monitor data. Table 1 lists the more commonly used snapshot table functions.

Note: For details regarding the individual snapshot table functions (such as the columns returned and their data types), see the DB2 documentation topics that are linked from the snapshot table function names. Also, for a complete list, see the DB2 documentation topic Snapshot monitor SQL table functions.

Table 1. Snapshot table functions

Back to top

Snapshot monitor data organization

All the snapshot table functions return a table of monitor data, where each row represents an instance of the database object being monitored, and each column represents a monitor element. A monitor element represents a specific attribute of the state of the database system.

All the monitor elements available for snapshot monitoring are listed in the following DB2 documentation topic: Snapshot monitor logical data groups and monitor elements. The column names of the snapshot table functions correspond directly to the monitor element names listed in this topic.

Back to top

Monitor switches

A number of the monitor elements that can be captured by the snapshot table functions are under monitor switch control. This is indicated in the table of snapshot table functions when a particular monitor switch is mentioned in the description for some functions. DB2 contains monitor switches to provide DBAs with the option of suppressing the collection of sets of monitor elements so to optimize database performance. There are also a large number of monitor elements that are not under switch control.

Table 2 lists all the monitor switches and their database manager level and database level identifiers.

Table 2. Monitor switches

At the database manager level, monitor switches are set in the dft_monswitches database manager configuration parameter. To see the settings of all the monitor switch settings use the GET DATABASE MANAGER MONITOR SWITCHES command. To enable or disable a monitor switch setting at the database manager level, use the UPDATE DBM CFG command, identifying the individual monitor switch to be altered. For example, the following command turns off the DFT_MON_TIMESTAMP monitor switch, ending the collection of timestamp monitor data:

 db2 update dbm cfg 

 using DFT_MON_TIMESTAMP off

 

Each application connected to a database has its own set of monitor switches independent of the database manager and other applications. Applications inherit their monitor switch settings from the database manager when connecting to a database. To see the settings of all the monitor switch settings for an application, use the GET MONITOR SWITCHES command. You can alter monitor switch settings for an application using the UPDATE MONITOR SWITCHES command. For example, the following command turns on the LOCK monitor switch, enabling the collection of monitor elements used by the SNAPSHOT_LOCK snapshot table function:

 db2 update monitor switches 

 using LOCK on

 

Back to top

Means of accessing snapshot data

There are two means of accessing monitor data with the snapshot table functions: direct access and file access.

With direct access to snapshot data, an authorized user issues queries with snapshot table functions and receives result sets containing monitor data. To be an authorized user (for snapshot monitoring tasks) means that you must have SYSADM, SYSCTRL, or SYSMAINT authority.

With file access, authorized users can make specific collections of snapshot data available for all users. To do this, authorized users call the SNAPSHOT_FILEW stored procedure, identifying the snapshot request type and the affected partition and database. The SNAPSHOT_FILEW stored procedure saves the monitor data into a file on the database server. Any database user can then issue queries with corresponding snapshot table functions (using parameters that indicate file access). The monitor data they receive is pulled from the files generated by the SNAPSHOT_FILEW stored procedure. Therefore, users will only receive results for snapshot table functions for which an authorized user has used the SNAPSHOT_FILEW stored procedure.

Capturing monitor data snapshots with direct access

To capture a direct access snapshot using a snapshot table function, complete the following steps:

1. Connect to a database. This can be any database in the instance you need to monitor. To be able to issue an SQL query with a snapshot table function, you must be connected to a database.

   For example:

   db2 connect to sample

   
   

   Note: The examples presented in this section assume that you have created the sample database. If you have not done so, you can create it by running the db2sampl utility.

2. Determine the type of snapshot you need to capture, and the database and partition you need to monitor. In addition to collecting this information, turn on any applicable monitor switches (you can determine this by checking the snapshot table function descriptions in Table 1. Snapshot table functions).

   If you, for example, are intending to capture a snapshot of table activity data (with function SNAPSHOT_TABLE), you will need to activate the TABLE monitor switch:

   db2 update dbm cfg using DFT_MON_TABLE on

   
   
3. Issue a query with the desired snapshot table function.

   For example, here is a query that captures a snapshot of table activity information about the SAMPLE database for the current connected partition:

   db2 "select * from table (SNAPSHOT_TABLE('sample', -1)) as T"

   
   

   The snapshot table functions have two input parameters:

   • a VARCHAR(255) for the database name. If you enter NULL, the name of the currently connected database is used.

     Note 1: This parameter does not apply to snapshot table functions that only return database manager information (for example, SNAPSHOT_DBM). These snapshot table functions only have a parameter for partition number.

     Note 2: For the following list of snapshot table functions, if you enter a NULL for the currently connected database, you will get snapshot information for all databases in the instance: SNAPSHOT_DATABASE, SNAPSHOT_APPL, SNAPSHOT_APPL_INFO, SNAPSHOT_LOCKWAIT, SNAPSHOT_STATEMENT, SNAPSHOT_BP.

   • a SMALLINT for the partition number. For the partition number parameter, enter the integer (a value between 0 and 999) corresponding to the partition number you need to monitor. To capture a snapshot for the currently connected partition, enter a value of -1 or a NULL. To capture a global snapshot, enter a value of -2.

The following example will demonstrate the steps presented previously. In this scenario, there are three applications connected to the Sample database. Two are connected locally, and one is connected from a remote client. One of the local applications has made some updates to records in the STAFF table of the Sample database. Meanwhile, the remote application has made an insertion of a record into the SALES table of the Sample database. The second local application is used to perform snapshot monitoring activities.

The following is the sequence of commands and statements for each of the three applications:

Monitoring application:
Set the DFT_MON_TABLE monitor switch.

 db2 update dbm cfg using 

 DFT_MON_TABLE on

 

Application 1 (remote application):
Insert a record into the SALES table.

 db2 "insert into sales values 

 ('03/20/2003','LEE', 'Atlantic',

 5)"

 

Application 2 (local application):
Update 12 records in the STAFF table.

 db2 "update staff set salary = 

 salary * 1.1 where JOB = 

 'Clerk'"

 

Monitoring application:
Capture a snapshot of information about the tables in the database Sample.

 db2 connect to sample

 

 db2 "select * from table

 (SNAPSHOT_TABLE('sample',-1)) 

 as T"

 

The result set for the previous query has a number of columns, so it can be difficult to read from the command line. If you are only interested in a few specific monitor elements, you can restrict the select statement to the relevant monitor element columns. For example, here is such a query and its corresponding result set:

Monitoring application:

 db2 "select snapshot_timestamp, 

 table_name, rows_written, 

 rows_read from table 

 (SNAPSHOT_TABLE('sample',

 -1))as T"

 

 SNAPSHOT_TIMESTAMP         TABLE_NAME      ROWS_WRITTEN ROWS_READ

 -------------------------- --------------- ------------ ---------

 2003-04-07-09.33.27.468598 SYSROUTINES                0         4

 2003-04-07-09.33.27.468598 STAFF                     12        47

 2003-04-07-09.33.27.468598 SALES                      1         0

 2003-04-07-09.33.27.468598 SYSTABLES                  0         2

 2003-04-07-09.33.27.468598 SYSPLAN                    0         1

 2003-04-07-09.33.27.468598 SYSEVENTMONITORS           0         1

 2003-04-07-09.33.27.468598 SYSDBAUTH                  0         5

 2003-04-07-09.33.27.468598 SYSBUFFERPOOLS             0         1

 2003-04-07-09.33.27.468598 SYSTABLESPACES             0         3

 2003-04-07-09.33.27.468598 SYSVERSIONS                0         1

 

   10 record(s) selected.

Storing the results of monitor snapshots captured at regular intervals can provide a wealth of information useful for determining trends in the status and behavior of your DBMS. A simple means of doing this is creating a table (or tables) for your monitor data in the database of an instance you are monitoring. For example, the following table that is being created will store monitor data about the numbers of applications connected to databases in the instance.

Monitoring application:

 db2 "create table instance_snap 

 (snap_time timestamp NOT NULL, 

 local_cons bigint, 

 rem_cons_in bigint)"

 

The following statement captures a snapshot of the number of connections to databases in the instance, along with a timestamp, and inserts this data into the INSTANCE_SNAP table, created above.

Monitoring application:

 db2 "insert into instance_snap 

 select snapshot_timestamp, 

 local_cons, rem_cons_in from 

 table (snapshot_dbm

 (-1))as snapshot_dbm"

 

 db2 "select * from instance_snap"

 

 

 SNAP_TIME                  LOCAL_CONS    REM_CONS_IN

 -------------------------- ------------- ------------

 2003-04-07-09.40.49.867659             2            1

 

   1 record(s) selected.

 

The above output indicates there are two local applications and one remote application connected to the database Sample.

Capturing monitor data snapshots with file access

Using the snapshot table functions with file access presents a safe means of providing all users with access to snapshot monitor data. There are some limitations to this approach:

• The snapshot monitor data available from the SNAPSHOT_FILEW files is only as recent as the last time an authorized user called the SNAPSHOT_FILEW stored procedure. You can ensure that recent snapshot monitor data is available by making calls to the SNAPSHOT_FILEW stored procedure at regular intervals. For example, you can create a DB2 CLP script with calls to the SNAPSHOT_FILEW stored procedure. Using DB2's Task Center tool, you can schedule the script to run, say, every Sunday at midnight.
• Users issuing queries with the snapshot table functions cannot identify a database or partition to monitor. The database name and partition number identified by the authorized user issuing the SNAPSHOT_FILEW calls determines the contents of the files accessible by the snapshot table functions.
• In providing open access to snapshot monitor data, sensitive information such as the list of connected users and the SQL statements they have submitted to the database is available to all users who have the execution privilege for the snapshot table functions (this privilege is assigned to PUBLIC by default). No actual data from databases or user passwords can be exposed using the snapshot monitor table functions.

When calling the SNAPSHOT_FILEW stored procedure, in addition to identifying the database and partition to be monitored, you need to specify a snapshot request type number. Each of these numbers represents a set of snapshot monitor information that can be accessed by one or more snapshot table functions. Table 3 lists the snapshot table functions and their corresponding snapshot request type numbers.

Table 3. Snapshot request types

To capture a snapshot to a file using the SNAPSHOT_FILEW stored procedure, complete the following steps:

1. Connect to a database. This can be any database in the instance you need to monitor. To be able to issue an SQL query with a snapshot table function, you must be connected to a database.

   For example:

   db2 connect to sample

   
   

   Note: The examples presented in this section assume that you have created the sample database. If you have not done so, you can create it by running the db2sampl utility.

2. Determine the type of snapshot the database users need to capture, and the database and partition they need to monitor. In addition to collecting this information, turn on any applicable monitor switches (you can determine this by checking the snapshot table function descriptions in Table 1. Snapshot table functions).
3. Call the SNAPSHOT_FILEW stored procedure with the appropriate parameter settings for the snapshot request type, database, and partition.

   For example, here is a call that will capture a snapshot of application information about the SAMPLE database for the current connected partition:

   db2 "call SNAPSHOT_FILEW(6, 'sample',-1)"

   
   

   The SNAPSHOT_FILEW stored procedure has three input parameters:

   • a SMALLINT for the snapshot request type (see Table 3. Snapshot request types, which provides a cross-reference of the snapshot table functions and their corresponding snapshot request type numbers).
   • a VARCHAR(128) for the database name. If you enter NULL, the name of the currently connected database is used.

     Note: This parameter does not apply to snapshot table functions that only return database manager information (for example, SNAPSHOT_DBM). These snapshot table functions only have parameters for the snapshot request type number and the partition number.

   • a SMALLINT for the partition number. For the partition number parameter, enter the integer (a value between 0 and 999) corresponding to the partition number you need to monitor. To capture a snapshot for the currently connected partition, enter a value of -1 or a NULL. To capture a global snapshot, enter a value of -2.

Accessing snapshot monitor data from SNAPSHOT_FILEW files

To access snapshot data from SNAPSHOT_FILEW files using a snapshot table function, complete the following steps:

1. Connect to a database. This can be any database in the instance you need to monitor. To be able to issue an SQL query with a snapshot table function, you must be connected to a database.

   For example:

   db2 connect to sample

   
   
2. Determine the type of snapshot you need to capture. For every snapshot table function with which you intend to access SNAPSHOT_FILEW files, an authorized user must have issued a SNAPSHOT_FILEW stored procedure call with the corresponding snapshot request type numbers. Also, you cannot identify a database name and partition number when accessing files generated by SNAPSHOT_FILEW because these are specified by the user issuing the SNAPSHOT_FILEW calls.
3. Issue a query with the desired snapshot table function.

   Here is an example of a query that captures a snapshot of information about the applications connected to the SAMPLE database on the current connected partition:

   db2 "select * from table SNAPSHOT_APPL_INFO((CAST (NULL AS VARCHAR(1)), CAST (NULL AS INTEGER))) AS as A"

   
   

   Note that you must enter NULL values for the database name and partition number parameters as these are determined in the call of the SNAPSHOT_FILEW stored procedure. Also, the database name parameter does not apply to the database manager level snapshot table functions because they only have a parameter for partition number.

Back to top

Conclusion

With the snapshot table functions, you can easily capture snapshots of database system monitor data using SQL. The capability of storing selected sets of monitor data in SQL tables makes many monitoring applications possible. The following list presents some examples:

• Make selected sets of database system snapshot monitor data available to users without SYSADM, SYSMAINT, or SYSCTRL privileges.
• Capture and store database system snapshots in regular time intervals.
• Conduct statistical analyses of database system usage and performance over particular time periods.

Each of the above can easily be implemented using nothing more than SQL and the DB2 Task Center. Try it for yourself to increase your knowledge of what's going on with your database and enhance your ability to administer and tune for highest performance.

Resources

• "Tuning DB2 Universal Database using the Statement Event Monitor" (developerWorks, March 2003): Find further information about monitoring DB2 UDB.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

• Participate in developerWorks blogs and get involved in the developerWorks community.

About the author

		Dirk deRoos (BA, BCS) is a technical writer on the DB2 Information Development team. He has co-authored The Official Guide to DB2 Version 8.1.2 (Prentice-Hall, 2003), and written chapters for DB2: The Complete Reference (Osborne/McGraw-Hill, 2001). Dirk specializes in DB2 application development, with a focus on .NET.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top