DB2 9 for LUW Advanced Database Administration for Experts

• 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 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

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.

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

• 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

• 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 single-dimensional 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

• 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
• Unit 1 - Advanced Connectivity and Remote Administration
• Exercise 1: DB2 Advanced Connectivity
• Unit 2 - Advanced Locking
• Unit 3 - Advanced Monitoring
• Exercise 2: DB2 Advanced Monitoring with SQL

Day 2

• Unit 4 - Advanced Problem Determination
• Exercise 3: DB2 Advanced Problem Determination
• Unit 5 - DB2 Database Auditing
• Exercise 4: DB2 Database Audit implementation
• Unit 6 - Advanced Table Space Management
• Exercise 5: DB2 Advanced DMS Table Space Management

Day 3

• Unit 7 - Advanced Data Movement
• Exercise 6: DB2 Advanced Data Movement
• Unit 8 - Advanced Table Reorganization
• Exercise 7: DB2 Advanced Table Reorganization

Day 4

• Unit 9 - Multiple Dimension Clustering
• Exercise 8: DB2 Multidimensional Clustering
• Unit 10 - Autonomic DB2 Utilities
• Exercise 9: Autonomic Database Utilities

Day 5

• Unit 11 - Table Partitioning
• Exercise 10: Table Partitioning