Snapshot monitoring administrative views on DB2 version 10.1 for Linux, UNIX, and Windows

This article demonstrates some of the important and frequently used DB2® snapshot monitoring administrative views in version 10.1. This article is for DB2 database administrators tasked with DB2 monitoring. Snapshot monitoring views are now the preferred means of accessing snapshot data, which represents a huge leap forward by giving administrator access to snapshot data via SQL. All of the information that database administrators have been used to getting from snapshots can be selected from the administrative views.

Share:

Anil Sharma (anil_sharma@in.ibm.com), Database Administrator, IBM

author photoAnil Sharma is lead database administrator for the Cloud Platform team in the Industry Solutions Group at IBM Software Labs in India. He is an IBM Certified Advanced Database Administrator for IBM DB2 and an IBM Certified Solution Designer for IBM InfoSphere Warehouse.



Bharat Purohit (bpurohit@in.ibm.com), Staff Software Engineer, IBM

Author photo of Bharat PurohitBharat Bhushan Purohit has worked on multiple web technologies and has nine-plus years of software industry experience. He is currently working with the cloud platform team as lead developer and has some patents on cloud too.



Sarang Ratnalikar (sratnali@in.ibm.com), Staff Software Engineer, IBM

Author photo of Sarang RatnalikarSarang Deepak Ratnalikar is a Software Engineer and has nine-plus years of industry experience. Currently he is part of the IBM Cloud Platform team and is responsible for different activities like development in J2EE, build, and so on.



13 June 2013

Also available in Chinese

Introduction

The snapshot monitoring administrative views provide a primary, easy-to-use programmatic interface for monitoring DB2 version 10.1 for Linux®, UNIX®, and Windows® through SQL. These views can be invoked from an SQL-based application, a DB2 command line, or a command script. The administrative views will always be based on the most current version of the table functions, and therefore allow for more application portability. Since the columns might vary from one release to the next, it is recommended that specific columns be selected from the administrative views.

Authorized users can capture snapshots of monitor information for a DB2 instance by using snapshot administrative views. The snapshot administrative views provide a simple means of accessing data for all database partitions of the connected database.


Required authorization for snapshot monitoring views

For all built-in views in the SYSIBMADM schema, you need SELECT privilege on the view. You can use the following query to check whether your authorization ID, or a group or a role to which you belong, has SELECT privilege:

SELECT GRANTEE, GRANTEETYPE FROM SYSCAT.TABAUTH
WHERE TABSCHEMA = ’SYSIBMADM’ AND TABNAME = ’view_name’ AND SELECTAUTH <> ’N’

Where view_name is the name of the built-in view. If your authorization ID, or a group or a role to which you belong, is listed in the GRANTEE column, then you have access to the specified built-in view.


Process to capture DB 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.

The following section demonstrates some of the frequently used snapshot administrative views, which are very helpful for an aspirant DB2 DBA for his/her routine database monitoring activities.


Frequently used snapshot monitoring views

NOTE: The following sample SQL statements are executed on test database 'TEST_DB'. The DB2 product used in the testing is DB2 Linux, UNIX, and Windows version 10.1 Enterprise Server Edition.

APPL_PERFORMANCE

As shown in Listing 1, the APPL_PERFORMANCE administrative view displays information about the percentage of rows selected by an application. The information returned is for all database partitions for the currently connected database. This view can be used to look for applications that might be performing large table scans or to look for potentially troublesome queries. It helps you to understand which application might be causing a performance problem.

Listing 1. APPL_PERFORMANCE view
$ db2 "SELECT SNAPSHOT_TIMESTAMP, SUBSTR(AUTHID,1,10) AS AUTHID,
SUBSTR(APPL_NAME, 1, 10) AS APPL_NAME, AGENT_ID, FROM SYSIBMADM.APPL_PERFORMANCE"
                
SNAPSHOT_TIMESTAMP           AUTHID    APPL_NAME   AGENT_ID
---------------------------- --------- ----------- -------------
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          11888
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          12855
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app           9947
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          10572
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          12368
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          11243
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          64539
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          11585
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app           2460
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          12394
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          11861
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          11111
2013-05-06-02.54.57.702679   DB2USR2   db2jcc_app          12907
                
13 record(s) selected.

APPLICATIONS

The APPLICATIONS administrative view returns information about the connected database applications, as shown in Listing 2. The view is an SQL interface for the LIST APPLICATIONS SHOW DETAIL CLP command, but only for the currently connected database.

