DB2 z/OS DB2 LUW
z/OS CICS VTAM

DB2 LUW

SYSIBMADM Administration Views

ADMINTABCOMPRESSINFO	Compression Informationen
ADMINTABINFO	Table INFOS
ADMINTEMPCOLUMNS	Infos über Columns für temporary tables
ADMINTEMPTABLES	Infos über temporary tables
APPLICATIONS	Infos über Applications (List applications)
APPL_PERFORMANCE	Infos über Application Performance (RW,…)
AUTHORIZATIONIDS	Authorizations (Wer darf was in der DB)
BP_HITRATIO	Trefferrate des Bufferpools
BP_READ_IO	Infos über BP-Read
BP_WRITE_IO	Infos über BP-Write
CONTACTGROUPS	GET CONTACTGROUPS
CONTACTS	GET CONTACTS
CONTAINER_UTILIZATION	Table space container and utilization
DBCFG	Get db config
DBMCFG	Get dbm config
DBPATHS	Retrieve database paths
DB_HISTORY	List history
ENV_FEATURE_INFO	Installierte Zusatz Features
ENV_INST_INFO	Entspricht DB2LEVEL
ENV_PROD_INFO	Entspricht (DB2LICM)
ENV_SYS_INFO	Infos über das installierte System (UNIX, …)
ENV_SYS_RESOURCES	Mehr Infos über Server OS
LOCKS_HELD	Welche Applicationen halten Locks
LOCKWAITS	Welche Applicationen werden gelockt
LOG_UTILIZATION	Infos über benutzte LOGS
LONG_RUNNING_SQL	Info über Zeiten einer Application
MON_BP_UTILIZATION	Monitor Bufferbools
MON_CONNECTION_SUMMARY	Monitor Connections
MON_CURRENT_SQL	Monitor Laufende SQL-Statements
MON_CURRENT_UOW	Monitor Laufende UOW
MON_DB_SUMMARY	Monitor DB Info
MON_LOCKWAITS	Monitor Locking
MON_PKG_CACHE_SUMMARY	Monitor über (Cache, IO, CPU, …..)
MON_SERVICE_SUBCLASS_SUMMARY	Monitor Subclasses (Neu ab 9.7 FP 1)
MON_TBSP_UTILIZATION	Monitor Tablespace
MON_WORKLOAD_SUMMARY	Monitor WLM
NOTIFICATIONLIST	GET NOTIFICATION LIST
OBJECTOWNERS	Besitzer eines Objektes
PDLOGMSGS_LAST24HOURS	Probleme in den letzten 24 Stunden (Diag)
PRIVILEGES	Infos über Grands (Berechtigungen)
QUERY_PREP_COST	Infos über Kosten einer Query
REG_VARIABLES	DB2SET (Environment Variables)
SNAPAGENT	SNAP Laufende Agents
SNAPAGENT_MEMORY_POOL	SNAP Memory Verbrauch eines Agents
SNAPAPPL	SNAP Applications
SNAPAPPL_INFO	SNAP Mehr Infos über Applications
SNAPBP	SNAP Infos über Bufferpool
SNAPBP_PART	SNAP Infos über Partition
SNAPCONTAINER	SNAP Infos über TS-Container
SNAPDB	SNAP Infos über Database
SNAPDBM	SNAP DBM Infos über Installierte Version
SNAPDBM_MEMORY_POOL	SNAP DBM Infos Memory Pool
SNAPDB_MEMORY_POOL	SNAP DB Infos Memory Pool
SNAPDETAILLOG	SNAP Infos über benutzte LOGS
SNAPDYN_SQL	SNAP Dynamic SQL (welche SQL laufen)
SNAPFCM	SNAP Fast Communication Manager
SNAPFCM_PART	SNAP (FCM Partition)
SNAPHADR	SNAP (High Availability Disaster Recovery)
SNAPLOCK	SNAP Locking
SNAPLOCKWAIT	SNAP Locking Waits
SNAPSTMT	SNAP Running SQL-Statements
SNAPSTORAGE_PATHS	SNAP Infos über Storage Path
SNAPSUBSECTION	SNAP
SNAPSWITCHES	SNAP (Get DBM Monitor Switches)
SNAPTAB	SNAP Table Informationen
SNAPTAB_REORG	SNAP Infos über Reorgs
SNAPTBSP	SNAP Tablespace Info
SNAPTBSP_PART	SNAP Tablespace Partitions
SNAPTBSP_QUIESCER	SNAP Infos über Quiesce Tablespace
SNAPTBSP_RANGE	SNAP Ranges
SNAPUTIL	SNAP Infos über Utilitys (Backup, Load, ..)
SNAPUTIL_PROGRESS	SNAP Infos über Utilitys Laufzeiten
TBSP_UTILIZATION	List Tablespaces
TOP_DYNAMIC_SQL	List Top Running SQL

