|
|
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. |
|
Objectives:
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) |
|
Overview:
- 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. |
|
Topic Outline:
- Complex Multi-Table Queries JOINs Join table and condition syntax (old and new) Inner Joins of 3 or More Tables Self Join Outer Join Outer Joins of 3 or More Tables Joined Table Expressions Subqueries UNION UNION UNION ALL UNION Everywhere - Views Advantages of Views CREATE VIEW Syntax View Hierarchy Read-only Views 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 GET DIAGNOSTICS 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 ROWID Generated Always Generated by Default ROWID with LOBs ROWID with partitioned tables Sequences Identity Column new functionality - Alter Identity Columns Sequences vs. Identity Columns vs ROWIDs GENERATE_UNIQUE( ) Summary – Advantages and restrictions - Date/Time Arithmetic Date/time Special Registers Related Scalar Functions Durations and Labeled Durations Date/time Arithmetic - Data Type Usage Character String Formats Character Subtypes Graphic String Formats Binary String Formats Date/Time Formats Numeric Formats Distinct Data Type Strong Typing Data Type Promotion Constants - Global Temporary Tables Created Temporary Tables Declared Temporary Tables - Miscellaneous Advanced Topics UNICODE support CASE Expressions Multiple DISTINCTs in One SQL Statement Qualified Columns in INSERT and UPDATE Scalar Fullselect 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 Comparing NULLs - Publishing Support (Overview) Built-in DB2 Functions for XML Publishing XML Data Type XML2CLOB XMLELEMENT XLMATTRIBUTES XMLFOREST XMLCONCAT XMLAGG - Overview of New Efficiencies (Optional) Materialized Query Tables (MQTs) DPSIs - Data-partitioned Secondary Indexes Increased Parallelism Star Joins and Sparse Indexes Variable Length Index Keys - Dynamic SQL (Optional) |
|
|