课程大纲

课程简介

Audience

    This intermediate course is for System administrators, database administrators, and technical personnel involved in planning, implementing, and maintaining DB2 databases.

This course is appropriate for students using DB2 in a z/Linux environment.

Prerequisites

You should be able to:

    Use basic OS functions such as utilities, file permissions, hierarchical file system, commands, and editor

    State the functions of the Structured Query Language (SQL), and be able to construct DDL, DML, and authorization statements

    Discuss basic relational database concepts and objects such as tables, indexes, views, and joins

    These skills can be developed by taking:

    Basic OS functions:

    Windows Operating System Training

    Linux Operating System Training

    AIX Operating System Training

    or have equivalent administration experience

    SQL and relational database basics

    DB2 SQL Workshop (CE12), (3E12)

    or have equivalent knowledge of SQL basics

Skills taught

    Administer a DB2 database system using commands and GUI tools

    Manage System Managed Storage (SMS) and Database Managed Storage (DMS) databases and apply data placement principles

    Implement a given logical database design using DB2 to support integrity and concurrency requirements

    List and describe the components of DB2

    Define a DB2 recovery strategy and perform the tasks necessary to support the strategy

    Use autonomic features of DB2

    Implement DB2 security

Course outline

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