> > CL462G Detaillierte Beschreibung

DB2 9 for LUW Advanced Database Administration for Experts (CL462G)

Kursbeschreibung Kurstermine Detaillierter Kursinhalt

Detaillierter Kursinhalt

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.


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