Db2 Consulting, 24×7 Support & Managed Services — Db2 LUW and Db2 for z/OS | MinervaDB

Db2 consulting evidence: top SQL by CPU and bufferpool hit ratios

-- Db2 consulting evidence, LUW: which statements own the CPU, the reads and the waits?
SELECT SUBSTR(stmt_text, 1, 80)                           AS statement,
       num_exec_with_metrics                               AS executions,
       total_cpu_time / 1000                               AS cpu_ms,      -- microseconds -> ms
       total_act_time                                      AS activity_ms,
       total_act_wait_time                                 AS wait_ms,
       lock_wait_time                                      AS lock_wait_ms,
       DECIMAL(rows_read, 31, 0) / NULLIF(rows_returned, 0) AS read_per_returned,
       sort_overflows                                      AS sorts_spilled
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) AS t   -- -2 = all members
ORDER BY total_cpu_time DESC
FETCH FIRST 15 ROWS ONLY;

-- Bufferpool hit ratios per pool (system pools excluded).
SELECT bp_name,
       CASE WHEN pool_data_l_reads > 0
            THEN DECIMAL(100 * (1 - DOUBLE(pool_data_p_reads) / pool_data_l_reads), 5, 2) END  AS data_hit_pct,
       CASE WHEN pool_index_l_reads > 0
            THEN DECIMAL(100 * (1 - DOUBLE(pool_index_p_reads) / pool_index_l_reads), 5, 2) END AS index_hit_pct
FROM TABLE(MON_GET_BUFFERPOOL(NULL, -2)) AS b
WHERE bp_name NOT LIKE 'IBMSYSTEMBP%';

-- Prove the access path of one statement:
--   db2 "SET CURRENT EXPLAIN MODE EXPLAIN"  ->  run the statement  ->  db2exfmt -d PRODDB -1 -o plan.txt

Database configuration baseline for Db2 12.1, with reason and activation per change

# Db2 consulting baseline, Db2 12.1 LUW, database PRODDB (illustrative values)
# Each line: change ; why ; when it takes effect
db2 "UPDATE DB CFG FOR PRODDB USING SELF_TUNING_MEM ON DATABASE_MEMORY AUTOMATIC"   # STMM owns memory ; online
db2 "ALTER BUFFERPOOL IBMDEFAULTBP SIZE AUTOMATIC"                                # STMM may grow or shrink it ; online
db2 "UPDATE DB CFG FOR PRODDB USING SHEAPTHRES_SHR AUTOMATIC SORTHEAP AUTOMATIC"  # needs instance SHEAPTHRES = 0 ; online
db2 "UPDATE DB CFG FOR PRODDB USING LOCKLIST AUTOMATIC MAXLOCKS AUTOMATIC"        # avoid lock escalation ; online
db2 "UPDATE DB CFG FOR PRODDB USING LOGFILSIZ 65536 LOGPRIMARY 40 LOGSECOND 60"   # 256 MB log files ; LOGFILSIZ at next activation
db2 "UPDATE DB CFG FOR PRODDB USING AUTO_RUNSTATS ON AUTO_STMT_STATS ON"          # statistics stay current ; online

# Switching from circular to archive logging puts the database in BACKUP PENDING.
# Do it only in a window, and take a full backup immediately afterwards.
db2 "UPDATE DB CFG FOR PRODDB USING LOGARCHMETH1 DISK:/db2/archlogs"

# Verify: current value next to the value pending activation.
db2 "GET DB CFG FOR PRODDB SHOW DETAIL" | grep -E "SORTHEAP|LOCKLIST|LOGFILSIZ|LOGARCHMETH1"

Top dynamic SQL from the statement cache, ranked by CPU

-- Db2 consulting evidence, z/OS: top dynamic SQL from the statement cache.
-- Prerequisites: statement statistics trace, e.g.
--   -START TRACE(MON) CLASS(30) IFCID(316,318)
-- and DSN_STATEMENT_CACHE_TABLE created under the current SQLID.
EXPLAIN STMTCACHE ALL;

SELECT STMT_ID,
       STAT_EXEC                        AS executions,
       DEC(STAT_CPU, 15, 3)             AS cpu_seconds,
       DEC(STAT_ELAP, 15, 3)            AS elapsed_seconds,
       STAT_GPAG                        AS getpages,
       STAT_SYNR                        AS sync_reads,
       STAT_ROWX                        AS rows_examined,
       STAT_ROWP                        AS rows_processed,
       SUBSTR(STMT_TEXT, 1, 80)         AS statement
FROM DSN_STATEMENT_CACHE_TABLE
ORDER BY STAT_CPU DESC
FETCH FIRST 15 ROWS ONLY;

V13R1M511 readiness: non-UTS table spaces, synonyms and per-object synchronous I/O

-- Db2 consulting readiness check for V13R1M511 (read-only catalog queries).
-- Confirm code values against the Db2 13 catalog reference for your
-- maintenance level before using the results to plan conversions.

-- 1. Table spaces that are not universal table spaces
--    ('G' = partition-by-growth, 'R' = partition-by-range; LOB 'O' and XML 'P' excluded).
SELECT DBNAME, NAME, TYPE, SEGSIZE, PARTITIONS
FROM SYSIBM.SYSTABLESPACE
WHERE TYPE NOT IN ('G', 'R', 'O', 'P')
ORDER BY DBNAME, NAME;

-- 2. Synonyms, which must become aliases or views before V13R1M511.
SELECT CREATOR, NAME, TBCREATOR, TBNAME
FROM SYSIBM.SYSSYNONYMS
ORDER BY CREATOR, NAME;

-- 3. Synchronous read I/O per table space partition from real-time statistics
--    (NSYNCREADIO, new at FL 509): object-level I/O attribution without a trace.
SELECT DBNAME, NAME, PARTITION, NSYNCREADIO, REORGINSERTS, REORGDELETES
FROM SYSIBM.SYSTABLESPACESTATS
ORDER BY NSYNCREADIO DESC
FETCH FIRST 20 ROWS ONLY;

Debezium Db2 connector registration, with prerequisites and secrets handled outside the request

# Db2 consulting modernization: register a Debezium Db2 connector with Kafka Connect.
# Prerequisites we verify first:
#   - ASN Capture running for the source tables (SQL replication; IBM states this
#     needs a separate IBM InfoSphere Data Replication licence)
#   - Db2 for z/OS: 10-byte RBA/LRSN, since 6-byte LSNs are not supported; z/OS support
#     is incubating in Debezium, so production use is a documented risk decision
# The password is resolved by Kafka's file config provider, never stored in this request.
curl -s -X POST http://connect.internal:8083/connectors \
  -H "Content-Type: application/json" \
  -d '{
    "name": "db2-orders-cdc",
    "config": {
      "connector.class": "io.debezium.connector.db2.Db2Connector",
      "database.hostname": "db2prod.internal",
      "database.port": "50000",
      "database.user": "cdc_reader",
      "database.password": "${file:/etc/kafka/secrets/db2.properties:password}",
      "database.dbname": "PRODDB",
      "topic.prefix": "db2prod",
      "table.include.list": "SALES.ORDERS,SALES.ORDER_LINES",
      "snapshot.mode": "initial",
      "schema.history.internal.kafka.bootstrap.servers": "kafka-1.internal:9092",
      "schema.history.internal.kafka.topic": "schema-history.db2prod"
    }
  }'

# Verify: connector and task state, then lag on the target side.
curl -s http://connect.internal:8083/connectors/db2-orders-cdc/status