Canada's Leader in Professional Development

855-581-7246 Call us: 416-368-7246 ext 2

DB2 9 for LUW Multiple Partition DBA Workshop

Currently no upcoming Class Dates


This course replaces course CF242. This is the classroom version of the Instructor-led online course DB2 9 for LUW Multiple Partition DBA Workshop Instructor-led online (3L240)

This course teaches you how to perform database administration tasks using IBM DB2 Enterprise 9 with the Database Partitioning Feature (DPF). These tasks include customization for the DB2 Enterprise 9 with DPF system, creating and populating partitioned databases, designing a database for parallel use, and using partitioned DB2 utilities. There is no actual installation of DB2 Enterprise software in this workshop. A DB2 for Linux system is used to exercise various administrative functions.

Course Materials

The course materials address DB2 9.7 for Linux, UNIX, and Windows.

Hands-On Labs

Fourteen labs are included to address DB2 9.7 for Linux, UNIX, and Windows.


Prepare for IBM Certification Test 731: DB2 9 for Linux, UNIX, and Windows Database Administration.


  • Describe the steps to install and customize DB2 in a partitioned environment
  • Use GUI and Command Line tools to administer the database
  • Create objects and load data into DB2 databases
  • Define a DB2 partitioned recovery strategy and perform the tasks necessary to support the strategy
  • Describe the application development process with respect to DB2 considerations
  • Identify how a database should be designed to take advantage of the parallel architecture
  • Use DB2 utilities to manage data and maintain your database
  • Implement DB2 security

Note: Actual installation of DB2 will not be performed in this workshop. A pre-installed system is used to exercise various administrative functions.


This intermediate course is for:

  • Database Administrators, Application Developers, and Consultants who are involved in planning, implementing, or maintaining DB2 DPF partitioned databases.
  • This course is also appropriate for customers who have acquired the IBM Smart Analytics System.


You should be able to:

  • Use basic Linux/UNIX functions such as utilities, file permissions, hierarchical file system, commands, and the vi editor
  • State the functions of the Structured Query Language (SQL) and be able to construct DDL, DML, and authorization statements
  • Discuss basic relational concepts and objects such as tables, indexes, views, and joins
  • These skills can be developed by taking:
  • Linux/UNIX basics (for example, AIX Basics)
  • DB2 SQL Workshop
  • DB2 Family Fundamentals

You will be using a Linux environment in a VMware image for the lab exercises.

It will be helpful for students running DB2 on other operating systems and platforms - such as DB2 on an AIX - to have experience with using and administering their operating system environment.


5 days


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


  • 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


  • 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


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

Click here to reach us by Email Contact Us
About | Terms of Use | Privacy Visit our Facebook page   Visit ot Linkedin page   View our Tweets