> > CL240G Detailed outline

DB2 9 for LUW Multiple Partition DBA Workshop (CL240G)

Course Description Schedule Course Outline

Detailed Course Outline

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