What you will learn
 

At the end of this course, students will be able to:

  • Create instances, databases and relational objects to support application development and user data access.
  • Use IMPORT, EXPORT and LOAD utilities to manipulate data.
  • Perform basic and advanced database and tablespace recovery.
  • Understand the basic security features of DB2.
  • Understand and control the locking behaviour of DB2.
  • Know methods of supporting application development.
  • Use advanced features for REORG, RUNSTATS and LOAD utilities.
  • Know performance enhancing techniques for utilities.
  • Use EXPLAIN tools and techniques.
  • Review Multi-Dimensional Clustering (MDC).
  • Alter the configuration of DB2 to enable automatic maintenance of DB2 tables.
Audience
  Database administrators
 
Prerequisites
 
  • Relevant Operating Systems experience (Linux, UNIX or Windows).
  • Knowledge of relational database theory and SQL.
  • Previous experience of using DB2 is recommended.
 
Duration
  Five days.

Outline for DB2 Administration for Linux, Unix and Windows

1. The DB2 Family

  • The DB2 family of products
  • DB2 communications options

2. Getting started with DB2 (INSTANCES)

  • The DB2 hierarchy of objects
  • TCP/IP Connectivity for DB2
  • Instance creation and configuration
  • CLP (command line processor) coding options
  • The DB2 Administration Server

3. DB2 and GUI

  • Administration (GUI) tools
  • Configuring a DB2 Client to access remote instances and databases
  • using the Command Editor to run queries or commands
  • Using the Control Center to create a database object

4. Creating databases and tablespaces

  • Know how to create a database
  • Create SMS and DMS tablespaces
  • List tablespace and container information for a database
  • Alter a tablespace and change container sizes
  • List and change the database and instance configuration files

5. Creating Tables and other objects

  • Creating tables and indexes
  • Using data types and nullity attributes for columns
  • Defining Referential and table Check constraints
  • Creating views and nested table expressions

6. TRIGGERS, UDT and UDF

  • when and how to use DB2 triggers
  • create and code triggers
  • when and how to use UDT’s
  • Define sourced and external UDF
  • Define table functions

7. DB2 LOCKING

  • How DB2 controls concurrency and integrity for multiple users
  • Know the factors effecting data locking
  • use commands to effect the behavior of DB2 locking
  • use monitors to analyze user locking activity
  • database configuration parameters that effect locking
  • How to GRANT privileges
  • Know of administration Authorities in DB2
  • How to create tables and other objects for use of groups

8. DB2 Data Management

  • Identify logging techniques that DB2 uses
  • basic features of some DB2 Utilities, that help manage data
  • when to use these utilities

9. IMPORT/EXPORT/LOAD Utilities

  • when and how to use  IMPORT, EXPORT and LOAD
  • Choose options to improve performance
  • the effects of using LOAD and how to bring a table to normal status
  • Compare LOAD and IMPORT

10. Basic Database Recovery

  • when and how to use BACKUP and RECOVERY operations.
  • Choose a database configuration for “full recovery” after failure.
  • ROLLFORWARD through the transaction logs to reapply changes to data.
  • Check the status of a tablespace.
  • Accomplish point-in-time recovery of a tablespace

11. Disaster Recovery for tables and tablespaces

  • Complete a redirected restore, to replace lost or changed containers for tablespaces.
  • Recover from dropped table

12. Incremental backup and recovery techniques

  • why and how to choose an incremental backup activity.
  • Determine options for database and tablespace recovery, when incremental backup has been used.
  • Setup for a database for incremental backup activity

13. Logical Design and Normalization

  • Logical design through normalization
  • Denormalization
  • Techniques for de-normalized table access

14. Index Design and the Design Advisor

  • Index design choices
  • Using the design advisor

15. Multi-Dimensional Clustering (MDC)

  • the terminology of multi-dimensional data.
  • Know why and how to create a table that is clustered in multiple dimensions.
  • Create generated columns to be used for dimension columns.
  • Understand the concept of monotonicity associated with generated dimension columns.

16. Advanced table options

  • Using table range partitions
    • table row compression
  • advanced options for REORG, RUNSTATS utilities
  • Supporting identity columns in utilities

17. Configuring for self tuning of DB2

  • automatic maintenance with utilities
  • Self tuning memory
  • database global memory
  • automatic tuning