Overview
This course is designed to teach you how to:
Fully use the advanced technical functions and features of DB2 LUW.
Perform advanced monitoring using the DB2 administrative views and routines in SQL queries. Use the db2diag.log file messages to direct your investigation of problems using db2pd, INSPECT, db2support, and DB2 traces. Use DB2's health monitoring and Health Center to review the health indicator status for the instance, database, and table spaces. The type of problems monitored and resolved include buffer pool activity, lock contention, long running SQL statements, and log space utilization. Understand how automatic and manual first occurrence data capture can help to collect the diagnostic information needed to solve problems.
Configure and manage the implementation of DB2 instance or database level auditing, including using the db2audit command and creation of audit policies which can be assigned to specific tables, users or database roles to perform selective collection of audit records.
Explore DB2's management of disk space usage in Database Managed Storage (DMS) table spaces, including the activities of the rebalancer. Use DB2 commands and utilities to check the high water mark on table spaces and to monitor the rebalance operation. Move data from one table to another or from one database to another using utilities, such as db2move and db2relocatedb.
Implement automatic storage management for table spaces or enable automatic resize options for
DMS managed table spaces to reduce administration requirements and complexity.
Exploit and monitor the REORG utility processing for offline and online table, and index reorganization
This includes planning for the disk space and database log space necessary for reorganization.
Configure the options and policies to automate the collection of table and index statistics or the reorganization of DB2 tables and indexes to improve performance and provide efficient disk space utilization. Monitor and review DB2's automated operations using health monitoring and diagnostic log messages.
Utilize the REORG Utility to implement row compression for large tables, to reduce disk utilization and improve I/O performance for a DB2 database and understand the automatic creation of compression dictionaries.
For Multidimensional Clustering (MDC) tables, determine how to select the dimension columns and table space extent size for efficient implementation of MDC tables. Compare the block indexes used
Course Outline :: CL462G: DB2 9 for LUW Advanced Database Administration for Experts::
for MDC table with rows based indexes. Select the MDC rollout option that best matches application needs and achieves the best performance results.
Determine configuration options to use for connecting DB2 clients to DB2 LUW database servers using
TCP/IP using the configuration assistant. Implement DB2 support for Lightweight Directory Access
Protocol (LDAP) or DB2 thin client support to reduce administration requirements. Enablement of remote administration through use of DB2 Administration Server (DAS).
Plan and implement range based table partitioning for large DB2 tables. Utilize the ALTER TABLE
ATTACH and DETACH options to support roll-in and roll-out operations for range-partitioned tables.
Compare the advantages of selecting or combining range partitioning with the hash-based partitioning used in DB2 DPF databases or the multiple dimensions provided by MDC tables.
You get practical experience in the planning and utilization of a wide variety DB2 LUW utilities and functions by performing a series of lab exercises using DB2 Enterprise 9.5 installed on a Linux platform.
The exercises build skills that can be applied to DB2 database servers on any Linux, UNIX or Windows environment.
Training Paths that reference this course are:
PureData System for Transactions - Database Administrator
IBM Smart Analytics System
Advanced Database Administration – Single Partition and DB2 pureScale
Database Administration - Multiple Partition
Audience
This is an advanced course for DB2 LUW experienced database administrators who support DB2 for UNIX, Windows, and Linux databases.
This course is appropriate for students using DB2 in a z/Linux environment.
Prerequisites
You should complete:
DB2 9 Database Administration Workshop for Linux (CL201) or
DB2 9 Database Administration Workshop for UNIX (CL211) or
DB2 9 Database Administration Workshop for Windows (CL231) or
Fast Path to DB2 9 for Experienced Relational DBAs (CL281) or
DB2 9 for Linux, UNIX, and Windows Quickstart for Experienced Relational DBAs (CL481) or
DB2 9 for LUW Multiple Partition DBA Workshop (CF24) or
Course Outline :: CL462G: DB2 9 for LUW Advanced Database Administration for Experts::
DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs (CG24) or have equivalent experience
Key Topics
Advanced Connectivity and Remote Administration
Describe and verify the relevant registry variables for distributed architecture
Describe the flow through the different directories used by DB2 for connectivity between clients and servers
Maintain the directories for database connections
Enable DB2 LUW LDAP support
Explain the differences between the different options for client connectivity including the IBM Data
Server Client and IBM Data Server Run Time Client
Implement automatic client rerouting and list the relevant entries
Describe the purpose of DB2 Administration Server (DAS)
Establish the TOOLS CATALOG database and identify how it is used
Utilize the Configuration Assistant to maintain database connection configurations for clients
Advanced Locking for DB2 9
Describe the types of locks used by DB2 to support applications using different isolation levels
Configure the database parameters locklist and maxlocks to minimize lock escalations and lock waits
Utilize SNAPSHOT and Event monitors to analyze application lock waits and deadlocks
Set the DB2 registry variables to improve application efficiency, including DB2_EVALUNCOMMITTED,
DB2_SKIPINSERTED and DB2_SKIPDELETED
Describe using DB2 expressions and functions to implement optimistic locking, including RID_BIT and
ROW CHANGE TOKEN
Explain the use of Block level locks for Multidimensional Clustering, MDC tables
Advanced Monitoring
Compare using GET SNAPSHOT commands to running SQL queries to collect snapshot monitor data
Describe the impact of enabling the Snapshot Monitor switches on the monitor data available and the performance overhead associated with collecting more detailed monitor data
Use the DB2 provided Administrative Views and Routines in SQL SELECT statements to retrieve snapshot performance statistics
Check database health indicators, like log space available and table space utilization using CLP queries with the Administrative Views
Advanced Problem Determination
Course Outline :: CL462G: DB2 9 for LUW Advanced Database Administration for Experts::
Describe the types of information collected by DB2 that can be used to diagnose problems, including the db2diag.log and the administration notification log
Plan the use of various diagnostic tools to address specific problems, including the db2diag command, db2pd, db2dart, inspect and db2level
Collect supporting information for DB2 database problems using db2support and db2trc
Explain how automatic and manual First Occurrence Data Capture (FODC) facilities can help collect diagnostic information
Analyze DB2 generated messages in the db2diag.log file and choose appropriate tools and commands to resolve the problems
Utilize the Health Monitoring functions of DB2 to check the health indicators for a DB2 instance, database, or table space
DB2 Database Auditing
Describe the tasks for DB2 database auditing performed by the SYSADM user
List the security administration tasks for DB2 databases that require the SECADM database authority in performing database level audits
Utilize the db2audit command in implement instance level auditing and to configure the audit data and archive locations
Create audit policies to enable collection of specific categories of audit data for a DB2 database
Assign audit policies to selected tables, users or database roles using the AUDIT statement
Advanced Table Space Management
Describe the benefits and limitations of using SMS, DMS and Automatic Storage management for table spaces
Examine GET SNAPSHOT FOR TABLESPACES reports to obtain the current disk space usage, the High
Water Mark and describe the mapping of extents to the DMS table space containers
Utilize the DB2DART utility to list and analyze the current High Water Mark for a DMS table space
Monitor the processing done by the Rebalancer using LIST UTILITIES and GET SNAPSHOT FOR
TABLESPACES output
Plan and implement changes to disk space allocations using ALTER TABLESPACE options: ADD,
EXTEND, RESIZE, DROP, and BEGIN NEW STRIPE SET
Plan the implementation of Large Row Identifiers and Large table spaces to support increased table size
Use the Health Monitor and Storage Management functions to track and monitor table space utilization
Advanced Data Movement
Course Outline :: CL462G: DB2 9 for LUW Advanced Database Administration for Experts::
Describe the options for moving data between tables including using the Import, Export and Load utilities as well as an SQL Insert with a subselect
Configure the LOAD Utility options to optimize the performance of loading data into DB2 tables
Select the best method for duplication of table and index definitions, including the db2look utility
Utilize the db2move utility to move a group of tables into the same or a different database
Copy the objects for a schema using the db2move utility or the ADMIN_COPY_SCHEMA procedure
Change the Automatic Storage paths for a database
Use the RESTORE Utility with a REDIRECT option to move an 1entire database or selected table spaces to a different location
Setup a db2relocatedb command file to rename a database, copy a database or move table space containers
Advanced Table Reorganization
Describe the reasons for reorganizing tables and indexes
Examine a REORGCHK report to determine which tables and indexes to reorganize
Use GET SNAPSHOT commands, the db2pd command, or queries with DB2 provided table functions to monitor REORG Utility progress
Analyze DB2 generated messages in the db2diag.log file and administration notification log to resolve the problems
Utilize the REORG utility to implement row compression for a table
Compare using REORG to build a compression dictionary to automatic dictionary creation
Plan the use of offline and online table and index reorganizations to minimize the impact to applications and optimize performance
Understand the locking and logging required for online and offline REORGs
Multidimensional Clustering
Compare the features and performance advantages of multidimensional clustering (MDC) to singledimensional clustering
Define the concepts of MDC tables, including cell, slice, and dimension
Describe the characteristics of the block indexes used for MDC tables including the index maintenance performed for SQL INSERT, DELETE, and UPDATEs
Explain how the block and row indexes can be combined to efficiently process SQL statements
Utilize the LOAD Utility to roll-in new data into a MDC table
Select options for efficient data roll-out and roll-in
Analyze the effects on table space size of selecting alternative dimensions and extent sizes
Autonomic DB2 Utilities
Course Outline :: CL462G: DB2 9 for LUW Advanced Database Administration for Experts::
Describe the automatic database maintenance facilities provided by DB2 for database backups, table statistics collection and table reorganization
Configure a database for automated utilities using the Configure Automatic Maintenance wizard
Explain the evaluation cycles for each of the automated utilities
Select options for automatic statistics collection, including real time statistics
Analyze the DB2 event messages generated by automatic utility evaluation and execution
Table Partitioning
Describe the options for handling data roll-in and roll-out using DB2 Version 8.2 features, including
DPF database partitioning, Multi-Dimensional Clustering (MDC) and UNION ALL views.
Describe the basic concepts for range-based table partitioning, including global indexing and multiple table spaces.
Define the data partition ranges for a table using the short and long form syntax.
List the steps used for data roll-in and roll-out for table partitioning, including ATTACH, DETACH and
ADD for data partitions.
Compare the roll-in and roll-out functions for table partitioning to using DPF database partitions or
MDC tables.
Plan the use of online SET INTEGRITY as part of the roll-in and roll-out processing for range-partitioned tables.
Describe the maintenance for refresh immediate materialized query tables when used with table partitioning.
Select between table partitioning, MDC, and DPF database partitioning depending on the application and data characteristics.
Agenda
Day 1
Welcome
Exercise 1: DB2 Advanced Connectivity
Exercise 2: DB2 Advanced Monitoring with SQL
Day 2
Exercise 3: DB2 Advanced Problem Determination
Course Outline :: CL462G: DB2 9 for LUW Advanced Database Administration for Experts::
Exercise 4: DB2 Database Audit implementation
Exercise 5: DB2 Advanced DMS Table Space Management
Day 3
Exercise 6: DB2 Advanced Data Movement
Exercise 7: DB2 Advanced Table Reorganization
Day 4
Exercise 8: DB2 Multidimensional Clustering
Exercise 9: Autonomic Database Utilities
Day 5
Exercise 10: Table Partitioning
Objectives
Monitor a DB2 LUW database using command line processor queries
Use the db2diag command to search the DB2 diagnostic log messages to resolve various common database problems
Implement DB2 Instance audit data collection using the db2audit command or database level auditing by creating audit policy objects and assigning the policies to objects using the AUDIT command.
Configure a DB2 database for autonomic utilities, including database statistics collection and table reorganization
Analyze REORGCHK reports to determine if the table or the index reorganization would improve database efficiency. Invoke and monitor the processing for the REORG utility running offline or online
Manage the disk space allocated in DMS table spaces using ALTER TABLESPACE to extend or to reduce the containers, and monitor the progress of the DB2 rebalancer process
Implement automatic resize for DMS table spaces or Automatic Storage management for table spaces to reduce the complexity of managing DB2 LUW databases
Use the DB2LOOK utility to extract the Data Definition Language (DDL) for existing database objects
Plan and execute the DB2MOVE utility to copy selected table data for an entire schema for objects from one DB2 database to another
Course Outline :: CL462G: DB2 9 for LUW Advanced Database Administration for Experts::
Configure the TCP/IP connections from DB2 clients to DB2 database servers to support application access or remote administration by database administrators
Plan the movement of DB2 databases or table spaces using the Backup and Restore utilities or using the db2relocatedb command with non-DB2 utilities
Plan and implement MDC tables to improve application performance, including selecting the appropriate table space extent size
Utilize range-based partitioned tables to support large DB2 tables that require very efficient roll-in and roll-out capabilities
Programme scope, delivery arrangements and suitability are confirmed during enquiry.