DB2 monitoring: Migrate from snapshot monitor interfaces to in-memory metrics monitor interfaces

Improve performance and gain unprecedented access to DB2 monitor data

IBM® DB2® for Linux®, UNIX® and Windows® Version 9.7 introduced a lightweight monitor infrastructure and a set of interfaces that reports monitor elements from this infrastructure. This infrastructure, called the in-memory metrics monitor infrastructure, is separate from the snapshot monitor infrastructure that was the primary monitor infrastructure in previous releases. In this article, learn how to migrate from the snapshot monitor interfaces to the new in-memory metrics monitor interfaces. The in-memory metrics monitor interfaces and infrastructure provide several benefits over the snapshot monitor interfaces and infrastructure including improved performance, additional monitor elements, additional accumulation points for monitor data, and finer granularity of control over data collection.

Scott Walkty (swalkty@ca.ibm.com), Software Developer, IBM

Scott WalktyScott Walkty is a software developer on the DB2 Monitoring and Workload Manager team. He is one of the original developers on the Workload Manager solution and has spent the last few years working on various monitor enhancements for DB2. He previously worked for five years on the DB2 Tools team. Scott holds a master's degree in computer science from the University of Manitoba.



Mike Springgay (springga@ca.ibm.com), Senior Architect, DB2 development, IBM  

Mike SpringgayMike Springgay is a Senior Architect within the DB2 for Linux, UNIX, and Windows development team. He joined the DB2 development team 16 years ago and is currently responsible for client server connectivity, routine infrastructure, monitor, and SQL Compatibility areas.



Laura McGlinchey (mcglinch@ie.ibm.com), Software Developer, IBM

Laura McGlincheyLaura McGlinchey is a software developer on the DB2 Monitoring team based at the Dublin Software Lab. She joined the DB2 Development team five years ago after taking part in IBM's Extreme Blue internship programme. Laura holds a degree in applied computing from Letterkenny Institute of Technology (Ireland).



Shakti Tripathy (tripathy@ie.ibm.com), Software Developer, IBM

Shakti TripathyShakti Tripathy is a DB2 software engineer on the Monitor Solutions team based at the Dublin Software Lab. He has been developing monitor solutions for the DB2 engine for over five years. Previously, he spent over 10 years developing relational database applications. Shakti holds a PhD from the Queen's University of Belfast (Northern Ireland).



17 July 2014

Introduction

IBM DB2 for Linux, UNIX and Windows Version 9.7 introduced a lightweight monitor infrastructure called the in-memory metrics monitor infrastructure. It is accompanied by a set of interfaces that report monitor elements from this infrastructure. The in-memory metrics monitor infrastructure is separate from the snapshot monitor infrastructure, which was the primary monitor infrastructure for prior releases. Each release of DB2 for Linux, UNIX and Windows since Version 9.7 has introduced monitor interfaces that use the in-memory metrics monitor infrastructure.

This article provides detailed examples to help you migrate from the snapshot monitor interfaces to the new in-memory metrics monitor interfaces.


Comparison and benefits

The preferred way to access monitor data is through SQL table functions and views. SQL interfaces provide a uniform way to access monitor data in different programming languages and through the DB2 command line. Also, by using these interfaces, you can apply the rich query and aggregation capabilities of SQL to the monitor data. For example, you can easily write a monitor query that reports the 10 longest running SQL statements on the database server.

Both the in-memory metrics and snapshot infrastructures support SQL access to monitor data. The in-memory metrics infrastructure provides a set of table functions in the SYSPROC schema that are prefixed with MON_GET and a set of views in the SYSIBMADM schema that are prefixed with MON. The snapshot infrastructure provides a set of table functions in the SYSPROC schema that are prefixed with SNAP and a set of views in the SYSIBMADM schema. No common prefix is used for the snapshot views. You can also use a set of C APIs and CLP commands to access snapshot monitor data. No equivalent set of interfaces exists for the in-memory metrics infrastructure because you can invoke the SQL interfaces from C (for example, by using CLI functions) or directly from the command line.

The in-memory metrics monitor interfaces and infrastructure provide the following benefits over the snapshot monitor interfaces and infrastructure:

