This course is not scheduled. Inquire about Onsite training at your facility.
Overview
| Course code | CL310 | Skill level | Intermediate |
|---|---|---|---|
| Duration | 4.0 days | Delivery type | Classroom |
| Course type | Public or Private on-site | ||
| Public price | USD $2,600.00 plus tax | ||
This course is designed to provide you with technical information about the functions and features in two DB2 product updates, DB2 9.1 and DB2 9.5 for Linux, UNIX, and Windows as well as information on some product changes that became available in DB2 Version 8.2.2. The information is presented in two parts. First, the information on the DB2 9.1 features like Table Partitioning, XML data support, Self-tuning Memory Management and data row compression will be presented. The planning and implementation considerations for the DB2 9.5 functions will be presented in part two, which will include database roles for security, real-time statistics collection and workload management capabilities. This course is the classroom delivered version of Instructor Led on Line course (3L310).
View this course in other countries
Training Paths that reference this course are:
Audience
This intermediate course is for database administrators, application designers, technical support, and technical specialists who require information about the features and functions of DB2 9.5 for Linux, UNIX, and Windows.
Skills taught
After completing this course, you should be able to:
- Describe the new functions and features of DB2 91 and 9.5 for Linux, UNIX, and Windows
- Discuss the advantages and potential impact of these two releases of DB2 to your environment
- Plan the installation and migration to DB2 9.1 or DB2 9.5 for Linux, UNIX, and Windows
- Describe the new functions and options for administration of DB2 databases provided by DB2 9.1 and DB2 9.5
- Assess the applicability of the DB2 9.1 and DB2 9.5 features and functions in your application environment to maximize the performance, recoverability, security and operational management
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
DB2 9.5 Performance-related Considerations
- Describe the thread based process model implemented in DB2 9.5 and the impact on database and instance configuration options
- Select appropriate settings for INSTANCE_MEMORY, DATABASE_MEMORY and APPL_MEMORY using the DB2 9.5 memory architecture
- Plan the implementation of real time statistics collection
- Utilize the ROW CHANGE expression and RID_BIT function to support applications using optimistic locking
- Describe the advantages of using deferred index cleanup for rollout operations on MDC tables
- List some of the Risky access strategies that may be avoided by the DB2 9.5 optimizer
- Describe the changes in DB2 9.5 to enable parallel processing for creating new indexes to improve performance
DB2 9.5 Security and Audit Enhancements
- Describe the use of database roles to simplify database security
- List the security administration tasks for DB2 databases that require the SECADM database authority, including managing roles, creating trusted contexts and performing database level audits
- Explain the differences between using database roles and group-based privileges
- Plan the implementation of a trusted context to improve database security for a three-tiered application system
- Explain the differences between an explicit trusted connection and an implicit trusted connection
- 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
Application Development Enhancements and Tools
- Describe the support for array data types in Version 9.5.
- Explain the benefits of using the new DECFLOAT data type provided by DB2 9.5.
- Use the CREATE VARIABLE statement to define a Global Variable that enables sharing data between different SQL statements running in the same session (or connection) without the need for application logic to support this data transfer.
- List some of the key features provided by IBM Design Studio for supporting database application development.
DB2 9.5 Installation and Migration Considerations
- Identify the new names for DB2 components used for DB2 9.5
- Effectively plan the installation of DB2 9.5 server and client software
- Plan the migration of DB2 instances and Databases from DB2 Version 8 or DB2 9.1 to utilize DB2 9.5
- List the new database manager and database configuration options supported by DB2 9.5
- Describe the key changes in DB2 instance and database configuration in DB2 9.5
- Plan for installation and maintenance of a DB2 instance using a non-root user on a Linux or UNIX system
DB2 9.5 XML Support Enhancements
- Explain how the XQuery updating expressions enable you to modify portions of an existing XML document instead of having to construct a new one
- Utilize a LOAD utility to load tables with XML columns
- Add small XML columns to a table or change existing XML columns and store these documents in a row of the base table instead of in the default XML storage object
- Describe the functions, XMLRow, XMLGroup and XSLTransform, provided for XML data in DB2 9.5
- List some of the support enhancements for XML data including check constraints, triggers and replication support
Please see Remarks section for the continuation...
Remarks
DB2 9.5 High Availability and Database Recovery Features
- Describe using the integrated Cluster Management options in DB2 9.5 to simplify implementation and administration when DB2 databases with Cluster Managers
- Utilize the DB2 high availability instance configuration utility db2haicu to configure and administer your database solutions in clustered environments
- Describe selecting a HADR peer window to reduce the risk of data loss during cascading or multiple failures
- Plan and implement DB2 Advanced Copy Services to provide high performance database backup and restore facilities
- Configure the DB2 database option auto_del_rec_obj to automate removal of database objects
- Utilize the Single System View enhancements in DB2 9.5 to simplify backup and recovery of DB2 multiple partition DPF databases
DB2 9.5 Problem Determination and Database Resilience Enhancements
- List several ways to access the DB2 messages that are written to the administration notification log, including a SQL query with the PD_GET_LOG_MSGS table function.
- Utilize the PD_GET_DIAG_HIST table function to query DB2 messages recorded in several different message and error logs based on the level of impact.
- Describe the First Occurrence Data Capture (FODC) facilities implemented in DB2 9.5.
- Use the db2fodc command to manually capture diagnostic information for hang condition on a DB2 server.
- Configure the DB2_CAPTURE_LOCKTIMEOUT registry variable to collect diagnostic information for lock timeouts.
- Implement the storage protection features of DB2 9.5 using the DB2_MEMORY_PROTECT and DB2_THREAD_SUSPENSION registry variables.
DB2 9.5 Partitioned Database (DPF) Enhancements
- Describe the changes in DB2 9.5 for updating the configuration of partitioned databases using the UPDATE DATABASE CFG command.
- Plan and implement the enhanced REDISTRIBUTE command options provided by DB2 9.5 to efficiently redistribute data to new database partitions or remove database partitions.
- Describe the changes to REDISTRIBUTE processing to reduce the need to reorganize tables after data redistribution.
- Explain the impact on database recoverability when using the enhanced REDISTRIBUTE command options.
- Utilize the STOP AT and TABLE options of REDISTRIBUTE to better manage large data redistribution operations for partitioned DB2 databases.
Introduction to DB2 Workload Management
- Explain the need for DB2 workload management
- Describe DB2 workload management
- List the components of DB2 workload management
- Define DB2 service classes
- Explain how to use DB2 WLM workloads to group connections
- Describe how thresholds are used with DB2 WLM
- Explain how work actions are used to map work classes
- Describe the monitoring capabilities of DB2 WLM
- Explain how Query Patroller works in conjunction with DB2 WLM
Agenda
Part 1 DB2 9.1 Features and Functions
Day 1
- Welcome
- Unit 1 - Database and Application Enhancements
- Unit 2 - Database Rebuild Support
- Unit 3 - Self Tuning Memory Manager
- Unit 4 - XML Basics
- Unit 5 - Installation Enhancements
Day 2
- Unit 6 - Table Partitioning
- Unit 7 - Large Row ID Support
- Unit 8 - Statistical Views
- Unit 9 - Data Row Compression
- Part 2 DB2 9.5 Features and Functions
Day 3
- Unit 10: DB2 9.5 Performance-related Considerations
- Unit 11: DB2 9.5 Security and Audit Enhancements
- Unit 12: Application Development Enhancements and Tools
- Unit 13: DB2 9.5 Installation and Migration Considerations
- Unit 14: DB2 9.5 XML Support Enhancements
Day 4
- Unit 15: DB2 9.5 High Availability and Database Recovery Features
- Unit 16: DB2 9.5 Problem Determination and Database Resilience Enhancements
- Unit 17: DB2 9.5 Partitioned Database (DPF) Enhancements
- Unit 18: Introduction to DB2 Workload Management
My IBM
We're here to help
Easy ways to get the answers you need.
or call us at
Call 1-800-426-8322
Open M-F 9AM-7PM ET.
