DB2 Database Administration Workshop

Course

Inhouse

£ 1,700 + VAT

Description

  • Type

    Course

  • Methodology

    Inhouse

  • Duration

    5 Days

The aim of this Course is to provide the delegate with the basic skills required to function as a DBA. On completion of this Course the student will be able to: define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP,use data compression techniques within a tablespace,understand the application development process,bind plans and packages,resolve Referential Integrity. Suitable for: This course provides advanced DB2 for z/OS (Version 8) training, and covers many of the key tasks normally performed by a Database Administrator.

About this course

Familiarity with the z/OS, ISPF host environment is required. Prior exposure to DB2 or another relational database would be advantageous but not essential.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

Course Objectives
The aim of this Course is to provide the delegate with the basic skills required to function as a DBA.

On completion of this Course the student will be able to:

  • define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
  • use data compression techniques within a tablespace
  • understand the application development process
  • bind plans and packages
  • resolve Referential Integrity violations
  • resolve Table Check Constraint violations
  • use DCL statements GRANT and REVOKE to control access to
  • understand and resolve locking issues
  • understand and use the LOAD Utility
  • understand use the UNLOAD Utility
  • LOAD and UNLOAD data in multiple formats
  • understand and use the CHECK DATA Utility
  • define and use Materialized Query Tables (MQTs)
  • understand Automatic Query Rewrite

