Test 732: DB2 9 DBA for z/OS

Section 1 - Database Design and Implementation (26%)

1. 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.)
2. Explain the different performance implications of identity column, row ID, and sequence column definitions

(applications, utilities), reorder row format
3. Design indexes (key structures, type of index, index page structure, index column order, index space,

clustering, compression, index on expression)
4. Design table spaces (choose a DB2 page size, clustering) and determine space attributes (automatic space)
5. Perform partitioning (table partitioning, index partitioning, DPSI, universal table space)
6. Normalize data (E-R model, process model) and translate data model into physical model (denormalize tables)
7. Implement user-defined integrity rules (referential integrity, user-defined functions, check constraints, triggers)
8. Use the appropriate method to alter DB2 objects (table, index, table space, database)
9. Create and Manage XML objects
10. Understand impacts of different encoding schemes.

Section 2 - Operation and Recovery (28%)

1. Issue database-oriented commands for normal operational conditions (START, STOP, DISPLAY)
2. Issue database-oriented commands and utility control statements for use in abnormal conditions (RECOVER, RESTART)
3. Identify and perform actions that are needed to protect databases from planned and unplanned outages and ensure that timely image copies are taken periodically.
4. Load and unload data into and from the created tables
5. Reorganize objects when necessary
6. Monitor the object by collecting statistics (run stats, real time stats and DSNACCOR)
7. Monitor and manage threads (distributed, local)
8. Monitor utilities
9. Identify and respond to advisory/restrictive statuses on objects
10. Establish timely checkpoints (checkpoint frequencies, system quiesce points)
11. Identify and perform problem determination (e.g., traces and other utilities)
12. Perform health checks (maintenance, check utilities, offline utilities, queries)
13. 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)
14. Describe the special considerations for recovery in a data sharing environment
15. Implement disaster recovery
1. Identify options in disaster recovery
2. Plan for disaster recovery
3. Perform disaster recovery (offsite, local)


16. Design and alter index structures (data-partitioned secondary indexes [DPSI], VARCHAR column index implications, backward index scan, sparse indexes, index on expression)
17. Monitor and manage virtual storage

Section 3 - Security and Auditing (10%)

1. 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)


2. Protect connection to DB2 (SSL)
3. Identify conditions when external security mechanisms (such as RACF) should be used in place of DB2 internal security mechanisms
4. Audit DB2 activity and resources and identify primary audit techniques
5. Identify and respond appropriately to symptoms from trace output or error messages that signify security problems
6. Create and maintain roles and trusted contexts

Section 4 - Performance (31%)

1. Plan for performance monitoring by setting up and running monitoring procedures (continuous, detailed, periodic, exception)
2. Analyze performance (manage and tune CPU requirements, memory, I/O, locks, response time, index and table compression)
3. Analyze and respond to RUNSTATS statistics analysis (real-time, batch, catalog queries, reports, histograms)
4. Determine when and how to perform REBIND
5. Analyze and tune cache structures
1. buffer pool tuning (sizes, threshold, object separation)
2. RID pool, sort pool, and EDM pool


6. Calculate cache requirements for new applications (DBD sizes, plan and package, average and maximum sizes, number of data sets)
7. Evaluate and set appropriately the performance parameters for different utilities
8. Describe the performance concerns for the distributed environment (DDF, DBAT threads, pool threads, connection pooling)
9. Describe DB2 interaction with WLM (distributed, stored procedures, user-defined functions, RRS)
10. Interpret traces (statistics, accounting, performance) and explain the performance impact of different DB2 traces
11. Identify and respond to critical performance metrics (excessive I/O wait times, lock-latch waits and CPU waits; deadlocks, timeouts, RID failures)
12. 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 (5%)

1. Run catalog health checks using queries and utilities
2. Identify the critical ZPARMs (database-, object- and application-oriented)
3. Identify the migration/upgrade modes (feature availability, mode descriptions, migration paths)
4. Identify & explain Datasharing components