Db2 Mainframe

1. z/OS DB2 Subsystem Fundamentals

Subsystem Identification

Every DB2 subsystem on z/OS has a 1-4 character SSID (Subsystem ID), e.g., DB2P (production), DB2T (test), DB2D (development). The SSID is used in all commands and JCL.

Address Spaces

Starting and Stopping the Subsystem

// From the z/OS console:
-DB2P START DB2
-DB2P STOP DB2 MODE(QUIESCE)       /* Graceful — waits for threads to finish  */
-DB2P STOP DB2 MODE(FORCE)         /* Forced — terminates active threads      */

// Start with specific ZPARM member:
-DB2P START DB2,PARM(DSNZPARM),MSTR(DB2PMSTR),DBM1(DB2PDBM1)

DSNZPARM — Subsystem Parameter Module

DSNZPARM is the load module containing all subsystem parameters. Built from macro DSNTIJUZ and loaded at startup.

Key parameter groups:

Changing ZPARMs at runtime (DB2 12+):

-DB2P SET SYSPARM LOAD(DSNZP01N)

This loads a new ZPARM module without restarting DB2. Not all parameters are dynamic — some require a restart (documented in IBM's "Installable Parameters" reference).

2. Storage and Buffer Pools

Buffer Pool Architecture

DB2 for z/OS has 50 buffer pools for 4K pages (BP0-BP49), 10 for 8K (BP8K0-BP8K9), 10 for 16K (BP16K0-BP16K9), and 10 for 32K (BP32K0-BP32K9). Each tablespace and indexspace is assigned to exactly one buffer pool.

Assignment Strategy

ALTER BUFFERPOOL

-- Increase virtual pool size to 50,000 4K pages (~200 MB)
-DB2P ALTER BUFFERPOOL(BP1) VPSIZE(50000)

-- Set thresholds
-DB2P ALTER BUFFERPOOL(BP1)
      VPSEQT(80)          /* Max % of pool for sequential steal    */
      DWQT(50)            /* Deferred write threshold (%)          */
      VDWQT(10)           /* Vertical deferred write threshold (%) */

-- Activate a buffer pool (if not already active)
-DB2P ALTER BUFFERPOOL(BP2) VPSIZE(20000)

Tuning Parameters

Monitoring Buffer Pool Health

-DB2P DISPLAY BUFFERPOOL(BP1) DETAIL(*)

Key metrics from output:

Simulated Buffer Pools

For cost-effective development/test, use simulated pools that consume no real storage:

-DB2P ALTER BUFFERPOOL(BP10) VPSIZE(5000) PGSTEAL(NONE)

PGSTEAL(NONE) means pages are never cached — every read goes to disk. Used only for testing or very low-priority data.

Group Buffer Pools (Data Sharing)

In a data sharing group, group buffer pools (GBP) reside in the coupling facility and cache cross-member interest pages.

-DB2P ALTER GROUPBUFFERPOOL(GBP1)
      GBPCACHE(YES)         /* Cache changed pages in GBP           */
      RATIO(50)             /* % of castout buffer threshold        */

3. Object Administration

Database and Tablespace Hierarchy

DATABASE
  └── TABLESPACE (holds tables)
       └── TABLE
            └── INDEX → INDEXSPACE

Creating a Database

CREATE DATABASE MYAPPDB
  STOGROUP SYSDEFLT
  BUFFERPOOL BP1
  INDEXBP BP2
  CCSID EBCDIC;

Tablespace Types

Creating a PBR UTS Tablespace

CREATE TABLESPACE ORDERTS
  IN MYAPPDB
  USING STOGROUP SYSDEFLT
  PRIQTY 7200
  SECQTY 7200
  BUFFERPOOL BP1
  LOCKSIZE ROW
  MAXROWS 255
  SEGSIZE 64
  COMPRESS YES
  PARTITION BY RANGE(ORDER_DATE)
  (PARTITION 1 ENDING AT ('2026-03-31') INCLUSIVE,
   PARTITION 2 ENDING AT ('2026-06-30') INCLUSIVE,
   PARTITION 3 ENDING AT ('2026-09-30') INCLUSIVE,
   PARTITION 4 ENDING AT ('2026-12-31') INCLUSIVE);

Creating a PBG UTS Tablespace

CREATE TABLESPACE CUSTTS
  IN MYAPPDB
  USING STOGROUP SYSDEFLT
  BUFFERPOOL BP1
  LOCKSIZE ROW
  SEGSIZE 32
  MAXPARTITIONS 256
  COMPRESS YES;

Creating Tables

CREATE TABLE MYSCHEMA.ORDERS
  (ORDER_ID      INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
   CUSTOMER_ID   INTEGER NOT NULL,
   ORDER_DATE    DATE NOT NULL WITH DEFAULT,
   TOTAL_AMOUNT  DECIMAL(15,2) NOT NULL DEFAULT 0,
   STATUS        CHAR(2) NOT NULL DEFAULT 'OP',
   CREATED_TS    TIMESTAMP NOT NULL WITH DEFAULT,
   PRIMARY KEY (ORDER_ID))
  IN MYAPPDB.ORDERTS
  COMPRESS YES
  APPEND NO;

Partitioning Strategies

Compression

-- Enable compression on existing tablespace
ALTER TABLESPACE MYAPPDB.ORDERTS COMPRESS YES;
-- Compression takes effect after next REORG

Compression saves DASD and reduces I/O. Typical savings: 50-70% for repetitive business data. CPU trade-off is minimal on modern z15/z16 with hardware compression (zEDC).

4. Binding — Plans and Packages

Concepts

BIND PACKAGE

//BINDPKG  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2P)
  BIND PACKAGE(COLLID01) -
       MEMBER(MYPRGRM) -
       LIBRARY('MYAPP.PROD.DBRM') -
       ACT(REP) -
       ISOLATION(CS) -
       CURRENTDATA(NO) -
       VALIDATE(BIND) -
       EXPLAIN(YES) -
       RELEASE(COMMIT) -
       QUALIFIER(MYSCHEMA) -
       ENCODING(EBCDIC)
  END
/*

BIND PLAN

//BINDPLN  EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2P)
  BIND PLAN(MYPLAN) -
       PKLIST(COLLID01.*) -
       ACT(REP) -
       ISOLATION(CS) -
       VALIDATE(BIND) -
       CURRENTDATA(NO) -
       ACQUIRE(USE) -
       RELEASE(COMMIT)
  END
/*

Key BIND Options

REBIND

Rebind to pick up new statistics or changed ZPARMs:

DSN SYSTEM(DB2P)
REBIND PACKAGE(COLLID01.MYPRGRM) EXPLAIN(YES)
END

For mass rebind of all packages in a collection:

DSN SYSTEM(DB2P)
REBIND PACKAGE(COLLID01.(*)) EXPLAIN(YES)
END

Plan vs Package Trade-offs

5. Utilities

This is the most operationally critical section. DB2 utilities run as batch jobs via JCL.

Common JCL Pattern for DB2 Utilities

//UTILSTEP EXEC PGM=DSNUTILB,PARM='DB2P,xxxxxxxx',REGION=0M
//STEPLIB  DD DISP=SHR,DSN=DB2.V13.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  <utility control statements>
/*
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,50))

The xxxxxxxx in PARM is the utility ID (1-16 characters). Each running utility must have a unique ID within the subsystem.

REORG TABLESPACE

Reorganizes data to reclaim fragmented space and restore clustering order.

//REORGTS  EXEC PGM=DSNUTILB,PARM='DB2P,RG01ORDS',REGION=0M
//STEPLIB  DD DISP=SHR,DSN=DB2.V13.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSREC   DD DSN=MYAPP.REORG.SYSREC,
//            DISP=(NEW,DELETE,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(500,100))
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(200,50))
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(200,50))
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(200,50))
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(200,50))
//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(200,50))
//SYSIN    DD *
  REORG TABLESPACE MYAPPDB.ORDERTS
    SHRLEVEL CHANGE
    STATISTICS TABLE(ALL) INDEX(ALL) REPORT NO
    COPYDDN(SYSCOPY)
    SORTDEVT SYSDA
    SORTNUM 4
    LOG NO
    TIMEOUT TERM
/*
//SYSCOPY  DD DSN=MYAPP.REORG.ICOPY,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(200,50))

SHRLEVEL options:

Inline COPY and STATS: the example above includes STATISTICS TABLE(ALL) INDEX(ALL) (inline RUNSTATS) and COPYDDN(SYSCOPY) (inline image copy). This avoids separate utility runs post-REORG.

REORG INDEX

//SYSIN    DD *
  REORG INDEX(MYSCHEMA.ORDERS_PK)
    SHRLEVEL CHANGE
    STATISTICS INDEX(ALL)
    SORTDEVT SYSDA
    SORTNUM 4
/*

RUNSTATS

Collects statistics for the optimizer. Must run after REORG and LOAD.

//RUNSTATS EXEC PGM=DSNUTILB,PARM='DB2P,RS01ORDS',REGION=0M
//STEPLIB  DD DISP=SHR,DSN=DB2.V13.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  RUNSTATS TABLESPACE MYAPPDB.ORDERTS
    TABLE(ALL)
      COLUMN(ALL)
      COLGROUP(ORDER_DATE, STATUS)
      FREQVAL COUNT 20 BOTH
      HISTOGRAM NUMQUANTILES 100
    INDEX(ALL)
      KEYCARD
    SHRLEVEL CHANGE
    REPORT NO
    UPDATE ALL
/*

Key RUNSTATS options:

COPY (Image Copy)

//IMGCOPY  EXEC PGM=DSNUTILB,PARM='DB2P,IC01ORDS',REGION=0M
//STEPLIB  DD DISP=SHR,DSN=DB2.V13.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSCOPY  DD DSN=MYAPP.ORDERTS.FULLCPY.D260331,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(300,50))
//SYSIN    DD *
  COPY TABLESPACE MYAPPDB.ORDERTS
    FULL YES
    SHRLEVEL REFERENCE
    COPYDDN(SYSCOPY)
/*

Copy types:

RECOVER

//RECOVER  EXEC PGM=DSNUTILB,PARM='DB2P,RC01ORDS',REGION=0M
//STEPLIB  DD DISP=SHR,DSN=DB2.V13.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  RECOVER TABLESPACE MYAPPDB.ORDERTS
    TOLOGPOINT X'00000ABC12340000'
    TORBA X'00000ABC12340000'
/*

Point-in-time recovery:

  RECOVER TABLESPACE MYAPPDB.ORDERTS
    TOPOINT IN TIME '2026-03-31-10.30.00'

Recovery to current: simply RECOVER TABLESPACE MYAPPDB.ORDERTS with no TORBA/TOLOGPOINT — applies image copy + all subsequent log records.

LOAD

//LOADDATA EXEC PGM=DSNUTILB,PARM='DB2P,LD01ORDS',REGION=0M
//STEPLIB  DD DISP=SHR,DSN=DB2.V13.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSREC   DD DSN=MYAPP.ORDERS.LOADDATA,DISP=SHR
//SYSIN    DD *
  LOAD DATA INDDN(SYSREC)
    INTO TABLE MYSCHEMA.ORDERS
    REPLACE
    LOG NO
    ENFORCE CONSTRAINTS
    STATISTICS TABLE(ALL) INDEX(ALL)
    COPYDDN(SYSCOPY)
    (ORDER_ID     POSITION(1)   INTEGER,
     CUSTOMER_ID  POSITION(5)   INTEGER,
     ORDER_DATE   POSITION(9)   DATE EXTERNAL,
     TOTAL_AMOUNT POSITION(19)  DECIMAL EXTERNAL(15,2),
     STATUS       POSITION(35)  CHAR(2))
/*
//SYSCOPY  DD DSN=MYAPP.ORDERTS.LOADCPY.D260331,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(300,50))

QUIESCE

Establishes a recovery point (quiesce point) without taking a full copy:

//SYSIN    DD *
  QUIESCE TABLESPACE MYAPPDB.ORDERTS
/*

CHECK DATA / CHECK INDEX / CHECK LOB

Verifies structural integrity:

//SYSIN    DD *
  CHECK DATA TABLESPACE MYAPPDB.ORDERTS
    SCOPE ALL
    SORTDEVT SYSDA
    SORTNUM 4
/*

//SYSIN    DD *
  CHECK INDEX(ALL) TABLESPACE MYAPPDB.ORDERTS
    SHRLEVEL REFERENCE
    SORTDEVT SYSDA
    SORTNUM 4
/*

MODIFY RECOVERY

Deletes old image copy and log records from SYSIBM.SYSCOPY to free catalog space:

//SYSIN    DD *
  MODIFY RECOVERY TABLESPACE MYAPPDB.ORDERTS
    DELETE AGE(90)
/*

6. Performance — EXPLAIN and Access Path Analysis

EXPLAIN Infrastructure

Create the EXPLAIN tables under the binder's schema:

CREATE TABLE MYSCHEMA.PLAN_TABLE LIKE DSN8C13.PLAN_TABLE IN MYAPPDB.EXPLTS;
CREATE TABLE MYSCHEMA.DSN_STATEMNT_TABLE LIKE DSN8C13.DSN_STATEMNT_TABLE IN MYAPPDB.EXPLTS;
CREATE TABLE MYSCHEMA.DSN_FUNCTION_TABLE LIKE DSN8C13.DSN_FUNCTION_TABLE IN MYAPPDB.EXPLTS;
CREATE TABLE MYSCHEMA.DSN_FILTER_TABLE LIKE DSN8C13.DSN_FILTER_TABLE IN MYAPPDB.EXPLTS;
CREATE TABLE MYSCHEMA.DSN_PREDICAT_TABLE LIKE DSN8C13.DSN_PREDICAT_TABLE IN MYAPPDB.EXPLTS;

Running EXPLAIN

Inline with BIND:

BIND PACKAGE(COLLID01) MEMBER(MYPRGRM) LIBRARY('MYAPP.PROD.DBRM') EXPLAIN(YES)

Ad-hoc:

EXPLAIN ALL SET QUERYNO = 1001 FOR
  SELECT O.ORDER_ID, O.ORDER_DATE, C.CUSTOMER_NAME
  FROM MYSCHEMA.ORDERS O
    INNER JOIN MYSCHEMA.CUSTOMERS C
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
  WHERE O.ORDER_DATE BETWEEN '2026-01-01' AND '2026-03-31'
    AND O.STATUS = 'CL';

Reading PLAN_TABLE

SELECT QUERYNO, QBLOCKNO, PLANNO, METHOD, CREATOR, TNAME,
       ACCESSTYPE, MATCHCOLS, ACCESSNAME, INDEXONLY,
       PREFETCH, SORTC_UNIQ, SORTC_JOIN, SORTC_ORDERBY,
       SORTC_GROUPBY, TSLOCKMODE, TIMESTAMP
FROM MYSCHEMA.PLAN_TABLE
WHERE QUERYNO = 1001
ORDER BY QUERYNO, QBLOCKNO, PLANNO;

Access Types

Predicate Analysis

Common Stage 2 traps:

DSN_STATEMNT_TABLE — Statement-Level Cost

SELECT QUERYNO, PROCMS, PROCSU, COST_CATEGORY, REASON_CODE
FROM MYSCHEMA.DSN_STATEMNT_TABLE
WHERE QUERYNO = 1001;

PROCMS = estimated milliseconds. COST_CATEGORY = 'A' (optimal), 'B' (suboptimal — missing index/stats), 'C' (expensive — tablespace scan).

RID Pool and Sort Optimization

The RID pool stores Record IDs during list prefetch and multiple index access. If the RID pool overflows, DB2 falls back to a tablespace scan.

-DB2P DISPLAY BUFFERPOOL(BP1) DETAIL(*)

Check RID POOL FAIL count. If non-zero, increase MAXRBLK in ZPARM.

Sort work files: allocate sufficient SORTWK datasets. DB2 uses in-memory sort when possible; overflow goes to SORTWK. Monitor SORT OVERFLOW in DB2 accounting trace (IFCID 3).

IFCID Traces

IFCID (Instrumentation Facility Component Identifier) traces collect detailed performance data:

Starting a trace:

-DB2P START TRACE(PERFM) DEST(SMF) CLASS(1,2,3) IFCID(3,239)
-DB2P STOP TRACE(PERFM) TNO(01)

RMF / SMF Data

SMF Type 101 (DB2 accounting) and Type 102 (DB2 statistics) records feed into tools like IBM Db2 Administration Tool, BMC MainView, or OMEGAMON. Use these for long-term trend analysis.

7. Data Sharing

Overview

DB2 data sharing allows multiple DB2 subsystems (members) on different LPARs to share the same data concurrently. Uses a Parallel Sysplex with coupling facility structures.

Architecture Components

Member-Specific vs Group-Level Commands

// Member-specific (only affects the member you issue it on):
-DB2P DISPLAY DATABASE(MYAPPDB) SPACENAM(ORDERTS)

// Group-level (affects all members — use GROUP keyword):
-DB2G DISPLAY DATABASE(MYAPPDB) SPACENAM(ORDERTS) SCOPE(GROUP)
-DB2G DISPLAY GROUPBUFFERPOOL(GBP0) DETAIL(*)
-DB2G START DATABASE(MYAPPDB) SPACENAM(ORDERTS) ACCESS(RW) SCOPE(GROUP)

Coupling Facility Sizing

Key considerations:

• Lock structure: size based on max concurrent locks across all members. Under-sizing causes lock structure full (LSF) events and false contention.
• SCA: relatively small; size per IBM recommendations.
• GBP: size based on inter-member read/write ratio. Higher cross-invalidation = larger GBP needed.

Data Sharing Performance Concerns

8. Security

RACF Integration

DB2 for z/OS uses RACF (or compatible ESM) for external security. Two levels:

1. Connection security — controls who can connect to DB2 (RACF class DSNR)
2. Object security — controls who can access DB2 objects (DB2 GRANT/REVOKE or RACF class DSNADM)

RACF Resource Classes

Authorization IDs

GRANT / REVOKE

-- Grant SELECT on a table
GRANT SELECT ON TABLE MYSCHEMA.ORDERS TO USRAPP01;

-- Grant with GRANT option (can re-grant)
GRANT SELECT, INSERT, UPDATE ON TABLE MYSCHEMA.ORDERS
  TO USRDBA01 WITH GRANT OPTION;

-- Grant EXECUTE on a package
GRANT EXECUTE ON PACKAGE COLLID01.MYPRGRM TO USRAPP01;

-- Grant to a RACF group (secondary AuthID)
GRANT SELECT ON TABLE MYSCHEMA.ORDERS TO GRPPROD;

-- Revoke
REVOKE INSERT ON TABLE MYSCHEMA.ORDERS FROM USRAPP01;

Row and Column Access Control (RCAC)

DB2 12+ supports fine-grained access:

-- Column mask: hide salary from non-HR users
CREATE MASK MYSCHEMA.SALARY_MASK ON MYSCHEMA.EMPLOYEES
  FOR COLUMN SALARY RETURN
    CASE WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'GRPHR') = 1
         THEN SALARY
         ELSE 0.00
    END
  ENABLE;

-- Row permission: users see only their department
CREATE PERMISSION MYSCHEMA.DEPT_PERM ON MYSCHEMA.EMPLOYEES
  FOR ROWS WHERE DEPT_CODE IN (
    SELECT DEPT_CODE FROM MYSCHEMA.USER_DEPTS
    WHERE USER_ID = SESSION_USER)
  ENFORCED FOR ALL ACCESS
  ENABLE;

-- Activate RCAC on the table
ALTER TABLE MYSCHEMA.EMPLOYEES ACTIVATE ROW ACCESS CONTROL ACTIVATE COLUMN ACCESS CONTROL;

Trusted Contexts

Allow credential propagation from middleware without password re-authentication:

CREATE TRUSTED CONTEXT WEBCTX
  BASED UPON CONNECTION USING SYSTEM AUTHID WEBSRVID
  ATTRIBUTES (JOBNAME 'WASSVR*')
  WITH USE FOR USRAPP01 WITHOUT AUTHENTICATION,
               USRAPP02 WITHOUT AUTHENTICATION
  ENABLE;

Audit Traces

-DB2P START TRACE(AUDIT) DEST(SMF) CLASS(1,2,3,4,5,6,7,8)

Audit IFCID classes: 1 = access attempts, 2 = GRANT/REVOKE, 3 = CREATE/ALTER/DROP, 4 = first read/write, 5 = BIND, 6 = authority checks, 7 = changes to audit policy, 8 = utility execution.

9. Monitoring and Diagnostics

-DISPLAY Commands

These are the DBA's primary real-time diagnostic tools:

// Database/tablespace status
-DB2P DISPLAY DATABASE(MYAPPDB) SPACENAM(ORDERTS) LOCKS CLAIMERS USE

// Active threads
-DB2P DISPLAY THREAD(*) TYPE(*)

// Specific plan's threads
-DB2P DISPLAY THREAD(*) TYPE(*) PLAN(MYPLAN)

// Buffer pool summary
-DB2P DISPLAY BUFFERPOOL(BP1) DETAIL(*)

// Log status
-DB2P DISPLAY LOG

// Utility status
-DB2P DISPLAY UTILITY(*)

// Archive log status
-DB2P DISPLAY ARCHIVE

Restrictive Status Codes

When -DISPLAY DATABASE shows abnormal status:

Resolving pending states:

// Recovery pending
-DB2P START DATABASE(MYAPPDB) SPACENAM(ORDERTS) ACCESS(RO)
// Then run RECOVER utility, then:
-DB2P START DATABASE(MYAPPDB) SPACENAM(ORDERTS) ACCESS(RW)

// Terminate a hung utility
-DB2P TERM UTILITY(RG01ORDS)

OMEGAMON for DB2

IBM OMEGAMON (or BMC MainView for DB2) provides real-time monitoring dashboards with drill-down into:

• Thread activity and SQL elapsed time
• Lock contention and deadlocks
• Buffer pool hit ratios
• Dynamic SQL cache efficiency
• Coupling facility metrics (data sharing)

DSNACCOR / DSNACCOX — Access Path Advisory

These stored procedures identify packages with inefficient access paths that would benefit from REBIND:

CALL SYSPROC.DSNACCOX(
  'MYSCHEMA',     -- qualifier
  '',             -- owner
  'COLLID01',     -- collection
  '',             -- package name (blank = all)
  '',             -- planname
  1,              -- query type (1 = all)
  'Y',            -- check rebind recommended
  NULL            -- output table
);

Output in DSNACCOX_OUTPUT table identifies packages where:

• Statistics have changed significantly since last BIND
• Access paths have degraded
• New indexes are available but not used

DB2 Accounting and Statistics Reports

// Start accounting trace
-DB2P START TRACE(ACCTG) DEST(SMF) CLASS(1,2,3)

// Start statistics trace
-DB2P START TRACE(STAT) DEST(SMF) CLASS(1,3,4,5,6)

Key SMF record types:

• SMF 101 — DB2 accounting (per-thread resource usage)
• SMF 102 — DB2 statistics (subsystem-wide counters)
• SMF 100 — DB2 trace data (IFCIDs)

10. Catalog and Directory

Catalog (SYSIBM Tables)

The DB2 catalog is a set of tables in the SYSIBM schema describing all DB2 objects. Key tables:

Common Catalog Queries

-- List all tables in a schema
SELECT NAME, TYPE, DBNAME, TSNAME, CARDF, NPAGES
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'MYSCHEMA'
ORDER BY NAME;

-- Find all indexes on a table
SELECT I.NAME, I.UNIQUERULE, I.CLUSTERING, I.NLEAF, I.NLEVELS,
       I.FIRSTKEYCARDF, I.FULLKEYCARDF
FROM SYSIBM.SYSINDEXES I
WHERE I.TBCREATOR = 'MYSCHEMA' AND I.TBNAME = 'ORDERS'
ORDER BY I.CLUSTERING DESC, I.NAME;

-- Check image copy status for a tablespace
SELECT DSNAME, ICTYPE, SHRLEVEL, TIMESTAMP, DSNUM
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'MYAPPDB' AND TSNAME = 'ORDERTS'
  AND ICTYPE IN ('F', 'I')
ORDER BY TIMESTAMP DESC
FETCH FIRST 10 ROWS ONLY;

-- Find packages that reference a table
SELECT DNAME AS PACKAGE_NAME, DCOLLID AS COLLECTION, BQUALIFIER,
       DCONTOKEN, BINDTIME
FROM SYSIBM.SYSPACKDEP
WHERE BNAME = 'ORDERS' AND BQUALIFIER = 'MYSCHEMA'
ORDER BY BINDTIME DESC;

-- Check RealTime statistics for REORG candidates
SELECT DBNAME, NAME AS TSNAME, REORGINSERTS, REORGDELETES,
       REORGUPDATES, REORGDISORGLOB, REORGCLUSTERSENS, STATSLASTTIME
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = 'MYAPPDB'
ORDER BY REORGDISORGLOB DESC;

-- Find columns with missing stats (COLCARDF = -1)
SELECT TBCREATOR, TBNAME, NAME, COLTYPE, COLCARDF
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'MYSCHEMA' AND COLCARDF = -1
ORDER BY TBNAME, COLNO;

-- List plans and their package lists
SELECT P.NAME AS PLANNAME, PL.COLLECTIONID, PL.NAME AS PACKAGENAME
FROM SYSIBM.SYSPLAN P
  INNER JOIN SYSIBM.SYSPKLIST PL ON P.NAME = PL.PLANNAME
WHERE P.NAME = 'MYPLAN'
ORDER BY PL.COLLECTIONID, PL.NAME;

Directory (Internal Structures)

The DB2 directory is separate from the catalog — it contains internal structures DB2 needs at runtime. Never modify directory tables directly.

Catalog Health

-- Check catalog tablespace sizes
SELECT DBNAME, NAME, NACTIVEF, SPACE
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'DSNDB06'
ORDER BY SPACE DESC;

DSNDB06 is the catalog database. Monitor its tablespace sizes and REORG regularly, especially SYSDBASE (DBD01 data), SYSPLAN, and SYSPACKAGE after heavy DDL or BIND activity.

Anti-Patterns

Related Skills