Db2 12 z/OS DBA common mistakes
Db2 12 z/OS DBA · Understand the traps and how to avoid them.
Treating a -904 (resource unavailable) as a permanent application error and telling the developer to fix code, instead of recognizing it as an unavailable resource, typically a lock timeout or an unavailable table space.
Candidates memorize SQLCODE lists superficially and confuse -904 with -911 (deadlock/timeout) or with object-not-found errors, because the reason codes in the message text are not read.
What to do instead
Practice reading the reason code attached to -904: 00C9008E indicates lock timeout, 00C90081 indicates an unavailable resource such as a stopped table space or an index in rebuild pending. Map reason codes to the correct DBA action: lock analysis versus utility completion or START ACCESS maintenance.
Planning a migration to Db2 12 by catalog level only, without checking function level and the fallback SPE requirements, then discovering that a fallback is blocked after activating new-function mode.
The catalog level, function level, and application compatibility levels are separate concepts, and candidates assume enabling new function is a single irreversible switch with no prerequisite maintenance.
What to do instead
Learn the sequence: install the Db2 12 code at the current catalog level, apply the required fallback SPE, raise the catalog level, then activate function level 500 or later. Remember that the catalog level does not fall back; only the function level can be lowered, and only within documented limits.
Choosing partitioning and index design purely from the SQL predicates shown, without checking whether the partitioning index columns match the join and local-predicate columns used by the queries, resulting in partition scans across all partitions.
Candidates focus on column data types and cardinality but forget that partition elimination depends on the partitioning index key and on whether predicates are stage 1 and indexable.
What to do instead
For each large table, verify that the partitioning key is used in the dominant queries' local predicates, that the partitioning index is designed for limit-key and partition elimination, and that secondary indexes are chosen with the same discipline. Justify each index against a specific query pattern.
Running REORG or LOAD with inline statistics and then assuming RUNSTATS has refreshed everything, without checking whether the statistics profile covers the columns and indexes the optimizer actually needs.
Candidates equate utility completion with current statistics, and do not distinguish table, column, index, and histogram statistics or the effect of the STATISTICS profile and STATISTICS HISTORY settings.
What to do instead
Use the STATISTICS profile with TABLE, INDEX, and KEYCARD and FREQVAL options appropriate to the workload. Verify freshness via the statistics timestamp columns in the catalog, and re-collect column statistics after significant data change rather than relying on inline collection alone.
Assuming that a utility job that ended with condition code 4 succeeded without checking whether the utility left an advisory REORG-pending or COPY-pending state that blocks subsequent access.
Candidates read only the final condition code and skip the utility's phase-level messages and the resulting restrictive states recorded in the catalog and displayed by DISPLAY DATABASE.
What to do instead
Check the utility output for pending states and use DISPLAY DATABASE RESTRICT to confirm. Know which states block read versus write, and know the corrective action: REORG to remove advisory REORG-pending, COPY or an inline copy to clear COPY-pending, and the correct utility restart or TERM UTILITY handling for failed jobs.
Designing a backup strategy around full image copies only, without aligning log retention, the conditional restart control record, and the recover-pending state after a failure.
Candidates treat backup and log management as separate topics and do not rehearse the recovery sequence that a real outage demands, so they miss the dependency between the active log, archive log, and the last consistent point.
What to do instead
Practice a full recovery scenario: identify the last image copy, determine which log ranges are needed, confirm archive log availability and retention, and know how the conditional restart control record affects restart. Verify that the log inventory supports the recovery point objective before the outage.
Reading only the elapsed time of a long-running query in a monitor report and recommending an index, without examining the access path, the sort and RID list usage, and whether the cost is in Db2 or in the application's fetch pattern.
Candidates rely on a single accounting metric and do not cross-check the plan table or the EXPLAIN output against the actual access path and the class-based accounting fields.
What to do instead
Correlate the accounting report fields for class 1, 2, and 3 elapsed time with the access path from EXPLAIN. Determine whether time is spent in Db2 processing, in waiting for locks or I/O, or in the application between fetches, and base the tuning recommendation on that distinction.
Granting a role or privilege and assuming it takes effect immediately for an existing thread or plan, without accounting for authorization caching, plan and package authorization, and the difference between collection and package ownership.
Candidates learn the GRANT syntax but not the authorization model's runtime behavior, including cached authorization identifiers and the dependency of static SQL on package authorization at bind time.
What to do instead
Learn that authorization changes may require the thread to be refreshed or the plan or package to be rebound for static SQL. Distinguish owner, collection, and package authorization, and verify effective privileges with the appropriate catalog queries rather than assuming the grant is live.
Treating a data sharing member failure as equivalent to a standalone subsystem failure and proposing an immediate restart, without checking group buffer pool recovery, retained locks, and the member's restart-light or restart-full state.
Candidates study data sharing concepts in isolation and do not connect member restart behavior to the coupling facility structures and to the locks the failed member held.
What to do instead
Work through member restart: determine whether the member can restart light, identify retained locks and their effect on other members' transactions, and confirm that group buffer pool and lock structure recovery completed before declaring the member available. Verify the effect on the surviving members' transactions that depended on the failed member's locks.
Memorizing SQLCODE values, utility keywords, and subsystem parameter names as isolated facts instead of as consequences of the Db2 architecture, so that questions framed as a scenario cannot be answered even when the underlying fact is known.
The exam tests DBA judgment in context, and candidates who study lists without connecting them to the thread lifecycle, logging, locking, and storage architecture cannot transfer knowledge to unfamiliar scenarios.
What to do instead
Study each topic by tracing a concrete transaction or utility through the architecture: attachment and thread creation, authorization, plan selection, locking, logging, and commit. Rehearse scenario questions where the answer depends on interpreting an observed state rather than recalling a definition.