DB2 UDB Advanced Database Administration for Experts

start > courses and registration > training feature sheet

code.

CF460

length.

5 days

type.

Instructor-Led

partner.

IBM

price.

$3,125

IACET credits 0.0

Learn to fully use the advanced technical functions and features of DB2 UDB.

Perform advanced monitoring using the snapshot table functions and extend the functionality for your specific needs. 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 space. Problems monitored and resolved include buffer pool activity, lock contention, long running SQL statements, and log space utilization.

Investigate the DB2 UDB process model, including its processes for log management, Input/Output (I/O) prefetching, page cleaning and utility processing for load, backup, and restore. Support intra-partition parallelism of SQL queries with appropriate settings of configuration parameters.

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.

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. Also, 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.

Explore SQL Procedure Language (SQL PL) routine language elements, execution model, design, development, debugging, and deployment. Improve performance of SQL PL procedures using best practice examples.

For Multidimensional Clustering (MDC) tables, determine how to select dimension columns and table space extent size. Determine configuration options to use for connecting DB2 clients to DB2 UDB database servers using TCP/IP, configuration assistant, and Lightweight Directory Access Protocol (LDAP). Support remote administration through use of DB2 Administration Server (DAS).

Federate DB2 databases to allow multiple databases to be accessed in a single SQL statement. Compare and contrast the functionality of SQL based and Q based replication.

You get practical experience in the planning and utilization of a wide variety DB2 UDB utilities. Also, complete series of lab exercises using DB2 UDB Enterprise Server Edition Version 8.2.

Phoenix, AZ	jun.9.2008 - jun.13.2008	register
Phoenix, AZ	sep.8.2008 - sep.12.2008	register
Phoenix, AZ	nov.3.2008 - nov.7.2008	register
El Segundo/Los Angeles, CA	jun.9.2008 - jun.13.2008	register
San Francisco, CA	jun.9.2008 - jun.13.2008	register
Sacramento/Elk Grove, CA	jun.9.2008 - jun.13.2008	register
Rancho Cordova, CA	jun.16.2008 - jun.20.2008	register
San Francisco, CA	sep.8.2008 - sep.12.2008	register
El Segundo/Los Angeles, CA	sep.8.2008 - sep.12.2008	register
Sacramento/Elk Grove, CA	sep.8.2008 - sep.12.2008	register
El Segundo/Los Angeles, CA	nov.3.2008 - nov.7.2008	register
Sacramento/Elk Grove, CA	nov.3.2008 - nov.7.2008	register
Englewood/Denver, CO	sep.8.2008 - sep.12.2008	register
Englewood/Denver, CO	nov.3.2008 - nov.7.2008	register
Washington, DC	oct.20.2008 - oct.24.2008	register
Miami, FL	may.19.2008 - may.23.2008	register
Orlando, FL	jul.7.2008 - jul.11.2008	register
Atlanta, GA	sep.15.2008 - sep.19.2008	register
Chicago, IL	oct.6.2008 - oct.10.2008	register
Overland Park, KS	nov.3.2008 - nov.7.2008	register
Boston, MA	jun.9.2008 - jun.13.2008	register
St louis/Maryland Heights, MO	nov.3.2008 - nov.7.2008	register
Charlotte, NC	jul.14.2008 - jul.18.2008	register
Durham/Raleigh, NC	aug.18.2008 - aug.22.2008	register
New York/Downtown, NY	jul.28.2008 - aug.1.2008	register
Irving/Dallas, TX	aug.11.2008 - aug.15.2008	register
Bellevue/Seattle, WA	jun.9.2008 - jun.13.2008	register
Bellevue/Seattle, WA	sep.8.2008 - sep.12.2008	register
Bellevue/Seattle, WA	nov.3.2008 - nov.7.2008	register

who can benefit

This is an advanced course for DB2 UDB experienced database administrators who support DB2 UDB for UNIX, Windows, and Linux databases.

prerequisites

You should complete:

	DB2 Universal Database Administration Workshop for Linux (CF201) or
	DB2 Universal Database Administration Workshop for UNIX (CF211) or
	DB2 Universal Database Administration Workshop for Windows NT (CF231) or
	DB2 UDB Administration Workshop for Sun Solaris (CF271) or
	Fast Path to DB2 UDB for Experienced Relational DBAs (CF281) or
	DB2 UDB Advanced Administration for Experienced Relational DBAs (CF482) or
	DB2 UDB Multi Partition Database Administration Workshop for UNIX (CF241) or
	DB2 UDB Multi Partition Environment for Single Partition DBAs (CG241)
	or have equivalent experience

skills gained

	Monitor a DB2 UDB database using command line processor queries
	Use the db2diag command to search the DB2 diagnostic log messages to resolve various common database problems
	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
	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 from one DB2 database to another
	Design, develop, debug, deploy, and improve the performance of SQL PL procedures using best practice examples
	Configure TCP/IP connections from DB2 clients to DB2 database servers to support application access or remote administration by database administrators
	Describe DB2 DataPropagator basic components and control tables that support SQL based replication and Q based replication
	Select DB2 database manager and database configuration options to effectively utilize the DB2 facilities for parallel processing of application SQL. This includes DB2 Utilities in SMP system environments
	Plan and implement MDC tables to improve application performance, including selecting the appropriate table space extent size