Listing 2. APPLICATIONS view
$ db2 "SELECT AGENT_ID, SUBSTR(APPL_NAME, 1, 10) AS APPL_NAME, APPL_STATUS
FROM SYSIBMADM.APPLICATIONS WHERE DB_NAME = 'TEST_DB'"
               
AGENT_ID   APPL_NAME    APPL_STATUS
---------- ------------ --------------
20546      db2jcc_app   UOWWAIT
17842      db2jcc_app   UOWWAIT
19638      db2jcc_app   UOWWAIT
18638      db2jcc_app   UOWWAIT
25730      db2jcc_app   UOWWAIT
25230      db2jcc_app   UOWWAIT
22401      db2jcc_app   UOWWAIT
151        db2fw2       CONNECTED
24572      db2jcc_app   UOWWAIT
24914      db2jcc_app   UOWWAIT
164        db2fw15      CONNECTED
               
11 record(s) selected.

Some other useful application monitoring administrative views are SYSIBMADM.SNAPAPPL, SYSIBMADM.SNAPAPPL_INFO.

BP_HITRATIO

The BP_HITRATIO administrative view returns buffer pool hit ratios, including total hit ratio, data hit ratio, XDA hit ratio, and index hit ratio for all buffer pools and all database partitions in the currently connected database, as shown in Listing 3. Buffer pool hit ratios are one of the most fundamental metrics and give an important overall measure of how effectively the system is exploiting memory to avoid disk I/O.

Listing 3. BP_HITRATIO view
$ db2 "SELECT SUBSTR (DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME, 1, 14) AS BP_NAME,
TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT
FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM"
                
DB_NAME   BP_NAME          TOTAL_HIT_RATIO_PERCENT DATA_HIT_RATIO_PERCENT
--------- ---------------- ----------------------- ----------------------
TEST_DB   IBMDEFAULTBP                       99.98                  99.98
TEST_DB   IBMSYSTEMBP4K                          -                      -
TEST_DB   IBMSYSTEMBP8K                          -                      -
TEST_DB   IBMSYSTEMBP16K                         -                      -
TEST_DB   IBMSYSTEMBP32K                         -                      -
                
5 record(s) selected.

BP_READIO

The BP_READIO administrative view returns buffer pool read performance information, as shown in Listing 4. It gives you an indication of the average number of rows that are read from database tables in order to find the rows that qualify. This view can be used to look at each buffer pool to see how effective the pre-fetchers are.

Listing 4. BP_READIO view
$ db2 "SELECT SUBSTR(BP_NAME, 1, 15) AS BP_NAME, TOTAL_PHYSICAL_READS,
AVERAGE_READ_TIME_MS, DBPARTITIONNUM FROM SYSIBMADM.BP_READ_IO ORDER BY DBPARTITIONNUM"
                
BP_NAME         TOTAL_PHYSICAL_READS AVERAGE_READ_TIME_MS DBPARTITIONNUM
--------------- -------------------- -------------------- --------------
IBMDEFAULTBP                14452675                    0              0
IBMSYSTEMBP4K                      0                    -              0
IBMSYSTEMBP8K                      0                    -              0
IBMSYSTEMBP16K                     0                    -              0
IBMSYSTEMBP32K                     0                    -              0
                
5 record(s) selected.

BP_WRITEIO

The BP_WRITEIO administrative view returns buffer pool write performance information per buffer pool, as shown in Listing 5.

Listing 5. BP_WRITEIO view
$ db2 "SELECT SUBSTR(BP_NAME, 1, 15) AS BP_NAME, TOTAL_WRITES, AVERAGE_WRITE_TIME_MS,
DBPARTITIONNUM FROM SYSIBMADM.BP_WRITE_IO ORDER BY DBPARTITIONNUM"
                
BP_NAME         TOTAL_WRITES         AVERAGE_WRITE_TIME_MS DBPARTITIONNUM
--------------- -------------------- --------------------- --------------
IBMDEFAULTBP                  543797                     0              0
IBMSYSTEMBP4K                      0                     -              0
IBMSYSTEMBP8K                      0                     -              0
IBMSYSTEMBP16K                     0                     -              0
IBMSYSTEMBP32K                     0                     -              0
                
5 record(s) selected.

CONTAINER_UTILIZATION

The CONTAINER_UTILIZATION administrative view returns information about table space containers and utilization rates. It retrieves a similar report to the LIST TABLESPACES command on a single partitioned database, as shown in Listing 6.

