Objectives

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

• describe the new features and facilities introduced with DB2 9
• identify the withdrawn features relevant to their own organisation
• identify the new features that would particularly benefit their own installation.

Who Should Attend

This course is designed for systems and database administrators, application developers and designers, and all others needing an introduction to the new release and what it offers.

Prerequisites

A working knowledge of DB2 facilities and features.

Duration

2 days

Fee

£795 (ex. VAT)

Course Code

DBV9

Contents

Summary of DB2 9 for z/OS Features

Introduction; New and changed features; Integration of XML and relational data; Online schema change evolution; SQL compatibility and consistency; Improved auditing and security; Improved performance; Utility and support tool enhancements; Features and functions no longer supported.

Integration of XML and Relational Data

XML support - DB2 UDB for z/OS Version 8; Enhanced XML support - DB2 UDB for z/OS Version 9; Comparison of the relational and XML data models; XML data model; XML data example; XML data storage structure; XML storage structure names; Inserting XML data; Updating XML data; Deleting XML data; Retrieving XML data; Indexing XML data; Application programming support; Database administration support; Limiting XML storage.

Online Schema Change Evolution

Online schema changes; Universal tablespace; Range-partitioned universal tablespace; Partition-by-growth universal tablespace; Table cloning (Version 8); Table cloning (DB2 9); Table cloning - restrictions; Renaming of columns and indexes; Restrictions on renaming; Impact of renaming; Support for SMS storage classes; Suppression of logging; Changing logging - effect on XML and LOB data.

SQL Compatibility & Consistency

Increased compatibility and consistency; New data types; Implicitly hidden columns; INSTEAD OF triggers; MERGE statement; TRUNCATE statement; SELECT FROM enhancements; INTERSECT and EXCEPT operations; INTERSECT example; EXCEPT example; Online Analytical Processing enhancements; ROW_NUMBER specification; RANK specification; DENSE_RANK specification; Update detection; Update detection considerations; Additional built-in functions; Fetching LOB data; FETCH enhancements for LOB and XML data; Referencing LOB data (DB2 Version 8); File reference variables for LOB data; File reference variables for XML data; INSERT enhancements; UPDATE enhancements; SQL procedures (DB2 Version 8); Compound SQL procedure statements; SQL procedure enhancements; Indexes on expressions.

Security & Auditing Enhancements

Original DB2 security; DB2 security tables; Using RACF for DB2 security; Trusted context support; Defining a trusted context; Roles; Secure Socket Layer (SSL) protocol support; Improved auditing.

Improved Performance

‘Not logged’ tablespaces; Increased indexspace page sizes; Index compression; Index key randomisation; Asymmetric index page splitting; Sparse index and in-memory data caching; Large object data retrieval performance enhancements; Large object locking performance enhancements; Optimistic locking; APPEND for data inserts; Optimisation enhancements - statistics; Optimisation enhancements - techniques; Optimization Service Center for DB2 for z/OS.

Utility & Support Tool Enhancements

CHECK DATA, CHECK LOB and CHECK INDEX enhancements; COPY enhancements; LOAD enhancements; LOAD example; REBUILD INDEX enhancements; RECOVER enhancements; REORG enhancements; REPORT enhancements; RUNSTATS enhancements; UNLOAD enhancements; UNLOAD example; Other utility enhancements; LISTDEF enhancements; TEMPLATE enhancements; BACKUP SYSTEM enhancements; RESTORE SYSTEM enhancements; Stand-alone utility enhancements; Support tool enhancements.

Features No Longer Supported

Simple tablespaces; DB2-managed stored procedures; DB2 Estimator; Net.Data; JDBC/SQLJ Driver for OS/390.

Migration Considerations

The Installation CLIST; Install Main Panel; Installation panels; CLIST Calculations panels; Migration paths; Migration steps; Migration considerations.