Ten Common Usage Patterns of pureXML for DB2 for zOS Applications

1
Ten Common Usage Patterns of pureXML for DB2 for
z/OS Applications

• Guogen (Gene) Zhang, gzhang_at_us.ibm.com

2
Agenda

• Overview of pureXML in DB2 9 for z/OS
• Ten common usage patterns of pureXML
• Q A

3
XML and the Web before DB2 pureXML
XML
Relational Database
Application Server
Client
Mapping
Mapping
Objects
Relational schema
XML

• XML to object mapping and object to relational
  mapping
• Very costly
• Complex
• Inflexible

4
DB2 pureXML and the Web
XML
SOA Gateway
relational
XML
Client
DB2 pureXML

• End-to-End XML
• No expensive object mapping
• Pass Thru XML from/to database
• SOA-Gateway
• Device/application to handle network protocols,
  security, reliability, performance
• Easy to manage
• Simple pre- and post-processing of XML e.g. via
  XSLT

5
DB2 9 for z/OS XML Features

• First-class XML type, native storage of XQuery
  Data Model (XDM)
• Complete SQL/XML constructor functions
• XMLDOCUMENT, XMLTEXT, XMLCOMMENT, XMLPI
• From V8 XMLELEMENT, XMLATTRIBUTES,
  XMLNAMESPACES, XMLFOREST, XMLCONCAT, XMLAGG
• XMLPARSE, XMLSERIALIZE, XMLCAST
• Important SQL/XML functions with XPath
• XMLEXISTS, XMLQUERY, XMLTABLE
• XML indexes
• use for XMLEXISTS and XMLTABLE
• XML Schema repository, Validation UDF, and
  decomposition
• DRDA (distributed support) and application
  interfaces
• Utilities and tools

XMLTABLE and XMLCAST available through PTF
UK33493
6
XPath support in DB2 9 z/OS

• XPath used in XMLEXISTS, XMLQUERY, XMLTABLE,
  indexing
• XPath 1.0 -
• XPath 1.0 constructs in XPath 2.0 semantics (no
  value comp)
