Home About Courses Schedule Services Webinars Contact Search

PureXML in DB2 for z/OS

SEE SCHEDULE

Duration: 2 days

Method: Instructor led, Hands-on workshops

Price: $1125.00

Course Code: DB1091



Audience

This course is intended for business analysts, end users, programmers and team leads who need to use pureXML to access XML data in a DB2 for z/OS table.

Description

This course introduces the attendee to the PureXML features of DB2 for z/OS versions 8 through 11.

Objectives

Upon successful completion of this course, the student will be able to:

  • Describe the importance of using XML for storage
  • Describe the XML data type and how DB2 for z/OS stores the XML data
  • List the advantages of using PureXML over the previous XML implementation
  • Explain using the XML “path” type expression to access a portion of an XML document
  • Review and discuss sample queries that illustrate SQL/XML capabilities to access XML data
  • Build and use SQL/XML queries to access XML data for Select, Insert, Update, and Delete
  • Build and use new XQuery features including FLWOR expressions and their join syntax.

Prerequisites

Significant experience using SQL is recommended. The Fundamentals of XML course or previous experience with XML is recommended. The features discussed require DB2 for z/OS.

Topics

  1. DB2 PureXML Overview
    • The XML Data Type
    • XML Indexes
    • Decomposing XML Documents into Relational Tables
    • Benefits of DB2 pureXML Technology
    • pureXML Restrictions and Limits
  2. DB2 and XPATH
    • DB2 and XPath
    • XPath Expressions
    • Path Expression Examples
    • Filter Expressions
    • Arithmetic Expressions
    • Comparison Expressions
    • Built-in Functions
    • XML Namespaces and Name Qualification
  3. Using XML with DB2
    • The XML Data type
    • Implicit Objects Created to Support XML Column
    • XML Tablespace and Index Overview
    • Querying the Catalog
    • SQL/XML Functions
    • XML Publishing Functions to Construct XML Sequences
    • XML Serialization
    • XMLPARSE
    • XMLQUERY
    • XMLEXISTS
    • Empty on Null vs. Null on Null
    • XMLTABLE
    • XMLCAST
  4. INSERT, UPDATE and DELETE with XML
    • Insertion into XML Columns
    • Updates of XML Columns
    • Partial XML Document Update
      • Delete a node with XMLMODIFY
      • Insert a node with XMLMODIFY
      • Insert a node with XMLELEMENT
      • Delete an node with XMLMODIFY
    • Deletion of XML Data from Tables
    • Variables and Casting (XMLEXISTS / XMLQUERY)
    • XMLEXISTS and Indexing
      • NOT EXISTS
      • UPPER function
    • LOAD Utility / Considerations
    • UNLOAD Utility
  5. The XMLTABLE and XMLCAST Functions
    • XMLTABLE Function
      • With Namespaces
      • Replacing Nulls
      • Repeating Elements
      • Ordinality Column
    • XMLTABLE Examples
    • Inserting Values Returned from XMLTABLE
    • Using XMLTABLE to Create Views
      • Performance Implications
    • XML to SQL Casting
  6. Programming Language Support for DB2 XML
    • XML and JDBC
      • Insert From File as Binary Data
      • Data Retrieval into String Variable
      • Data Retrieval into DB2XML Object
    • Embedded SQL with XML in COBOL
    • Host Variables for XML Data (CLOB / BLOB)
    • COBOL Update Example
    • Embedded SQL Performance
  7. Indexes and XML
    • Data Types Associated with Pattern Expressions
    • UNIQUE Keyword in XML Index Definition
    • Example Queries and Supporting Indexes
    • XML Index Considerations
  8. XML Schemas, Validation and XML Schema Repository (XSR)
    • XML Schema Management with the XSR
    • XML Schema Validation
    • XML Registration Using IBM Data Studio
    • Create Table Using Schema Validation
  9. Version 10 XML New Features
    • V10 XML New Features Overview
    • Node Level XML Updates – XMLMODIFY
    • XML Parameters
    • Binary XML
    • XML Concurrency Control
    • XML Date and Time Data Types
    • XML Modifier
    • XML zIIP and zAAP Processing
    • XML Enhanced Index Support
  10. DB2 XML - XQUERY
    • PureXML
    • XQuery Overview
    • XML – New DB2 Version 11 Support
    • Where Does XQuery Appear?
    • FLWOR Statements
    • Simple FLOWR Example
    • FLWOR Where Clause
    • FLWOR Does Not Filter Rows
    • Using As on the Passing Clause
    • FLWOR Let Clause
    • Define Multiple Variables with Let
    • Define Variables on Child Nodes
    • Conditions in the Return Clause
    • Sorting the Returned Elements
    • Joinng Two XML Documents
    • Join Returning XML Result
    • Join with Conditional Return
    • Review