XML and DB2

1. XML and DB2 By Josephine Cheng, Jane Xu IBM Santa Teresa Laboratory Slides edited from the original slides of Yunyu Song

2. Outline • Net.Data • XML Extender • Create XML document from DB2 • Transform XML document to DB2 • Searching XML documents • Conclusion

3. Net.Data VI • A scripting language to create web applications which can access data from any ODBC compliant databases (e.g. DB2, Oracle, DRDA, ODBC, flat files and web registry data) • Can generate XML documents from SQL queries. • Its successor product is WebSphere Application server.

4. Generating XML documents from existing DB2 data • Net.Data Macro to generate XML Data

5. Generating XML documents from existing DB2 data • Resultset of XML data generated by Net.Data

6. Outline • Net.Data • XML Extender • Create XML document from DB2 • Transform XML document to DB2 • Searching XML documents • Conclusion

7. XML Extender • Repository for XML documents and their DTDs. • Data management functionalities (integrity, security, recoverability and management). • User has the option to store the entire document as an column or decomposed into multiple tables and columns. • Users can retrieve the entire document or extract XML elements and attributes dynamically in an SQL query.

8. XML Collection • SQL Collection Mapping It only works when you can collect all relevant data with a single SELECT statement, which is placed in the DAD. If multiple tables are involved you should use primary-foreign key relationships. • RDB_Node Collection Mapping It allows node definitions for multiple tables. All involved tables must have primary keys. This approach does not require SQL in the DAD. It does allow imports as well as exports.

9. XML Collection

10. XML Collection

11. XML Column • Store entire XML document in a column of XML type • Allows to query XML content • Associate and store DTD in DB2 for one or more document • Map element and attribute to DB2 table (side table) • Used when your XML storage is mainly archival or for reference, and document content is updated infrequently

12. XML Column

13. XML Column

14. DAD File • Define which DB2 tables hold the data • The mapping method: SQL Collection, RDB Node Collection, or XML Column • Whether to validate XML documents with a DTD before storing or translating the contents • Which DTD to use for validation and translation

15. Sample DAD (SQL Collection)

16. Sample DAD (RDB Node Collection)

17. Sample DAD (XMLColumn)

18. XML in DB2

19. Outline • Net.Data • XML Extender • Create XML document from DB2 • Transform XML document to DB2 • Searching XML documents • Conclusion

20. Create XML from DB2 • Generate XML fragments (SQL Collection) • Generate whole XML documents (RDB Node Collection)

21. Generate XML fragments (SQL Collection)

22. Examples I SELECT XML2CLOB( XMLELEMENT(NAME "Department", XMLATTRIBUTES(e.workdept AS "name"), XMLAGG(XMLELEMENT(NAME "emp", e.lastname) ORDER BY e.lastname) )) AS "dept_list" FROM employee e WHERE e.workdept IN ('C01','E21') GROUP BY workdeptThis query produces the following output. dept_list <Department name = "C01"> <emp>KWAN</emp> <emp>NICHOLLS</emp> <emp>QUINTANA</emp> </Department> <Department name = "E21"> <emp>GOUNOT</emp> <emp>LEE</emp> <emp>MEHTA</emp> <emp>SPENSER</emp> </Department>

23. Example II • REC2XML Fuction select rec2xml(1.0, 'COLATTVAL','MyRow',DEPTNO, DEPTNAME, MGRNO) AS MYROWS FROM DEPARTMENT MYROWS -------------------------------------------------------------------- <MyRow> <column name="DEPTNO">A00</column> <column name="DEPTNAME">SPIFFY COMPUTER SERVICE DIV.</column> <column name="MGRNO">000010</column> </MyRow> <MyRow> <column name="DEPTNO">B01</column> <column name="DEPTNAME">PLANNING</column> <column name="MGRNO">000020</column> </MyRow>

24. Compose XML documents(RDB Node)

25. Examples dxxGenXML(CLOB(100K) DAD, /* input */ char(resultTabName) resultTabName, /* input */ integer overrideType /* input */ varchar(1024) override, /* input */ integer maxRows, /* input */ integer numRows, /* output */ long returnCode, /* output */ varchar(1024) returnMsg) /* output */ dxxRetrieveXML(char(collectionName) collectionName, /* input */ char(resultTabName) resultTabName, /* input */ integer overrideType, /* input */ varchar_value override, /* input */ integer maxRows, /* input */ integer numRows, /* output */ long returnCode, /* output */ varchar(1024) returnMsg) /* output */

26. Outline • Net.Data • XML Extender • Create XML document from DB2 • Transform XML document to DB2 • Searching XML documents • Conclusion

27. Transform XML into DB2 • XML RDB_Node Collection • XML column

28. RDB_Node collection • In this case it is the reverse of generating XML from an RDB Node Collection. The end result of this is pure relational data, in tables, and ready to be accessed with the usual database tools. You can do Queries, indexes, views, optimizations etc. • dxxShredXML Takes the DAD as a parameter, similar to dxxGenXML Used for applications that do occasional updates • dxxInsertXML Uses a DAD already stored, similar to dxxRetrieveXML Used for applications that make regular updates Takes an enabled XML collection as input parameter

29. UDTs provided by XML extender(XML Column)

30. Store XML as XML Column in DB2 • Ensure that the database is XML-enabled • Optional: Locate or create a DTD for your documents and store it in DB2XML.DTD_REF. • Create the table to store the XML documents and add the XML column. Also create any side tables. • Create a DAD to map the XML to the relational DB structures. • Enable the XML column, specifying the DAD. • Create indexes for the side-tables. • Insert some XML documents and start performing queries.

31. Key Features of XML Column I • The XML extender supports a subset of XPath, providing a way to locate specific data within an XML document. • Combined with the provided extracttype functions (extractInteger, extractTimestamp, extractVarChar, etc.) you can retrieve values as database types.

32. Example I

33. Example II

34. Key features of XML Column II • You can select to copy specific element and attribute values to a side table. Doing that lets you search and manipulate the values directly via SQL just like any other column data, linking to the stored XML to pick up related values. • You can create side table indexes, to improve search time.

35. Use side table for fast search

36. Key features of XML Column III • XPath based element or attribute update UPDATE sales_tab SET order = Update(order, '/Order/Customer/Name', 'Customer X') WHERE sales_person = 'Salesperson Y' Using the Update function, you can correctly alters both the stored XML document and any relevant side tables.

37. Outline • Net.Data • XML Extender • Create XML document from DB2 • Transform XML document to DB2 • Searching XML documents • Conclusion

38. Searching XML docs (XML Column) • Example of XML Column

39. Searching XML documents (Column) • Using side tables. • Can make a view of the joint.

40. Searching XML documents (Column) • Example of query against attribute. • The UDF is expensive. Alternatives: • Use DAD to define CUSTOMER as another index in the side table. • Enable XML column to use text indexing (e.g. section search with db2tx.contains).

41. Searching XML documents (Collect) • Search can be done directly against the SQL based tables, or using procedures dxxGenXML() and dxxRetrieveXML(). • The DAD is used to specify whether to retrieve the entire document or a fragment. • The DAD is also used to specify the search criteria which can be based either on tables or SQL query.

42. Outline • Net.Data • XML Extender • Create XML document from DB2 • Transform XML document to DB2 • Searching XML documents • Conclusion

43. Conclusion: XML in DB2