• more numeric data types and some generic types.
• namespace declaration from XQuery prolog
• - Axes only forward axes (child, descendant,
  descendant-or-self, self, attribute, //, /, _at_)
  parent axis (..) are supported.
• Types supported in XPath expressions
• xsboolean, xsinteger, xsdecimal, xsdouble,
  xsstring
• Functions supported fnabs, fnboolean,
  fncompare, fnconcat, fncontains, fncount,
  fndata, fnstring-length, fnnormalize-space,
  fnnot, fnround, fnstring, fnsubstring,
  fnsum, fndistinct-values, fnmax, fnmin,
  fnupper-case, fnlower-case, fntranslate,
  fntokenize, fnmatches, fnreplace, fnposition,
  fnlast, fnname, fnlocal-name

Functions in blue available through PTF UK34342
7
What You Can Do with pureXML
- Managing XML data the same way as relational
data

• Create tables with XML columns or alter table add
  XML columns
• Insert XML data, optionally validated against
  schemas
• Create indexes on XML data
• Efficiently search XML data
• Extract XML data
• Decompose XML data into relational data
• Construct XML documents from relational and XML
  data
• Handle XML data in all the utilities and tools

XML
8
Some Guidelines to XML Usage

• XML columns provide flexibility that cannot be
  achieved using relational columns.
• LOB columns cannot be queried efficiently
• The best table design is typically hybrid tables
  with relational and XML columns.
• Heavily used columns, such as primary key and
  foreign keys, are better kept in regular columns.
• XML columns are better kept to small docs if
  sub-documents are frequently accessed, not the
  whole large documents.

9
Ten Common XML Usage Patterns

• Receive documents and put into different tables
  according to document types
• Validate a document against an XML schema during
  insertion
• Verify if a document conforms to complex
  constraints beyond a schema
• Split a large document into smaller documents and
  relational columns
• Generate XHTML for web pages and XML for web
  services

10
Ten Common XML Usage Patterns (contd)

• Generate new documents from existing documents
  for transaction processing and workflow
• Generate XML data for relational data
• Use SQL for reporting and analytics on XML data
• Invoke Web services and use the result in SQL
  queries
• Use XML for object persistence (SDO/binary XML)

11
1. Receive documents and insert appropriately

• Scenario insert new documents (request), or
  update existing row with new document
  (response)PAYMENT(ID, REQUEST,
  RESPONSE)ACCTAPPL(ID, REQUEST, RESPONSE)
• Solution Get the document type and ID from a
  document, and find what to do.
• Options You can do this from application,
  external stored procedure, or SQL PL stored proc

12
1. Receive docs - Example
ltfxRequest myID 123gt ltfxRequestgt

• To get document type, use //fnlocal-name(.) to
  get the local name of top element.
• fnname() is not reliable as prefix can change
• Use //_at_myID for attribute, or //myID for
  subelement
• If extracting one item from a document, using
  XMLCAST(XMLQUERY() ), if its multiple items,
  use XMLTABLE() function.
• SELECT myDocType, myIDFROM XMLTABLE(.
  PASSING myXMLDocCOLUMNS myDocType VARCHAR(10)
  PATH /fnlocal-name(.), myID INT
  PATH /_at_myID) X

13
1. Receive docs - Example

• Use XMLCAST and fnlocal-name() in an SQL PL CASE
  statement
• CREATE PROCEDURE ReceiveDoc(IN var BLOB(1M))
  LANGUAGE SQL
• CASE (XMLCAST(XMLQUERY(//fnlocal-name(.)'
  PASSING XMLPARSE(DOCUMENT var) ) as VARCHAR(10))
  )
• WHEN (PAYMENT) THEN
• MERGE INTO PAYMENT USING (VALUES( myID,
  myDoc)) AS N(ID, DOC) ON (PAYMENT.ID
  N.ID)WHEN MATCHED THEN UPDATE SET RESPONSE
  N.DOCWHEN NOT MATCHED THEN INSERT VALUES(N.ID,
  N.DOC, NULL)
• WHEN (ACCTAPPL) THENMERGE INTO ACCTAPPL ...
• ELSE RAISE_ERROR(...)
• END CASE

var argument from SQL PL stored proc (XML doc in
LOB) myID XMLCAST(XMLQUERY('//_at_myID' PASSING
XMLPARSE(DOCUMENT
var) ) AS INT)myDoc var
14
2. Validate a document against an XML schema

• Scenario Need to validate a document before
  insertion
• Solution DSN_XMLVALIDATE function, need to
  specify a single schema
• Options Call the function explicitly in INSERT
  statement, or enforce it through a SQL PL stored
  procedure
• For small docs (lt 32KB), INSTEAD OF TRIGGER can
  be used with dummy view/table and a VARBINARY
  column

15
2. Validate a doc - Example

• Table CREATE TABLE purchase_orders(
• ID INT NOT NULL,
• content XML )
• Direct callINSERT INTO purchase_orders
  VALUES(1,DSN_XMLVALIDATE(myPO,SYSXSR.POSCHEMA)
  )
• Stored proc
• CREATE PROCEDURE INSERTMYTABLEVALID(IN col1
  INT, IN xmlcol BLOB(1M))LANGUAGE SQLBEGIN
  INSERT INTO purchase_orders VALUES( col1,
  XMLPARSE(DOCUMENT DSN_XMLVALIDATE(xmlcol,
  SYSXSR.ORDERSCHEMA)) )END
• Applications callCALL INSERTMYTABLEVALID(1,
  blob)

16
2. Validate a doc Example COBOL using LOB

• 1) XML in applications' memory (hostvar), if it's
  UTF-8, use BLOB hostvar is the best (use CLOB or
  DBCLOB if it's not UTF-8)
• EXEC SQL INCLUDE COBIIVAR END-EXEC.
• EXEC SQL INCLUDE COBSVAR END-EXEC.
• EXEC SQL INCLUDE SQLCA END-EXEC.
• ...
• 01 BLOB-VAR USAGE IS SQL TYPE IS BLOB(16M).
• ...
• EXEC SQL
• INSERT INTO T VALUES (XMLPARSE(DOCUMENT
  DSN_XMLVALIDATE(BLOB-VAR, 'SYSXSR.MYSCHEMA')) )
• END-EXEC.
• ...
• 2) XML in a file, use LOB file reference variable
  is best. Assuming XML in UTF-8
• ...
• 01 BLOBFR1 SQL TYPE IS BLOB-FILE.
• ... example file is in HFS
• MOVE '/u/li637/blob10a.txt' TO BLOBFR1-NAME.
• MOVE 20 TO BLOBFR1-NAME-LENGTH.
• MOVE SQL-FILE-READ TO BLOBFR1-FILE-OPTION.

17
3. Verify if a document conforms to complex
constraints beyond schema

