DB2 Administrative Views

25 August 2009

Updated 13 November 2009

 

Half of the time that I need to get some information about a DB2 system I can’t remember if I need to use the GET or LIST command.  Is it LIST DBM CFG or GET DBM CFG?  So I get a syntax error on my first attempt most times I try.  I also get tired of pouring through massive amounts of output when I’m only interested in a few candidates.  For example, if I want to examine the snapshot output for the few applications that are experiencing lock waits I hate looking through the output for hundreds of applications that the GET SNAPSHOT FOR APPLICATIONS command gives me.  Fortunately, I no longer need to do this in DB2 v9.  We now have the Administrative Views that allow me to use SQL select output to get information that I once had to use a LIST, GET or other command to perform.  Some examples include:

 

GET SNAPSHOT

LIST TABLESPACES

LIST TABLESPACE CONTAINERS

GET DBM CONFIGURATION

GET DB CONFIGRUATON

db2set –all

db2pd

db2mtrk

LIST UTILITIES

 

Snapshot Monitor Administrative Views

All of the information that you have been used to getting from snapshots can be selected from the Administrative Views.  In addition, a number of convenience views have been created that do things like calculate the buffer pool hit ratios and show tablespace sizes in kilobytes as well as pages.  Here is a case where having an SQL script is a big advantage over the snapshot.  When I am tasked with finding why some queries are a problem, but no one can say which queries, I immediately suspect that those queries are doing table scans.  To help narrow which queries are likely candidates I will determine if any tables have an exceptional number of reads and then focus on the queries that select from one of those few tables.  I used to have to do a snapshot on tables, but on a database with hundreds of tables it was very time consuming to review the entire output.  This is especially bad because a snapshot shows ALL of the information for each table, even if I’m only interested in rows read.  I can now just issue the following query to see just tables that have more than double the average number of reads among tables with more than 1000 rows read:

 

select a.rows_read, substr(a.tabname,1,20) Table

 from sysibmadm.snaptab a

where a.rows_read

      > 2*(select avg (b.rows_read) from sysibmadm.snaptab b where b.rows_read > 1000)

order by rows_read desc;

 

To see the full list of the snapshot views please see: Snapshot Routines and Views where you can also drill down to see the description of each view and a description of each column in each view. Since these are views you can also join them where appropriate where you would need to look at output of multiple snapshots when using the old command.

 

I said in the introduction that you can replace several command line commands with the snapshot views, so I have created this little table to show a cross reference here:

 

LIST TABLESPACES

SYSIBMADM.TBSP_UTILIZATION

LIST TABLESPACE CONTAINERS

SYSIBMADM.CONTAINER_UTILIZATION

LIST UTILITIES

SYSIBMADM.SNAPUTIL

db2mtrk, db2pd

These utilities and several variations, most of which can be reproduced with a snapshot administration view.

 

The whole list of snapshot views mimics some option of the GET SNAPSHOT command so it is not listed in my table.  In many cases the administrative views do more than the command line versions do.  For example, the LIST TABLESPACES SHOW DETAIL command does not show if AUTORESIZE is on for the tablespace. 

 

Snapshot Monitor Convenience Views

Even though it is now much easier to do things like calculate the buffer pool hit ratio because you can put formulas in SQL, our friends who develop DB2 have gone a step further and created a set of views that calculate many performance related measures for us such as buffer pool hit ratio and other useful ratios.  Also where several measures for memory and disk space were only in pages, several of these views show them in kilobytes as well as pages.  Even though these views are listed at the bottom of the Snapshot Views page where you can drill down for column descriptions, I thought that it would be handy to list them here as well.

 

Name

Description

SYSIBMADM.APPLICATIONS

Information about connected database applications. 

SYSIBMADM.APPL_PERFORMANCE

Information about the rate of rows selected versus the number of rows read by an application.

SYSIBMADM.BP_HITRATIO

Buffer pool hit ratios, including total, data, and index, in the database. 

SYSIBMADM.BP_READ_IO

Information about buffer pool read performance. 

SYSIBMADM.BP_WRITE_IO

Information about buffer pool write performance.

SYSIBMADM.CONTAINER_UTILIZATION

Information about table space containers and utilization rates.

SYSIBMADM.LOCKS_HELD

Information on current locks held.

ISYSIBMADM.LOCKWAIT

Information about DB2 agents working on behalf of applications that are waiting to obtain locks.

SYSIBMADM.LOG_UTILIZATION

Information about log utilization for the currently connected database.

SYSIBMADM.LONG_RUNNING_SQL

Information about the longest running SQL in the currently connected database.

SYSIBMADM.QUERY_PREP_COST

Information about the time required to prepare different SQL statements. 

SYSIBMADM.TBSP_UTILIZATION

Table space configuration and utilization information.

