The test contains five sections totalling approximately 69 multiple-choice questions.
The
percentages after each section title reflect the approximate distribution of the total question set
across the sections.
Section 1 - Database Design and Implementation (30.5%)
a. Design tables and views (columns, data type considerations for large objects, XML, column
sequences,user-defined data types, temp tables, clone tables, MQTs, automatic creation of
objects, etc.)
b. Explain the different performance implications of identity column, row ID, and sequence
column definitions(applications, utilities), reorder row format
c. Design indexes (key structures, type of index, index page structure, index column order,
index space,
clustering, compression, index on expression)
d. Design table spaces (choose a DB2 page size, clustering) and determine space attributes
(automatic space)
e. Perform partitioning (table partitioning, index partitioning, DPSI, universal table space)
f. Normalize data (E-R model, process model) and translate data model into physical model
(denormalize tables)
g. Implement user-defined integrity rules (referential integrity, user-defined functions, check
constraints, triggers)
h. Use the appropriate method to alter DB2 objects (table, index, table space, database)
i. Create and Manage XML objects
j. Understand impacts of different encoding schemes.
Section 2 - Operation and Recovery (29%)
a. Issue database-oriented commands for normal operational conditions (START, STOP,
DISPLAY)
b. Issue database-oriented commands and utility control statements for use in abnormal
conditions (RECOVER, RESTART)
c. Identify and perform actions that are needed to protect databases from planned and
unplanned outages and ensure that timely image copies are taken periodically.
d. Load and unload data into and from the created tables
e. Reorganize objects when necessary
f. Monitor the object by collecting statistics (run stats, real time stats and DSNACCOR)
g. Monitor and manage threads (distributed, local)
h. Monitor utilities
i. Identify and respond to advisory/restrictive statuses on objects
j. Establish timely checkpoints (checkpoint frequencies, system quiesce points)
k. Identify and perform problem determination (e.g., traces and other utilities)
l. Perform health checks (maintenance, check utilities, offline utilities, queries)
m. Develop backup scenarios (tables spaces; indexes; full pack; hardware; Flash copies; full,
incremental, reference update; copy-to-copy, non-data objects; catalog) and recovery
scenarios (table spaces, indexes, roll forward, roll back, current point in time, prior point in
time, system point in time copy and restore, catalog and directory)
n. Describe the special considerations for recovery in a data sharing environment
o. Implement disaster recovery
1. Identify options in disaster recovery
2. Plan for disaster recovery
3. Perform disaster recovery (offsite, local)
p. Design and alter index structures (data-partitioned secondary indexes [DPSI], VARCHAR
column index implications, backward index scan, sparse indexes, index on expression)
q. Monitor and manage virtual storage
Section 3 - Security and Auditing (7%)
a. Protect DB2 objects
1. Establish security profile (define authorization roles)
2. Identify the appropriate DB2 privileges required for access to DB2 resources
3. Define and implement authorization and privileges on user and system database
objects (revokes, grants)
b. Protect connection to DB2 (SSL)
c. Identify conditions when external security mechanisms (such as RACF) should be used in
place of DB2 internal security mechanisms
d. Audit DB2 activity and resources and identify primary audit techniques
e. Identify and respond appropriately to symptoms from trace output or error messages that
signify security problems
f. Create and maintain roles and trusted contexts
Section 4 - Performance (29%)
a. Plan for performance monitoring by setting up and running monitoring procedures
(continuous, detailed, periodic, exception)
b. Analyze performance (manage and tune CPU requirements, memory, I/O, locks, response
time, index and table compression)
c. Analyze and respond to RUNSTATS statistics analysis (real-time, batch, catalog queries,
reports, histograms)
d. Determine when and how to perform REBIND
e. Analyze and tune cache structures
1. buffer pool tuning (sizes, threshold, object separation)
2. RID pool, sort pool, and EDM pool
f. Calculate cache requirements for new applications (DBD sizes, plan and package, average
and maximum sizes, number of data sets)
g. Evaluate and set appropriately the performance parameters for different utilities
h. Describe the performance concerns for the distributed environment (DDF, DBAT threads,
pool threads, connection pooling)
i. Describe DB2 interaction with WLM (distributed, stored procedures, user-defined functions,
RRS)
j. Interpret traces (statistics, accounting, performance) and explain the performance impact of
different DB2 traces
k. Identify and respond to critical performance metrics (excessive I/O wait times, lock-latch
waits and CPU waits; deadlocks, timeouts, RID failures)
l. Review and tune SQL
1. Interpret EXPLAIN output (HINTs & Visual Explain, index advisor)
2. Analyze access paths (query parallelism; indexable, stage 1, and stage 2 predicate
types; join methods; block fetching
3. Dynamic SQL considerations (caching, parameter markers, reopt, identification, DSN
statement cache tables, OSC)
4. Explain the performance impact of multi-row functionality (multi-row insert scenario,
multi-row fetch, reordered row format)
Section 5 - Installation and Migration / Upgrade (4.5%)
a. Run catalog health checks using queries and utilities
b. Identify the critical ZPARMs (database-, object- and application-oriented)
c. Identify the migration/upgrade modes (feature availability, mode descriptions, migration
paths)
d. Identify & explain Datasharing components
