DB28 - DB2 z/OS Version 9, 10 & 11 New Features - 3 Days

Virtual or On-site course enquiry (from £165 pp per day)

Course Description

This course is aimed at Systems Programmers, Programmers and DBA's who are moving from DB2 Version 8 to DB2 Version 11 and therefore need to understand the new features and facilities of DB2 Versions 9, 10 & 11. This lecture based three-day course covers all of the new features in detail.

Pre-requisites

A strong working knowledge of DB2 Version 8 is required to attend this course.

The aim of this course is to provide the delegate, familiar with DB2 V8, with the necessary skills required to use the new Version 9, 10 & 11 features.

Environment

The lectures apply to DB2 running in a z/OS environment.

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

SYSTEM ENHANCEMENTS: Virtual Storage Management; Storage Enhancements; z/OS Security Server / RACF Support; WLM Enhancements; Upgrading from V8 to V10; Deprecated and Unsupported Features; Virtual Storage Relief; Log / Latch Contention Enhancement; Catalog Contention Enhancement; Increased Number of Packages; Workfile Database Enhancements; Support for Extended Address Volumes; Extended RBA and LRSN; Log RBA / LRSN Format; Conversion to New Log Format - Steps; Conversion to 10 Byte RBA / LRSN Format; 10 Byte RBA / LRSN Format - Considerations; Permitted Number of Open Datasets (DSMAX)
AVAILABILITY AND CAPACITY ENHANCEMENTS: Version 8 Recap - Types Of Tablespace; Version 8 Recap - Tablespace Definition; Version 9 - Universal Tablespaces; Universal Tablespace Benefits; Maximum Number Of Partitions; Partition-By-Growth Tablespace; Creating A Partition-By-Growth Tablespace; Partition-By-Growth Tablespaces - Behaviour; Partition-By-Growth - Partition Allocation; Partition-By-Growth Tablespaces - Insert; Partition-By-Growth Tablespaces - Reorg; Partition-By-Growth Tablespaces - Other Utilities; Partition-By-Range Tablespace; Partition-By-Range Tablespaces - Considerations; Universal Tablespaces - Catalog Changes; Implicit Object Creation; Automatic Database Creation; Implicit Database Creation - Considerations; Automatic Tablespace Creation; Default Bufferpool Usage; Other Implicit System Required Objects; SMS Storage Classes with Db2 Defined Datasets; Clone Tables - Fast Data Replacement; Why Use Clone Tables?; Creating A Clone Table; Dropping A Clone Table; Clone Table Considerations; Creating a Clone - Catalog Activity; Creating a Clone - Index and Lob Catalog Activity; Clone Tables - The Exchange Command; Clone Tables - Exchange Considerations; Clone Tables - Locking Considerations; Clone Tables - DB2 Commands; Clone Tables - Authority; Clone Tables - Utilities; Column Renaming; Column Renaming Restrictions; Alter Long Varchar to Varchar; Refresh EARLY Code Without an IPL; Conditional Restart Log Truncation by Timestamp; Improved Insert Performance - The Append Parameter; Index Renaming; Catmaint Enhancement - Update Schema; Update Schema Restrictions; V10 Universal Tablespace Enhancements; Alter Tablespace Enhancements; Alter Tablespace Enhancements - Considerations; Pending Changes; Pending Changes - Areor State; Pending Changes - Reorg Materialisation; Pending Changes - Reorg Partition Resizing; Alter Tablespace Examples; Alter Tablespace - Member Cluster; Online Schema Changes - Recovery; Partition Rotation Enhancement; Version 10 Partition Rotation Enhancements; Rotating Partitions - Considerations; Default Tablespace; Autonomic Checkpoint; Dynamically Adding Active Log Datasets; Data Compression Enhancements; Version 10 - Compress on Insert / Merge / Load; Compression Considerations; Pending Changes Recap - Version 10; Recovery of Objects after Pending Changes Applied; Pending Changes - Recovery Example; Pending Changes - Running Modify Recovery; Pending Changes - Recovery Considerations; Restrictions between PIT Recovery and Reorg; Partition Limit Key Enhancement; Partition Limit Key Example; Partition Limit Key Alteration Considerations; Workfile Database Enhancements; The Wfstguse_Agent_Threshold Parameter; The Wfstguse_System_Threshold Parameter; Compression Dictionary - Replication Enhancement; Drop Column Support; Drop Column - Reorg Considerations; Drop Column - Recover Considerations; Drop Column - Restrictions; Defer Define Object Enhancements; Breaking Into Release Deallocate Threads
SQL ENHANCEMENTS: Trigger Recap; Trigger Recap - Before and After Triggers; Instead Of Triggers; Instead Of Triggers - Restrictions; Instead Of Triggers - Authorisation; Instead Of Triggers - Catalog Changes; The Merge Statement; Merge Statement Restrictions; The Merge Statement - Rowset Processing; Merge Statement Example; Multi Row Processing - Get Diagnostics Recap; Get Diagnostics - Statement Information; Get Diagnostics - Condition Information; Get Diagnostics - Merge Example; Explaining Merge Statements; Merge Statement Authorisation; Select From Merge / Update / Delete Statements; Select from Insert - Version 8 Recap; Select from Merge; Select from Update; Select from Delete; Order By and Fetch First in Subselect; The Order By Order Of Clause; The Truncate Statement; Truncate Examples; Using Truncate With Multi-Level Security; Truncate Restrictions; Union, Intersect and Except; Version 8 Recap - Union; Intersect and Except; Intersect and Except Examples; Set Current Schema Enhancement; Cultural Sort and Case Insensitive Comparison; Collation Key Examples; Implicitly Hidden Columns; Bigint - Data Type; Bigint - Scalar Function; Binary / Varbinary - Data Types; Binary - Data Type and Function; Decfloat - Data Type; Decfloat - Scalar Function; New Scalar Functions; Temporal Tables; Temporal Tables and Versioning; Temporal Tables - Versioning Example; System Temporal Tables - Data Access; Temporal Tables - Considerations; Temporal Tables - Application Controlled; Business Temporal Tables - Data Selection; Business Temporal Tables - Update and Delete; Business Temporal Tables - Update Example; Business Temporal Tables - Delete Example; Temporal Tables - Catalog Information; Greater Support for Implicit Casting; Datetime Constants; Variable Timestamp Precision; Variable Timestamp Precision - Current Timestamp; Variable Timestamp Precision - Row Change Timestamps; Variable Timestamp Precision - Catalog Changes; New ‘Timestamp with Time Zone’ Data Type; Timestamp with Time Zone - String Representation; Implicit Time Zone; Timestamp with Time Zone - Assignment and Comparison; Current Timestamp with Time Zone - Special Register; Session Time Zone - Special Register; Time Zone Expressions and Arithmetic; New Timestamp Functions; Application Programming Considerations; Global Variables; Qualifying Global Variables; Global Variables - Scope; Stored Procedures - Array Data Type for SQL/PL; Ordinary Arrays; Associative Arrays; The Array_Agg Function; Arrays - New Built-In Functions; Passing Arrays - Examples; Sequences - Recap; Create Sequence Syntax - Recap; Aliases for Sequences; Temporal Tables - Recap; System Temporal Tables - Recap; System Temporal Tables and Versioning - Recap; System Temporal Tables - Data Access - Recap; Application Controlled Temporal Tables - Recap; Business Temporal Tables - Data Selection - Recap; Temporal Special Registers; Temporal Special Registers - Bind Options; Temporal Support on Views; Declared Temporary Tables - Recap; Declared Temporary Table - Commit Behaviour; Declared Temporary Tables - Logging Enhancement; Not Logged Temporary Tables - Undo Processing; Declared Temporary Tables and Thread Reuse; Group By Clause - Review; New Group By Features; Group By Grouping Sets; Group By Rollup; The Grouping Function; Group By Cube; Like_Blank_Insignificant Dsnzparm
PROGRAMMING ENHANCEMENTS: Stored Procedures Recap; An SQL PL Procedure Example; Procedure Preparation; Native (Internal) SQL Procedures; How Native SQL Procedures Work; Creating Native and External SQL Procedures; Creating Native SQL Procedures; Altering Native SQL Procedures; Native SQL Procedures - Versioning; Native SQL Procedures - Binding Across Servers; Native SQL Procedures - Command Enhancements; Other Sql-Pl Enhancements; Sql Pl - Get Diagnostics Enhancement; Sql Pl - FOR Statement; Sql Pl - Nested Compound Statements; SQL Procedure Example - Nested Compound Statement; Sql Pl - New Special Registers; Debugging Native Stored Procedures; LOB Data Manipulation - Recap; LOB Definition And Manipulation; Base Table Definition; LOB Tablespace And Auxiliary Table Requirements; LOB Tablespace / Table Definition; LOB Data Manipulation; Using LOB Locators; Lob - Host Variables; Examples using LOB Variables; Examples of Lob Manipulation using Locator Variables; File Reference Variables; Defining File Reference Variables; File Reference Variables - Example; Loading Data using File Reference Variables; File Reference Variables - New Dsnzparm; Fetch Continue; Fetch Continue Example; Fetch Continue Considerations; DB2 JCC Java Driver; JCC Driver Enhancements; Spatial Support; Change to the SPUFI Defaults Panel; Recap - SQL/PL Scalar Functions; SQL/PL Scalar Function Enhancements; Inline and Non-Inline SQL/PL Scalar Functions; SQL/PL Scalar Functions - New Control Statements; Non Inline Functions - Binding Considerations; Non Inline Function Examples; Altering Non Inline SQL Functions; Native SQL Functions - Versioning; Enhanced SQL Table Function Support; Enhanced Native SQL Procedure Support; Extended Indicator Variables; Extended Indicator Variables - Example; New Universal Language Interface Program (Dsnuli); Locking Recap - V9 - Skip Locked Data; V10 Locking Enhancement - Access To Committed Data; Connectivity Enhancements; JDBC Type 2 Driver - Block Fetch; Database Access Thread (DBAT) Enhancement; Support for 64-bit ODBC Driver; Ensuring Application Compatibility; Archive Tables; Archive Table Control; Archive Table Considerations; Archive Table / Temporal Table Comparisons; Apache Hadoop Support for Big Data; Distributed Enhancements; Other Enhancements
XML ENHANCEMENTS: eXtensible Markup Language Introduction; XML Terminology; An XML Document Example; XML Data Model; Well Formed Documents; Valid Documents; Version 9 Enhancements - Overview of pureXML; XML Data Type; XML Columns - Summary Of Objects Created; Adding XML Columns to a Table; Node Storage; Relational Data vs XML (Hierarchical) Data; Displaying XML Information; The DB2 XML Parser; Schema Validation - The XML Schema Repository (XSR); XML Decomposition; Accessing XML Data; Summary Of Xml Functions; Sample Data used in Examples; XML2Clob Function; XMLSerialize Function; XMLElement Function; XMLAttributes Function; XMLForest Function; XMLConcat Function; XMLAgg Function; XMLNamespaces Function; XMLComment Function; XMLDocument Function; XMLPi Function; XMLText Function; XMLParse Function; XPath Specification; XML Document used in Examples; Xpath Function - XMLQuery; Xpath Function - XMLExists; Xpath Function - XMLCast; Xpath Function - XMLTable; XPath Axes; XPath Functions; XML Indexes; Index Considerations; Application development; Utilities; XML Document Processing Enhancements; Using XmlModify to Insert Nodes; Using XmlModify to Update / Delete Nodes; XML Indexing Enhancement; XML Date and Time Support; XML Schema Validation Enhancement; XML Type Modifier; XML Validation Information; XML and Check Data; Support for Multiple Versions of XML Documents; XML Versioning Example; Support for Binary XML; Binary XML Conversion; XML in Native SQL Stored Procedures / UDFs; Support for DEFINE NO for LOBs and XML; Xquery Support; XQuery Introduction; FLWOR Constructs; XML Document used in Examples; FLWOR Constructs - Example; FLWOR Expressions with XMLExists; FLWOR Expressions with Formatted Output; FLWOR Constructs - WHERE Clause; FLWOR Constructs - RETURN Clause; FLWOR Constructs - Built In Functions; Selecting Nodes; Specifying XPath Predicates; Xml Performance Enhancements; Xml Flwor Query Rewrite
PERFORMANCE ENHANCEMENTS: System Z and zIIP Exploitation; Virtual Storage Management; Index Page Size; Index Page Size Specification; Index Compression; Index Compression Considerations; Index Compression - Estimation; Index Compression - Dsn1 Sample Output; Table vs. Index Compression Comparisons; Resource Limit Facility Enhancement; Buffer Manager Enhancements; Logging Recap; Not Logged Tablespaces; Switching On and Off Not Logged; Logging Considerations; Not Logged with XML Tablespaces; Not Logged with LOB Tablespaces; Not Logged - SYSLGRNX and SYSCOPY; Recovering Not Logged Tablespaces; Load / Reorg with Not Logged Tablespaces; Rebuild Index with Not Logged Tablespaces; Check Data with Not Logged Tablespaces; Quiesce with Not Logged Tablespaces; Internet Protocol Version 6 Support; Run Time Reoptimization Enhancement; Locking Recap - Page / Row Lock Modes; Row Change Timestamps; Row Change Timestamp Selection; Row Change Timestamp Insertion; Optimistic Locking - Locking Recap - Isolation Levels; Locking Recap - Sensitive Static Cursors; Locking Recap - Sensitive Cursor Update / Delete Holes; Locking Recap - Sensitive Cursor Positioned Updates; Optimistic Locking; Optimistic Locking Example; Skip Locked Data; Index On Expression; Index On Expression Considerations; Relief for Sequential Key Insert; Randomized Index Ordering; Reordered Row Format; Moving to Reordered Row Format; Histogram Statistics; Page-Range Screening Optimization; Global Query Optimization; Package Management Enhancement; Package Switching; Deleting Old Packages; RID Pool Enhancements; Range-list Index Scan; IN List Enhancements; Predicate Evaluation Table; Dynamic Prefetch Enhancements; Dynamic Statement Cache Enhancements; Insert Performance Improvement; BufferPool Enhancements; Work File Enhancements; Non-Key Columns in a Unique Index; Mass Delete Locking Enhancement; Other Performance Enhancements; Version 9 Lob Recap; Version 9 LOB Recap - Object Definition; LOB Enhancements - Inline LOBS; Other LOB Enhancements; Hash Tables; Tablespaces for Hash Tables; Creating a Hash Table; Hash Table Considerations; Hash Table Catalog Changes; A New Access Path Framework; Access Path Framework - New Repository; Access Path Framework - New Catalog Tables; Subsystem Level Access Path Hints; The DSN_USERQUERY_TABLE Table; Inserting Rows Into DSN_USERQUERY_TABLE; Access Path Framework - The Bind Query Command; Access Path Framework - The Free Query Command; Access Path Framework - Access Plan Stability; Version 9 Recap - Package Management; Version 9 Recap - Package Switching; Deleting Old Packages; Access Plan Stability - V10 Enhancements; Explain - New Special Register; Lastused Information for Packages; Changes to the Explain Facility; Plan Table Layout; Plan Table Column Definitions; The Dsn_Statemnt_Table; The Dsn_Function_Table; Excluding NULL Keys from an Index; General Performance Enhancements; Pseudo Deleted Rows - Index Entry Cleanup; Row Relocation Enhancement
UTILITY / COMMAND ENHANCEMENTS
SECURITY ENHANCEMENTS

Course Format

The course is purely lecture based and contains no practical exercises. A comprehensive Student Guide is supplied which contains detailed documentation of the new version together with many syntax examples where relevant. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.

Get in touch