UD03 - DB2 for LUW - Database Administration Workshop - 5 Days

Course Description

This course provides advanced DB2 for LUW training, and covers all of the key tasks normally performed by a Data Base Administrator.

Pre-requisites

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

Objectives

The aim of this course is to provide the delegate with the necessary skills required to function as a DBA within a LUW environment.

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

  • use the DB2 Toolset
  • define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
  • define partitioning databases and tables
  • use IBM Data Studio and the Data Studio Web Console
  • run IMPORT, EXPORT and LOAD statements
  • perform BACKUP, RESTORE, RECOVER and ROLLFORWARD commands
  • understand the concepts of High Availability Disaster Recovery (HADR)
  • define Referential Integrity constraints and Table Check constraints
  • resolve integrity violations
  • understand the application development process
  • bind plans and packages
  • set up database security
  • understand and resolve locking issues
  • monitor database activity
  • investigate system, application and SQL performance issues
  • perform database replication tasks

Environment

The course applies to DB2 (up to v12) running in a Linux, Unix or Windows environment.

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

GETTING STARTED WITH DB2 FOR LUW
DB2 Servers
DB2 Components
IBM Data Studio
IBM Data Studio Web Console

RUNNING SQL AND COMMANDS
Connecting To The Database
Running SQL Scripts from IBM Data Studio
The DB2 Command Window and Command Line Processor
Command Line Syntax
On-Line Help
Interactive / Non-Interactive Modes
Clp Option Flag
Clp Termination
The Update Command Options Command

DB2 INSTANCES
What Is An Instance?
Setting Up Instances
Working With Multiple Instances
Attaching to Instances
Connecting to Other Instances / Servers
Configuring Instances
Updating the Database Manager Configuration
Database Manager Configuration Parameters
Implementing Changes
Db2 Administration Server

DATABASE DEFINITION
The Structure Of DB2 Objects
Database Definition
Default Tablespaces
Database Placement – Automatic Storage Yes
Database Placement – Automatic Storage No
Automatic Storage Databases
Database Creation using IBM Data Studio
Database Definition Tasks
Creating Restrictive Databases
Altering a Database
Database Connectivity – Connect vs Activate
Catalog Tables
Database Configuration
Updating the Configuration
Summary Of Database Configuration Parameters
Automatic Database Manager / Database Configuration
Update Database Configuration – Manual Keyword
Database Directories
Create Bufferpool
Online Bufferpool Maintenance
Useful SQL Routines

TABLESPACE DEFINITION
Tablespace Organisation
Data Placement – SMS or DMS?
Data Placement Considerations
Containers, Storage Groups Pages And Extents
Multiple Container Considerations
Create Tablespace Command
Create Tablespace Parameters
Sms Tablespace Creation
Dms Tablespace Creation
Automatic Storage Tablespace Creation
Storage Groups
Maximum Sizes
Dms Minimum Space Requirements
Alter Tablespace Command
Converting From DMS to Automatic Storage
Altering Dms Containers
Containers, Stripes and Stripe Sets
Creating A Tablespace Using IBM Data Studio
Displaying Tablespace Information
Tablespace States
Dropping A Tablespace
Page And Row Organisation

TABLE / INDEX DEFINITION
Table Definition
Table / Column Names
Copying Table Definitions
Table Authority
Table Related Commands
Db2 Column Types
Null Values
Compressing a Table
Row Compression – Null and Default Compression
Has Compression Been Switched On?
Implicitly Hidden Columns
Row Change Timestamps
Row Change Timestamp Selection
Row Change Timestamp Insertion
Variable Timestamp Precision
Variable Timestamp Precision – Current Timestamp
Lob Data Options
The Alter Table Statement
The Rename Table Statement
Column Renaming
Column Renaming Restrictions
Drop Column Support
Drop Column - Restrictions
Range Clustered Tables
Range Clustered Tables - Conisderations
Insert Time Clustering
Column Organized Tables
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables – Comparisons
Listing Table / Tablespace Information
Schema Definition
View Definition
Creating A View Of Two Tables
Read Only Views
View Restrictions
Views - Check Options
Db2 Synonym
Alias Definition
Indexes
Index Organisation - The B Tree Index
Index Clustering
Index Definition
Renaming Indexes
Improving Cluster Performance
Improving Insert Performance
Multidimensional Clustering
Defining Multidimensional Clustering
Defining Multidimensional Clustering Indexes
Using Generated Columns with MDC
Db2look - Statistics and DDL Extraction Tool

