DB/2 – LUW- The “sysibmadm” schema

Background

Post configuring DBeaver to enable IBM DB2/UDB Instance access, wanted to explore Queries that will help my understanding.

Views

View Definition CLP Command Based Link
APPLICATIONS Connected database applications LIST APPLICATIONS SHOW DETAIL SNAPAPPL_INFO administrative view Link
SNAPTAB table logical data group snapshot information for the currently connected database GET SNAPSHOT FOR TABLES ON database-alias SNAPTAB_REORG administrative view, the SNAPTAB administrative view Link
TBSP_UTILIZATION Table space configuration and utilization information LIST TABLESPACES SNAPTBSP, SNAPTBSP_PART administrative views and TABLESPACES catalog view Link
mon_bp_utilization Buffer Pool Utilization db2 get snapshot for all bufferpools Link

Code

sysibmadm.applications

SQL


/*
	APPLICATIONS administrative view - Retrieve connected database application information
    https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022011.html
 */
SELECT
           tblApp.DB_NAME AS "database"
         , tblApp.AUTHID "userID"
         , tblApp.APPL_NAME AS "applicationName"
         , tblApp.APPL_ID AS "applicationID"
         , tblApp.CLIENT_NNAME AS "clientHost"
         , tblApp.TPMON_CLIENT_WKSTN AS "clientWorkStation"
         , tblApp.TPMON_CLIENT_APP AS "clientApp"
         , tblApp.CLIENT_PLATFORM AS "clientPlatform"
         , tblApp.CLIENT_PROTOCOL AS "clientProtocol"
         , tblApp.APPL_STATUS AS "applStatus"

--         , tblApp.*

FROM SYSIBMADM.APPLICATIONS tblApp

ORDER by
           tblApp.DB_NAME ASC
         , tblApp.AUTHID ASC
		 , tblApp.APPL_NAME

Output

SYSIBMADM.APPLICATIONS_20180817_0102PM

sysibmadm.snaptab

SQL


SELECT 

          CURRENT_SERVER AS "Database"

       , tblSNT.TABSCHEMA AS "Schema"

       , tblSNT.TABNAME  AS "Table"       

       , tblSNT.TAB_TYPE AS "TableType"

       , tblSNT.TAB_FILE_ID AS "FileIDOfSnapTable"

	  -- , tblSST.FID AS "FileIDOfTable"

       , tblTS.TBSP_NAME AS "TSNameInTableUtil"

	   , tblSST.TBSPACE AS "TSNameDataInSYSTable"

	   , tblSST.INDEX_TBSPACE AS "TSNameIndexInSYSTable"	   

       , tblTS.TBSP_TYPE AS "TSType"

       , tblSNT.DATA_OBJECT_PAGES AS "PagesDO"

       , tblSNT.INDEX_OBJECT_PAGES AS "PagesIO"

       , tblSNT.LOB_OBJECT_PAGES   AS "PagesLOB"    

       , tblSNT.ROWS_READ AS "RowsRead"

       , tblSNT.ROWS_WRITTEN AS "RowsWritten"

       , tblSNT.OVERFLOW_ACCESSES AS "OverflowAccess"

       , tblSNT.PAGE_REORGS AS "PagesReOrg"

FROM   sysibmadm.snaptab tblSNT

INNER JOIN SYSIBM.SYSTABLES tblSST

       on  tblSNT.TABSCHEMA = tblSST.CREATOR

       and tblSNT.TABNAME = tblSST.NAME       

LEFT OUTER JOIN SYSIBMADM.TBSP_UTILIZATION tblTS

        ON tblSNT.TAB_FILE_ID = tblTS.TBSP_ID

ORDER BY

          ( tblSNT.ROWS_READ + tblSNT.ROWS_WRITTEN ) DESC

       ,  tblSNT.TABSCHEMA

       , tblSNT.TABNAME

Output

sysibmadm.snaptab.20180817.1128AM.PNG

SYSIBMADM.TOP_DYNAMIC_SQL

SQL


