DB2 Version 9.7 for Linux, UNIX, and Windows

Capturing database system snapshots using snapshot administrative views and table functions

Authorized users can capture snapshots of monitor information for a DB2® instance by using snapshot administrative views or snapshot table functions. The snapshot administrative views provide a simple means of accessing data for all database partitions of the connected database. The snapshot table functions allow you to request data for a specific database partition, globally aggregated data, or data from all database partitions. Some snapshot table functions allow you to request data from all active databases.

About this task

While new snapshot table functions may be required in future releases if new monitor data is available, the set of snapshot administrative views will remain the same with new columns added to the view, making the administrative views a good choice for application maintenance over time.

Each snapshot view returns a table with one row per monitored object per database partition with each column representing a monitor element. Each table function returns a table with one row per monitored object for the specified partition. The column names of the returned table correlate with the monitor element names.

For example, a snapshot of general application information for the SAMPLE database is captured as follows using the SNAPAPPL administrative view:

SELECT * FROM SYSIBMADM.SNAPAPPL

You can also select individual monitor elements from the returned table. For example, the following statement returns only the agent_id and appl_id monitor elements:

SELECT agent_id, appl_id FROM SYSIBMADM.SNAPAPPL

Snapshot administrative views and table functions cannot be used in conjunction with either of the following:

Monitor switches commands/APIs
Monitor reset commands/APIs

This restriction includes:

GET MONITOR SWITCHES
UPDATE MONITOR SWITCHES
RESET MONITOR

This limitation is due to the fact that such commands use an INSTANCE ATTACH, while snapshot table functions make use of DATABASE CONNECTs.

To capture a snapshot using a snapshot administrative view:

Before you begin

You must have SYSADM, SYSCTRL, SYSMAINT, or SYSMON authority to capture a database snapshot. To obtain a snapshot of a remote instance, you must first connect to a local database belonging to that instance.

Procedure

To capture a snapshot using a snapshot administrative view:
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 administrative view, you must be connected to a database.
2. Determine the type of snapshot you need to capture. If you want to capture a snapshot for a database other than the currently connected database, or if you want to retrieve data from a single database partition, or global aggregate data, you need to use a snapshot table function instead.
3. Issue a query with the appropriate snapshot administrative view. For example, here is a query that captures a snapshot of lock information for the currently connected database:
```
   SELECT * FROM SYSIBMADM.SNAPLOCK
```
To capture a snapshot using a snapshot table function:
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.
2. Determine the type of snapshot you need to capture.
3. Issue a query with the appropriate snapshot table function. For example, here is a query that captures a snapshot of lock information about the SAMPLE database for the current connected database partition:
```
   SELECT * FROM TABLE(SNAP_GET_LOCK('SAMPLE',-1)) AS SNAPLOCK
```
  The SQL table functions have two input parameters:
  database name
  
  VARCHAR(255). If you enter NULL, the name of the currently connected database is used.
  
  partition number
  
  SMALLINT. For the database partition number parameter, enter the integer (a value between 0 and 999) corresponding to the database partition number you need to monitor. To capture a snapshot for the currently connected database partition, enter a value of -1. To capture a global aggregate snapshot, enter a value of -2. To capture a snapshot from all database partitions, do not specify a value for this parameter.
  Note:
  1. 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:
    - SNAP_GET_DB_V95
    - SNAP_GET_DB_MEMORY_POOL
    - SNAP_GET_DETAILLOG_V91
    - SNAP_GET_HADR
    - SNAP_GET_STORAGE_PATHS
    - SNAP_GET_APPL_V95
    - SNAP_GET_APPL_INFO_V95
    - SNAP_GET_AGENT
    - SNAP_GET_AGENT_MEMORY_POOL
    - SNAP_GET_STMT
    - SNAP_GET_SUBSECTION
    - SNAP_GET_BP_V95
    - SNAP_GET_BP_PART
  2. The database name parameter does not apply to the database manager level snapshot table functions; they have only a parameter for database partition number. The database partition number parameter is optional.