Beispiele :

wer hält die locks .

SELECT SL.AGENT_ID
     , SL.LOCK_OBJECT_TYPE
    , SL.LOCK_MODE
    , SL.LOCK_STATUS
     , LW.AGENT_ID
    , LW.LOCK_MODE
    , LW.LOCK_OBJECT_TYPE
    , LW.AGENT_ID_HOLDING_LK
    , LW.LOCK_MODE_REQUESTED
FROM SYSIBMADM.SNAPLOCK SL
Inner join SYSIBMADM.SNAPLOCKWAIT LW
ON LW.AGENT_ID = SL.AGENT_ID
and LW.DBPARTITIONNUM = SL.DBPARTITIONNUM
;

Anzeige welche Applicationen connected sind.

       SELECT     AGENT_ID, SUBSTR(APPL_NAME,1,10) AS APPL_NAME,
                AUTHID,
           APPL_STATUS
    FROM     SYSIBMADM.APPLICATIONS
    WHERE     DB_NAME = 'SAMPLE';

Zeigt top 5 Dynamic SQL’s

        SELECT     NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S,
            SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT
        FROM     SYSIBMADM.TOP_DYNAMIC_SQL
        ORDER     BY NUM_EXECUTIONS DESC
    FETCH     FIRST 5 ROWS ONLY;

List Tablespaces

        SELECT TBSP_ID, SUBSTR(TBSP_NAME,1,20) as TBSP_NAME,
               TBSP_TYPE,TBSP_CONTENT_TYPE,
               SUBSTR(TBSP_STATE,1,20) TBST_STATE
       FROM   SYSIBMADM.TBSP_UTILIZATION;

Bufferpool hit ratio

        SELECT     SUBSTR(DB_NAME,1,8) AS DB_NAME,
                SUBSTR(BP_NAME,1,40) AS BP_NAME,
           TOTAL_HIT_RATIO_PERCENT
       FROM     SYSIBMADM.BP_HITRATIO;

Zeigt derzeit verwendete „log utilization“ für database

        SELECT     SUBSTR(DB_NAME,1,10) DB_NAME,
                LOG_UTILIZATION_PERCENT
            TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB
        FROM     SYSIBMADM.LOG_UTILIZATION;

Zeigt alle “critical log messages” der letzten 24 Stunden

        SELECT     TIMESTAMP, SUBSTR(DBNAME,1,10) DBNAME,
                SUBSTR(MSG,1,80) MSG
        FROM     SYSIBMADM.PDLOGMSGS_LAST24HOURS
       WHERE     MSGSEVERITY = 'C'
    ORDER     BY TIMESTAMP DESC;

Zeigt “Database paths”

SELECT SUBSTR(TYPE,1,20) TYPE, SUBSTR(PATH,1,50) PATH
FROM SYSIBMADM.DBPATHS;

Wieviel “shared memory” kann benutzt werden

       SELECT SUBSTR(NAME,1,20) NAME, INT(VALUE)*4/1024 MB
       FROM   SYSIBMADM.DBCFG
       WHERE NAME = 'database_memory';

Liste aller MQT's in der Datenbank

       SELECT SUBSTR(CREATOR,1,20) CREATOR,
              SUBSTR(NAME,1,50) NAME
       FROM   SYSIBM.SYSTABLES
       WHERE TYPE = 'S';

Liste aller MDC's (Multi Dimensionale Cluster) in der Datenbank.

       SELECT SUBSTR(A.TABSCHEMA,1,18) TABSCHEMA,
               SUBSTR(A.TABNAME,1,30)    TABNAME
        FROM   SYSCAT.INDEXES A, SYSCAT.TABLES B
        WHERE (A.TABNAME=B.TABNAME
        AND    A.TABSCHEMA=B.TABSCHEMA)
        AND    A.INDEXTYPE = 'BLOK';

Prüfen ob “Query Parallelism” benutzt wird

       SELECT SUBSTR(NAME,1,20) NAME, SUBSTR(VALUE,1,20) VALUE
       FROM   SYSIBMADM.DBMCFG
       WHERE NAME = 'intra_parallel';