SYSIBMADM.TOP_DYNAMIC_SQL

The top dynamic SQL statements sortable by number of executions, average execution time, number of sorts, or sorts per statement. 

 

 

Miscellaneous Administrative Views

In addition to the snapshot and snapshot convenience views several others are now available.  Several of these are an SQL version of some command line command and I’ve listed the command in the descriptions in the table below.  There are also views for information that you would have to gather from several places in the database and/or operating system.  There is some really good stuff here and I highly recommend reviewing this table.  All of these views are described further on the Supported Administrative Routines and Views page where you can click links to see column descriptions.  This link has both table functions and views, so you will have to search through the descriptions for the views that I have shown below.  

 

Name

Description

SYSIBMADM.DBCFG

GET DB CFG SHOW DETAIL

This administrative view returns database configuration information. 

SYSIBMADM.DBMCFG

GET DBM CFG SHOW DETAIL

This administrative view returns database manager (instance) configuration information. 

SYSIBMADM.REG_VARIABLES

db2set -all

This administrative view returns the DB2 registry settings from all database partitions.

SYSIBMADM.ENV_INST_INFO

db2level

This administrative view returns information about the current instance. 

SYSIBMADM.ENV_PROD_INFO

db2licm -l

This administrative view returns information about installed DB2 products. 

SYSIBMADM.ENV_SYS_INFO

Similar to various OS commands like “oslevel”

This administrative view returns information about the system. 

SYSIBMADM.ENV_SYS_RESOURCES

Similar to various OS commands like “vmstat”

This administrative view returns operating system, CPU, memory and other information related to the system

SYSIBMADM.AUTHORIZATIONIDS

This administrative view contains a list of authorization IDs that have been granted privileges or authorities, along with their types, for the currently connected database.

SYSIBMADM.OBJECTOWNERS

This administrative view contains all object ownership information for the currently connected database.

SYSIBMADM.PRIVILEGES

This administrative view contains all explicit privileges for the currently connected database.

SYSIBMADM.ADMINTABINFO

This administrative view returns size and state information for tables, materialized query tables (MQT) and hierarchy tables. 

SYSIBMADM.CONTACTGROUPS

This administrative view returns the list of contact groups.

SYSIBMADM.CONTACTS

This administrative view returns the list of contacts defined on the database server.

SYSIBMADM.DB_HISTORY

LIST HISTORY

This administrative view returns information from the history file that is associated with the currently connected database partition. 

SYSIBMADM.DBPATHS

This administrative view returns the values for database paths required for tasks such as split mirror backups.

SYSIBMADM.NOTIFICATIONLIST

This administrative view returns the list of contacts and contact groups that are notified about the health of an instance.

SYSIBMADM.

PDLOGMSGS_LAST24HOURS

This administrative view returns problem determination log messages that were logged in the DB2 notification log. The information is intended for use by database and system administrators.

 

 

Additional Examples

At the 2009 Information on Demand conference, Chris Eaton presented a very informative session on Administrative Views and Table Functions.  I have included a number of his very useful examples here:

 

Find Long Running SQL:

 

SELECT ELAPSED_TIME_MIN,

              SUBSTR(AUTHID,1,10) AS AUTH_ID,

              AGENT_ID,

APPL_STATUS,

SUBSTR(STMT_TEXT,1,20) AS SQL_TEXT
FROM    SYSIBMADM.LONG_RUNNING_SQL
WHERE ELAPSED_TIME_MIN > 0
ORDER BY ELAPSED_TIME_MIN DESC

Show Bufferpool Hit Ratios:

 

SELECT SUBSTR(BP_NAME,1,20) as BP_NAME,

             TOTAL_HIT_RATIO_PERCENT as ALL_HR,

             DATA_HIT_RATIO_PERCENT as DATA_HR,

             INDEX_HIT_RATIO_PERCENT as INX_HR,

             XDA_HIT_RATIO_PERCENT as XML_HR

FROM SYSIBMADM.BP_HITRATIO;

 

 

Show Queries and Statics from the Package Cache:

 

SELECT

    SUBSTR(STMT_TEXT,1,20) AS STMT,

    SECTION_TYPE AS TYPE,

    NUM_EXECUTIONS,

    TOTAL_ACT_TIME AS TOTAL_TIME,

    TOTAL_ACT_WAIT_TIME AS WAIT_TIME

FROM TABLE(MON_GET_PKG_CACHE_STMT('','','',-1))

 

Show Lock Wait Chains:

 

