|
The IBM Certified Database
Administrator is the lead database administrator (DBA) for the DB2 UDB Version
8.1 product for the z/OS operating system. This individual has significant
experience as a DBA and extensive knowledge of the DB2 Universal Database,
specifically the new features and functionality related to Version 8.1. This
specialist is capable of performing the intermediate to advanced tasks related
to database design and implementation, operation and recovery, security and
auditing, performance, and installation and migration specific to the z/OS
operating system. Section 1 - Database Design and Implementation (26%) - Design tables and views (columns, data type considerations for large
objects, column sequences, user-defined data types, temp tables, MQTs, etc.)
- Explain the different performance implications of identity column, row
ID, and sequence column definitions (applications, utilities)
- Design
indexes (key structures, type of index, index page structure, index column
order, index space, clustering)
- Create objects (create and alter
database objects), design table spaces (choose a DB2 page size, clustering),
and determine space attributes (automatic space)
- Perform partitioning
(table partitioning, index partitioning)
- Normalize data (E-R model,
process model) and translate data model into physical model (denormalize
tables) . +Implement user-defined integrity rules (referential
integrity, user-defined functions, check constraints, triggers)
Section 2 - Operation and Recovery (28%) - Issue
database-oriented commands for normal operational conditions (START, STOP,
DISPLAY)
- Issue database-oriented commands and utility control
statements for use in abnormal conditions (RECOVER, RESTART)
- Identify
and perform actions that are needed to protect databases from planned and
unplanned outages (BACKUP, RESTORE, monitoring) and ensure that timely image
copies are taken periodically
- Load data into the created tables
- Reorganize objects when necessary
- Monitor the object by
collecting statistics
- Monitor threads (utilities, distributed, local,
indoubt, new special registers)
- Identify and respond to restrictive
statuses on objects
- Establish timely checkpoints (checkpoint
frequencies, system quiesce points)
- Perform problem determination (run
traces [DB2, DRDA, ODBC, JDBC], SQL queries, dumps, GET DIAGNOSTICS)
- Perform health checks (maintenance, check utilities, offline
utilities, queries)
- 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)
- Describe
the special considerations for recovery in a data sharing environment
- Implement disaster recovery
- Plan for disaster
recovery
- Perform disaster recovery (offsite, local)
Section 3 - Security and Auditing (10%) - Protect
DB2 objects
- Establish security profile (define
authorization roles)
- Identify the appropriate DB2 privileges required
for access to DB2 resources
- Define and implement authorization and
privileges on user and system database objects (revokes, grants)
- Protect connection to DB2. Describe access to the DB2 subsystem (local
request, remote request). Coordinate the effort between DB2 and RACF team
(groupings, secondary authorization identifiers, stored procedures). Identify
conditions when external security mechanisms (such as RACF) should be used in
place of DB2 internal security mechanisms.
- Audit DB2 activity and
resources and identify primary audit techniques
- Identify and respond
appropriately to symptoms from trace output or error messages that signify
security problems
Section 4 - Performance (31%) - Plan for
performance monitoring by setting up and running monitoring procedures
(continuous, detailed, periodic, exception)
- Analyze the create and
alter process for DB2 objects (table, index, table space definition)
- Analyze performance (manage and tune CPU requirements, memory, I/O,
locks, response time)
- Analyze and respond to RUNSTATS statistics
analysis (real-time, batch, catalog queries, reports)
- Determine when
and how to run the REORG utility
- Design and alter index structures
(data-partitioned secondary indexes [DPSI], VARCHAR column index implications,
backward index scan, sparse indexes)
- Analyze cache (buffer pool
tuning, pool sizes, threshold, page set positioning, sort pool, RID pool, EDM
pool) and recommend buffer pool changes
- Calculate cache requirements
for new applications (DBD sizes, plan and package, average and maximum sizes,
number of data sets)
- Evaluate and set appropriately the performance
parameters for different utilities
- Describe the performance concerns
for the distributed environment (DDF, DBAT threads, pool threads, connection
pooling)
- Describe DB2 interaction with WLM (distributed, stored
procedures, user-defined functions)
- Interpret traces (statistics,
accounting, performance) and explain the performance impact of different DB2
traces
- Identify and respond to critical performance thresholds
(excessive I/O wait times, lock-latch waits and CPU waits; deadlocks, timeouts)
- Review and tune SQL
- Interpret EXPLAIN output
(HINTs)
- Analyze access paths (query parallelism; indexable, stage 1,
and stage 2 predicate types; join methods; block fetching
- Explain the performance impact of multi-row functionality in Version 8
(multi-row insert scenario, multi-row fetch)
Section 5 - Installation and Migration (5%) - Identify and explain the application of run-time considerations and
parameters
- Run catalog healthchecks using queries and utilities
- Identify the critical ZPARMs (database-, object- and
application-oriented)
|
