DB2 UDB Version 8 for z/OS SQL and Applications
 

This course:

This course is an in-depth look at the many new and enhanced SQL and Application features of DB2 UDB for z/OS Version 8.  IBM calls DB2 Universal Database (UDB) for z/OS Version 8, "The biggest release in DB2 history and represents a major reengineering of the product. Over half of the development time in V8 was spent on SQL." In this course you are introduced to these new features of version 8 and we also provide you with details on how to utilize the features through examples. Attend this course to obtain the knowledge required to select and implement the new features that best fit your environment.  The course covers new and changed functionality in Version 8 for z/OS.

Topics Covered:

• DB2 UDB for z/OS Version 8 Overview
• V8 SQL Application Enhancements
• Application Enhancements in V8

Duration: 3-Day Lecture with Hands-on Exercises
                     or 2-Day Lecture Only Class

Delivery: Class lecture

Audience: 

All personnel who require updated information pertaining to DB2 UDB for z/OS Version 8.

Prerequisites: Working knowledge of DB2 version 7

Outline:

1. Version 8 Enhancements Overview What's New?

     DB2 V8 Packaging
     Review of Unicode
     How this impacts SQL

2. Application & SQL Enhancements

     New SQL Limits
     Common Table Expressions
     Recursive SQL
     Scalar Full Select
         Restrictions
     Character Based String Functions
     Extensions to Expressions
     Multi-Row Insert, Update, Fetch
         DDL examples
         SQL examples
         COBOL examples
         Insert within Select
         Dynamic Scrollable Cursors
         Cursor Comparisons
         Multi-row Fetch with Scrollable Cursors
         Comparison to Static Scrollable Cursors
         Examples of Each Cursor type
         Locking implications
         Use of Scrollable cursors & Built-In Functions
     Using GROUP BY Expressions
     Multiple DISTINCT
     IS NOT DISTINCT FROM
     REOPT ONCE
     GET DIAGNOSTICS
     Enhanced Unicode Support
     STAR JOIN Enhancements
     IN - List Improvements
     Type 2 & 4 Java, DB2 Universal Drivers
     Dynamic Statement Cache
     DROP - Declared Temp Tables
     Materialized Query Tables (MQT)
         How they can help
         How are they supported
         Refreshing MQT's
         System verses User Types
         Special registers for Rewrite
         Static verses Dynamic
         How the Optimizer uses MQT's
         Informational RI
         Statistics with MQT's
         Support in Catalog
     PLAN_TABLE Changes
     Visual Explain New Features
     Query Performance Enhancements
         Avoiding SORTS
         Predicates
         New Stage 1 & Indexable Predicates
         Mismatched String Types
     EXPLAIN Global Prepare Cache
     SEQUENCE
         How they compare to Identity Columns
         Associated parameters
         Examples of using Sequence
     Volatile Tables
     Transparent Rowid
     SET CURRENT PACKAGE PATH
     SET ENCRYPTION PASSWORD
     SET SCHEMA
     Session Variables
     New Built-In Functions
     New SQL Codes and Warnings
     New Reserved Words
     Summary

3. Application Programming Functions & Changes in V8

     Program Prep in V8
         EBCDIC vs. UNICODE
     Using GET DIAGNOSTICS in Applications
     Stored Procedures Changes
     Enhanced management of Constraints
     Improved Sort Management
     Classification of Indexes
     Comprehensive Statistics History
     Distribution Statistics
     DDL & Utility Changes to Know
         Rebalancing Partitions
         RUNSTATS
         DPSI Support
     Summary