Objectives

On successful completion of this course, attendees will be able to:

• normalise data and know when to denormalise
• understand how and when to implement constraints
• design and implement physical databases covering: storage groups, databases, table spaces, tables, indexes, aliases, synonyms, etc.
• design and implement efficient indexes
• design efficient SQL
• understand the differences in design for online and batch tasks
• assess security and related requirements
• be aware of the implication of locking, commit frequency, etc., on concurrency
• use EXPLAIN to evaluate design performance.

Who Should Attend

IT developers and designers, DB2 database administrators and application programmers.

Prerequisites

Attendees should have a basic knowledge of DB2 concepts (as provided by the one-day RSM course DB2 for z/OS - Features & Facilities).

Duration

4 days

Fee

£1625 (ex. VAT)

Course Code

DADD

Contents

DB2 9 for z/OS enhancements overview

New data types; NOT LOGGED tablespaces; Index enhancements; Cloned tables; Universal tablespaces; etc. This segment introduces many of the enhancements introduced with DB2 9

On-line schema enhancements

Changing data types and lengths; amending indexes to have true VARCHAR columns; separating partitioning and clustering. This segment introduces the enhancements introduced to assist with on-line schema management.

Generic utility tasks

Using LISTDEF to build reusable object lists; using TEMPLATE to generate data set names and attributes and simplify JCL; the use of the OPTIONS statement to control execution parameters. This segment examines the use of LISTDEF, TEMPLATE and OPTIONS statements in setting up and controlling utility jobs.

Utility performance and availability

LOAD enhancements; REBUILD INDEX enhancements; REORG enhancements; UNLOAD enhancements. This segment describes the major enhancements in the utilities mentioned.

Enhanced partition management

Index defined vs table defined partitioning; adding new partitions; rotating partitions; rebalancing partitions. This segment describes the enhancements available to assist with partition management.

UDTs, UDFs and stored procedures

Need and definition of schema; need for, definition of and use of user defined distinct types (UDTs); need for, definition of and use of user defined functions (UDFs); need for, definition of and use of stored procedures; UDT, UDF and stored procedure security. This segment examines the role of UDTs, UDFs and stored procedures, their definition and control.

Large object and XML data types

Large object data; relationship between base and auxiliary tables; large object locking considerations; large object logging and recovery considerations; XML data support; XML data model; XML data storage; Indexing XML data; XML data backup & recovery. This segment covers the use, definition, and recovery of large object and XML data types.

Program preparation

Program preparation steps; relevance of packages and collections; package versioning; accessing different tables at execution time; common problems. This segment examines program preparation and the options available to provide flexibility within applications.

Hands-on exercises

Hands-on exercises complement the course material to assist in full understanding of the subject matter.