| UD03 - DB2 for LUW Database Administration Workshop |
| Duration | 5 Days (customisation) |
| Availability |
|
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 lectures apply to DB2 running in a Linux, Unix or Windows environment.
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
- The LUW Environment
- Db2 Administration Tools
- DB2 INSTANCES
- What Is An Instance?
- Setting Up Instances
- Configuring Instances
- Updating the Database Manager Configuration
- Database Manager Configuration Parameters
- Implementing Changes
- Working With Multiple Instances
- DATABASE DEFINITION
- The Structure Of DB2 Objects
- Database Definition
- Default Tablespaces
- 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
- Block Based Bufferpools - Prefetch Enhancements
- TABLESPACE DEFINITION
- Tablespace Organisation
- Data Placement - SMS or DMS?
- Data Placement Considerations
- Containers, Pages And Extents
- Maximum Sizes
- Create Tablespace Command
- Create Tablespace Parameters
- Sms Tablespace Examples
- Dms Tablespaces
- Automatic Storage Tablespaces
- Dms Minimum Space Requirements
- Alter Tablespace Command
- 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
- Db2 Column Types
- Null Values
- Compressing a Table
- Row Compression - Null and Default Compression
- Has Compression Been Switched On?
- Lob Data Options
- The Alter Table Statement
- The Rename Table Statement
- 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
- Alias Definition
- Indexes
- Index Organisation - The B Tree Index
- Index Clustering
- Index Definition
- Renaming Indexes
- Multidimensional Clustering
- Defining Multidimensional Clustering
- Defining Multidimensional Clustering Indexes
- Using Generated Columns with MDC
- Db2look
- PARTITIONING
- Introduction and Terminology
- The DB2Nodes.cfg File
- 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
- RUNNING SQL AND COMMANDS
- Connecting To The Database
- Using IBM Data Studio to run SQL Scripts
- The DB2 Command Window and Command Line Processor
- Command Line Syntax
- On-Line Help
- Interactive / Non-Interactive Modes
- Clp Option Flag
- Clp Termination
- MOVING DATA
- Overview of Data Movement Utilities
- Import / Export Utilities
- Import Utility
- Import Authorities
- Export Utility
- Export Authorities
- Load Utility
- Load Utility Considerations
- Load Examples
- Load from a Cursor
- Online Table Load
- Load - Lock With Force Parameter
- The Load Query Command - Table States
- Recovering From Load Failure / Load Pending
- Load Restart Example
- The Load Wizard
- Load / Import Differences
- DB2Move Command
- DB2Move Examples
- Tablespace Pending States
- Dealing with Backup Pending after LOAD
- The Quiesce Utility
- Quiesce Enhancement
- Utility Progress Monitoring
- The Admin_Cmd Procedure
- BACKUP AND RECOVERY
- Data Recovery Overview
- Methods Of Recovery
- Logging Overview
- Circular Logging
- Archival Logging
- 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 Considerations
- Database Restore Using Redirect
- Rollforward Utility / Rollforward Pending
- The Rollforward Utility
- Rollforward Considerations
- Restore / Rollforward Examples
- The Recover Utility
- Logging / Backup / Recovery Summary
- The Admin_Cmd Procedure
- High Availability Disaster Recovery
- Configuring a database to use HADR
- Connecting to the standby database
- Executing HADR Commands
- APPLICATION PROGRAMMING
- Db2 Environments
- Development Cycle For Embedded Sql in C
- SQLJ Programming - Development Cycle
- Precompiling A C Program
- Binding A C Program
- Levels Of Optimisation
- Stored Procedure Review
- An SQL Procedure Example
- Selecting Data - Singleton Selects
- Selecting Data - Cursor Operations
- Building and Rebinding Stored Procedures
- 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 IBM Data Studio
- Informational Constraints
- MATERIALIZED QUERY TABLES
- What Are Materialized Query Tables?
- MQT Features
- Creating an MQT
- Create MQT Example
- Altering an MQT
- Alter MQT Example
- 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
- Registering Existing Tables as MQT
- Maintenance of MQTs using Staging Tables
| - LOCKING
- Implications Of Concurrent Processing
- Database Manager Locks
- Objects Of Locks
- Isolation Levels
- Lock Modes
- Lock Compatibility
- Lock Example
- Change in Cursor Stability Behaviour V9.7
- 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
- SECURITY
- 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
- 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
- Further GUI Automatic Reorg Features
- The Admin_Cmd Procedure
- SYSTEM PERFORMANCE
- Database Performance Configuration Parameters
- Self Tuning Memory Allocation
- Data Sorts
- Concurrent Application Tuning
- Asynchronous Page Cleaner
- Blocking Data
- The Database Configuration Advisor
- MONITORING
- Error Logging
- Database Monitoring
- Snapshot Monitor
- Turning Monitoring Switches On
- Snapshot Commands
- Taking a Snapshot using Sql
- Summary of Activity Monitor SQL Routines
- Summary of Health Snapshop SQL Routines
- Summary of Snapshot Related SQL Routines
- Summary of ADMIN_CMD Related SQL Routines
- Summary of Configuration Related SQL Routines
- Summary of Environment Related SQL Routines
- Summary of WebSphere MQ Related SQL Routines
- Summary of Security Related SQL Routines
- Summary of Stored Procedure Related SQL Routines
- Summary of Redistribution Related SQL Routines
- Summary of Management Tool Related SQL Routines
- Summary of Miscellaneous Tool Related SQL Routines
- Examples Of SQL Functions / Views
- Event Monitors
- The Create Event Monitor Command
- Event Monitor Example
- Activating Monitors
- Formatting File Monitor Output
- Event Monitors - Writing to SQL tables
- The Activity Monitor
- Health Monitoring
- Health Indicator Configuration
- Recommendation Advisor
- Diagnosis of Lock Timeout
- Lock Timeout Report File Example
- DB2PD Command
- SQL PERFORMANCE AND TUNING
- SQL Explain Tools
- Explain Tables
- The Db2 Explain Bind Option
- The Db2expln Tool
- The DynExpln Tool
- Interpreting DB2Expln and Dynexpln Output
- The Db2advis Tool - Index Advisor
- The Design Advisor
- The Visual Explain Tool
- The Explain Operator Details Window
- Visual Explain Operators
- Visual Explain - The Table Statistics Window
- Visual Explain - The Column Statistics Window
- The Index Statistics Window
- The Explainable Statements Window
- 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
- DB2 TOOLS
- IBM Data Studio
- IBM Data Studio Web Console
- Data Replication
- Overview
- Replication Tools
- Steps Required To Set Up A Replication Environment
- Defining A Replication Source
- Defining A Replication Subscription
- Configuring The Capture Program
- Configuring The Apply Program
- End-User Authentication File Creation
- Starting The Capture Program
- Starting The Apply Program
- Stopping The Capture And Apply Programs
- Troubleshooting
- DB2 SIZE LIMITS
- Naming Limits
- Numeric Limits
- String Limits
- Xml Limits
- Size Limits
- Tablespace Limits
- DB2 V9 NEW FEATURES
- General
- Backup, Recovery and High Availability
- Performance and Tuning
- Security
- XML
- Application Development
- DDL
- No Longer Available
- Deprecated Features
- DB2 V10 NEW FEATURES
- General
- Performance and Tuning
- DDL
- Security
- XML
- Application Development
- No Longer Available
- Deprecated Features
|
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.
|