Listing 6. CONTAINER_UTILIZATION view
$ db2 "SELECT SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME, INT(TBSP_ID) AS TBSP_ID,
INT(CONTAINER_ID) AS CONTAINER_ID, INT(TOTAL_PAGES) AS TOTAL_PAGES,
INT(USABLE_PAGES) AS USABLE_PAGES FROM SYSIBMADM.CONTAINER_UTILIZATION"
                
TBSP_NAME     TBSP_ID   CONTAINER_TYPE   TOTAL_PAGES  USABLE_PAGES
------------- --------- ---------------- ------------ ------------
SYSCATSPACE           0 FILE_EXTENT_TAG        98304         98300
TEMPSPACE1            1 PATH                       1             1
USERSPACE1            2 FILE_EXTENT_TAG     13549568      13549536
                
3 record(s) selected.

TBSP_UTILIZATION

The TBSP_UTILIZATION administrative view returns table space configuration and utilization information, as shown in Listing 7.

Listing 7. TBSP_UTILIZATION view
$ db2 "SELECT TBSP_ID, SUBSTR(TBSP_NAME, 1, 20) as TBSP_NAME, TBSP_TYPE,
TBSP_CONTENT_TYPE, substr(TBSP_STATE,1,20) as TBSP_STATE
FROM SYSIBMADM.TBSP_UTILIZATION"
                
TBSP_ID    TBSP_NAME            TBSP_TYPE  TBSP_CONTENT_TYPE TBSP_STATE
---------- -------------------- ---------- ----------------- ----------
0 SYSCATSPACE          DMS        ANY               NORMAL
1 TEMPSPACE1           SMS        SYSTEMP           NORMAL
2 USERSPACE1           DMS        LARGE             NORMAL
3 SYSTOOLSPACE         DMS        LARGE             NORMAL
4 SYSTOOLSTMPSPACE     SMS        USRTEMP           NORMAL
                
5 record(s) selected.

LOG_UTILIZATION

The LOG_UTILIZATION administrative view returns information about log utilization for the currently connected database. A single row is returned for each database partition, as shown in Listing 8.

Listing 8. LOG_UTILIZATION view
$ db2 "SELECT substr(DB_NAME, 1, 8) as DB_NAME, LOG_UTILIZATION_PERCENT,
TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB FROM SYSIBMADM.LOG_UTILIZATION"
                
DB_NAME  LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB    TOTAL_LOG_AVAILABLE_KB
-------- ----------------------- -------------------- ----------------------
TEST_DB                     0.06                 5829                8353155
                
1 record(s) selected.

SNAPDETAILLOG

The SNAPDETAILLOG administrative view allows you to retrieve snapshot information from the detail_log logical data group for the currently connected database. The view provides information equivalent to the GET SNAPSHOT FOR DATABASE on database-alias CLP command, as shown in Listing 9.

Listing 9. SNAPDETAILLOG view
$ db2 "SELECT SUBSTR(DB_NAME, 1, 8) AS DB_NAME, FIRST_ACTIVE_LOG, LAST_ACTIVE_LOG,
CURRENT_ACTIVE_LOG, CURRENT_ARCHIVE_LOG, DBPARTITIONNUM AS DB_PARTI_NUM
FROM SYSIBMADM.SNAPDETAILLOG"
                
DB_NAME  FIRST_ACTIVE_LOG LAST_ACTIVE_LOG CURRENT_ACTIVE_LOG CURRENT_ARCHIVE_LOG DPN
DBPARTITIONNUM
-------- ---------------- --------------- ------------------ ------------------- ---
TEST_DB              5174            5183            5180                      -   0
                
1 record(s) selected.

LONG_RUNNING_SQL

The LONG_RUNNING_SQL administrative view returns SQL statements executed in the currently connected database. This view can be used to identify long-running SQL statements in the database, as shown in Listing 10.

Listing 10. LONG_RUNNING_SQL view
$ db2 "SELECT SUBSTR(STMT_TEXT, 1, 50) AS STMT_TEXT, AGENT_ID, ELAPSED_TIME_MIN, 
APPL_STATUS FROM SYSIBMADM.LONG_RUNNING_SQL"	
                
