Db2 for z/OS Database Administration Refresher

SEE SCHEDULE

Duration: 4 Days

Method: Instructor Led, Hands-on Workshops.

Price: $2450.00

Course Code: DB3012

Audience

Db2 for z/OS System Database Administrators.

Description

Is your Db2 for z/OS skill set out of date? Are you preparing for the IBM certification test? Have you been away from Db2 for z/OS and returning after a period of absence? Has your experience with Db2 for z/OS been concentrated in a single area of responsibility and you now need to branch out into additional areas? Then this course is for you. This course is intended for Db2 DBAs requiring extensive knowledge of Db2 for z/OS with the intention of performing the intermediate to advanced tasks related to database design and implementation, operation and recovery, security and auditing, performance, and installation and migration/upgrades specific to the z/OS operating system.

Objectives

Upon successful completion of this course, the student will be able to:

Assist in Db2 for z/OS Installation and Migration
Display a working knowledge of the Db2 Address spaces
Design and Implementation Db2 for z/OS Databases
Identify and perform recovery scenarios
Conduct performance reviews and implement performance recommendations
Display a working knowledge of security authorization and auditing requirements

Prerequisites

Minimum 1 year hands-on experience as a Db2 for z/OS DBA.

Topics

I. Installation and Migration Overview
- Db2 address spaces and resource usage
- Db2 critical ZPARMs
- Data sharing components and commands
- Pre-migration checklists
- Db2 catalog and directory (DSNDB06, DSNDB01, tablespaces and tables)
II. Database Design and Implementation
- Tables and views (columns, data type considerations for large objects, XML, column sequences, user-defined data types, temp tables, clone tables, temporal tables, MQTs, archive transparency, etc.)
- Use and implications of identity column, row ID, and sequence column definitions (applications, utilities), hash access
- Indexes (key structures, type of index, index page structure, index column order, index space, clustering, compression, index on expression, include column)
- Table spaces (Db2 page size, clustering, partitioning, universal table spaces, space attributes)
- Partitioning (table partitioning, index partitioning, DPSI, universal table space)
- Normalize data (E-R model, process model) and translate data model into physical model (when to denormalize tables and impact)
- Integrity rules (referential integrity, user-defined functions & data types, check constraints, triggers)
- Alter Db2 objects (table, column, drop column, alter limit key, index, table space, database, online schema changes)
- Impact of different encoding schemes
III. Operation and Recovery
- Normal operational conditions (START, STOP, DISPLAY commands)
- Commands and utilities for abnormal conditions (RECOVER, RESTART)
- Load and unload data into and from tables
- Reorganize objects when necessary (reorg avoidance, automatic mapping table, new reorg features)
- Monitor and collect statistics (run stats, in-line statistics, real time stats, stats feedback tables, autonomic stats, and statistics related stored procedures)
- Monitor and manage threads and utilities (distributed, local, MODIFY DDF)
- Identify and respond to advisory/restrictive statuses on objects
- Identify and perform problem determination (traces and other utilities, plans and packages)
- Perform health checks (check utilities, offline utilities, catalog queries)
- Identify and perform recovery scenarios (off-site recovery, data sharing, 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, offline utilities, new Extended RBA and LRSN)
IV. Additional Database Functionality
- SQL constructs (temporal, archive, table functions, built-in scalar functions, recursive, common table expressions)
- SQL/PL (Array data type, new array data type functions, functions and procedures)
- SQL/XML (results database, XML functions, cross loader with XML, xpath expressions, FLWOR, pattern matching and regular expressions)
- Stored Procedures (native, external, autonomous, ziip considerations)
- User-defined functions (scalar functions, table functions, SQL/external functions)
- Global variables (in stored procedures, in SQL/PL, distributed considerations)
V. Performance
- Performance monitoring procedures (continuous, detailed, periodic, exception)
- Analyze performance (manage and tune CPU requirements, memory, I/O, locks, response time, index and table compression)
- RUNSTATS statistics analysis (real-time, batch, catalog queries, reports, histograms)
- BIND and REBIND (APCOMPARE, APREUSE, APPLCOMPAT, etc.)
- Db2 interaction with WLM (distributed, stored procedures, user-defined functions, RRS)
- Work with Db2 traces (trace classes & IFCIDs, statistics, accounting, performance, etc.)
- Identify and respond to critical performance metrics (excessive I/O wait times, lock-latch waits and CPU waits; deadlocks, timeouts, RID failures)
- Review and tune SQL (access paths, EXPLAIN tables, awareness of query transformation and predicate processing, use of Virtual Indexes)
- Dynamic SQL Performance (DSN_STATEMENT_CACHE_TABLE, parameter markers, literal replacement, REOPT)
- Design features for performance (hash row access, inline LOBs)
- Controlling access paths (SYSSTATSFEEDBACK table, SYSQUERY)
VI. Distributed Access
- Implementing distributed data access (communications database)
- ZPARMs for DDF
- DDF setup (Db2 Connect, Client, Drivers, profile tables, RLMT)
- Performance settings for DDF access
VII. Security and Auditing
- Privileges and authorities
- Protect access to Db2 and its objects
- Audit Db2 activity and resources
- Identify and respond to trace output or error messages that signify security problems