Db2 12 z/OS DBA study plan
Db2 12 z/OS DBA · Give each week a clear focus.
8 weeks · About 6 hours per week
Db2 Architecture, Subsystems, and Installation/Migration & Performance Monitoring, Tuning, and Statistics
Map the Db2 12 for z/OS address space structure (MSTR, DBM1, IRLM, DIST, DDF, RLIMIT, SSP) and write a one-page diagram explaining each member's role and failure impact.
Trace the installation workflow from SMP/E RECEIVE/APPLY through subsystem parameter module assembly and CLIST-driven installation, listing the key z/OS data sets (BSDS, active/archive logs, catalog, directory) created along the way.
Identify the top 10 DSNZPARMs that affect memory, logging, and concurrency (for example EDMSTMTC, EDMDBDC, CTHREAD, IDBACK, IDTHR, ZHYPERLINK) and record their defaults, valid ranges, and online-changeable status.
Set up a personal lab or sandbox plan (or a documented paper exercise if no system access) that you will use for the remaining seven weeks.
Your checkpoint
A one-page subsystem architecture diagram plus a DSNZPARM quick-reference table of at least 10 parameters with defaults, ranges, and whether they can be changed online.
Db2 Architecture, Subsystems, and Installation/Migration & Availability, Data Sharing, and Operations Management
Compare migration and function-level activation steps for moving to Db2 12 (catalog level, function level, APPLY key, fallback considerations) and write the ordered checklist a DBA would follow.
Explain the role of the BSDS, active logs, archive logs, and the Db2 catalog during migration and describe what changes at each function level.
Describe data sharing fundamentals (group buffer pools, GBP structure sizes, coupling facility list and lock structures, IRLM and XCF) and note how they alter migration planning.
Practice reading a DSNTIJTC or similar migration job output and identify which steps are conditional.
Your checkpoint
A migration runbook outline with ordered steps, checkpoints, and rollback/fallback notes, plus a short data sharing impact section.
Database Design, Objects, and Schema Management & SQL and Application Performance for DBAs
Design a normalized schema and then deliberately denormalize part of it, documenting the trade-offs for a given query workload.
Write and test DDL for tables, indexes, table spaces (partitioned by range and by growth), and STOGROUPs, including a universal versus segmented versus partitioned choice with justification.
Compare the Db2 12 index types (clustering, non-clustering, partitioning, DPSI, index on expression) and pick the right one for three sample queries.
Analyze three SQL statements and rewrite at least one to improve access path (for example predicate sargability, avoiding stage 2, using a matching index scan).
Your checkpoint
A schema design document for a sample workload that includes DDL scripts, an index strategy table, and before/after EXPLAIN output for one rewritten query.
SQL and Application Performance for DBAs & Performance Monitoring, Tuning, and Statistics
Interpret EXPLAIN output (plan table columns such as ACCESS TYPE, MATCHCOLS, METHOD, and the access path) for at least five queries and classify each as stage 1 or stage 2.
Explain when RUNSTATS statistics (cardinality, frequency, histogram, KEYCARD) change access path selection and construct a case where stale statistics produce a bad plan.
Describe the roles of REBIND, plan stability (dynamic plan stability and plan management), and the impact of package and plan cache behavior on performance.
List the key accounting, statistics, and monitor trace fields (for example QWAC, QWHS, and IFCID categories) used to diagnose a slow statement.
Your checkpoint
A performance case study write-up of one slow query, including EXPLAIN evidence, statistics analysis, the fix applied or proposed, and the expected improvement.
Db2 Utilities and Data Movement & Backup, Recovery, and Log Management
Compare LOAD, UNLOAD, REORG, RUNSTATS, COPY, RECOVER, and CHECK utilities by purpose, key options (SHRLEVEL, LOG, SORTKEYS, inline statistics), and typical job syntax.
Explain utility restart, the SYSUTILX and utility directory, and how to clean up a failed utility job.
Design a backup strategy that combines full image copies, incremental copies, and log-based recovery, and state the RTO/RPO each option implies.
Walk through a point-in-time recovery scenario using RECOVER and conditional restart, naming the required inputs (BSDS, archive logs, image copies, CURRENT COPYONLY).
Your checkpoint
A utility cheat sheet covering at least seven utilities with syntax skeletons and key options, plus a written point-in-time recovery scenario with ordered steps.
Backup, Recovery, and Log Management & Performance Monitoring, Tuning, and Statistics
Explain the Db2 log record format, log offload, dual logging, and how the BSDS tracks log inventory, and describe what happens when an active log fills.
Describe the difference between system-level and object-level recovery and when each is appropriate, including the role of the Db2 catalog and directory in recovery.
Interpret key Db2 statistics trace fields (buffer pool hit ratios, EDM pool usage, log write rates, lock/latch waits) and identify two likely tuning actions from a sample report.
Describe how to use the Db2-supplied monitors and reports (for example DSNTSTAT, DSNTLOCK, and the accounting trace) to diagnose contention.
Your checkpoint
A monitoring playbook that maps five symptom categories (buffer pool, EDM pool, log, locking, CPU) to the trace fields to check and the first tuning action to try.
Security, Authorization, and Auditing & Availability, Data Sharing, and Operations Management
Distinguish authentication (RACF, exit, and other supported mechanisms) from authorization (GRANT/REVOKE, roles, ownership, and the different privilege types such as system, database, table, and package privileges).
Explain the role of trusted contexts, roles, and the Db2 authorization exit in a modern security design, and describe how to audit privilege changes.
Describe availability features (data sharing, CF recovery, group attach, and the role of the IRLM) and how planned and unplanned outages are handled.
Describe operational tasks a DBA owns: conditional restart, log truncation, subsystem parameter changes, and the use of commands such as -DISPLAY, -START, -STOP, and -SET LOG.
Your checkpoint
A security and operations checklist covering authentication versus authorization, a sample GRANT/REVOKE exercise, and an outage-handling runbook for three scenarios.
Db2 Architecture, Subsystems, and Installation/Migration & Database Design, Objects, and Schema Management & SQL and Application Performance for DBAs & Db2 Utilities and Data Movement & Backup, Recovery, and Log Management & Performance Monitoring, Tuning, and Statistics & Security, Authorization, and Auditing & Availability, Data Sharing, and Operations Management
Revisit each domain's weakest topic from your earlier deliverables and close the gap with a short written summary.
Take at least two timed practice sets across all domains and record missed-topic patterns by domain.
Re-derive the most error-prone facts from memory: DSNZPARM defaults, utility options, log and BSDS relationships, and authorization privilege types.
Build a personal one-page exam-day reference of the 20 facts you most often got wrong.
Your checkpoint
A final gap-closing summary listing every missed topic from practice sets, the corrected fact for each, and a one-page exam-day reference sheet.
Milestones
Week 2: You can explain, without notes, the Db2 12 subsystem address spaces, the migration and function-level activation sequence, and how data sharing changes that sequence.
Week 4: You can read EXPLAIN output for a query and justify the access path choice, and you can name the statistics that would change it.
Week 6: You can design a backup and recovery strategy for a given RTO/RPO and name the utilities and inputs each scenario requires.
Week 8: You score at or above your target on two timed practice sets and have a corrected-fact list covering every missed topic.