Profience

DB2 11 for z/OS Database Administration Workshop

Why take this course?

After completing this course, you will be able to
  • describe the major components of the DB2 product and environments.
  • code DDL statements to create and maintain DB2 objects.
  • implement a DB2 database.
  • use stored procedures and triggers.
  • resource serialization, locking and concurrency
  • use DB2's security and authorization mechanisms to control access to the DB2 data.
  • use DB2's Explain facility to view and optimize DB2 data access paths.
  • use the DB2 Catalog to monitor database performance.
  • create effective backup and recovery strategies for applications.

This course provides comprehensive and practical training for database administrators working with DB2. You will learn about Database Design, data modelling, logical and physical design, and Administration, including data integrity, backup and recovery, security, performance and application support. We will review the DB2 product from a DBA perspective and give you extensive hands-on exercises so you leave with a ready-to-use, working knowledge of the different components of a DBA's toolkit.

Who should attend?

Existing and future DB2 database administrators who need formal skills training to implement, maintain and enhance DB2 implementations.

Any prerequisites?

You should have a good understanding of DB2 concepts and facilities. Ideally, you have at least 3 months of practical DB2 experience in application development or database administration.

You will also be familiar with
  • DB2's data integrity control mechanisms.
  • database administration related utilities.

Dates and fees

5 Days   ? - $ 1,747 (CAD) ? Up to 3 - $ 3,494 Up to 6 - $ 5,244   ? Plus - $ 874
 
Sorry, no classes are currently scheduled, please contact us to request a class
Contact us to arrange your preferred date and/or in-class training

Topics

Introduction
  • Database Administration
  • Database Life Cycle
  • DBA Tasks
  • DBA Deliverables
DB2 Overview
  • The DB2 Environment
DB2 Objects
  • DB2 Objects
  • Database
  • Storage Group
  • Tablespace
  • Table
  • Column Datatypes
  • Column Attributes
  • Index
  • Indexspace
  • View
  • Alias
  • DB2 Schemas and Object Names
  • Object Dependency
Designing DB2 Databases - Logical
  • Database Design Process
  • Initial Logical Design
  • Modelling Entity Types
  • The Primary Key
  • Modelling Relationships
  • One-to-One Relationship
  • One-to-Many Relationship
  • Many-to-Many Relationship
  • Modelling Attributes
  • Single Valued Attribute
  • Multi Valued Attribute
  • Initial Logical Design
  • Refined Logical Design
  • Transaction Analysis
  • Logical Design
Physical Database Design
  • Tablespace Options
  • Column Attributes
  • Indexing Strategies
  • Using Views
Implementing the Database
  • Data Definition Language
  • DATABASE
  • STOGROUP
  • TABLESPACE
  • DISTINCT TYPE
  • TABLE
  • VIEW
  • INDEX
  • ALIAS
Implementing Declarative Constraints
  • Table Constraints
  • NOT NULL Constraint
  • UNIQUE Constraint
  • PRIMARY KEY Constraint
  • CHECK Constraint
  • VALIDPROC
  • Referential Constraint
  • Basic Concepts
  • Additional Terms
  • Defining Referential Integrity
  • Design Considerations
  • Operational Implications
Implementing Procedural Constructs
  • User Defined Functions
  • Stored Procedures
  • Triggers
Implementing Security
  • Application Security
  • Authorization ID
  • Flavours of the AUTHID
  • Primary AUTHID
  • Secondary AUTHID
  • Current SQLID
  • Access to DB2 Data
  • Privileges and Authorities
  • Security Privileges
  • Security Authorities
  • Data Control Language
  • GRANT
  • REVOKE
  • CHAIN
  • Ownership Privileges
  • Application Plan
  • Group Authorization
The DB2 Catalog and Directory
  • DB2 Directory
  • DB2 Catalog
  • Using the Catalog
Managing DB2 Performance
  • What is Performance?
  • DB2 vs. System Performance
  • Performance Factors
  • Performance
  • Performance Tuning
  • Monitoring
  • Implementing Changes
Concurrency Control
  • Why Concurrency Control?
  • The Performance Dilemma
  • DB2 Locking Strategy
  • Lock Size
  • Lock Size Definition
  • Lock Duration
  • Lock Mode
  • Claims and Drains
  • Lock Avoidance
  • Reducing Contention
  • Deadlock
DB2 Access Paths
  • What is an Access Path?
  • Access Paths
  • Matching Index Scan
  • Non-Matching Index Scan
  • Index Lookaside
  • Tablespace Scan
  • Partition Scan
  • Multiple Index Access
  • Multiple Index
  • Join Methods
  • Nested Loop Join
  • Choosing the Outer & Inner Tables
  • Nested Loop Join
  • Merge Scan Join
  • Hybrid Join
  • Using Multiple Indexes in Joins
  • Combining Join Methods
  • The Optimizer
  • The EXPLAIN Facility
  • The PLAN_TABLE
  • Invoking EXPLAIN
  • Simulating Host Variable Usage
  • The EXPLAIN Output
  • PLAN_TABLE
  • DSN_STATEMNT_TABLE
  • DSN_PREDICAT_TABLE
  • DSN_PREDICATE_SELECTIVITY
  • DSN_PTASK_TABLE
  • DSN_QUERY_TABLE
  • DSN_QUERYINFO_TABLE
  • DSN_SORT_TABLE
  • DSN_SORTKEY_TABLE
  • DSN_STRUC_TABLE
  • DSN_VIEWREF_TABLE
  • DSN_VIRTUAL_INDEXES
  • DSN_VIRTUAL_KEYTARGETS
  • Interpreting EXPLAIN output
  • Improving performance using EXPLAIN
  • Access Path Hints
Backup and Recovery
  • Why Backup and Recovery?
  • Backup and Recovery
  • Basic Terminology
  • Malfunction
  • Point of Consistency
  • Marking a Point of Consistency
  • Direction of Recovery
  • The DB2 Utilities
  • COPY TABLESPACE
  • COPY INDEXSPACE
  • QUIESCE TABLESPACE
  • RECOVER TABLESPACE
  • RECOVER INDEXSPACE
  • REBUILD INDEX
  • Design Considerations
DB2 Utilities
  • Online Utilities
  • Standalone Utilities
  • CHECK
  • LOAD
  • UNLOAD

Competency Assessments

At the request of the person booking, a technical competency assessment will be conducted with the student.
This assessment will be a combination of written and multiple choice questions as well as specific coding tests.
Upon a minimum pass rate of 85%, students will receive a Certificate of Competency instead of the standard certificate of course completion.
Please Contact us for more details or specific needs.