DURATION
2 days
Audience
Database administrators, application designers, technical support, and technical specialists who require information about the features and functions of DB2 V9 for Linux, UNIX, and Windows.
Prerequisites
There are no prerequisites for this course.
Objectives
After completing this course, you should be able to:
Describe the new functions and features of DB2 V9 for Linux, UNIX, and Windows
Assess the applicability of these features and functions in your environment
Discuss the advantages and potential impact of this release of DB2 to your environment
Course outline
Database and Application Enhancements
Discuss changes to default database creation behavior, including the ability to auto-configure the database configuration parameters
Implement automatic storage databases and automatic storage table spaces
Find information on automatic storage databases and table spaces
Review ways to handle automatic storage space growth
Work with reverse scan indexes
Set up and use Auto RUNSTATS
Review changes to snapshot monitoring
Work with administration feature changes
List the new sample programs included with DB2 9
Database Rebuild Support
Describe some limitations and issues with Database Recovery in DB2 UDB Version 8, including Disaster Recovery
Explain the advantages of using the REBUILD option for the RESTORE command in DB2 9
List the types of information included in each DB2 backup image and describe how it is used to support rebuilding a database
Plan for supporting database and disaster recovery scenarios using DB2 database and table space backups using the RESTORE command with a REBUILD option
Utilize LIST UTILITIES SHOW DETAIL to monitor progress of RESTORE utility during database rebuilding
Self Tuning Memory Manager
Describe the basic DB2 UDB database memory model and how database shared memory was managed in Version 8
Explain how the Self Tuning Memory Manager (STMM) feature can tune the largest memory consumers for database shared memory including lock memory, buffer pools and sort memory
Describe the effect that setting DATABASE_MEMORY to AUTOMATIC, COMPUTED or a numeric value has on STMM memory tuning
Determine if STMM memory tuning might be effective with existing or planned DB2 DPF partitioned databases
Plan the configuration changes required to activate, deactivate STMM memory tuning
Describe the application workload characteristics where STMM would be expected to provide the most value or least potential benefits
XML Basics
Review the motivation for using XML and IBM's XML database strategy
Explain the overall integration between relational data and XML data
Review Service Oriented Architecture (SOA) concepts applied to DB2 and XML
Describe the DB2 pureXML datastore
Installation Enhancements
Plan a new installation of DB2 and migration of existing databases
List hardware prerequisites for a variety of platforms
List software prerequisites
Review component and name changes in DB2 9
Discuss migration possibilities
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.
Large Row ID Support
Describe the limits on table size and rows per page based on the Row Identifiers (RIDs) in DB2 UDB Version 8.
Explain the database design issues associated with the 4-byte RIDs used prior to DB2 9.1.
Describe the new limits for table size and rows per page based on the new 6-byte RIDs in DB2 9.1.
Plan the migration of existing DMS based table to large RIDs using the ALTER TABLESPACE CONVERT TO LARGE option.
Describe the options for enabling large RIDs for existing tables in DMS table spaces after converting the table space to a large table space.
Investigate which tables would benefit from the implementation of large RIDs.
Statistical Views
Describe the steps and processing associated with the SQL compiler analysis and selection of access plans.
Explain how the SQL Compiler uses statistical information about tables and indexes to estimate the cardinality of query results.
Describe the types of queries that standard cardinality estimation, based on table and index statistics, might not be accurate.
Use the RUNSTATS utility to collect the catalog statistics for statistical views.
Plan the use of Statistical Views to provide additional detailed statistics for improving cardinality estimates during query compilation.
Utilize Explain reports to investigate and diagnose problems with cardinality estimates and implement statistical views.
Data Row Compression
Describe the types of compression available in DB2 for Linux, UNIX and Windows databases
Explain how the REORG utility is used to build the compression dictionary and compress the data in a table
Plan the implementation of data row compression for tables
Utilize the ROWCOMPESTIMATE option of INSPECT to estimate the compression ratio for existing tables
Describe the steps required to utilize row compression with range partitioned tables
Query the DB2 catalog statistics to review the results of row compression for tables
Agenda
Day 1
Welcome
This is a version-specific reference outline. Confirm the current syllabus and delivery arrangements with EIEIO.