course materials

The course materials cover DB2 UDB for Linux, UNIX, and Windows Version 8.2.

hands-on labs

Eleven labs are included to address advanced usage of DB2 UDB for Linux, UNIX, and Windows Version 8.2.

ibm education advantage program eligibility:

	Yes - IBM Education Card
	Yes - IBM Education Pack
	Yes - IBM Corporate Education Pass
	Yes - IBM Education Pack - online account

related courses, exams and materials

	before related training and products

	CF482 DB2 UDB Advanced Administration for Experienced Relational DBAs
	CF201 DB2 Universal Database Administration Workshop for Linux
	CF211 DB2 Universal Database Administration Workshop for UNIX
	CF231 DB2 Universal Database Administration Workshop for Windows
	CF241 DB2 UDB Multi Partition Database Administration Workshop for UNIX

course content details

	DB2 UDB advanced connectivity

This unit covers the planning and implementation of DB2 client connectivity to DB2 UDB database servers using TCP/IP. It also describes the different configuration options available to configure a DB2 client to connect to a DB2 UDB server system. This includes using the configuration assistant tool and supports for LDAP. The configuration of the DAS to support remote administration tools and thin client support are also explained.

	Advanced monitoring

The unit will explaine how to monitor an active DB2 database utilizing SQL queries through the command line processor. The lecture includes examples of SQL queries to monitor buffer pool activity, lock contention, long running SQL statements, and log space utilization. The lab exercises allow you to run a set of scripted SQL queries to monitor a test database in several application scenarios.

	Advanced locking

This unit provides a detailed discussion of locking techniques used by DB2 UDB. You will gain a better understanding of the relationship between DB2 processing of application SQL and the application's setting for isolation level. The lab exercise covers monitoring of lock related information by using snapshot monitoring, event monitors, and the db2pd command.

	Advanced Problem Determination

The lecture will explain how to use various DB2 tools, including the DB2 commands, db2diag, db2pd, INSPECT, db2support, and db2 traces to handle database problems. A series of db2diag.log file messages that are generated from common database problems are presented with the suggested diagnostic commands to retrieve additional supporting information. This topic also describes using DB2's health monitoring and Health Center review the health indicator status for the instance, database, and table spaces.

	DB2 UDB process model and parallelism

This lecture will describe the processes DB2 uses to perform different database functions, including log management, I/O prefetching, page cleaning, and utility processing for LOAD, backup and restore. It will also describe the configuration of the database manager and database to support intra-partition parallel processing for SQL queries.

	Advanced table space management

This lecture will describe how DB2 manages the disk space allocated in DMS table spaces, including the processing of the rebalancer. Learn how to alter DMS table spaces to increase container size, drop, or decrease containers or allocate a new container without rebalancing the current extents. Learn to check the high water mark size using GET SNAPSHOT commands and the db2dart utility, and monitor the rebalancer processing with the LIST UTILITIES command.

	Advanced data movement

This lecture will describe the use of the DB2 utilities db2look and db2move for moving table data and generating DB2 data definitions. Learn how to optimize LOAD utility performance and select LOAD recovery options. The use of the db2relocatedb command to allow table space containers or entire databases to be moved or copied with non-DB2 utilities will be explained with examples.

	Advanced table reorganization

The lecture describes the REORG utility processing for both offline and online table, and index reorganization. This includes planning for the disk space and database log space necessary for reorganization. Examine the REORGCHK report details to determine which tables and indexes require reorganization to improve efficiency. The monitoring of REORG processing with GET SNAPSHOT FOR TABLES and the db2pd command will also be covered.

	DB2 UDB SQL PL procedures

This lecture covers the SQL PL and SQL procedures. This includes SQL PL language elements, execution model, designing, developing, debugging, deploying, and improving the performance of SQL PL procedures using best practice examples. Content will highlight enhancements in DB2 Version 8.2.

	MDC tables

This lecture describes the performance characteristics of accessing data in MDC tables to access standard DB2 tables with a clustered or non-clustered index. The basic concepts and planning considerations for selecting dimension columns and table space extent size will be discussed. Learn how to use DB2 design Advisor to analyze a defined SQL workload to suggest, which tables may benefit from conversion to a MDC table.

	Autonomic DB2 utilities

This unit will describe the DB2 options to automate the collection of table and index statistics. Describe the reorganization of DB2 tables and indexes to improve performance and efficient disk space utilization. Learn how to configure the options and policies that control automatic table maintenance. Learn how to monitor and review DB2's automated operations using health monitoring and diagnostic log messages.

	Federated database access

This unit will introduce the steps required to configure the database manager. Create the wrappers, servers, user mappings, and nicknames necessary to support application access to remote data sources.

	DB2 UDB replication

This unit describes the replication features and functions of DB2 DataPropagator. Get a basic overview of DB2 DataPropagator components and architecture, including the differences between SQL based and Q based replication.