- Tel: +44(0)1275 859666
DB09 - DB2 Advanced SQL Workshop - 2 Days
View Dates and LocationsCourse Description
This training course teaches the delegate how to write advanced SQL statements including many new features introduced in recent DB2 releases.
Pre-requisites
A working knowledge of the DB2 host environment is advantageous but not essential.
The delegate should be able to code basic to intermediate SQL statements - these skills can be acquired by attending our SQL Workshop.
Objectives
The aim of this course is to provide the delegate with the necessary skills to write advanced SQL queries using DB2 for z/OS.
On completion of this course the student will be able to:
- use recent DDL enhancements
- use new data types
- use the MERGE statement
- code UNION, INTERSECT and EXCEPT statements
- use new SQL Scalar Functions
- use the various GROUP BY features
- code Common Table Expressions
- code recursive SQL statements
- store, generate and manipulate XML data
- use Business and System Temporal Tables
Environment
The course applies 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.
On-site course enquiry (from £165 pp per day)Course Details
View Dates and Locations- Recent Structural Changes
- New Data Types
- 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
- Hidden Columns
- Row Change Timestamps
- Locking enhancements
- Advanced DML Statements
- The MERGE Statement
- SELECT from INSERT / UPDATE / DLEETE / MERGE
- UNION and UNION ALL
- INTERSECT and INTERSECT ALL
- EXCEPT and EXCEPT ALL
- New Scalar functions
- The GROUP BY Clause
- Additional GROUP BY Features
- GROUP BY ROLLUP
- The GROUPING Function
- GROUP BY CUBE
- GROUP BY Grouping Sets
- The CASE Statement
- Common Table Expressions
- Recursive SQL
- XML PROCESSING
- eXtensible Markup Language Introduction
- Well Formed Documents
- XML Data
- Integration of XML Data
- XML Serialization Function - XML2Clob
- XML Publishing Function - XMLElement
- XML Publishing Function - XMLAttributes
- XML Publishing Function - XMLForest
- XML Publishing Function - XMLConcat
- XML Publishing Function - XMLAgg
- XML Publishing Function - XMLNamespaces
- Overview of pureXML
- XML Data Model
- XML Parser
- XML Schema Repository (XSR)
- XPATH
- XPath Axes
- XPath Functions
- XML Document Storage Infrastructure
- Accessing XML Data
- SQL/XML Publishing Functions
- XMLCOMMENT
- XMLDOCUMENT
- XMLPI
- XMLTEXT
- XMLSERIALIZE
- XMLPARSE
- XMLQUERY
- XMLEXISTS
- XMLCAST
- XMLTABLE
- XML Indexes
- Index Considerations
- Application development
- Database Administration Support
- XML Schema
- XML Decomposition
- TEMPORAL TABLES
- 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