DB09 - DB2 Advanced SQL Workshop
Duration2 Days (customisation)
Availability

Course 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.

Course Details

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