SELECT 

       -- ( tblTDS.NUM_EXECUTIONS * tblTDS.AVERAGE_EXECUTION_TIME_S ) AS "weight"

         tblTDS.AVERAGE_EXECUTION_TIME_S AS "AverageExecTimeInSec"

       , tblTDS.NUM_EXECUTIONS AS "# of Executions"

       , tblTDS.STMT_TEXT AS "SQL"

       , tblTDS.STMT_SORTS AS "StmtSorts"

       , tblTDS.SORTS_PER_EXECUTION AS "SortsPerExec"      

FROM   SYSIBMADM.TOP_DYNAMIC_SQL tblTDS

ORDER BY
         ( tblTDS.NUM_EXECUTIONS * tblTDS.AVERAGE_EXECUTION_TIME_S ) DESC
       , tblTDS.AVERAGE_EXECUTION_TIME_S DESC
       , tblTDS.NUM_EXECUTIONS DESC

FETCH FIRST 100 ROWS ONLY

;

Output

SYSIBMADM.TOP_DYNAMIC_SQL_20180817_1234PM

SYSIBMADM.ENV_SYS_RESOURCES

SQL


SELECT  * 

FROM  SYSIBMADM.ENV_SYS_RESOURCES tblSR

WHERE  tblSR.NAME IN
(
           'CPU_TOTAL'
         , 'CPU_SPEED'
         , 'MEMORT_TOTAL'
         , 'MEMORY_FREE'
         , 'CPU_USAGE_TOTAL'

)

;

Output

SYSIBMADM.ENV_SYS_RESOURCES_20180817_0113PM

SYSIBMADM.DBPATHS

SQL


/*
 * DBPATHS administrative view - Retrieve database paths
 * https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>/com.ibm.db2.luw.sql.rtn.doc/doc/r0022037.html
*/
SELECT 

	     tblDP.PATH
	  ,  tblDP.TYPE

FROM SYSIBMADM.DBPATHS tblDP

ORDER BY
	     tblDP.PATH
	  ,  tblDP.TYPE

Output

sysibmadm.dbpaths_20180817_0142PM

SYSIBMADM.ENV_SYS_RESOURCES

SQL


SELECT  * 

FROM  SYSIBMADM.ENV_SYS_RESOURCES tblSR

WHERE  tblSR.NAME IN
(
           'CPU_TOTAL'
         , 'CPU_SPEED'
         , 'MEMORT_TOTAL'
         , 'MEMORY_FREE'
         , 'CPU_USAGE_TOTAL'

)

;

Output

SYSIBMADM.ENV_SYS_RESOURCES_20180817_0113PM

SYSIBMADM.TBSP_UTILIZATION

SQL


SELECT 

          CURRENT_SERVER AS "Database" 

		, tblTBU.TBSP_NAME AS "TSNAME"

		, tblTBU.TBSP_ID AS "TSID"		  

		, tblTBU.TBSP_TYPE AS "TSType"

		, tblTBU.TBSP_CONTENT_TYPE AS "TSContentType"	

		, tblTBU.TBSP_TOTAL_SIZE_KB/1024 AS "TotalSizeMB"

		, tblTBU.TBSP_USED_SIZE_KB/1024 AS "SizeMB"		

		, tblTBU.TBSP_FREE_SIZE_KB/1024 AS "FreeMB"

		, tblTBU.TBSP_UTILIZATION_PERCENT AS "%Util"		

		, tblTBU.TBSP_INCREASE_SIZE AS "TBIncreaseSize"

		, tblTBU.TBSP_INCREASE_SIZE_PERCENT AS "TBIncreaseSize%"

		-- , tblTBU.*

FROM   SYSIBMADM.TBSP_UTILIZATION tblTBU

ORDER BY

          tblTBU.TBSP_TOTAL_SIZE_KB DESC

		, tblTBU.TBSP_USED_SIZE_KB DESC         

        , tblTBU.TBSP_NAME ASC

Output

SYSIBMADM.TBSP_UTILIZATION_20180817_0200PM.PNG

SYSIBMADM.SNAPAPPL

SQL


SELECT 

         tblAppInfo.PRIMARY_AUTH_ID AS "AuthorizationID"

        , sum(tblAppSnap.ROWS_READ) AS "RowsRead"

        , sum(tblAppSnap.ROWS_WRITTEN) AS "RowsWritten"

FROM   SYSIBMADM.SNAPAPPL tblAppSnap

