DB2 9 for LUW Multiple Partition DBA Workshop - Self Paced Virtual Class

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
• List the GUI tools in DB2
• Explore the various ways to access the GUI tools
• Use different methods to execute a command or statement in DB2
• Explore the use of the Command Window and the Command Line Processor (CLP)
• Create database objects using both the GUI tools and the CLP

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 table spaces
• 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

Create Objects

• Create database objects: tables, indexes, views, aliases
• Alter tables and add check constraints, triggers, and referential integrity
• Describe the special data types for large objects and XML data and when they might be needed
• Identity other approaches to organizing data including range partitioned tables and multi-dimensional clustering
• Implement row compression for tables

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 processing for 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

Application Alternatives

• Prepare applications that access DB2 data for execution
• Use PRECOMPILE and BIND options that are appropriate for specific application requirements
• Describe the application alternatives available to access DB2 data or request other DB2 functions
• List the benefits, and possible disadvantages, of the various interfaces

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

Security

• Identify how authentication is done in DB2 UDB
• Use DB2 access control mechanisms to implement security within the database
• Use group IDs to create a control hierarchy
• Describe privileges within a database
• Describe privileges required for binding and executing a package
• Describe the difference between explicit privileges and implicit privileges
• Describe the different DB2 authorization levels
• List the new security features in DB2 V8 and V9

Monitoring and Problem Determination Tools

• 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

Day 1

• Welcome
• Unit 1 - Components and Concepts
• Starting your Lab Environment, and Paper Lab
• Unit 2 - Getting Started with DB2
• Unit 3 - Installation
• Installation Lab
• Unit 4 - Creating Databases

Day 2

• Creating Database Lab
• (Unit 5 -Partitioning and Database Partition Groups
• Partitioning Distribution Keys and Database Partition Groups Lab
• (Unit 6 - Data Placement on the Partition
• Creating Table Spaces Lab
• Unit 7 - Create Objects

Day 3

• Creating Objects Lab
• Unit 8 - Moving Data (Import and Load)
• Moving Data Lab - Import and Load
• Unit 8 Continued - Moving Data (Partitioned Load)
• Moving Data Lab - Partitioned Load
• Unit 9 - Application Alternatives
• Application Basics Lab

Day 4

• Unit 10 - Partitioned Database Backup and Recovery
• Backup and Recovery Lab
• Unit 11 - Scaling the Database
• Scaling Lab
• Unit 12 - Performance and Application Concepts
• Application Performance Tools Lab

Day 5

• Unit 13 - Security
• Security Lab
• Unit 14 - Monitoring and Problem Determination Tools
• Unit 15 - Locking and Concurrency
• Locking and Concurrency Lab