Db2 for z/OS Database Administration

SEE SCHEDULE

Duration: 5 Days

Method: Instructor Led, Hands-on Workshops

Price: $3200.00

Course Code: DB3010

Audience

Db2 Database Administrators who will be designing and managing databases on Db2 for z/OS.

Description

Starting with a logical data model we guide you through the implementation of a fully functional Db2 production database. During the hands-on workshops you will create basic database objects, use Db2 utilities to populate, manage and recover your objects and perform basic management and monitoring procedures. Physical Database Implementation and control are the key issues addressed by this course. The majority of the content concentrates on the Application DBAs role and responsibilities.

Objectives

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

Describe the Db2 System and Data Objects
Create Db2 Database Objects
Implement Db2 Naming Conventions
Identify the Db2 System Objects impacted when creating Data Objects
Manage Db2 Table Spaces and Index Spaces
Use the Db2 Catalog to monitor your Db2 Databases
Alter your Db2 Data Objects for performance and usability
Populate and maintain Db2 Databases using Db2 utilities
Prepare your Database for Recovery
Perform basic Database Recovery
Use Db2 commands to monitor Db2 databases
Identify security authorization requirements
Support your Development Group

Prerequisites

Introduction to Db2 & SQL coding basics (DB1000), TSO/ISPF, MVS JCL (MF1010)

Topics

I. Introduction
- Implementation Impact
- Support Structure
- Db2 Database Objects
- Db2 System Structure
- Operating Environment
- SQL Language Overview
- Application Development Overview
- Db2 Utilities Overview
- Db2 Commands
- What a DBA needs to Know
II. Database Object Creation
- Logical to Physical Transition
- Db2 Table Types
- Data Definition Statements (DDL)
- Db2 Object Naming Conventions
- Schema Resolution
- Db2 Catalog and Directory
- Database Object Dependencies
- Storage Management
- Object CREATE/ALTER/DROP
- Security Requirements
- Best Practices
III. Table Creation
- Db2 Tables
- TABLE DDL
- Column Definitions
- Null Characteristics
- Using Default values
- Primary and Unique Constraints
- Generated vs Natural Keys
- Referential Constraints
- Table Check Constraints
- Altering Tables
- Partition by Range Tables
- Calculating Record Lengths
- Best Practices
IV. Indexes
- Index Usage
- Types of Indexes
- Index Structures
- Index DDL
- Primary Key Unique Index
- Include columns
- Unique Index
- Clustering Index
- Non-Unique Index (secondary index)
- Expression-based index
- Index types unique to partitioned by range tables
- Matching Indexes to User Access
- Index Recommendations
V. Populating Your Database
- Online Utility Operation
- Load Utility
  - Loading empty tables
  - Load Refresh
  - Load append
  - RI Impact
  - Partition Loads
- Best Practices
VI. Preparing your Database for Use
- CHECK Utility to verify Integrity
- Preparing for Recovery
  - COPY
  - LISTDEF & TEMPLATEs
  - MERGECOPY
  - QUIESCE
  - MODIFY RECOVERY
  - Backup Considerations
- RUNSTATS and Statistics
- Catalog Queries
- Best Practices
VII. Introduction to Recovery
- Recovery Environment
  - Unit of Recovery & Commits
  - Logging
- Where to find Recovery information
- RECOVER Utility
- Tablespace Recovery
- Index Rebuild vs Recover
- Recommendations
VIII. REORG Utility
- REORG Purpose and Syntax
- REORG phases
- Online REORG
- Examples
- Best Practices
IX. Altering Tablespace Objects
- Alter Tablespace Object for performance
- Concurrency Options
- Alter Tablespace Objects to promote usability
- Immediate Changes
- Pending Changes
- Tablespace Conversions
- Implement Database Object modifications
- Apply pending changes using the REORG
- Recommendations
X. Altering Tables, Indexes and Adding Views
- Altering Tables for Usability and New Requirements
- Altering Indexes to improve Performance
- Alter Impact
- Managing Versions
- Views
- Recommendations
XI. Application Support
- Program Preparation Review
- Granting Developer Privileges
- DCLGENs
- PLANs/COLLECTIONs/PACKAGEs
- Troubleshooting Problems
- Explain Tables and Query Tuning Overview