DB2 UDB V8 for z/OS Database Administration: 702 Exam The 702 examDB2 UDB V8 for z/OS Database Administrationis divided into five sections. Section 1: Database Design and Implementation (26 Percent) Design tables and views: columns, data type considerations for large objects, column sequences, user-defined data types, temp tables, MQTs, and so on. Explain the various performance implications of identity column, ROWID, and sequence objects definitions (applications, utilities). Design indexes: key structures, type of index, index page structure, index column order, index space, clustering. Create and alter database objects, design table spaceschoose a DB2 page size, clusteringand determine space attributesautomatic space. Perform table and index partitioning. Normalize data Entity-Relationship model, process modeland translate data model into physical model, or denormalize tables. Implement user-defined integrity rules: referential integrity, user-defined functions, check constraints, triggers. Section 2: Operation and Recovery (28 Percent) 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 needed to protect databases from planned and unplanned outagesBACKUP, RESTORE, monitoringand 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, in-doubt, 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 scenariostables spaces; indexes; full pack; hardware; Flash copies; full, incremental, reference update; copy-to-copy, nondata objects; catalogand recovery scenariostable 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 offsite and local disaster recovery. Section 3: Security and Auditing (10 Percent) Protect DB2 objects. Establish a 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 subsystemlocal request, remote request; coordinate the effort between DB2 and RACF teamgroupings, 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 Percent) 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: DPSI, VARCHAR column index implications, backward index scan, sparse indexes. Analyze cachebuffer pool tuning, pool sizes, threshold, page set positioning, sort pool, RID pool, EDM pooland 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 various 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 tracesstatistics, accounting, performanceand explain the performance impact of various 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. Analyze access paths: query parallelism; indexable, stage 1, and stage 2 predicate types; join methods; block fetching. Explain the performance impact of multirow functionality in version 8: multirow insert scenario, multirow fetch. Section 5: Installation and Migration (5 Percent) Identify and explain the application of runtime considerations and parameters. Run catalog health checks, using queries and utilities. Identify the critical ZPARMs: database, object, and application oriented. Identify modes of version 8 migration. |