INNER JOIN SYSIBMADM.SNAPAPPL_INFO tblAppInfo

	on tblAppSnap.AGENT_ID = tblAppInfo.AGENT_ID

GROUP BY
         tblAppInfo.PRIMARY_AUTH_ID

ORDER BY
     sum
     	(
     		  tblAppSnap.ROWS_READ
     		+ tblAppSnap.ROWS_WRITTEN
 		) desc

Output

SYSIBMADM.SNAPAPPL_20180817_0208PM.PNG

SYSIBMADM.SNAPAPPL_INFO

SQL


SELECT 

         tblAppInfo.PRIMARY_AUTH_ID
       , tblAppInfo.APPL_NAME
       , tblAppInfo.DB_NAME
       , tblAppInfo.CLIENT_NNAME

       , tblAppSnap.ROWS_READ
       , tblAppSnap.ROWS_WRITTEN       

       , tblAppInfo.TPMON_CLIENT_WKSTN
       , tblAppInfo.TPMON_CLIENT_APP
       , tblAppInfo.CLIENT_PLATFORM
       , tblAppInfo.CLIENT_PROTOCOL

       , tblAppInfo.IS_SYSTEM_APPL

FROM   SYSIBMADM.SNAPAPPL tblAppSnap

INNER JOIN SYSIBMADM.SNAPAPPL_INFO tblAppInfo

	on tblAppSnap.AGENT_ID = tblAppInfo.AGENT_ID

ORDER BY

     (
     	  tblAppSnap.ROWS_READ

     	+ tblAppSnap.ROWS_WRITTEN
     )
     desc

Output

SYSIBMADM.SNAPAPPL_INFO.20180817_0220PM.PNG

SYSIBMADM.MON_BP_UTILIZATION

SQL


/*
 * 1a) Buffer Pools
 *     https://www.ibm.com/support/knowledgecenter/hu/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0052482.html
 *
 * 1b) Scenario: Monitoring buffer pool efficiency using built-in administrative views
 *     https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.admin.mon.doc/doc/c0022758.html
*/

WITH cteESR
 (
    hostname
 )
 AS
 (
    SELECT
            MAX
            (
                DECODE
                (
                      NAME
                    , 'HOST_NAME'
                    , VALUE
                )
            ) AS "hostname"

    FROM   SYSIBMADM.ENV_SYS_RESOURCES tblESR

    WHERE  tblESR.NAME IN
            (
               'HOST_NAME'
            )

 )

, cteBufferPool
as
(
    SELECT *

    FROM   SYSIBMADM.MON_BP_UTILIZATION tblBP

)

SELECT 

            cteBP.BP_NAME
			AS "name"

          , cteBP.DATA_PHYSICAL_READS
			AS "dataPhysicalReads"

          , cteBP.INDEX_PHYSICAL_READS
			AS "indexPhysicalReads"

          , cteBP.TOTAL_PHYSICAL_READS
			AS "totalPhysicalReads"

          , cteBP.TOTAL_WRITES
			AS "totalWrites"

          , cteBP.DATA_HIT_RATIO_PERCENT
			AS "dataHitRatio%"

          , cteBP.INDEX_HIT_RATIO_PERCENT
			AS "indexHitRatio%"

          , cteBP.PREFETCH_RATIO_PERCENT
			AS "prefetchRatio%"

          , cteBP.SYNC_WRITES_PERCENT
			AS "syncWrites%"

          --, cteBP.*

FROM cteBufferPool cteBP

CROSS JOIN cteESR

CROSS JOIN sysibmadm.env_inst_info tblEII

ORDER BY
	    cteBP.TOTAL_PHYSICAL_READS desc

FETCH FIRST 100 ROWS ONLY

Output

SYSIBMADM.MON_BP_UTILIZATION.20180826.0252PM.PNG

Source Code

GitHub

DanielAdeniji/IBMUDBSchemaSYSIBMADM
Link

 

References

  1. IBM
    • IBM Knowledge Center
      • Home > DB2 for Linux UNIX and Windows 9.7.0. . . > Snapshot routines and views
        • SNAPTAB_REORG
          • SNAPTAB_REORG administrative view and SNAP_GET_TAB_REORG table function – Retrieve table reorganization snapshot information
            Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s