1 / 41

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

Ten Common Usage Patterns of pureXML for DB2 for z/OS Applications. Guogen (Gene) Zhang, gzhang@us.ibm.com. Agenda. Overview of pureXML in DB2 9 for z/OS Ten common usage patterns of pureXML Q & A. XML and the Web before DB2 pureXML. XML. Relational Database. Application Server. Client.

adora
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Ten Common Usage Patterns of pureXML for DB2 for z/OS Applications Guogen (Gene) Zhang, gzhang@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, //, /, @) & parent axis (..) are supported. • Types supported in XPath expressions: • xs:boolean, xs:integer, xs:decimal, xs:double, xs:string • Functions supported: fn:abs, fn:boolean, fn:compare, fn:concat, fn:contains, fn:count, fn:data, fn:string-length, fn:normalize-space, fn:not, fn:round, fn:string, fn:substring, fn:sum, fn:distinct-values, fn:max, fn:min, fn:upper-case, fn:lower-case, fn:translate, fn:tokenize, fn:matches, fn:replace, fn:position, fn:last, fn:name, fn:local-name Functions in blue: available through PTF UK34342

  7. 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 What You Can Do with pureXML - Managing XML data the same way as relational data 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 (cont’d) • 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 <fx:Request myID = “123”> … <fx:Request> • To get document type, use /*/fn:local-name(.) to get the local name of top element. • fn:name() is not reliable as prefix can change • Use /*/@myID for attribute, or /*/myID for subelement • If extracting one item from a document, using XMLCAST(XMLQUERY(…) …), if it’s multiple items, use XMLTABLE() function. SELECT myDocType, myIDFROM XMLTABLE(‘.’ PASSING :myXMLDocCOLUMNS myDocType VARCHAR(10) PATH ‘*/fn:local-name(.)’, myID INT PATH ‘*/@myID’) X

  13. 1. Receive docs - Example • Use XMLCAST and fn:local-name() in an SQL PL CASE statement: CREATE PROCEDURE ReceiveDoc(IN var BLOB(1M)) LANGUAGE SQL … CASE (XMLCAST(XMLQUERY(‘/*/fn:local-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('/*/@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 (< 32KB), INSTEAD OF TRIGGER can be used with dummy view/table and a VARBINARY column

  15. 2. Validate a doc - Example • TableCREATE 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. EXEC SQL INSERT INTO T VALUES (XMLPARSE(DOCUMENT DSN_XMLVALIDATE(:BLOBFR1, 'SYSXSR.MYSCHEMA')) ) END-EXEC. • 3) Use LOB locator to hold XML value…

  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/b[c + d = ../(e+f)]’ 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/step2[step3= 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/step2[step3=$x]' 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/ReturnId', docid varchar(50) PATH '@documentId' docname varchar(50) PATH '@documentName', doc xml PATH '.') as T ;

  21. 4. Split a doc – example (cont’d) <Return xmlns="http://www.irs.gov/efile" ...> <ReturnHeader ...> <ReturnId>01151620071030001526</ReturnId> ... </ReturnHeader> <ReturnData documentCount="30040"> <IRS1065 documentId="IRS1065" documentName="IRS1065" ...> … </IRS1065> <IRS1065ScheduleD ...> ... </IRS1065cheduleD> ... </Return> }

  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-Employee 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.CNT+1 as "rowspan"), D.DEPTNO), XMLELEMENT(NAME TD, XMLATTRIBUTES( X.CNT+1 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. 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

  25. 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/billTo' PASSING xmlpo), XMLElement(NAME “purchaseOrderNo”, PO.ponumber), XMLQUERY(‘/purchaseorder/items’ PASSING xmlpo), XMLElement(NAME "amount", XMLQuery ('fn:sum(/purchaseOrder/items/item/xs:decimal(USPrice))' PASSING xmlpo) ) ) ) FROM PurchaseOrders PO WHERE PO.ponumber = ‘200300001’;

  26. 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 '@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’;

  27. 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 => nested XML doc • Multiple tables (F-P key relationship) => nested XML doc

  28. 7. Generate XML data for relational data – Example 1 • Simple output – single table, each row => 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);

  29. 7. Generate XML – Example 2: One table => nested doc • Start from the innermost: SELECT ONE, TWO, XMLAGG( XMLELEMENT(NAME "text", TEXT) ORDER BY TEXT ) X FROM MYTABLE GROUP BY ONE, TWO =>

  30. 7. Generate XML – Example 2: One table => 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 ; =>

  31. 7. Generate XML – Example 2: One table => 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; =>

  32. 7. Generate XML – Example 3: multiple tables => nested doc DEPT(DEPTNO, DEPTNAME) -> 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';

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

  34. 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 '../../@orderDate', "shipTo City" VARCHAR(20) PATH '../../shipTo/city', "shipTo State" CHAR(2) PATH '../../shipTo/state', "Part #" CHAR(6) PATH '@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;

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

  36. 9. Invoke Web services - Example SELECT LONG, LAT FROM XMLTABLE(‘/LatLongReturn’ PASSING XMLPARSE(DB2XML.SOAPHTTPV('http://ws.cdyne.com/ziptogeo/zip2geo.asmx', 'http://ws.cdyne.com/GetLatLong', '<m:GetLatLong xmlns:m="http://ws.cdyne.com" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <m:zipcode>95123</m:zipcode> <m:LicenseKey>0</m:LicenseKey> </m:GetLatLong>')) COLUMNS LONG DECIMAL(5,2) PATH ‘AvgLongitude’, LAT DECIMAL(5,2) PATH ‘AvgLatitude’ ) X;

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

  38. 10. Use XML for object persistence - Idea • Object.serialize() => XML, insert into table • SELECT xobj FROM PT WHERE XMLEXISTS…xobj.deserialize() => Object

  39. Q & A • send emails to db2zxml@us.ibm.com

  40. References on DB2 z/OS pureXML • Detailed introduction presentation: ftp://ftp.software.ibm.com/software/data/db2zos/db29zospureXMLgzhang.pdf • DB2 z/OS XML Guide: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.xml/bknxspsh.htm • IBM developerWorks DB2 XML (LUW): http://www.ibm.com/developerworks/db2/zones/xml/index.html • Google pureXML wiki

More Related