Better performance
The in-memory metrics infrastructure has lower contention and resource usage compared to the snapshot infrastructure.
Additional monitor elements

Important

Monitor elements added in DB2 Version 9.7 and later are only in the in-memory metrics monitor interfaces. To take advantage of the new monitor elements, you must use the in-memory metrics monitor interfaces.

The in-memory metrics infrastructure reports some monitor elements that are not available in the snapshot infrastructure. For example, the in-memory metrics infrastructure reports the time-spent metrics, which provide a breakdown of where time is being spent in the database server.
Additional monitor accumulation points
The in-memory metrics interfaces report monitor data at more accumulation points than the snapshot interfaces report. The snapshot interfaces report monitor data that is aggregated by application and database. The in-memory metrics interfaces report monitor data that is aggregated by transaction, application, routine, database, and different DB2 Workload Manager (WLM) objects, including service class and workload.
Granularity of data collection
You enable or disable monitor controls for the snapshot infrastructure at the instance level, and they apply to all databases in the instance. You can control the in-memory metrics infrastructure at the database level or the WLM object level. For example, you can enable collection of in-memory metrics monitor data for a specific service class within a database.

One advantage of the snapshot infrastructure over the in-memory metrics infrastructure is that the CLP and C API interfaces to monitor data have a data reset capability. No reset capability is available in the SQL monitor interfaces. To help overcome this limitation, you can emulate the data reset capability with the SQL monitor interfaces (described in Monitoring in DB2 9.7, Part 1: Emulating data reset with the new DB2 9.7 monitoring table functions). The best practices article Tuning and monitoring database system performance has similar suggestions on how to emulate the data reset with SQL interfaces.

The in-memory metrics interfaces do not require SYSMON authority and are independent of the snapshot monitor switches. Therefore, the switches in Table 1 have no impact on the in-memory metrics infrastructure.

Table 1. Monitor switches with no impact on in-memory metrics infrastructure
Monitor switchDatabase manager configuration parameterDefault value
Buffer pooldft_mon_bufpoolOFF
Lockdft_mon_lockOFF
Sortdft_mon_sortOFF
Statementdft_mon_stmtOFF
Tabledft_mon_tableOFF
Timestampdft_mon_timestampON
Unit of Workdft_mon_uowOFF

The snapshot infrastructure uses multiple switches per instance to control what levels of data are collected. You control data collection for the in-memory metrics infrastructure by using a set of database configuration parameters, as shown in Table 2.

Table 2. Database configuration parameters for monitoring collection levels
Monitor data collection settingDatabase configuration parameterDefault value
Request metricsmon_req_metricsBASE
Activity metricsmon_act_metricsBASE
Object metricsmon_obj_metricsEXTENDED
Routine metricsmon_rtn_dataNONE
Routine executable listmon_rtn_execlistOFF

The defaults for a new database are those in Table 2.

After upgrading to a newer DB2 release, you should set the mon_req_metrics, mon_act_metrics, and mon_obj_metrics database configuration parameters to their default values if they are set to NONE. By setting these parameters to their default values, you can take advantage of the in-memory metrics monitor infrastructure, which has a low performance impact. If you use a large number of routines, you should also consider setting the mon_rtn_data configuration parameter to BASE so that you can monitor the routines.

You can enable the snapshot monitor switches and the in-memory metrics database configuration parameters simultaneously. By enabling both, you can gradually migrate from the snapshot interfaces to the in-memory metrics interfaces. Disable the snapshot switches as early as possible after you transition to the in-memory metrics interfaces.


In-memory metrics interface overview

This section describes the different types of monitor interfaces that report monitor data from the in-memory metrics infrastructure.

Data object interfaces

The monitor elements that are reported by the data object interfaces measure the impact of work that the database system performed on data objects such as indexes or tables. You can use this information to identify issues with particular data objects. The following table functions, which you control by using the mon_obj_metrics database configuration parameter, are examples of data object interfaces:

  • MON_GET_BUFFERPOOL
  • MON_GET_TABLESPACE
  • MON_GET_CONTAINER
  • MON_GET_TABLE
  • MON_GET_TABLE_USAGE_LIST
  • MON_GET_INDEX
  • MON_GET_INDEX_USAGE_LIST

Request interfaces

