IBM Db2 13 z/OS DBA Professional common mistakes
IBM Db2 13 z/OS DBA Professional · Understand the traps and how to avoid them.
Treating the Db2 catalog as if it were a static metadata store: quoting DDL from memory and assuming a column like REORG-pending status or an index's CLUSTERRATIO lives where it actually does not (for example, reading SYSIBM.SYSINDEXES when the attribute is in SYSIBM.SYSINDEXPART or SYSIBM.SYSINDEXSTATS), then selecting the wrong column in a scenario question.
The exam presents catalog-navigation scenarios where the plausible-sounding column name is a distractor. Candidates who memorised the catalog superficially pick a column that exists in a different table or a differently named column, and the option reads naturally so it is not re-checked.
What to do instead
Build a one-page map of the catalog tables you actually query: SYSIBM.SYSTABLES, SYSTABLESPACE, SYSINDEXES, SYSINDEXPART, SYSINDEXSTATS, SYSCOLUMNS, SYSTABLEPART, SYSSTOGROUP, SYSDATABASE, SYSVOLUMES, SYSUTILX, SYSCOPY, SYSIBM.SYSCHECKS and SYSIBM.SYSRELS. For each, note the two or three columns an exam question would target (for example, SYSCOPY.ICBACKUP, SYSCOPY.LOGICAL_PART, SYSTABLEPART.REORG_PENDING, SYSINDEXPART.LEAFNEAR). Practise writing the actual SELECT for each scenario until the table and column are automatic.
Confusing table space types and their attributes in DDL questions: writing a CLOSE YES or COMPRESS clause on a partition-by-growth (PBG) table space, or specifying SEGSIZE on a universal partition-by-range (PBR) table space, and choosing the option that looks most complete rather than the one that is syntactically valid for that type.
Segmented, partitioned, universal (PBG/PBR) and LOB table spaces share many keywords, and older study habits come from pre-universal table space releases. Options that combine clauses from different table space types read as thorough but are invalid.
What to do instead
Make a matrix with rows for segmented, classic partitioned, PBG, PBR, LOB and XML table spaces and columns for the clauses each accepts (SEGSIZE, NUMPARTS, MAXPARTITIONS, DSSIZE, CLOSE, COMPRESS, LOCKSIZE, MEMBER CLUSTER, GBPCACHE). Mark the illegal cells explicitly. When you meet a DDL option, first classify the table space type, then eliminate clauses the matrix forbids before comparing the remaining options.
Answering index design questions by instinct ("add an index on the predicate column") without checking whether the existing index supports the predicate as a matching or non-matching index scan, and ignoring the effect of the predicate's stage-1 versus stage-2 status and the index's cluster ratio on the access path.
Index advice in the abstract sounds right. The exam tests whether the candidate can read an access path: matching columns, screening, index-only access, and the difference between a matching index scan, a non-matching index scan and a table space scan. Candidates who skip that reasoning choose the answer that adds the most indexes.
What to do instead
For every index-tuning scenario, write the predicate set, mark each predicate stage 1 or stage 2, and identify the leading columns of each candidate index. Decide whether the index provides matching columns, whether an index-only access is possible, and whether CLUSTERRATIO or the partition statistics make a scan attractive. Only then pick the option, and reject options that add an index without explaining how it changes the matching columns.
Misreading EXPLAIN output: treating every row in PLAN_TABLE as a step actually executed, ignoring the ACCESS TYPE and METHOD columns, or assuming the cheapest-looking plan in the report is the one Db2 chose without checking the PLANNO grouping and the parent-child relationships.
EXPLAIN output is verbose and the exam supplies only a fragment. Candidates latch onto the table name or the cost number and skip the columns that define the access path (ACCESS TYPE, MATCHCOLS, ACCESSPATH, PREFETCH, SORTC_JOIN), so they pick an option describing a plan that is not in the fragment.
What to do instead
Practise reading EXPLAIN fragments in a fixed order: PLANNO and parent relationships first, then METHOD, then ACCESS TYPE and MATCHCOLS, then PREFETCH and page-range settings, and only last the cost columns. Learn the ACCESS TYPE codes (I, I1, N, R, M, MX, RW, MW) and what each implies about matching columns. Rehearse with EXPLAIN output from your own test subsystem so the column layout is familiar.
Treating REORG, RUNSTATS and COPY as interchangeable housekeeping: recommending REORG to fix stale statistics, or RUNSTATS to clear a REORG-pending advisory, and choosing the utility that runs fastest rather than the one that resolves the specific state described.
All three utilities are associated with table maintenance, and in a scenario the symptom (poor performance, an unavailable object, a pending flag) is described without naming the state. Candidates match on the symptom's domain rather than on the specific pending state or statistic that is wrong.
What to do instead
Memorise the pending states and what clears each: REORG-pending (cleared by REORG or LOAD REPLACE), COPY-pending (cleared by COPY, or by LOAD REPLACE with the appropriate options), CHECK-pending (cleared by CHECK DATA or LOAD REPLACE), and the informational REBUILD-pending on indexes. Pair each state with the catalog column that reports it and the utility that resolves it, and practise mapping a symptom sentence to that triple before reading the answer options.
Confusing conditional restart and normal restart: describing RECOVER or RESTART as the way to back out an uncommitted unit of work after an abend, or assuming a conditional restart always requires a full RECOVER of the table spaces involved.
Restart, conditional restart, backout and roll-forward are described in similar language in the manuals. Candidates remember that conditional restart 'fixes' the log but not the boundary between log processing and data recovery, so they select an option that overstates what a restart does.
What to do instead
Write out the recovery decision tree: normal restart, restart with a conditional restart control statement (specifying the log range or the point to stop forward recovery), roll-forward to current or to a point in time, and full object recovery from image copies. For each, note what it does to uncommitted work, what it needs from the log, and what it does not do (for example, restart does not restore damaged pages). Test the tree against scenario wording such as 'abend during update' versus 'damaged page'.
Ignoring the difference between LOG(COPY) and LOG(NOCOPY) and the interaction with the COPY-pending state and with RECOVER to a point in time, then choosing an option that assumes image copies are or are not required without checking the table space's logging attribute.
The logging attribute is one line in the DDL but changes what recovery options are valid and whether COPY-pending can be cleared by a utility or must be addressed by changing the attribute. Candidates read the scenario's recovery goal and skip the attribute.
What to do instead
For each recovery scenario, first note the table space's LOG attribute, the current COPY-pending status, and the recovery point required. Then apply the rule set: NOT LOGGED restricts recovery options and interacts with COPY-pending; LOG(NOCOPY) permits recovery but the object can still enter COPY-pending depending on settings; point-in-time recovery depends on the log range available. Build a small table of attribute versus valid recovery actions and consult it before reading options.
Confusing authority levels and their scope: assuming SYSADM is needed for routine object maintenance, treating DBADM as global, or believing SYSCTRL grants the ability to read table data, and selecting the option that grants the highest-sounding authority.
Db2 for z/OS has several administrative authorities (SYSADM, SYSCTRL, SYSOPR, DBADM, DBMAINT, SQLADM, and the newer system DBADM) with overlapping but distinct scopes. The names suggest a hierarchy that does not match the actual privileges, and candidates default to the strongest one.
What to do instead
Make an authority-versus-capability grid: create and drop objects, read and modify data, bind packages, run utilities, issue commands, manage databases, and administer the subsystem. Fill in each authority against each capability, marking where SYSCTRL stops short of data access and where DBADM is scoped to a database. Rehearse with 'which authority is the minimum needed' questions, since the exam usually asks for the least privilege, not the most.
Misjudging utility behaviour under concurrency and restart: assuming LOAD REPLACE leaves the table space available throughout, or that a failed REORG leaves the object in its original state with no pending flags, and picking the option that describes the utility as atomic.
Utility behaviour around availability (drain, claim, timeout, SHReLEVEL, and the switch phase) and around restart (RESTART(CURRENT) versus RESTART(PHASE)) is detailed and rarely used day to day. Candidates remember the utility's purpose but not its failure semantics.
What to do instead
For each of LOAD, REORG, REBUILD, RUNSTATS, COPY and RECOVER, note three things: what lock or drain it takes and what happens on timeout, whether it has a switch phase and what that means for availability, and what a failure leaves behind (pending states, restart options). Learn RESTART(CURRENT) versus RESTART(PHASE) and when each is appropriate. Rehearse with 'what happens if the job fails at phase X' questions.
Answering performance questions about parallelism, prefetch or sort without connecting them to the object and subsystem attributes that enable them: recommending DEGREE(ANY) without checking the subsystem's parallelism settings, or assuming sequential prefetch applies regardless of the access path and page-range configuration.
These are controlled by a mix of bind options, subsystem parameters (such as the parallel and prefetch-related ZPARMs), and object attributes. Candidates remember the bind option and forget the surrounding enablement, so they pick an option that promises parallelism the subsystem will not deliver.
What to do instead
For each tuning lever, list the full chain: the bind option, the relevant subsystem parameter, and any object or statistics prerequisite. For parallelism, note the bind DEGREE, the subsystem's parallel settings, and the requirement for adequate partitions or pages. For prefetch, note the access path type and the page-range and buffer pool settings. When an option proposes a lever, check every link in the chain before accepting it.