Overview
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.
Certification
Prepare for IBM Certification Test 731: DB2 9 for Linux, UNIX, and Windows Database Administration.
Public
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.
Prerequisits
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.
Objective
- 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.
Topics
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