The monitor elements that are reported by the request interfaces measure the total impact of work that the database system performed. The following table functions, which you control by using the mon_req_metrics database configuration parameter, are examples of request interfaces:

Note

Metrics from the MON_GET_DATABASE and MON_GET_DATABASE_DETAILS interfaces are effectively a hybrid of request and data object metrics.

  • MON_GET_UNIT_OF_WORK
  • MON_GET_UNIT_OF_WORK_DETAILS
  • MON_GET_CONNECTION
  • MON_GET_CONNECTION_DETAILS
  • MON_GET_DATABASE
  • MON_GET_DATABASE_DETAILS
  • MON_GET_SERVICE_SUBCLASS
  • MON_GET_SERVICE_SUBCLASS_DETAILS
  • MON_GET_WORKLOAD
  • MON_GET_WORKLOAD_DETAILS

Routine monitor interfaces report collected request metrics per routine. You control the data returned from these monitor interfaces by using the mon_rtn_data and mon_rtn_execlist database configuration parameters:

  • MON_GET_ROUTINE
  • MON_GET_ROUTINE_DETAILS
  • MON_GET_ROUTINE_EXEC_LIST

Activity interfaces

The monitor elements that are reported by the activity interfaces measure the impact of work that activities in the database system performed. An activity refers to the execution of the section that was compiled for an SQL statement or the execution of the LOAD utility. The following table functions, which you control by using the mon_act_metrics database configuration parameter, are examples of activity interfaces:

  • MON_GET_PKG_CACHE_STMT
  • MON_GET_PKG_CACHE_STMT_DETAILS
  • MON_GET_ACTIVITY
  • MON_GET_ACTIVITY_DETAILS

Example

To see all the in-memory metrics table function monitor interfaces that are available, you can use the SQL statement in Listing 1. The results include environment routines that return information about the database server environment. The names of the environment routines are prefixed by ENV_ (for example, ENV_GET_SYSTEM_RESOURCES).

Listing 1. SQL statement
SELECT
  substr(P.ROUTINENAME,1,48) as ROUTINENAME,
  substr(P.SPECIFICNAME,1,48) as SPECIFICNAME,
  case when P.ROWTYPE in ('B','O','P') 
       then CHAR('IN',3) 
       else CHAR('OUT',3) end as IN_OUT,
  cast(p.ORDINAL as char(3)) as ORD,
  substr(P.PARMNAME,1,40) as PARMNAME,
  substr(P.TYPENAME,1,16) as TYPE
FROM syscat.routines r,  syscat.routineparms p
WHERE p.routineschema=r.routineschema
          and p.routinename=r.routinename
          and p.specificname=r.specificname
          and r.functiontype='T'
          and substr(r.ROUTINENAME,1,4) in ('MON_','ENV_')
ORDER BY P.ROUTINESCHEMA,P.ROUTINENAME,P.SPECIFICNAME,IN_OUT,P.ORDINAL

Sample output is shown in Listing 2.

Listing 2. Sample output
ROUTINENAME		SPECIFICNAME		IN_OUT	ORD PARMNAME		TYPE
-----------		----------- 		------ ---  -------------	-----
...
MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	IN	1   EXECUTABLE_ID	VARCHAR

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT	1   ROUTINE_TYPE	CHARACTER

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT	2   ROUTINE_SCHEMA	VARCHAR

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT	3   ROUTINE_MODULE_NAME	VARCHAR

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT    	4   ROUTINE_NAME	VARCHAR

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT	5   SPECIFIC_NAME	VARCHAR

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT	6   ROUTINE_ID		INTEGER

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT    	7   SUBROUTINE_ID	INTEGER

MON_GET_SECTION_ROUTINE	MON_GET_SECTION_ROUTINE	OUT	8   LIB_ID		BIGINT
...

For more details on each of the interfaces and the impact of the configuration parameters, visit the DB2 for LInux, UNIX and Windows Knowledge Center.

Migrating from snapshot SQL interfaces to in-memory metrics SQL interfaces

Table 3 lists the snapshot SQL interfaces and the in-memory metrics replacements. Replacement functions have been added over successive DB2 releases. In Table 3, the Release column indicates the DB2 release in which the replacement interface was first available.

