DB2 for Operations Support course outline

DB2 for Operations Support

Duration: 2 days

Audience: Operations Support staff who will be working with DB2

Prequisites: 6 months experience of operations support

Objectives: Top Contents

On successful completion of this course each student will be able to:

* distinguish between relational and hierarchical access to data
* describe how a DB2 database is held on physical storage
* list the tasks involved in implementing a DB2 application
* explain the meaning of terminology such as table and view
* state how data is arranged in a DB2 table
* describe how a DB2 program is prepared for execution
* describe the security facilities of DB2
* state the main purpose of SPUFI and SQL
* describe the startup and shutdown procedures
* use the utilities to load and reorganise tables
* list the different recovery procedures available in a DB2 system

Contents: Top Objectives

Introduction
Comparison of DB2 tables with VSAM files and hierarchical databases (e.g. IMS, TOTAL); DB2 and the Relational Model; tasks and personnel involved in implementing a DB2 system; DML, DCL, and DDL; sample uses of DB2; DB2 address spaces, attachments to: CICS, TSO, IMS, batch; stored procedures; trailer site, online reorganisation. Web Enabled DB2, DB2 Connect.

Tables and DB2 architecture
How data is arranged; storage groups, databases, tablespaces, indexes, tables, views; basic table design, keys, table splitting; referential integrity, foreign keys, constraints, triggers; functions of the catalog tables; typical disk requirements; physical storage considerations.

SQL overview
SELECT statement, predicates, JOIN and UNION queries; scalar functions; labeled durations;column functions: BETWEEN, NULL, LIKE, EXISTS, IN; GROUP BY; UPDATE, DELETE, INSERT; CASE expressions; special registers; subquery with IN; subquery with NOT IN; correlated subselect; EXISTS, ANY, ALL; inner and outer joins; dynamic SQL; security: GRANT and REVOKE.

DB2 commands
How to enter commands; starting and stopping DB2; starting and stopping a database; displaying and cancelling threads; DISPLAY UTILITY, TERM UTILITY; traces; commands for log, bufferpools, procedures.

DB2 utilities
Invoking utilities: CHECK INDEX, CHECK DATA, IMAGECOPY, LOAD, UNLOAD, MERGECOPY, MODIFY, QUIESCE, RECOVER, REORG, REPAIR, REPORT, RUNSTATS, STOSPACE.

Logging
Active and archive log datasets; role of the bootstrap dataset; log manager, log buffers, log write; CICS and DB2 logging, two-phase commit.

Locking and integrity
Overview of locking; COMMIT, ROLLBACK. Lock types: transaction, page level, row level, table level, tablespace level; duration of locks; bind options; claim and drain locks.