Prüfen ob “Connection Concentrator” benutzt wird

       SELECT CASE WHEN int(b.value) > int(a.value)
          THEN 'ENABLED' ELSE 'DISABLED'
          END AS "CONNECTION CONCENTRATOR"
       FROM    sysibmadm.dbmcfg a, sysibmadm.dbmcfg b
       WHERE   a.name = 'max_coordagents'
       AND     b.name = 'max_connections';

Listet XML columns in der datenbank

       SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
               SUBSTR(TABNAME,1,30)    TABNAME,
               SUBSTR(COLNAME,1,30)    COLNAME
        FROM   SYSCAT.COLUMNS
        WHERE TYPENAME = 'XML';

Check if any tables have row level compression enabled

       SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
               SUBSTR(TABNAME,1,30)    TABNAME
        FROM   SYSCAT.TABLES
        WHERE COMPRESSION IN ('R','B');

Listed Tables die “Label Based Access Control (LBAC)” benutzen

       SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
               SUBSTR(TABNAME,1,30)    TABNAME
        FROM   SYSCAT.TABLES
        WHERE SECPOLICYID > 0;

Ist HADR eingeschaltet ?? (High Availability Disaster Recovery)

       SELECT SUBSTR(NAME,1,20) NAME,
              CASE WHEN VALUE = 'STANDARD'
              THEN 'NO' ELSE 'YES' END
       FROM   SYSIBMADM.DBCFG
       WHERE NAME = 'hadr_db_role';

Zeigt “Physical space” die von einer Tabelle benutzt wird

SELECT     SUBSTR(TABSCHEMA,1,18)      TABSCHEMA,
             SUBSTR(TABNAME,1,30)      TABNAME,
             SUM(DATA_OBJECT_P_SIZE) AS DATA_OBJECT_P_SIZE,
             SUM(INDEX_OBJECT_P_SIZE) INDEX_OBJECT_P_SIZE,
             SUM(LONG_OBJECT_P_SIZE) LONG_OBJECT_P_SIZE,
             SUM(LOB_OBJECT_P_SIZE)      LOB_OBJECT_P_SIZE,
             SUM(XML_OBJECT_P_SIZE)      XML_OBJECT_P_SIZE
       FROM     SYSIBMADM.ADMINTABINFO
       WHERE     TABSCHEMA NOT LIKE 'SYS%'
       GROUP BY TABSCHEMA, TABNAME;

Zeigt 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

Zeigt 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;

Zeigt Queries aus dem 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))

Zeigt “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
Inner Join sysibmadm.snaplockwait lw
      ON lw.agent_id_holding_lk = ai_h.agent_id
Inner Join sysibmadm.snapappl_info ai_w
      ON lw.agent_id = ai_w.agent_id

Zeigt Excessive Sorting: (Großes ORDER BY”

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 AIT_TIME
FROM TABLE(MON_GET_CONNECTION(NULL,-1))

Zeigt die Transaktion mit der meisten CPU und der meisten “ 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))

Zeigt die 20 meist benutzen Tabellen:

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 20 ROWS ONLY

Zeigt “Critical and Error messages” der letzen 24 Stunden

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

Zeigt messages aus dem “notify log” der letzen 3 Tagen.

SELECT TIMESTAMP, SUBSTR(MSG,1,400) AS MSG
FROM TABLE
( PD_GET_LOG_MSGS( CURRENT TIMESTAMP - 3 DAYS) )
AS PD
ORDER BY TIMESTAMP DESC

Zeigt durchschnittliche und Maximum Zeit eines Fullbackup.

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'

Zeigt alle commands aus der “recovery history” die Fehler hatten

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

Zeigt Informationen der ältesten Application mit uncommitted unit of work.

(Gut zu wissen, welche Applicationen welche Ressourcen halten)

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
INNER JOIN SYSIBMADM.SNAPAPPL_INFO AI
      ON AI.AGENT_ID = DB.APPL_ID_OLDEST_XACT
INNER JOIN SYSIBMADM.SNAPAPPL AP
      ON AI.AGENT_ID = AP.AGENT_ID

;

Zeigt Laufende Reorgs, auch INPLACE mit status

SELECT *
FROM SYSIBMADM.SNAPTAB_REORG
Where REORG_STATUS <> 'COMPLETED' -- nur Laufende Reorg

;

Zeigt “Database size and maximum capacity”

CALL GET_DBSIZE_INFO(?, ?, ?, 0);