In some cases, the information from more than one snapshot interface is consolidated into a single in-memory metrics interface. For example, the MON_GET_CONNECTION in-memory metrics table function returns information that is equivalent to the combination of information that the SNAP_GET_APPL and SNAP_GET_APPL_INFO snapshot table functions return.

Table 3. Snapshot SQL interfaces and their in-memory metrics replacements
Snapshot interfaceIn-memory metrics interfaceRelease
LOG_UTILIZATIONMON_TRANSACTION_LOG_UTILIZATIONV10.5
SNAP_GET_AGENT, SNAPAGENTMON_GET_AGENTV10.5
SNAP_GET_DB, SNAPDBMON_GET_DATABASEV10.5
SNAP_GET_DBM, SNAPDBMMON_GET_INSTANCEV10.5
SNAP_GET_UTIL, SNAPUTILMON_GET_UTILITYV10.5
SNAP_GET_DETAILLOG, SNAPDETAILLOGMON_GET_TRANSACTION_LOGV10.1
SNAP_GET_HADR, SNAPHADRMON_GET_HADRV10.1
SNAP_GET_AGENT_MEMORY_POOL, SNAPAGENT_MEMORY_POOL, SNAPDB_MEMORY_POOL, SNAP_GET_DB_MEMORY_POOL, SNAPDBM_MEMORY_POOL, SNAP_GET_DBM_MEMORY_POOLMON_GET_MEMORY_SET, MON_GET_MEMORY_POOLV9.7 FP5
APPLICATION_IDMON_GET_APPLICATION_IDV9.7 FP3
SNAPFCM, SNAP_GET_FCM, SNAPFCM_PART, SNAP_GET_FCM_PARTMON_GET_FCM, MON_GET_FCM_CONNECTION_LISTV9.7 FP2
APPL_PERFORMANCEMON_CONNECTION_SUMMARYV9.7 FP1
BP_HITRATIO, BP_READ_IO, BP_WRITE_IOMON_BP_UTILIZATIONV9.7 FP1
LOCKS_HELD, LOCKWAITS, SNAP_GET_LOCK, SNAPLOCK, SNAP_GET_LOCKWAIT, SNAPLOCKWAIT,MON_GET_LOCKS, MON_GET_APPL_LOCKWAIT, MON_FORMAT_LOCK_NAME, MON_LOCKWAITSV9.7 FP1
LONG_RUNNING_SQLMON_CURRENT_SQLV9.7 FP1
TBSP_UTILIZATIONMON_TBSP_UTILIZATIONV9.7 FP1
APPLICATIONSMON_GET_CONNECTIONV9.7
SNAP_GET_APPL, SNAPAPPL, SNAP_GET_APPL_INFO, SNAPAPPL_INFOMON_GET_CONNECTIONV9.7
SNAP_GET_BP, SNAPBP, SNAP_GET_BP_PART, SNAPBP_PARTMON_GET_BUFFERPOOLV9.7
SNAP_GET_CONTAINER, SNAPCONTAINER, CONTAINER_UTILIZATIONMON_GET_CONTAINERV9.7
SNAP_GET_STMT, SNAPSTMTMON_GET_ACTIVITYV9.7
SNAP_GET_TAB, SNAPTABMON_GET_TABLEV9.7
SNAP_GET_TBSP, SNAPTBSP, SNAP_GET_TBSP_PART, SNAPTBSP_PARTMON_GET_TABLESPACEV9.7

In general, the same monitor element names are used in both the old and new interfaces for the same monitor elements. However, there are cases where the replacement interface uses a more descriptive monitor element name than the snapshot interfaces or returns a different monitor element which reports equivalent information.

Table 4 through Table 16 provide mappings for monitor element names that differ between the snapshot and in-memory metrics monitor interfaces.

If required, you can use the CURRENT_TIMESTAMP special register to replace the snapshot_timestamp monitor element and use the CURRENT_SERVER special register to replace the database name monitor element.