Course Details

  • DB2 OVERVIEW
  • The Relational Model
  • Data Representation
  • Normalisation
  • SQL
  • The DB2 Environment
  • DB2 Architecture
  • DB2 Datasets
  • The Structure of DB2 Objects
  • Structured Query Language (SQL)
  • Data Definition Language (DDL)
  • Data Control Language (DCL)
  • DB2 Catalog
  • DB2 Directory
  • The Optimiser
  • Overview of DB2 Locking
  • DB2 Logging and Recovery Overview
  • Image Copy/Logging/Recovery Cycle
  • PHYSICAL DATABASE REQUIREMENTS
  • Storage Groups
  • Databases
  • Tablespaces
  • Pages
  • Tablespace Organisation
  • Page and Row Organisation
  • The Simple Tablespace
  • The Segmented Tablespace
  • The Partitioned Tablespace
  • Large Tablespaces (EA Enabled)
  • Extended Addressability (EA) Enabled Tablespaces
  • Creating Large Tablespaces
  • Altering DSSIZE
  • Tablespace Thresholds
  • Create Tablespace Parameters:
  • Alter Tablespace
  • Tables
  • Copying Table Definitions
  • Rename Table
  • DB2 Column Types
  • Null Values
  • User Defined Default Values
  • Unique Table Rows
  • Rowid Data Type
  • Rowid Characterstics
  • Rowid Restrictions
  • Using a Rowid Column as the Partitioning Key
  • Identity Columns
  • Identity Columns - Definition
  • Identity Columns - Examples
  • Temporary Tables
  • Global Temporary Table
  • Declared Temporary Tables
  • Declared Temporary Table Considerations
  • Declared Temporary Tables - Comparisons
  • Synonym
  • Alias
  • Indexes
  • Index Organisation - The B Tree Index
  • Index Clustering
  • Defining an Index
  • A Partitioning Index
  • Create Index Paramaters
  • Indexes of Large / EA Enabled Tablespaces
  • Index Piecesize
  • Index Design Considerations
  • Alter Index
  • Views
  • Read Only Views
  • Views - With Check Option
  • Creating a View of Two Tables
  • ASCII Server Support
  • Space Search Algorithms
  • PARTITIONING DATA
  • The Partitioned Tablespace
  • Maximum Number of Partitions
  • Considerations when Partitioning
  • Create Tablespace - DSSIZE Parameter
  • Data Set Names
  • Table-Controlled Partitioning
  • Table-Controlled Partitioning Example
  • Altering a Table to Add Table Partitioning
  • Converting to Table-controlled Partitioning
  • Table-controlled Partitioning Catalog Changes
  • Table-controlled Partitioning Terminology
  • Index-controlled Partitioning Terminology
  • Index classification
  • Clustering
  • Clustering Within Partition
  • Changing the Clustering Sequence
  • Data Partitioned Secondary Indexes
  • Creating a DPSI
  • Design Considerations - Why Partition At All?
  • Design Considerations - Non-Partitioned Index Problems
  • Design Considerations - DPSI Benefits
  • Design Considerations - DPSI Problems
  • DPSIs and Utilities
  • DPSIs and Planning
  • Partition Management
  • Adding Partitions
  • Adding Partitions - Considerations
  • Index-controlled to Table-controlled partitioning
  • Rotating Partitions
  • Rotate Partition Syntax
  • Rotating Partitions - Considerations
  • Altering Partition Boundaries
  • Rebalancing Partitions using REORG
  • Rebalancing Partitions - Considerations
  • Considerations for User Applications
  • Display Database Command - Increased Partition Support
  • APPLICATION PROGRAMMING OVERVIEW
  • DB2 Environments
  • Important Note
  • Development Cycle With DB2
  • SQL Statement Format
  • SQL Statements used in Application Programs
  • Multi-row processing
  • Multi-row Fetch and Insert
  • New Syntax for Declare Cursor
  • Fetching Rowsets
  • Fetch Examples
  • Host Variable Arrays
  • SQL Include
  • SQL Communication Area
  • Table Declaration
  • Cursors
  • Declare Cursor
  • Open Cursor
  • Fetching Rows
  • Row Update
  • Row Deletion
  • Close Cursor
  • Scrollable Cursors
  • With Hold Option
  • The Optimize Statement
  • The Fetch First Statement
  • Scrollable Cursors
  • Dynamic Scrollable Cursors
  • Insensitive Cursor
  • Sensitive Static Cursor with Insensitive Fetch
  • Sensitive Static Cursor with Sensitive Fetch
  • Fetching from a Scrollable Cursor
  • Sensitive Fetches - Update and Delete Holes
  • Sensitive Fetches - Updated Data
  • Sensitive Static Cursor with Sensitive Fetch
  • Sensitive Dynamic and Asensitive Cursors
  • Sensitive Cursors - Fetching Rows
  • Scrollable Cursor Considerations
  • Table Names in Application Programs
  • Error Handling
  • SQL Error Codes
  • The SQLCA
  • Get Diagnostics
  • Diagnostic Information for Multi-Row Fetch
  • Diagnostic Information for Multi-Row Insert
  • PLANS AND PACKAGES
  • Bind
  • Rebind
  • Plans and Packages
  • Advantages of Using Packages
  • Binding Packages
  • Binding Plans
  • Defaults for Binding
  • Binding in Batch
  • Identifying a Collection Within a Program
  • Program Execution in Batch
  • Plan Names in Application Programs
  • IDENTITY COLUMNS AND SEQUENCES
  • Identity Columns
  • Identity Column Parameters
  • Altering Identity Columns
  • Identity Columns - Data Sharing Implications
  • Using Identity Columns with the Load Utility
  • Sequences
  • Create Sequence Syntax
  • Sequence Ordering
  • Altering Sequences
  • Dropping Sequences
  • Sequence Authorities
  • Using Sequences in Applications
  • Sequences - Considerations and Restrictions
  • Consumed Values / Gaps in a Sequence
  • Duplicate Sequence Values
  • Sequence Cycle Considerations
  • Defining a Constant Sequence
  • Cache Considerations
  • Sequences and Identity Columns Comparison
  • LOCKING
  • Implications of Concurrent Processing
  • IMS Resource Lock Manager (IRLM)
  • DB2 Locking Methods
  • The Lock Table Statement
  • Lock Modes
  • When Locks are Acquired
  • When Locks are Released
  • Isolation Levels
  • Controlling The Isolation Level At SQL Level
  • Keep Update Locks
  • Unit of Work in TSO
  • Commit and Rollback
  • Declaring Cursors With Hold
  • CICS Issues
  • IMS Issues
  • Savepoints
  • Savepoint Definition
  • Savepoints - Considerations and Restrictions
  • Transaction Deadlocks
  • Design Considerations
  • The Need for Application Restart
  • Transaction Deadlocks
  • Displaying Tablespace Locks
  • DB2 Locks On Objects Other Than User Data
  • Lock Avoidance - Latch Processing
  • Lock Avoidance Flow
  • Applications Affected
  • Currentdata (No) Implications
  • Controlling Concurrency For Utilities / Commands
  • The Claim Process
  • The Drain Process
  • The Drain / Claim Mechanism
  • Locking Design Considerations
  • REFERENTIAL INTEGRITY
  • What is Referential Integrity?
  • Implementation of Referential Integrity in DB2
  • Parent and Dependent Tables
  • The Primary Key
  • The Foreign Key
  • Referential Constraint Rules
  • Constraint Names
  • More Complex Referential Structures
  • DML Restrictions of RI
  • Check Pending Status
  • Definition of a Tablespaceset
  • DML Restrictions of RI
  • RI and The Load Utility
  • RI and The Check Utility
  • RI and The Report Tablespaceset Utility
  • Design Considerations
  • TABLE CHECK CONSTRAINTS
  • Overview of Check Constraints
  • Constraint Syntax
  • Allowable Constraints
  • When are Constraints Enforced
  • Current Rules
  • When is Check Pending Set
  • Catalog Changes
  • Authority Changes
  • MATERIALIZED QUERY TABLES
  • What Are Materialized Query Tables?
  • MQT Features
  • Creating an MQT
  • Altering an MQT
  • MQT Fullselect Features / Restrictions
  • Refresh Table
  • Populating User Maintained MQTs
  • Automatic Query Rewrite using MQT
  • Enabling Automatic Query Rewrite
  • Enabling Automatic Query Rewrite - DDL Options
  • Enabling Automatic Query Rewrite - Special Registers
  • AQR - Using Both Registers
  • Enabling Automatic Query Rewrite - System Properties
  • Enabling Automatic Query Rewrite - Query Properties
  • Determining if Query Rewrite Occurred
  • MQTs and Referential Integrity
  • MQTs and RI - Informational Constraints
  • DATABASE SECURITY
  • System Privileges
  • Database Privileges
  • Use Privileges
  • Table Privileges
  • Plan Privileges
  • Primary, Secondary and Current Authids
  • Implicit Privileges of Object Owners
  • Revoking Privileges
  • Version 8 Security Enhancements
  • Multilevel Security - Background
  • Defining RACF Security Labels
  • DB2 Multilevel Security - Seclabel Definition
  • Seclabel Behaviour
  • Seclabel Behaviour with Sql
  • Seclabel Behaviour with Utilities
  • DB2 Multilevel Security at Object Level
  • Encrypting Data
  • UTILITIES
  • Introduction
  • LOAD Utility
  • LOAD Utility Phases and Datasets
  • LOAD Partition Parallelism
  • Parallel Load Syntax
  • Online Load Resume
  • UNLOAD Utility
  • UNLOAD Syntax
  • UNLOAD Parameters
  • UNLOAD Options
  • UNLOAD Examples
  • Unloading from Copy Datasets
  • UNLOAD Restrictions
  • CHECK DATA Utility
  • CHECK Utility Phases and Datasets
  • Course Environment Development will be performed using:
  • DB2 running on a z/OS platform

Course Format: The course includes many practical sessions, designing, implementing, and tuning a Case Study system, using all DB2 utilities applicable to the role of the DBA. On completion of this course delegates will be ready to perform the daily tasks associated with a DBA role.. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course. An IBM SQL REFERENCE SUMMARY HANDBOOK is provided free to all attendees as part of the training course.

DB2 Database Administration Workshop

£ 1,700 + VAT