This course is not scheduled. Inquire about Onsite training at your facility.
Overview
| Course code | CG090 | Skill level | Intermediate |
|---|---|---|---|
| Duration | 3.0 days | Delivery type | Classroom |
| Course type | Public or Private on-site | ||
| Public price | USD $1,950.00 plus tax | ||
Get the technical information about the new functions and features in DB2 9 for Linux, UNIX, and Windows since V8.2.
Course Materials
The course material covers DB2 9 for Linux, UNIX, and Windows.
View this course in other countries
Training Paths that reference this course are:
Audience
This is an intermediate course for database administrators, application designers, technical support, and technical specialists who require information about the features and function of DB2 9 for Linux, UNIX, and Windows.
Skills taught
- Describe the new functions and features of DB2 9 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 (STMM)
- describe the basic DB2 UDB database memory model and how database shared memory was managed in Version 8
- explain how the 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 Database Partitioning Feature (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
Extensible Markup Language (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
Table partitioning
- describe the options for handling data roll-in and roll-out using DB2 Version 8.2 features, including DPF Database Partitioning, Multidimensional 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
System changes
- list discontinued, deprecated, and changed DB2 features
- explain how database monitoring and testing capabilities have improved
- discuss increased table size limits and table space capacity
- review backup and restore capabilities explore changes to the db2batch utility
- analyze automation changes, including automatic storage and administration
- discuss changes to Materialized Query Tables (MQT)
Large Row Identifiers (RID) support
- describe the limits on table size and rows per page based on the 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 Database Managed Storage (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
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
SQL changes
- analyze the new and changed SQL functions
- examine the new MQT improvements, such as explaining why an MQT may not have been used in a query
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
Basics of Developer Workbench
- explore Developer Workbench use for routine development support
- list the functionality of Developer Workbench, including XML support
- perform Developer Workbench startup, and connect to the SAMPLE database
- use the SQL Editor to create and execute an SQL statement
- review tools for developing Java (JDBC and SQL for Java (SQLJ)) C, C++, NET CLR routines
Label Based Access Control (LBAC)
- describe the facilities of LBAC that would be used to implement row level security or column level security
- assign a SECADM user authority that will be used to administer LBAC based security
- explain how a table level security can be simulated using LBAC column level security labels
- describe the various objects that must be created to implement LBAC based security including security label components, security policies and security labels
- plan the implementation of LBAC based security to achieve various security requirements.
- utilize the functions SECLABEL, SECLABEL_BY_NAME and SECLABEL_TO_CHAR in SQL statements to access an LBAC protected table
Agenda
Day 1
- Welcome
- Database and application enhancements
- Database rebuild support
- STMM
- XML basics
Day 2
- Table partitioning
- System changes
- Large RID support
- Installation enhancements
- SQL changes
Day 3
- Statistical views
- Data row compression
- Basics of Developer Workbench
- LBAC
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.
