DB2 for z/OS Advanced SQL
for Application Developers
|Duration: 3 days|
|Participants: Experienced application developers and database administrators who require advanced knowledge of SQL and DB2 programming techniques.|
Upon successful completion of this course you will be able to:
- Code sophisticated inner and outer joins (including self-joins), subqueries and unions, and efficiently use VIEWs.
- Understand NTEs (Nested Table Expressions), and code their replacement – CTEs (Common Table Expressions.) Code and use recursive SQL statements with CTEs.
- Code multi-row FETCH and INSERT, using host variables that are arrays.
- Code static and dynamic scrollable cursors, and understand the subtle differences between SENSITIVE and INSENSITIVE. Use SAVEPOINTs with cursors.
- Describe the impact of Isolation Level on locking and data sharing.
- Effectively use ROWID, SEQUENCE objects, Identity Columns and GENERATE_UNIQUE( ).
- Use the date/time/timestamp special registers, scalar functions, and labeled durations for date and time arithmetic.
- Describe DB2's data type support and usage.
- Utilize Global Temporary Tables.
- Describe and use a variety of advanced features, including UNICODE support, CASE, Scalar fullselect, new scalar and aggregate built-in functions and special registers, handling nulls, etc.
- Use the new DB2 built-in functions for XML publishing to generate HTML by just coding SELECT statements. (Optional)
- Describe some new DB2 efficiencies, such as MQTs (Materialized Query Tables), DPSIs (Data Partitioned Secondary Indexes), increased parallelism for data members, Star Joins, etc.
- Write Dynamic SQL Statements. (Optional)
- This course teaches advanced and embedded SQL for DB2 UDB for z/OS Version 8. It contains extensive hands-on workshops to give students the opportunity to apply the material and strengthen the learning process.
|Prerequisites: This course is designed for developers with experience coding and executing basic SQL DML statements (SELECT, INSERT, UPDATE, and DELETE), and coding DB2 programs with embedded SQL and cursors.|
|Format: Lecture, discussion, and hands-on workshops. Numerous workshops enhance the learning process.|
- Complex Multi-Table Queries
Join table and condition syntax (old and new)
Inner Joins of 3 or More Tables
Outer Joins of 3 or More Tables
Joined Table Expressions
Advantages of Views
CREATE VIEW Syntax
VIEW considerations and limitations with Data Modification
- Nested Table Expressions
- Common Table Expressions
- Recursive SQL
- Multi-row and Other Embedded Code Enhancements
Multi-row FETCH and INSERT
SELECT from INSERT
Embedded SELECT with Order By
- Scrollable Cursors
Static Scrollable Cursors
Dynamic Scrollable Cursors
Sensitive vs. Insensitive Cursors
Positioned UPDATE and DELETE
Update and Delete Holes
Insensitive Scrolling and Holes
Locking, Isolation Levels and Scrollable Cursors
SAVEPOINT with cursor containing an INSERT
- Isolation Override – WITH Clause
WITH and SELECT
WITH and INSERT
WITH and UPDATE
WITH and DELETE
- Unique Row Identification and Sequence Values
Generated by Default
ROWID with LOBs
ROWID with partitioned tables
Identity Column new functionality - Alter Identity Columns
Sequences vs. Identity Columns vs ROWIDs
Summary – Advantages and restrictions
- Date/Time Arithmetic
Date/time Special Registers
Related Scalar Functions
Durations and Labeled Durations
- Data Type Usage
Character String Formats
Graphic String Formats
Binary String Formats
Distinct Data Type
Data Type Promotion
- Global Temporary Tables
Created Temporary Tables
Declared Temporary Tables
- Miscellaneous Advanced Topics
Multiple DISTINCTs in One SQL Statement
Qualified Columns in INSERT and UPDATE
Expressions in GROUP BY
New Built-in Functions
New Special Registers
DISTINCT FROM Comparison Operator
Long Names, Longer SQL Statements, Index Keys, Column Names, and Predicates
Aggregate function enhancements
Expanded use of expressions
- Publishing Support (Overview)
Built-in DB2 Functions for XML Publishing
XML Data Type
- Overview of New Efficiencies (Optional)
Materialized Query Tables (MQTs)
DPSIs - Data-partitioned Secondary Indexes
Star Joins and Sparse Indexes
Variable Length Index Keys
- Dynamic SQL (Optional)