select substr(ai_h.appl_name,1,10) as "Hold App",

    substr(ai_h.primary_auth_id,1,10) as "Holder",

    substr(ai_w.appl_name,1,10) as "Wait App",

    substr(ai_w.primary_auth_id,1,10) as "Waiter",

    lw.lock_mode as "Hold Mode",

    lw.lock_object_type as "Obj Type",

    substr(lw.tabname,1,10) as "TabName",

    substr(lw.tabschema,1,10) as "Schema",

    timestampdiff(2,char(lw.snapshot_timestamp - lw.lock_wait_start_time))

        as "waiting (s)"

from  sysibmadm.snapappl_info ai_h,

      sysibmadm.snapappl_info ai_w, sysibmadm.snaplockwait lw

where lw.agent_id = ai_w.agent_id

and   lw.agent_id_holding_lk = ai_h.agent_id

 

Show Excessive Sorting:

 

SELECT

    APPLICATION_HANDLE AS APP_HDL,

    SUBSTR(CLIENT_USERID,1,10) AS USERID,

    TOTAL_SECTION_SORTS AS NUM_SORTS,

    TOTAL_SECTION_SORT_TIME AS TOTAL_TIME,

    TOTAL_SECTION_SORT_PROC_TIME AS SORT_TIME,

    TOTAL_SECTION_SORT_TIME - TOTAL_SECTION_SORT_PROC_TIME AS WAIT_TIME

FROM TABLE(MON_GET_CONNECTION(NULL,-1))

 

 

Show the transactions with the most CPU and most Wait Time:

 

SELECT

    APPLICATION_HANDLE AS APP_HDL,

    SUBSTR(CLIENT_USERID,1,10) AS USERID,

    TOTAL_RQST_TIME,

    TOTAL_CPU_TIME,

    TOTAL_WAIT_TIME,

    CLIENT_IDLE_WAIT_TIME

FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1))

 

Show the 5 most active tables:

 

SELECT

    SUBSTR(TABSCHEMA,1,10) AS SCHEMA,

    SUBSTR(TABNAME,1,20) AS NAME,

    TABLE_SCANS,

    ROWS_READ,

    ROWS_INSERTED,

    ROWS_DELETED 

FROM TABLE(MON_GET_TABLE('','',-1))

ORDER BY ROWS_READ DESC

FETCH FIRST 5 ROWS ONLY

 

Show the Critical and Error messages in the last 24 hours:

 

SELECT TIMESTAMP, SUBSTR(MSG,1,400) AS MSG
FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS
WHERE MSGSEVERITY IN ('C','E')
ORDER BY TIMESTAMP DESC

 

Show the messages in the notify log from the last 3 days:

 

SELECT TIMESTAMP, SUBSTR(MSG,1,400) AS MSG
FROM TABLE

       ( PD_GET_LOG_MSGS( CURRENT TIMESTAMP - 3 DAYS) )       

         AS PD
ORDER BY TIMESTAMP DESC

 

 

Show the average and maximum time taken to perform full backups

 

SELECT AVG(TIMESTAMPDIFF(4,CHAR(

             TIMESTAMP(END_TIME) - TIMESTAMP(START_TIME))))  AS AVG_BTIME,
         MAX(TIMESTAMPDIFF(4,CHAR(

             TIMESTAMP(END_TIME) - TIMESTAMP(START_TIME)))) AS MAX_BTIME
FROM SYSIBMADM.DB_HISTORY
WHERE OPERATION = 'B'
      AND OPERATIONTYPE = 'F'

 

Show any commands in the recovery history file that failed:

    

SELECT START_TIME, SQLCODE, SUBSTR(CMD_TEXT,1,50)
   FROM SYSIBMADM.DB_HISTORY

   WHERE SQLCODE < 0

 

Display information about the application that currently has the oldest uncommitted unit of work.  This is useful if to know about transactions that are holding too much log space:

 

SELECT AI.APPL_STATUS as Status,

        SUBSTR(AI.PRIMARY_AUTH_ID,1,10) AS "Authid",
        SUBSTR(AI.APPL_NAME,1,15) AS "Appl Name",
        INT(AP.UOW_LOG_SPACE_USED/1024/1024)
             AS "Log Used (M)",
        INT(AP.APPL_IDLE_TIME/60) AS "Idle for (min)",
        AP.APPL_CON_TIME AS "Connected Since"
FROM    SYSIBMADM.SNAPDB DB,
        SYSIBMADM.SNAPAPPL  AP,
        SYSIBMADM.SNAPAPPL_INFO AI
WHERE   AI.AGENT_ID = DB.APPL_ID_OLDEST_XACT
      AND    AI.AGENT_ID = AP.AGENT_ID;

 

 

***

 

In conclusion the administration views have the potential to make your life easier if you find your self having to run a lot of commands.  There are also several table functions that allow you to do additional stuff.  New views are being added with each new fix pack.  To keep up with them check the DB2 Information Center for your version on my Favorite Links page.  You will also need to run the db2updv7 command to make them available.

 

HOME | Search