Table 4. SNAP_GET_AGENT interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
AGENT_IDMON_GET_AGENTAPPLICATION_HANDLE
AGENT_PIDMON_GET_AGENTAGENT_TID
Table 5. SNAP_GET_AGENT_MEMORY_POOL interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
AGENT_IDMON_GET_MEMORY_POOLAPPLICATION_HANDLE
AGENT_PIDMON_GET_MEMORY_POOLEDU_ID
POOL_CONFIG_SIZEMON_GET_MEMORY_SETMEMORY_SET_SIZE
POOL_CUR_SIZEMON_GET_MEMORY_POOLMEMORY_POOL_USED
POOL_IDMON_GET_MEMORY_POOLMEMORY_POOL_ID
POOL_WATERMARKMON_GET_MEMORY_POOLMEMORY_POOL_USED_HWM
Table 6. SNAP_GET_APPL interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
AGENT_IDMON_GET_CONNECTIONAPPLICATION_HANDLE
AGENT_USR_CPU_TIME_S, AGENT_USR_CPU_TIME_MSMON_GET_CONNECTIONTOTAL_CPU_TIME
AGENT_SYS_CPU_TIME_S, AGENT_SYS_CPU_TIME_MSMON_GET_CONNECTIONTOTAL_CPU_TIME
LOCKS_HELDMON_GET_CONNECTIONNUM_LOCKS_HELD
LOCKS_WAITINGMON_GET_CONNECTIONNUM_LOCKS_WAITING
ROWS_WRITTENMON_GET_CONNECTIONROWS_MODIFIED
Table 7. SNAP_GET_APPL_INFO interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
AGENT_IDMON_GET_CONNECTIONAPPLICATION_HANDLE
APPL_STATUSMON_GET_AGENTEVENT_TYPE, EVENT_OBJECT and EVENT_STATE
COORD_NODE_NUMMON_GET_CONNECTIONCOORD_MEMBER
DBPARTITIONNUMMON_GET_CONNECTIONMEMBER
PRIMARY_AUTH_IDMON_GET_CONNECTIONSYSTEM_AUTH_ID or SESSION_AUTH_ID
Table 8. SNAP_GET_DB interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
APPL_ID_OLDEST_XACTMON_GET_TRANSACTION_LOGAPPLID_HOLDING_OLDEST_XACT
ASYNC_RUNSTATSMON_GET_DATABASETOTAL_ASYNC_RUNSTATS
DB_LOCATIONMON_GET_DATABASENot applicable. You can use the MON_GET_CONNECTION table function to get information about where the current connection originated from.
LOG_READ_TIME_S, LOG_READ_TIME_NSMON_GET_TRANSACTION_LOGLOG_READ_TIME
LOG_WRITE_TIME_S, LOG_WRTIE_TIME_NSMON_GET_TRANSACTION_LOGLOG_WRITE_TIME
ROWS_SELECTEDMON_GET_DATABASEROWS_RETURNED
SYNC_RUNSTATSMON_GET_DATABASETOTAL_SYNC_RUNSTATS
SYNCS_RUNSTATS_TIMEMON_GET_DATABASETOTAL_SYNC_RUNSTATS_TIME
Table 9. SNAP_GET_FCM_PART interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
FCM_DBPARTITIONNUMMON_GET_FCMMEMBER
FCM_MEMBERMON_GET_FCMMEMBER
Table 10. SNAP_GET_HADR interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
HADR_HEARTBEATMON_GET_HADRDivide the value of the TIME_SINCE_LAST_RECV element by the value of the HEARTBEAT_INTERVAL element.
HADR_LOCAL_HOSTMON_GET_HADRPRIMARY_MEMBER_HOST or STANDBY_MEMBER_HOST
HADR_PRIMARY_LOG_FILEMON_GET_HADRPRIMARY_LOG_FILE
HADR_PRIMARY_LOG_PAGEMON_GET_HADRPRIMARY_LOG_PAGE
HADR_PRIMARY_LOG_LSNMON_GET_HADRPRIMARY_LOG_POS
HADR_REMOTE_HOSTMON_GET_HADRPRIMARY_MEMBER_HOST or STANDBY_MEMBER_HOST
HADR_REMOTE_INSTANCEMON_GET_HADRPRIMARY_INSTANCE or STANDBY_INSTANCE
HADR_STANDBY_LOG_FILEMON_GET_HADRSTANDBY_LOG_FILE
HADR_STANDBY_LOG_PAGEMON_GET_HADRSTANDBY_LOG_PAGE
HADR_STANDBY_LOG_LSNMON_GET_HADRSTANDBY_LOG_POS
Table 11. SNAP_GET_LOCK interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
AGENT_IDMON_GET_LOCKSAPPLICATION_HANDLE
Table 12. SNAP_GET_LOCKWAIT interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
AGENT_IDMON_GET_LOCKAPPLICATION_HANDLE
AGENT_ID_HOLDING_LKMON_GET_APPL_LOCKWAITHLD_APPLICATION_HANDLE
Table 13. SNAP_GET_STMT interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
AGENT_IDMON_GET_ACTIVITYAPPLICATION_HANDLE
DEGREE_PARALLELISMMON_GET_ACTIVITYEFFECTIVE_QUERY_DEGREE
ROWS_WRITTENMON_GET_ACTIVITYROWS_MODIFIED
STMT_ELAPSED_TIME_S, STMT_ELAPSED_TIME_MSMON_GET_ACTIVITYCOORD_STMT_EXEC_TIME
STMT_SORTSMON_GET_ACTIVITYTOTAL_SECTION_SORTS
STMT_SYS_CPU_TIME_S, STMT_SYS_CPU_TIME_MSMON_GET_ACTIVITYTOTAL_CPU_TIME
STMT_USR_CPU_TIME_S, STMT_USR_CPU_TIME_MSMON_GET_ACTIVITYTOTAL_CPU_TIME
Table 14. SNAP_GET_TAB interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
DATA_OBJECT_PAGESMON_GET_TABLEDATA_OBJECT_L_PAGES
INDEX_OBJECT_PAGESMON_GET_TABLEINDEX_OBJECT_L_PAGES
LOB_OBJECT_PAGESMON_GET_TABLELOB_OBJECT_L_PAGES
LONG_OBJECT_PAGESMON_GET_TABLELONG_OBJECT_L_PAGES
ROWS_WRITTENMON_GET_TABLEThe sum of the values of the ROWS_INSERTED, ROWS_DELETED and ROWS_UPDATED elements is equal to the value of the ROWS_WRITTEN element
XDA_OBJECT_PAGESMON_GET_TABLEXDA_OBJECT_L_PAGES
Table 15. SNAP_GET_TBSP_PART interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
REBALANCER_MODEMON_GET_TABLESPACETBSP_REBALANCE_MODE
TBSP_CURRENT_SIZEMON_GET_TABLESPACEFor DMS table spaces, the value of the TBSP_CURRENT_SIZE element is equal to the value of the TABLESPACE_TOTAL_PAGES element multiplied by the value of the TABLESPACE_PAGE_SIZE element. For SMS table spaces, there is no equivalent element.
Table 16. SNAP_GET_UTIL interface
Element name in snapshot interfaceIn-memory metrics interfaceElement name in in-memory metrics interface
UTILITY_DBNAMEMON_GET_UTILITYYou can obtain the value from the CURRENT_SERVER special register.
UTILITY_DESCRIPTIONMON_GET_UTILITYUTILITY_DETAIL