STMT_TEXT                                AGENT_ID             ELAPSED_TIME_MIN APPL_STATUS
---------------------------------------- -------------------- ---------------- -----------
select subscriber0_.subscriberId as subs                20546                0 UOWWAIT
select customerbe0_.Id as Id9_, customer                17842                0 UOWWAIT
select seatservic0_.seatId as seatId1_,                 19638                0 UOWWAIT
select customerid0_.customerId as custom                22401                0 UOWWAIT
select table_0.owner, table_0.parentorgi                25164                0 UOWWAIT
select creditcard0_.customerId as custom                26414                0 UOWWAIT
select Id,version,modificationDate ,dele                26085                0 UOWWAIT
select addressbea0_.partyId as partyId1_                19802                0 UOWWAIT
                
8 record(s) selected.

TOP_DYNAMIC_SQL

The TOP_DYNAMIC_SQL administrative view returns the top dynamic SQL statements sortable by number of executions, average execution time, number of sorts, or sorts per statement, as shown in Listing 11. These are the queries that should get focus to ensure they are well tuned.

Listing 11. TOP_DYNAMIC_SQL view
$ db2 "SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS,SORTS_PER_EXECUTION,
SUBSTR(STMT_TEXT, 1, 60) AS STMT_TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL
ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 5 ROWS ONLY"
                
NUM_EXECUTIONS AVERAGE_EXECUTION_TIME_S STMT_SORTS  SORTS_PER_EXECUTION STMT_TEXT
-------------- ------------------------ ---------- -------------------- ------------------
225004596                 0                0                    0 select addressbea0
45396610                  0                0                    0 select rolebeanse0
21122825                  0                0                    0 select subscriber0
20209868                  0                0                    0 select seatservic0
18121900                  0                0                    0 select table_0.id,
                
5 record(s) selected.

SNAPAGENT

The SNAPAGENT administrative view allows you to retrieve agent logical data group application snapshot information for the currently connected database, as shown in Listing 12.

Listing 12. SNAPAGENT view
$ db2 "SELECT SUBSTR(DB_NAME, 1, 8), AGENT_ID, LOCK_TIMEOUT_VAL FROM SYSIBMADM.SNAPAGENT"
                
1        AGENT_ID             LOCK_TIMEOUT_VAL
-------- -------------------- --------------------
TEST_DB                20546                   30
TEST_DB                17842                   30
TEST_DB                19638                   30
TEST_DB                18638                   30
TEST_DB                26730                   30
TEST_DB                22401                   30
TEST_DB                  151                    3
TEST_DB                25164                   30
TEST_DB                  164                    3
TEST_DB                26414                   30
                
10 record(s) selected.

SNAPDB

The SNAPDB administrative view helps you to retrieve the status, platform, location and connect time as an aggregate view across all database partitions of the currently connected database, as shown in Listing 13.

Listing 13. SNAPDB view
$ db2 "SELECT SUBSTR(DB_NAME, 1, 20) AS DB_NAME, DB_STATUS, SERVER_PLATFORM, DB_LOCATION,
DB_CONN_TIME FROM SYSIBMADM.SNAPDB"
                
DB_NAME  DB_STATUS  SERVER_PLATFORM DB_LOCATION  DB_CONN_TIME
-------- ---------- --------------- ------------ --------------------------
TEST_DB  ACTIVE     LINUXX8664      LOCAL        2013-04-30-13.05.42.495027
                
1 record(s) selected.

SNAPDBM

The SNAPDBM administrative view returns snapshot monitoring information on database instance level, as shown in Listing 14. It is equivalent to the GET SNAPSHOT FOR DBM command.

Listing 14. SNAPDBM view
$ db2 "SELECT DB2_STATUS, DB2START_TIME, LAST_RESET, LOCAL_CONS, REM_CONS_IN,
(AGENTS_CREATED_EMPTY_POOL/AGENTS_FROM_POOL) AS AGENT_USAGE FROM SYSIBMADM.SNAPDBM"
                
DB2_STATUS DB2START_TIME              LAST_RESET  LOCAL_CONS REM_CONS_IN AGENT_USAGE
---------- -------------------------- ----------- ---------- ----------- -----------
ACTIVE     2013-04-30-13.03.50.552784 -                    2         203           0
                
1 record(s) selected.

SNAPDBM_MEMORY_POOL

The SNAPDBM_MEMORY_POOL administrative view retrieves database manager-level memory usage information. It returns a list of the memory pools and their current size for the database manager of the connected database, as shown in Listing 15.