PARTITIONING
Introduction and Terminology
The DB2Nodes.cfg File
Configuring DB2Nodes.cfg on Linux and Unix Machines
Configuring DB2Nodes.cfg on Windows Machines
Example Configurations
Creating Parallel Databases
Database Partition Groups
Creating a Database Partition Group
Listing Nodes and Database Partition Groups
Creating Partitioned Tablespaces
Creating Partitioned Tables
Hash Partitioning
Partitioning Maps
The Redistribute Database Partition Group Command
Using Backup and Restore
Table Partitioning
Benefits Of Table Partitioning
Table Partitioning Syntax
Partitioning at Automated Intervals
Adding Partitions to a Partitioned Table
Attaching and Detaching Partitions
Converting Non-Partitioned Tables to Partitioned
Using Database and Table Partitioning Together
Enabling Parallelism

MOVING DATA
Overview of Data Movement Utilities
Import / Export Utilities
Export Utility
Export Examples
Generating the Export Command
Export Authorities
Import Utility
Import Examples
Import Authorities
Import / Load Settings for the 'Modified' By Parameter
Load Utility
Load Examples
Recovering From Load Failure / Load Pending
Load Restart Example
Load Utility Considerations
Load from a Cursor
Online Table Load
Load - Lock With Force Parameter
The Load Query Command - Table States
Dealing with Backup Pending after LOAD
The Load Wizard
Load / Import Differences
Ingest Utility
Ingest Examples
Restarting Ingest
DB2Move Command
DB2Move Examples
DB2RelocateDb Command
DB2RelocateDb Examples
The Admin_Copy_Schema Procedure
The Admin_Move_Table Procedure
Tablespace Pending States
The Quiesce Command
Quiesce Examples
Utility Progress Monitoring
The Admin_Cmd Procedure

BACKUP AND RECOVERY
Data Recovery Overview
Methods Of Recovery
Logging Overview
Circular Logging
Archive Logging
Log File Database Configuration Settings
Log File Information
Logging Considerations
Recovery History File
Removal of History Records
Automated Removal of History Records
Single / Dual Logging
The Backup Utility
Backup Examples
Backup Considerations
Backup File Names
Automatic Backup
The Restore Utility
Restore Examples
Restore Considerations
Database Restore Using Redirect
Rollforward Utility / Rollforward Pending
The Rollforward Utility
Rollforward Considerations
Restore / Rollforward Examples
Tablespace Minimum Recovery Time
The Recover Utility
Recover Example
Logging / Backup / Recovery Summary
The Admin_Cmd Procedure
High Availability Disaster Recovery (HADR) Overview
HADR Limitations / Restrictions
HADR Setup - Step 1 – Select the Primary Database
HADR Setup - Step 2 –Primary Database Configuration
HADR Setup - Step 3 – Standby Database Creation
HADR Setup - Step 4 –Standby Database Configuration
HADR Setup - Step 5 – Specify Synchronisation Mode
HADR Setup - Step 6 – Start HADR
HADR States
HADR Takeover

APPLICATION PROGRAMMING ADMINISTRATION
Db2 Environments
Development Cycle For Embedded Sql in C
SQLJ Programming - Development Cycle
Precompiling A C Program
Binding A C Program
Levels Of Optimisation

LOCKING
Implications Of Concurrent Processing
Database Manager Locks
Objects Of Locks
Isolation Levels
Lock Modes
Lock Compatibility
Lock Example
Change in Cursor Stability Behaviour
Selecting Data with Update / Exclusive Locks
The Lock Table Statement
Lock Escalation
Lock Wait And Deadlocks
Commit Points
Savepoints
Savepoints – Considerations and Restrictions
Optimistic Locking using Row Change Timestamps
Optimistic Locking Example
Row Change Timestamp Manipulation
The Quiesce Utility

DATA INTEGRITY
Referential Integrity
The Primary Key
The Foreign Key
Referential Integrity Rules
Referential Integrity Constraint Names
Self Referencing Referential Structures
Referential Integrity Performance
Check Constraints
Check Constraint Syntax
Allowable Check Constraints
When Are Check Constraints Enforced
Integrity Pending (aka Check Pending) State
The Set Integrity Command
Running Set Integrity Via The Gui
Load Utility Considerations
Informational Constraints

