Target Audience
This training course is aimed at the Database Administrator, already familiar with DB2 running in a z/OS environment, who requires an in-depth knowledge of the key tasks normally performed by a LUW DB2 DBA.
Pre-requisites
A strong knowledge and usage of DB2 for z/OS in a Database Administration environment is required. In addition, the delegate should have experience in using the operating system.
Course Objectives
The aim of this course is to cross train a z/OS DB2 DBA with the necessary skills required to function as a DB2 DBA within a LUW environment.
On completion of this course the student will be able to:
-
use the DB2 for Windows GUI Toolset
-
define / maintain DB2 objects using DDL statements CREATE, ALTER and DROP
-
use the Command Line Processor
-
define Referential Integrity constraints and Table Check constraints
-
resolve integrity violations
-
understand the application development process
-
bind plans and packages
-
resolve security issues using DCL statements GRANT and REVOKE
-
understand and resolve locking issues
-
understand and run all DB2 Utilities
-
perform database backup and recovery tasks
-
monitor database / application performance
-
perform database replication tasks
Course Environment
The lectures apply to DB2 UDB running in a Linux, Unix or Windows environment.
Course Details
- DB2 INSTANCES
- What Is An Instance?
- Setting Up Instances
- The Db2set Command
- Configuring Instances
- Updating the Database Manager Configuration
- Database Manager Configuration Parameters
- Implementing Changes
- Automatic Database Manager / Database Configuration
- Working With Multiple Instances
- DATABASE DEFINITION
- The Structure Of Udb Objects
- Database Definition
- Default Tablespaces
- Database Placement
- Automatic Storage Databases
- Database Creation Using The Gui
- Database Definition Tasks
- Altering a Database
- Database Connectivity - Connect vs Activate
- Catalog Tables
- Database Configuration
- Updating the Configuration
- Summary Of Database Configuration Parameters
- Automatic Database Configuration
- 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
- Create Tablespace Command
- Create Tablespace Parameters
- Sms Tablespace Examples
- Dms Tablespaces
- Automatic Storage Tablespaces
- Dms Minimum Space Requirements
- Dms Maximum Space
- Alter Tablespace Command
- Altering Dms Containers
- Containers, Stripes and Stripe Sets
- Creating A Tablespace Using The Gui
- 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
- Null and Default Compression
- Compression - Row Format
- Has Compression Been Switched On?
- Lob Data Options
- The Alter Table Statement
- Advanced GUI Alter Table Functionality
- 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
- Range Partitioned Tables
- COMMAND LINE PROCESSOR
- CLP Syntax
- On-Line Help
- Interactive / Non-Interactive Modes
- CLP Option Flag
- CLP Termination
- REFERENTIAL INTEGRITY
- What is Referential Integrity?
- The Primary Key
- The Foreign Key
- Referential Constraint Rules
- Constraint Names
- More Complex Referential Structures
- Check Pending / Set Constraints
- Running Set Constraints via the GUI
- TABLE CHECK CONSTRAINTS
- Overview of Check Constraints
- Constraint Syntax
- Allowable Constraints
- When Are Constraints Enforced
- Check Pending
- Running Set Constraints via the GUI
- Catalog Information
- MOVING DATA
- Import / Export Utilities
- Import Utility Syntax
- Import Authorities
- Export Utility Syntax
- Export Authorities
- Load Utility
- Load Utility Features
- Load Examples
- Load Utility Syntax
- Recovering from Load Failure / Load Pending
- Load Utility and Referential / Check Constraints:
- Load Authorities
- Load / Import Differences
- Tablespace Pending States
- Backup Pending
- Check Pending / Set Constraints
- Running Set Constraints via the GUI
- Replication - Data Propagator
- BACKUP AND RECOVERY
- Data Recovery Overview
- Methods of Recovery
- Logging Overview
- Circular Logging
- Archival Logging
- Log File Information
- Recovery History File
- The Backup Utility
- Backup File Names
- Backup Examples
- The Restore and Rollforward Utilities
- Rollforward Utility / Rollforward Pending
- Rollforward Considerations
- Recovery Examples
- Logging / Backup / Recovery Summary
- The Quiesce Utility
- APPLICATION PROGRAMMING OVERVIEW
- Db2 Environments
- Development Cycle For Embedded Sql in C
- SQLJ Programming - Development Cycle
- JDBC Drivers - The Universal Driver
- Single Row Selects
- Including External Sql into C Programs
- Sql Include
- Sql Communication Area
- Host Variable Declaration in C Programs
- Cursor Selects
- Declare Cursor
- Open Cursor
- Fetch A Row
- Row Update
- Row Deletion
- Close Cursor
- With Hold Option
- Fetch First Clause
- The Optimize Statement
- Precompiling A Program
- Binding A Program
- Levels Of Optimisation
- Run Time Reoptimization - Bind Option REOPT
- 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
- Lock Modes
- Lock Example
- Lock Compatibility
- Isolation Levels
- Lock Escalation
- The Lock Table Statement
- Commit Points
- Lock Wait And Deadlocks
- Savepoints
- Savepoints - Considerations and Restrictions
- The Quiesce Utility
- SHARE
- 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
- Informational Constraints
- MONITORING
- Error Logging
- Database Monitoring
- Snapshot Monitoring
- Alerts
- Monitoring Commands
- Turning Monitoring Switches On
- Event Monitoring
- The Create Event Monitor Command
- DB2 Trace Facility
- Explain
- Explain Facility Tools
- The DB2 Explain Function
- Explain Tables
- The DB2EXPLN Applet
- The Visual Explain Tool
- 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 the GUI
- Privileges Required for Programming
- Grant Examples
- Revoke Examples
- APPLICATION PERFORMANCE
- The DB2 Optimizer
- Default Catalog Statistics
- Levels of Optimisation
- Operational Utilities
- Rebinding
- The RUNSTATS Utility
- The REORGCHK Utility
- The REORG Utility
- Other Application Performance Issues
- Data Sorts
- Concurrent Application Tuning
- Asynchronous Page Cleaner
- Blocking Data
- SYSTEM PERFORMANCE
- Database Performance Configuration Parameters
- Data Sorts
- Concurrent Application Tuning
- Asynchronous Page Cleaner
- Blocking Data
- The Database Configuration Advisor
- UDB TOOLS
- The Control Center
- Control Center Enhancements
- Command Editor
- Storage Management Tool
- The Journal
- The Development Center
- Sql Assist Enhancements
- Replication Center
- Additional Wizards and Gui Tools
- 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
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.
|