DB2 9 for LUW Multiple Partition Environment for Single Partition DBAs (SPVC)

DB2 DPF Partitioned Database Components and Concepts

• Identify the kinds of business applications that use parallel databases
• Describe the basic architecture of each of the hardware platforms on which parallel databases may run
• Define the strengths of the DB2 DPF partitioned architecture
• Identify the key features that set DB2 in a DPF partitioned environment apart from other members of the DB2 family

Getting Started with DB2

• Describe the purpose of DAS

Installation

• Identify the steps to install and customize DB2 in a partitioned environment
• Use db2_all and rah commands

Creating a Partitioned DB2 Database

• Describe DB2 database partitioning
• Create a database in a DB2 partitioned environment with or without Automatic Storage enabled
• List the three types of Storage Management for table spaces
• Describe the three default system table spaces
• Access and update the database manager configuration file and the database configuration files
• Access and update the system database directory and list the local database directory
• Use tools to issue commands and SQL statements

Partitioning and Database Partition Groups

• Identify considerations when choosing a distribution key
• Describe join strategies in partitioned databases
• Identify the syntax for creating database partition groups

Data Placement on the Partitions

• List the advantages and disadvantages for each type of table space management
• Create SMS table spaces
• Create DMS tablespaces
• Create Automatic Storage managed table spaces
• Use the GET SNAPSHOT commands and db2pd commands to check table space status
• Use SQL functions specific to the partitioned environment
• Identify catalog views that contain information about your partitioned environment

Moving Data

• Utilize the EXPORT Utility to extract data from a DB2 database
• Identify the different methods for inputting data, including the Import Utility, using buffered and unbuffered SQL INSERT and the LOAD Utility
• Describe the process of partitioning and loading data
• Identify how the partitioned database options on the LOAD command can be used to control the data partitioning and load processing

Partitioned Database Backup and Recovery

• Describe the three types of recovery support provided by DB2
• Explain the principles DB2 uses for its recovery/restart functions
• Describe the configuration options for DB2 logging and explain the differences between circular and archive logs
• Use the BACKUP, RESTORE and ROLLFORWARD commands to back up and recover a DPF partitioned database
• Recover the database to a prior point in time using the RECOVER DATABASE command
• State general considerations regarding disaster recovery and implementation of a server cluster for high availability

Scaling the Database

• Identify how the partitioned database configuration can be scaled by adding new database partitions
• Differentiate between the three options on the REDISTRIBUTE command
• Utilize the REDISTRIBUTE command to add or remove database partitions from a database partition group
• Troubleshoot problems that may occur during redistribution

Database and Application Performance

• Describe the steps used by the DB2 Optimizer to generate access plans
• List the major influences for SQL optimization, including Catalog statistics, database memory configuration, optimization class selection and database partition groups
• Utilize the RUNSTATS utility to collect detailed table and index statistics
• Explain the major goals for table and index reorganization
• Use the REORGCHK report to plan effective use of the REORG utility
• Describe the use of the DB2 explain tools to analyze access strategies for SQL statements

Monitoring and Problem Determination

• Use error logs
• Identify the different monitor types
• Identify how to use the independent trace facility (db2trc)
• Use the dp2pd problem determination tool to obtain statistics from a running instance
• Identify additional commands for listing application information

Locks and Concurrency

• List objects that may be locked by the database manager
• Discuss available lock modes and their compatibility
• Influence locking strategies used by the database manager

Agenda

• DB2 DPF Partitioned Database Components and Concepts
• Getting Started with DB2
• Installation
• Creating a Partitioned Database
• Partitioning and Database Partition Groups
• Data Placement on the Partitions
• Moving Data
• Partitioned Database Backup and Recovery
• Scaling the Database
• Database and Application Performance
• Monitoring and Problem Determination
• Locks and Concurrency