Note: Utility progress monitor elements are not yet provided. You can use the SNAP_GET_UTIL_PROGRESS function until replacements are available.

Examples of migrating snapshot interfaces to in-memory metrics interfaces

The following queries provide examples of migrating snapshot SQL interfaces to the new in-memory metrics monitor interfaces.

SNAP_GET_DB function

The following query retrieves the database status and database activation time for all members of the currently connected database:

SELECT DB_NAME, DB_STATUS,
DB_CONN_TIME, DBPARTITIONNUM
FROM TABLE (SNAP_GET_DB(CAST (NULL AS VARCHAR (128)), -2)) AS T

With the in-memory metrics monitor interfaces, queries are always directed at the connected database so there is no need for the DB_NAME column. If you require the DB_NAME column for compatibility, you can use the CURRENT SERVER special register.

You can rewrite the query to use the MON_GET_DATABASE function:

SELECT CURRENT SERVER, DB_STATUS, DB_CONN_TIME,
       MEMBER
FROM TABLE (MON_GET_DATABASE(-2)) AS T

SNAP_GET_TAB function

The following query retrieves the table schema, table name, table type, and partition number for all active tables across all members of the currently connected database:

SELECT TABSCHEMA, TABNAME, TAB_TYPE, DBPARTITIONNUM
FROM TABLE (SNAP_GET_TAB(CAST(NULL AS VARCHAR(128)),-2)) AS T

