DB2 9 Database Administration Workshop for Linux, UNIX, and Windows

Overview of DB2 9 on Linux, UNIX and Windows

• Contrast the DB2 Family of products
• Identify the DB2 Products
• Describe the functions of DB2 components
• Explore installation and parameters

Command Line Processor (CLP) and GUI usage

• Use the Command Line Processor
• Explore the GUI environment
• Describe the DAS role with GUI tools

The DB2 environment

• Specify the key features of an Instance
• Create and drop an Instance
• Use db2start and db2stop
• Distinguish between types of configuration
• Describe and modify the Database Manager Configuration

Creating databases and data placement

• Review specifics of creating a database
• Explore the System Catalog tables and views
• Compare DMS versus SMS table spaces
• Describe how to setup and manage a DB2 database with Automatic Storage enabled
• Differentiate between table spaces, containers, extents, and pages
• Define table spaces
• Use the get snapshot for tablespaces command to display table space statistics
• Explore Database configuration parameters

Creating database objects

• List DB2 object hierarchy and physical directories and files
• Create the following objects: Schema, Table, View, Alias, Index
• Explore the use of table partitioning
• Review the use of Temporary Tables
• Explore the use and implementation of Check Constraints, Referential Integrity and Triggers
• Exploring the need for and the use of Large Objects
• Recognize XML and its native store as critical infrastructure for emerging technologies

Moving data

• Discuss the INSERT statement and recognize its limitations
• Explain the differences between IMPORT and LOAD
• Explain the EXPORT, IMPORT, and LOAD syntax
• Create and use Exception Tables and Dump-Files
• Distinguish and resolve Table States: Load Pending and Set Integrity Pending
• Use the SET INTEGRITY command
• Discuss the db2move and db2look commands

Backup and recovery

• Describe the major principles and methods for backup and recovery
• State the three types of recovery used by DB2
• Explain the importance of logging for backup and recovery
• Describe how data logging takes place, including circular logging and archival logging
• Use the BACKUP, RESTORE, and ROLLFORWARD commands
• Perform a table space backup and recovery
• Restore a database to the end of logs or to a point-in-time
• Discuss the configuration parameters and the recovery history file and use these to handle various backup and recovery scenarios

Locking and concurrency

• Explain why locking is needed
• List objects that can be locked
• Describe and discuss the various lock modes and their compatibility
• Explain four different levels of data protection
• Set isolation level and lock time out for current activity
• Explain lock conversion and escalation
• Describe the situation that causes deadlocks

Problem determination

• Collect information for problem analysis and resolution
• Use error logs for basic problem analysis
• Describe four types of monitors: Snapshot Monitor, Event Monitor, Activity Monitor, and Health Monitor
• Describe the function of EXPLAIN and use this facility to assist basic analysis
• Use a series of basic commands to better work with connections and sessions
• Retrieve statistics and other information from a running DB2 instance
• Use RUNSTATS, REORGCHK, and REORG to resolve application performance problems

Security

• Use DB2 access control mechanisms to implement security within the database
• Use group IDs to create a control hierarchy
• Describe Label Based Access Control (LBAC)
• 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

Agenda
Day 1

• Welcome
• Unit 1 - Overview of DB2 on Linux, UNIX and Windows
• Lab 1 (Starting your lab environment)
• Unit 2 - Command Line Processor (CLP) and GUI usage
• Lab 2 (DB2 CLP usage)
• Unit 3 - The DB2 environment
• Lab 3 (Create an instance and explore the environment)

Day 2

• Unit 4 - Creating databases and data placement
• Lab 4 (Creating databases and data placement)
• Unit 5 - Creating database objects
• Lab 5 (Create objects)
• Unit 6 - Moving data
• Lab 6 (Moving data)

Day 3

• Unit 7 - Backup and recovery
• Lab 7 (Backup and recovery)
• Unit 8 - Locking and concurrency
• Lab 8 (Investigating DB2 locking)

Day 4

• Unit 9 - Problem determination
• Lab 9 (Problem determination)
• Unit 10 - Security
• Lab 10 (Security)