• Scenario some value relationship involving XML
  docs intra-document, cross documents, and or
  relational data, such as forms or transactions
• Solution Use SQL/XML queries with XMLQUERY,
  XMLTABLE, or XMLEXISTS with XPath.
• Options
• XMLEXISTS in CASE expression
• Each condition is coded as XMLQUERY/XMLEXISTS(x/
  a/bc d ../(ef) PASSING doc as x) you
  can have multiple docs passed in.
• Use XMLTABLE for more complex join conditions

18
3. Verify a doc - Examples

• Use case expression with XMLEXISTS (can be
  multiple conditions, multiple docs)
• SELECT CASE WHENXMLEXISTS('/step1/step2step3
  x step4/y PASSING xmlcol) THEN 1 ELSE 0 END
  CASE as Cond1, FROM
• Use XMLEXISTS to check one reference
• SELECT 1
• FROM SYSIBM.SYSDUMMYU
• WHERE EXISTS(SELECT 1 FROM T1, T2
• WHERE XMLEXISTS('/step1/step2step3x'
• PASSING xmlcol, T2.PK AS
  "x"))
• Use XMLTABLE for multiple conditions
• SELECT 1
• FROM SYSIBM.SYSDUMMYU
• WHERE EXISTS(SELECT 1 FROM T0, XMLTABLE(/a/b
  PASSING T0.X
• COLUMNS c1 INT
  PATH c,
• c2
  VARCHAR(10) PATH d) as XT,
• T1, T2
• WHERE XT.C1 T1.C1 AND XT.C2
  T2.C2 )

19
4. Split a large document into smaller documents
and relational columns

• Scenarios to leverage relational features more
  and make documents smaller (for performance)
• Solution Use XMLTABLE function or XDBDECOMPXML
  stored procedure
• XMLTABLE is more flexible
• XDBDECOMPXML requires XML schema to be annotated.

20
4. Split a doc XMLTABLE example

• INSERT INTO TB25MMF
• SELECT T.RETURNID, T.DOCID, T.DOCNAME,
  XMLDOCUMENT(T.DOC)
• FROM
• XMLTABLE(XMLNAMESPACES(DEFAULT
  'http//www.irs.gov/efile'),
• 'd/Return/ReturnData/' passing ? as "d"
• COLUMNS
• returnid char(20) PATH '../../ReturnHeader/Re
  turnId',
• docid varchar(50) PATH '_at_documentId'
• docname varchar(50) PATH '_at_documentName',
• doc xml PATH '.') as T
  

21
4. Split a doc example (contd)

• ltReturn xmlns"http//www.irs.gov/efile" ...gt
• ltReturnHeader ...gt
• ltReturnIdgt01151620071030001526lt/ReturnIdgt
• ...
• lt/ReturnHeadergt
• ltReturnData documentCount"30040"gt
• ltIRS1065 documentId"IRS1065"
  documentName"IRS1065" ...gt
• lt/IRS1065gt
• ltIRS1065ScheduleD ...gt ...
• lt/IRS1065cheduleDgt
• ...
• lt/Returngt

22
5. Generate XHTML for web pages and XML for web
services

• Scenario generate XHTML directly from SQL
  instead of in applications
• Solution SQL/XML constructors
• Benefit Simpler code, better performance

23
5. Generate XHTML or XML - Example
SELECT XMLElement(NAME "TABLE",
XMLATTRIBUTES('1' as "border"),
XMLElement(NAME CAPTION, 'Department-Employe
e Table'), XMLElement(NAME
TR, XMLFOREST('Dept No as TH, 'Dept Name as
TH, 'Emp
No as TH, 'Emp Name as TH, 'Phone as TH) ),
XMLAGG(

XMLCONCAT(
XMLELEMENT(NAME TR,
XMLELEMENT(NAME TD,

XMLATTRIBUTES( X.CNT1 as "rowspan"),

D.DEPTNO),
XMLELEMENT(NAME TD,

XMLATTRIBUTES(
X.CNT1 as "rowspan"),

D.DEPTNAME)
),
( SELECT
XMLAGG(XMLElement(NAME TR,

XMLForest(EMPNO as TD,

FIRSTNME ' ' LASTNAME as TD,

PHONENO as TD) ) )
FROM DSN8910.EMP E
WHERE E.WORKDEPT D.DEPTNO )
) ) )FROM DSN8910.DEPT D, (SELECT
WORKDEPT, COUNT()
FROM DSN8910.EMP GROUP BY WORKDEPT)
X(DEPTNO, CNT) WHERE D.DEPTNO X.DEPTNO AND
D.DEPTNO IN ('A00', 'C01')

