Background
Post configuring DBeaver to enable IBM DB2/UDB Instance access, wanted to explore Queries that will help my understanding.
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.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.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.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.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.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.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.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_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
Source Code
GitHub
DanielAdeniji/IBMUDBSchemaSYSIBMADM
Link