You can transform that query to use the MON_GET_TABLE function. You can use the NUM_COLUMNS_REFERENCED and SECTION_EXEC_WITH_COL_REFERENCES monitor elements to help determine the average number of columns that a particular query accesses. For example:

SELECT TABSCHEMA, TABNAME, TAB_TYPE, MEMBER, 
       NUM_COLUMNS_REFERENCED, SECTION_EXEC_WITH_COL_REFERENCES 
FROM TABLE (MON_GET_TABLE(CAST(NULL AS VARCHAR(128)),-2)) AS T

SNAP_GET_APPL function

The following query uses the SNAP_GET_APPL snapshot interface to retrieve details on the rows that were read and written for each application for all active databases:

SELECT AGENT_ID, ROWS_READ, ROWS_WRITTEN 
FROM TABLE (SNAP_GET_APPL(CAST(NULL AS VARCHAR(128)),-1)) AS T

You can convert that query to use the MON_GET_CONNECTION function:

SELECT APPLICATION_HANDLE, ROWS_READ, ROWS_MODIFIED
FROM TABLE (MON_GET_CONNECTION(CAST (NULL AS BIGINT), -1)) AS T

The AGENT_ID monitor element is replaced by the APPLICATION_HANDLE monitor element.

SNAP_GET_DETAILLOG function

The following query uses the SNAP_GET_DETAILLOG snapshot interface to retrieve log information for database member 1 for the currently connected database:

db2 “SELECT FIRST_ACTIVE_LOG, 
LAST_ACTIVE_LOG, 
CURRENT_ACTIVE_LOG, 
CURRENT_ARCHIVE_LOG 
FROM TABLE(SNAP_GET_DETAILLOG('', 1)) AS T”

You can rewrite that query to use the MON_GET_TRANSACTION_LOG function:

db2 “SELECT FIRST_ACTIVE_LOG, 
LAST_ACTIVE_LOG, 
CURRENT_ACTIVE_LOG, 
CURRENT_ARCHIVE_LOG 
FROM TABLE(MON_GET_TRANSACTION_LOG(1)) AS T”

SNAPUTIL view

The following example uses the SNAPUTIL view to list all utilities executing on the currently connected database:

SELECT UTILITY_TYPE, UTILITY_PRIORITY, 
       SUBSTR(UTILITY_DESCRIPTION, 1, 72)
              AS UTILITY_DESCRIPTION, 
       SUBSTR(UTILITY_DBNAME, 1, 17) AS UTILITY_DBNAME, 
       UTILITY_INVOKER_TYPE, 
       DBPARTITIONNUM 
  FROM SYSIBMADM.SNAPUTIL ORDER BY DBPARTITIONNUM

You can rewrite that query to use the MON_GET_UTILITY function:

db2 “SELECT u1.UTILITY_TYPE,
            u1.UTILITY_PRIORITY,
            SUBSTR(UTILITY_DETAIL, 1, 72) 
                 AS UTILITY_DESCRIPTION,
            CURRENT SERVER,
            u1.UTIITY_INVOKER_TYPE,
            u1.MEMBER
  FROM TABLE(MON_GET_UTILITY(-2)) as u1
  ORDER BY u1.MEMBER”

Conclusion

The in-memory metrics monitor interfaces provide unprecedented access to DB2 monitor data, with better performance than the snapshot monitor interfaces. With the equivalency information in this article, you are well equipped to begin migrating from the snapshot monitor interfaces to the in-memory metrics monitor interfaces.

Acknowledgements

The authors would like to acknowledge Serge Boivin and Leslie McDonald for their review and comments on this article.

Resources

Learn

Get products and technologies

  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, or use a product in a cloud environment.

Discuss

  • Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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=978047
ArticleTitle=DB2 monitoring: Migrate from snapshot monitor interfaces to in-memory metrics monitor interfaces
publish-date=07172014