SECURITY
Connecting to DB2 - Authentication
Authentication Methods
Authorities And Privileges
Instance / System Authorities
Database Authorities
Database Authority Summary
Schema Privileges
Table Privileges
View Privileges
Index Privileges
Package Privileges
The Grant Statement
The Control Privilege
The Revoke Statement
Catalog Information
Granting Using IBM Data Studio
Privileges Required For Programming
Grant Examples
Revoke Examples
Label-Based Access Control
LBAC – Step 1 – Define the Security Label Component
LBAC – Step 2 – Define the Security Policy
LBAC – Step 3 – Define the Security Labels
LBAC – Step 4 – Define the Table to use Security Labels
LBAC – Step 5 – Grant Security Labels to Users
LBAC – Step 6 – Use the Table
Label-Based Access Control – Column Protection
LBAC Catalog Tables
The Transfer Ownership Statement
Introduction to Trusted Context and Roles
Trusted Context and Roles - 3 Tier Problems
Security Enhancement - Trusted Contexts / Roles
Trusted Contexts
Creating Trusted Contexts
Roles
Trusted Context / Role Examples
Using a Trusted Connection in a JDBC Application
Creating Restrictive Databases
Row and Column Access Control
Scalar Functions for Row / Column Permissions
Row Access Control - Defining Row Permissions
Activating Row Permissions
Column Access Control - Defining Column Masks
Activating Column Masks

APPLICATION PERFORMANCE
The Db2 Optimizer
Levels Of Optimisation
Operational Utilities
Rebinding
The Runstats Utility
Runstats Parameters
Runstats - Sampling Options
Runstats - Statistics Profiling
Runstats - Throttling
Runstats Profiling Examples
Statistical Views
Statistical View Considerations
Automatic Statistics Collection
Automatic Statistics Profile Generation
Collection of Real Time Statistics
Volatile Tables
The Reorgchk Utility
The Reorg Utility
Offline / Online Table Reorg
Index Reorg
Automatic Table / Index Reorg
Maintenance Window Features
The Admin_Cmd Procedure

SYSTEM PERFORMANCE
Database Performance Configuration Parameters
Self-tuning memory manager (STMM)
Data Sorts
Concurrent Application Tuning
Asynchronous Page Cleaner
Blocking Data
The Database Configuration Advisor

MONITORING
Error Logging
Database Monitoring
Monitoring Elements
System Monitoring Table Functions
System Monitoring Example
Activity Monitoring Table Functions
Database Monitoring Table Functions
Database Monitoring Example
Monitoring Table Functions Summary
Administrative Views
Administrative View Examples
Event Monitors
Event Monitor Types
The Create Event Monitor Command
Event Monitor Example
Activating Monitors
Formatting File Monitor Output
Monitor Example – Investigating Poorly Performing SQL
Event Monitors - Writing to SQL tables
Snapshot Monitoring - Pre DB2 9.7
Snapshot Monitor Switches
Snapshot Commands
Taking a Snapshot using Sql
Monitoring SQL Routines
Examples Of SQL Functions / Views
Monitoring using the IBM Data Studio Web Console
Web Console Access
Web Console - Health Summary
Web Console – View Alerts
Web Console –Configuring Alerts
Web Console – View Application Connections
Web Console – Monitor Tablespaces
Web Console – Monitor Utilities
Web Console – Job Manager
Other Diagnosis Features - Diagnosis of Lock Timeout
Lock Timeout Report File Example
Other Diagnosis Features - DB2PD Command

SQL PERFORMANCE AND TUNING
SQL Explain Tools
Explain Tables
The Explain Command
The Db2 Explain Bind Option
The Db2expln Tool
Interpreting DB2Expln Output
The Db2advis Tool - Index Advisor
The Query Tuner
The Visual Explain Tool
The Explain Operator Details Window
Visual Explain Operators
Visual Explain – The Table Statistics Window
Visual Explain for Packages
Access Paths - Tablespace Scan (Relational Scan)
Non-Matching Index Scan
Matching Index Scan
Multiple Index Access
Index Only Access
Table Join Methods
Merge Scan Join
Nested Loop Join
Hash Join

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 on a Windows platform.

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.

Course Enquiry