Listing 15. SNAPDBM_MEMORY_POOL view
$ db2 "SELECT POOL_ID,POOL_CUR_SIZE,POOL_WATERMARK,POOL_CONFIG_SIZE
FROM SYSIBMADM.SNAPDBM_MEMORY_POOL"
                
POOL_ID        POOL_CUR_SIZE        POOL_WATERMARK       POOL_CONFIG_SIZE
-------------- -------------------- -------------------- --------------------
OTHER                      50331648             53084160             44630016
FCMBP                        851968               851968               851968
MONITOR                     1638400              2818048               393216
                
3 record(s) selected.

SNAPDB_MEMORY_POOL

The SNAPDB_MEMORY_POOL administrative view retrieves database-level memory usage information. It allows you to retrieve database level memory usage information for the currently connected database, as shown in Listing 16.

Listing 16. SNAPDB_MEMORY_POOL view
$ db2 "SELECT SUBSTR(DB_NAME, 1, 8) AS DB_NAME, POOL_ID,POOL_CUR_SIZE, POOL_CONFIG_SIZE,
POOL_WATERMARK FROM SYSIBMADM.SNAPDB_MEMORY_POOL"
                
DB_NAME  POOL_ID        POOL_CUR_SIZE        POOL_CONFIG_SIZE     POOL_WATERMARK
-------- -------------- -------------------- -------------------- --------------------
TEST_DB UTILITY                      262144            216924160              3014656
TEST_DB PACKAGE_CACHE             189071360           2147483648           1392443392
TEST_DB OTHER                        196608             20971520               196608
TEST_DB CAT_CACHE                   1507328              1114112              1572864
TEST_DB BP                      12796297216          12796297216          13814202368
TEST_DB BP                           851968               851968               851968
TEST_DB BP                           589824               589824               589824
TEST_DB BP                           458752               458752               458752
TEST_DB BP                           393216               393216               393216
TEST_DB SHARED_SORT                 2293760            167837696             97320960
TEST_DB LOCK_MGR                   17629184             17694720             17629184
TEST_DB DATABASE                   35520512             44433408             53215232
TEST_DB APPLICATION                   65536              1048576                65536
                
13 record(s) selected.

LOCKWAITS

The LOCKWAITS administrative view retrieves lock waits information. It returns information about DB2 agent working on behalf of applications that are waiting to obtain locks, as shown in Listing 17. Excessive lock wait time often translates into poor response time, so it is important to monitor.

Listing 17. LOCKWAITS view
$ db2 "SELECT SUBSTR(TABSCHEMA, 1, 8) AS TABSCHEMA,
SUBSTR(TABNAME, 1, 15) AS TABNAME, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_MODE_REQUESTED,
AGENT_ID_HOLDING_LK FROM SYSIBMADM.LOCKWAITS WHERE AGENT_ID=25187"
                
TABSCHEMA TABNAME    LOCK_OBJECT_TYPE LOCK_MODE LOCK_MODE_REQUESTED AGENT_ID_HOLDING_LK
--------- ---------- ---------------- --------- ------------------- -------------------
DB2USR2   SUBSCRIBER ROW_LOCK         X         NS                  76504
                
1 record(s) selected.

Restrictions

Snapshot administrative views cannot be used in conjunction with either of the following commands:

  • Monitor switches commands/APIs
  • Monitor reset commands/APIs

This limitation is due to the fact that such commands use an INSTANCE ATTACH, while snapshot views use of DATABASE CONNECT.


Conclusion

The trend in recent releases of DB2 has been to make more and more monitoring data available through SQL interfaces. This makes management of monitoring data with DB2 very straightforward because you can easily redirect the data from the administration views, for example, right back into DB2 tables. For deeper dives, event and activity monitor data can also be written to DB2 tables, providing similar benefits. With the vast majority of this monitoring data being so easy to store in DB2, a small investment to store system metrics (such as CPU utilization from 'vmstat') in DB2 is manageable as well. It makes analysis and long-term management simpler.

We made recommendations for snapshot monitoring views on DB2 Linux, UNIX, and Windows version 10.1 that can help you to ensure good snapshot monitoring and performance analysis. We discussed several monitoring views that help you to understand system performance. If your system is monitored well, you can more effectively resolve performance problems that might arise, which can help reduce the total cost of ownership and potentially increase the return on investment for your business.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=933579
ArticleTitle=Snapshot monitoring administrative views on DB2 version 10.1 for Linux, UNIX, and Windows
publish-date=06132013