Home About Courses Schedule Services Webinars Contact Search

pureXML in Db2 for z/OS

SEE SCHEDULE

Duration: 3 Days

Method: Instructor led, Hands-on workshops

Price: $1800.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

Get ready to become a power user of XQuery and XPath expressions in the processing of XML documents. Get ready to understand some of the efficiencies and inefficiencies of XML processing. Db2’s PureXML feature allows you to store well-formed XML documents as a column that has the XML data type. This allows for efficient search, retrieval, and updates within an XML document. This class spends time showing how to query these documents using the XQuery language (with XQuery expressions). Students will have many hands on workshops to reinforce the topics, and learn the most efficient ways to code and process.

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.
  • Understand the ‘Best Practices’ for indexing XML documents
  • Understand the ‘Best Practices’ for efficient XQuery processing

Prerequisites

Some experience in SQL programming is recommended. Some previous experience in XML documents would be nice, but not required. The features in this course are noted below are for Db2 for z/OS.

Topics

  • I. Db2 PureXML Overview
    • The XML Data Type
    • XML Indexes
    • Decomposing XML Documents into Relational Tables
    • Benefits of Db2 pureXML Technology
    • pureXML Restrictions and Limits
  • II. 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
  • III. 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
  • IV. 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
  • V. 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
  • VI. 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
  • VII. Indexes and XML
    • Data Types Associated with Pattern Expressions
    • UNIQUE Keyword in XML Index Definition
    • Example Queries and Supporting Indexes
    • XML Index Considerations
  • VIII. 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
  • IX. Db2 XML - XQUERY
    • 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
  • X. XML Performance
    • XMLEXISTS Fully Specified Paths
    • XML Index Examples
    • Reading Explains with XMLEXISTS predicates