TableHeader

Dept

Emp
24
(No Transcript)
25
6. Generate new documents from existing documents

• Scenario generating invoice/shipping slip from
  purchase order, workflow
• Solution use SQL/XML constructors with XMLQUERY
  and/or XMLTABLE

26
6. Generate new documents Example 1

• Does not take items, or take whole items

SELECT XMLDocument( XMLElement(NAME
"invoice", XMLAttributes( '12345' as
"invoiceNo"), XMLQuery ('/purchaseOrder/bi
llTo' PASSING xmlpo), XMLElement(NAME
purchaseOrderNo, PO.ponumber),
XMLQUERY(/purchaseorder/items PASSING xmlpo),
XMLElement(NAME "amount",
XMLQuery ('fnsum(/purchaseOrder/items/i
tem/xsdecimal(USPrice))' PASSING
xmlpo) ) ) ) FROM PurchaseOrders
PO WHERE PO.ponumber 200300001
27
6. Generate new documents Example 2

• Pick some item content, change names

SELECT XMLDocument( XMLElement(NAME
"invoice", XMLAttributes( '12345' as
"invoiceNo"), XMLELEMENT(NAME items,
XMLAGG(XMLELEMENT(NAME lineitem,
XMLFOREST(PartNo,
ProductName, Qty, USPrice) ) ),
XMLELEMENT(NAME amount, SUM(USPrice))
) ) FROM PurchaseOrders PO,
XMLTABLE(/purchaseorder/items/item PASSING
XMLPO COLUMNS "PartNo CHAR(6) PATH
'_at_partnum', "ProductName" CHAR(20) PATH
'productName', "Qty" INTEGER PATH
'quantity', "USPrice" DECIMAL(9,2) PATH
'USPrice', "ShipDate" DATE PATH 'shipDate) as
X WHERE PO.ponumber 200300001
28
7. Generate XML data for relational data

• Scenario distribute reference table data to
  distributed applications, and also receive that
  and put into a table
• Solution SQL/XML constructors and XMLTABLE or
  XDBDECOMPXML stored procedure
• Options
• Same table gt nested XML doc
• Multiple tables (F-P key relationship) gt nested
  XML doc

29
7. Generate XML data for relational data
Example 1

• Simple output single table, each row gt element
• SELECT
• XMLELEMENT(NAME "table",
• XMLAGG(XMLELEMENT (NAME "row",
• XMLFOREST(c1, c2, c3...))))
• FROM table
• Simple input reverse
• INSERT INTO table
• (SELECT FROM XMLTABLE('/table/row' PASSING ?
• COLUMNS c1 VARCHAR(50),
• c2 INTEGER,
• c3 CHAR(1)) AS X)

30
7. Generate XML Example 2 One table gt nested
doc

• Start from the innermost
• SELECT ONE, TWO,
• XMLAGG( XMLELEMENT(NAME "text", TEXT)
  ORDER BY TEXT ) X
• FROM MYTABLE
• GROUP BY ONE, TWO

gt
31
7. Generate XML Example 2 One table gt nested
doc

• Then get to the upper level
• SELECT ONE,
• XMLAGG( XMLELEMENT(NAME "two",
• XMLATTRIBUTES( TWO as
  "arg"), X) ORDER BY TWO) Y
• FROM (SELECT ONE, TWO,
• XMLAGG( XMLELEMENT(NAME "text",
  TEXT) ORDER BY TEXT ) X
• FROM MYTABLE
• GROUP BY ONE, TWO ) T
• GROUP BY ONE

gt
32
7. Generate XML Example 2 One table gt nested
doc

• Then get to three levels
• SELECT XMLELEMENT(name "document",
• XMLAGG( XMLELEMENT(NAME "one",
• XMLATTRIBUTES(ONE as "arg"),
  Y) ORDER BY ONE) ) Z
• FROM (
• SELECT ONE,
• XMLAGG( XMLELEMENT(NAME "two",
  XMLATTRIBUTES( TWO as "arg"), X) ORDER BY TWO) Y
• FROM (SELECT ONE, TWO,
• XMLAGG( XMLELEMENT(NAME "text", TEXT)
  ORDER BY TEXT ) X
• FROM MYTABLE
• GROUP BY ONE, TWO )T
• GROUP BY ONE ) T2

gt
33
7. Generate XML Example 3 multiple tables gt
nested doc
DEPT(DEPTNO, DEPTNAME) -gt EMP(EMPNO, LASTNAME,
FIRSTNME, WORKDEPT)
SELECT XMLELEMENT( NAME "Dept",
XMLATTRIBUTES ( D.DEPTNO AS "deptno",
D.DEPTNAME AS
"name" ), ( SELECT XMLAGG (XMLELEMENT
(NAME "Emp",
XMLATTRIBUTES(E.EMPNO as "empno"),

E.FIRSTNME ' ' E.LASTNAME ) )
FROM DSN8810.EMP E
WHERE E.WORKDEPT D.DEPTNO )
) FROM DSN8910.DEPT D WHERE D.DEPTNO 'D01'

34
8. Use SQL for reporting and analytics on XML data

• Scenario need report and analysis on
  transactional XML data
• Solution SQL with XMLTABLE() or XMLTABLE views
• Benefit No extra steps and storage needed.

35
8. Use SQL on XML data - Example
CREATE VIEW ORDER_VIEW AS SELECT PO.POID,
X. FROM PurchaseOrders PO, XMLTABLE(
'/purchaseorder/items/item' PASSING PO.XMLPO
COLUMNS "orderDate" DATE PATH
'../../_at_orderDate', "shipTo City" VARCHAR(20)
PATH '../../shipTo/city', "shipTo State"
CHAR(2) PATH '../../shipTo/state', "Part "
CHAR(6) PATH '_at_partnum', "Product Name"
CHAR(20) PATH 'productName', "Quantity"
INTEGER PATH 'quantity', "US Price"
DECIMAL(9,2) PATH 'USPrice', "Ship Date" DATE
PATH 'shipDate', "Comment" VARCHAR(60) PATH
'comment' ) AS X
SELECT "Product Name", "shipTo State", SUM("US
Price" "Quantity") AS TOTAL_SALE FROM
ORDER_VIEW GROUP BY "Product Name", "shipTo
State"
SELECT "shipTo City", "shipTo State", RANK()
OVER(ORDER BY SUM("Quantity")) AS SALES_RANK FROM
ORDER_VIEW WHERE "Product Name" 'Baby
Monitor' GROUP BY "shipTo State", "shipTo
City" ORDER BY SALES_RANK
36
9. Invoke Web services and use the result in SQL
queries

• Scenario Use web services inside SQL in SOA
  applications (web service consumer)
• Solution SOAPHTTPV and SOAPHTTPC functions with
  XMLTABLE in SQL, or external UDF

37
9. Invoke Web services - Example

• SELECT LONG, LAT
• FROM
• XMLTABLE(/LatLongReturn PASSING
  XMLPARSE(DB2XML.SOAPHTTPV('http//ws.cdyne.com/zip
  togeo/zip2geo.asmx', 'http//ws.cdyne.com/GetLatLo
  ng', 'ltmGetLatLong xmlnsm"http//ws.cdyne.com"
  SOAP-ENVencodingStyle"http//schemas.xmlsoap.org
  /soap/encoding/"gt ltmzipcodegt95123lt/mzipcodegt
  ltmLicenseKeygt0lt/mLicenseKeygt lt/mGetLatLonggt'))
• COLUMNS
• LONG DECIMAL(5,2) PATH AvgLongitude,
• LAT DECIMAL(5,2) PATH AvgLatitude ) X

38
10. Use XML for object persistence

• Scenario to persist application objects in DB
• Solution SDO, or home-grown solution, such as
  serialize method on objects, generating XML. Use
  XMLEXISTS to search with indexes.
• Benefit search on flexible objects/XML with
  indexes, without normalizing into many tables

39
10. Use XML for object persistence - Idea

• Object.serialize() gt XML, insert into table
• SELECT xobj FROM PT WHERE XMLEXISTSxobj.deserial
  ize() gt Object

40
Q A

• send emails to
• db2zxml_at_us.ibm.com

41
References on DB2 z/OS pureXML

• Detailed introduction presentation
  ftp//ftp.software.ibm.com/software/data/db2zos/db
  29zospureXMLgzhang.pdf
• DB2 z/OS XML Guide http//publib.boulder.ibm.com/
  infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.xm
  l/bknxspsh.htm
• IBM developerWorks DB2 XML (LUW)
  http//www.ibm.com/developerworks/db2/zones/xml/in
  dex.html
• Google pureXML wiki