1 / 43

An XML Document’s Life – Dr. Node!

An XML Document’s Life – Dr. Node! . Donna Di Carlo Terri Grissom, Michael Murley BMC Software, Inc. Agenda – Meet Dr. Node!. Overview of XML Data Type Parse document into a tree of nodes Examine nodes in a Parsed Document DB2 V10 XML features Multi-Versioning XMLMODIFY

trella
Download Presentation

An XML Document’s Life – Dr. Node!

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. An XML Document’s Life – Dr. Node! Donna Di Carlo Terri Grissom, Michael Murley BMC Software, Inc.

  2. Agenda – Meet Dr. Node! • Overview of XML Data Type • Parse document into a tree of nodes • Examine nodes in a Parsed Document • DB2 V10 XML features • Multi-Versioning • XMLMODIFY • XML Index Considerations • DB2 V10 DATE and TIMESTAMP Index types

  3. IBM®, DB2®, z/OS® are registered service marks and trademarks of International Business Machines Corporation, in the United States and/or other countries DSN1PRNT output references contained are also of IBM. The information contained in this presentation has not been submitted to any formal review and is distributed on an “As Is” basis. Acknowledgements / Disclaimers

  4. XML Data type introduced in DB2 V9, enhanced in V10 • pureXML is DB2 implementation • Part of ANSI SQL Standard • SQL/XML extension • Handling functions – XMLQUERY, XMLTABLE, XMLEXISTS • Conversion functions – XMLCAST, XMLPARSE, XMLSERIALIZE • XML indexes to improve performance • Optional Schema Validation XML Data Type

  5. Create Table IDUG.SCHEDULE ( IDUGID Integer, SCHEDULE XML) In XMLIDUG.CONF; Or Alter Table IDUG.SCHEDULE Add Column SCHEDULE XML; Create Table with XML Data Type ZPARM IMPDSDEF Yes All objects materialized No Only Base Ts materialized

  6. Select XMLTBOWNER, XMLTBNAME From SYSIBM.SYSXMLRELS Where TBOWNER = ‘IDUG’ AND TBNAME = ‘CONFERENCE’ AND COLNAME = ‘SCHEDULE’ SYSXMLRELS relates Base and XML table Reference Base table with SQL -not the XML table

  7. Detail of Implicitly Created Objects * Indicates DB2 V10 Multi-Versioned lengths and columns

  8. XML Space Determined by Base Space XML tablespace and base tablespace grow independently. XML document in partition number corresponding to base row partition number.

  9. XML Basics – Elements and Attributes Elements can contain: 1) Text 2) Other Elements 3) Attributes Start Tag End Tag <name>John Doe</name> Attribute <patient> <id>1050</id> <name prefix=“MR”> <last>Doe</last> <first>John</first> <middle></middle> </name> </patient> id and name are siblings Root Element last, first, and middle are siblings Empty Element

  10. <?xml version=“1.0” encoding=“IBM037”?> <!DOCTYPE patient [ <!ENTITY doctor “Dr Node”> ]> <?xml-stylesheet type="text/xsl" href="style.xsl"?> <patient xmlns=“http://patient.org”> <id>1050</id> <name prefix="MR"> <last>Doe</last> <first>John</first> <!-- No middle name for John --> <!-- <middle></middle> --> </name> <doc>&doctor;</doc> </patient> XML Document with all statement types Declaration Document Type Definition Processing Instruction Namespace Attribute Elements with Text Comments Entity Reference This slide represents every type of XML statement that can make up a document, but not necessarily all of the syntax for each statement.

  11. <?xml version=“1.0” encoding=“IBM037”?> <!DOCTYPE patient [ <!ENTITY doctor “Dr Node”> ]> <?xml-stylesheet type="text/xsl" href="style.xsl"?> <patient xmlns=“http://patient.org”> <id>1050</id> <name prefix="MR"> <last>Doe</last> <first>John</first> <!-- No middle name for John --> <!-- <middle></middle> --> </name> <doc>&doctor;</doc> </patient> Mapping of Statement Type to Node Type Node Type Declaration Document x’44’ Document Type Defn Doctype x’42’ Processing Instr Processing Instr x’50’ Namespace Namespace x’4E’ Attribute Attribute x’41’ Elements & Text Element x’45’ Text x’54’ Comments Comment x’43’ Example: <first>John</first> Element x’45’ – stores element ‘first’* Text x’54’ – stores text ‘John’ *SYSXMLSTRINGS stores element names and assigns a 4 byte stringid

  12. Document x’44’ Ascii D • Namespace x’4E’ Ascii N • Doctype x’42’ Ascii B • Element x’45’ Ascii E • Attribute x’41’ Ascii A • Text x’54’ Ascii T • Comment x’43’ Ascii C • Processing Instruction x’50’ Ascii P • Continue x’55’ Ascii U • Range Proxy x’52’ Ascii R Document ‘Skeleton’ – Nodes Node Types that correspond to your XML: Node Types to span documents across records: Every Node is assigned an ID

  13. Each node is assigned an ID: • Variable length, 1 to 8 bytes • Rightmost byte is always even; other bytes are odd • Ex: 02 is valid node id; 01 is invalid • Ex: A102 is valid node id; A1 is invalid • Starts at “02” and ascends by 2 for sibling nodes • Example: 02, 04… 9C, 9E, A0, A102, A104…A2… • At each subsequent branch of the tree, the ID starts over at “02” Initial Node ID Assignment Rules ID of child added to 0204? ID of sibling added after 020206? Local Id: 04 Absolute Id: 020204

  14. <?xml version=“1.0” encoding=“IBM037”?> <IDUG> <conf>EMEA 2012</conf> <session day=“Monday”> <id>J12</id> <speaker>Terri Grissom</speaker> </session> </IDUG> An Example – Meet the Patient Quiz: What is the Root Element? IDUG has how many children? IDUG 2 10 nodes Node Type Byte

  15. Surgery (Parsing) <?xml version=“1.0” encoding=“IBM037”?> <IDUG> <conf>EMEA 2012</conf> <session day=“Monday”> <id>J12</id> <speaker>Terri Grissom</speaker> </session> </IDUG> Node ID Root Node

  16. Select STRINGID, HEX(STRINGID), STRING From SYSIBM.SYSXMLSTRINGS Where STRINGID in (1261,1341,1342,1343,1011,1346,1348); Surgery Assistant - SYSXMLSTRINGS Stringid stored in node in XML table

  17. RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X PGSOBD='0000'X PGSBID='01'X 00800000 00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF FF001D00 1E020010 .............B.................. 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 ............D...........1....x.. 00000000 00450000 B0010200 02000000 00000005 3D000000 00450000 25010200 .....E..............=....E..%... 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 ........>....T.......EMEA 2012E. 00770104 00030000 00000000 053F0000 00004110 001A0102 00000000 00000544 .w...........?....A............D 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 ......MondayE................... 5410000B 01020003 4A313245 00002A01 06000100 00000000 00054200 00000054 T.......J12E..*...........B....T 10001601 02000E54 65727269 20477269 73736F6D 20 .......Terri Grissom DSN1PRNT – 10 nodes IDUG IDUG Document conf conf Node type x’44’ Emea 2012 Emea 2012 day= Monday session session Node type x’45’ Node type x’45’ Node type x’45’ Node type x’45’ Node type x’45’ Node type x’54’ Node type x’54’ Node type x’41’

  18. Nodes Under a Microscope IBM037 Xml Version 1 Document Node: 44 0000C901 02 00010000000031000004ED7800000000000000 IDUG Element Node: 450000B001020002000000000000053D00000000 conf Element Node: 45 00002501 020001 000000000000053E00000000 Txt Lgth Text: EMEA 2012 Text Node: 54 100011 01 02 0009 454D4541 20323031 32 RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 00000000 00450000 B0010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00770104 00030000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4A313245 00002A01 06000100 00000000 00054200 00000054 10001601 02000E54 65727269 20477269 73736F6D 20

  19. Nodes Under a Microscope session Element Node: 45 000077 01 04 0003 00000000 0000053F 00000000 Stringid : day Lgth Value : Monday Attribute Node: 41 10 001A 01 02 00000000 00000544 00000000 0006 4D6F6E646179 id Element Node: 45 00001F 01 04 0001 00000000000003F3 00000000 Txt Lgth Value: J12 Text Node: 54 10000B 01 02 0003 4A3132 RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 00000000 00450000 B0010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00770104 00030000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4A313245 00002A01 06000100 00000000 00054200 00000054 10001601 02000E54 65727269 20477269 73736F6D 20

  20. Document was parsed into 10 nodes • Order of nodes reflects a hierarchy • Document Node first • Root Element Node (IDUG) next • Tree of nodes stored top-down, left to right • Children nodes stored before Siblings • Ex: Text of conf (EMEA 2012) before conf sibling (session) • Each node is assigned an id based on its tree position • Element and Attribute names stored in SYSIBM.SYSXMLSTRINGS • Values assigned a 4 byte Stringid • 4 byte Stringid is stored in Element & Attribute Nodes Summary of how sample document was stored

  21. What is it? • New in V10 - supports multiple versions of an XML document • Benefits? • Improves concurrency thru lock avoidance • Allows sub-document update via XMLMODIFY • Prerequisites? • Automatic if V10 and Base table is a Universal Tablespace • How is it implemented? • Base table • XML column increases from Varchar(6) to Varchar(14) • Extra 8 bytes used to point to current version in XML table • XML table • Addition of Start_ts and End_ts fields Multi-Versioning – more versions of Dr. Node!

  22. RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X PGSOBD='0000'X PGSBID='01'X 00800000 00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 Introduction to Versioning DSN1PRNT of XML Tablespace: Currently only 1 version of the XML document in the XML tablespace. END_TS of all x’FF’ in the XML table indicates the current version.

  23. Replace value of id from J12 to K13: Update IDUG.CONFERENCE Set SCHEDULE = XMLMODIFY ( ‘replace value of node /IDUG/session/id with “K13” ’) Where IDUGID = 1000; XMLMODIFY – Update

  24. Insert node company: Update IDUG.CONFERENCE Set SCHEDULE = XMLMODIFY ( ‘insert node $co after /IDUG/session/id’, XMLPARSE (document ‘<company>BMC Software</company>’) as “co”) Where IDUGID = 1000; XMLMODIFY – Insert Insert Before/After - -> Create sibling Insert Into - -> Create child session now has 4 children Node id 0580 between 04 and 06

  25. Delete attribute day node: Update IDUG.CONFERENCE Set SCHEDULE = XMLMODIFY ( ‘delete nodes /IDUG/session/@day’) Where IDUGID = 1000; XMLMODIFY - Delete @ is a shortcut for attribute::day

  26. XML TS after Original Insert START_TS END_TS Original XML document RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 Document Node Length

  27. XML TS after 1 XMLMODIFY command START_TS END_TS Original XML document RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F 2A001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 After XMLMODIFY – Update id to K13 RECORD: XOFFSET='010F'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X PG 00800000 00000000 01C9C8A7 4326026F 2AFFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 Document Node Length

  28. XML TS after 2 XMLMODIFY commands START_TS END_TS Original XML document RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F 2A001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 After XMLMODIFY – Update id to K13 RECORD: XOFFSET='010F'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5 A6001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 After XMLMODIFY – Insert company RECORD: XOFFSET='020A'X PGSFLAGS='02'X PGSLTH=292 PGSLTH='0124'X 00800000 00000000 01C9C8A7 587FA5E5 A6FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000F2 01020001 00000000 31000004 ED780000 Document Node Length Document Node Length

  29. XML TS after 3 XMLMODIFY commands START_TS END_TS Original XML document RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F 2A001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 After XMLMODIFY – Update id to K13 RECORD: XOFFSET='010F'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5 A6001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 After XMLMODIFY – Insert company RECORD: XOFFSET='020A'X PGSFLAGS='02'X PGSLTH=292 PGSLTH='0124'X 00800000 00000000 01C9C8A7 587FA5E5 A6C9C8A7 6EF923E8 A3001D00 1E020010 00010000 00000000 00000000 440000F2 01020001 00000000 31000004 ED780000 After XMLMODIFY – Delete attribute RECORD: XOFFSET='032E'X PGSFLAGS='02'X PGSLTH=266 PGSLTH='010A'X 00800000 00000000 01C9C8A7 6EF923E8 A3FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000D8 01020001 00000000 31000004 ED780000 Document Node Length Document Node Length Document Node Length

  30. Base Table Points to Current Version Base Table XML Table

  31. DSN1PRNT Before Reorg – 4 records RECORD: XOFFSET='0014'X PGSFLAGS='00'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F 2A001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 00000000 00450000 B0010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00770104 00030000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4A313245 00002A01 06000100 00000000 00054200 00000054 10001601 02000E54 65727269 20477269 73736F6D 20 RECORD: XOFFSET='010F'X PGSFLAGS='00'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5 A6001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 00000000 00450000 B0010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00770104 00030000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4B313345 00002A01 06000100 00000000 00054200 00000054 10001601 02000E54 65727269 20477269 73736F6D 20 RECORD: XOFFSET='020A'X PGSFLAGS='02'X PGSLTH=292 PGSLTH='0124'X 00800000 00000000 01C9C8A7 587FA5E5 A6C9C8A7 6EF923E8 A3001D00 1E020010 00010000 00000000 00000000 440000F2 01020001 00000000 31000004 ED780000 00000000 00450000 D9010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00A00104 00040000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4B313345 00002902 05800001 00000000 00000543 00000000 54100014 0102000C 424D4320 536F6674 77617265 4500002A 01060001 00000000 00000542 00000000 54100016 0102000E 54657272 69204772 6973736F 6D20 RECORD: XOFFSET='032E'X PGSFLAGS='02'X PGSLTH=266 PGSLTH='010A'X 00800000 00000000 01C9C8A7 6EF923E8 A3FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000D8 01020001 00000000 31000004 ED780000 00000000 00450000 BF010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00860104 00030000 00000000 053F0000 00004500 001F0104 00010000 00000000 03F30000 00005410 000B0102 00034B31 33450000 29020580 00010000 00000000 05430000 00005410 00140102 000C424D 4320536F 66747761 72654500 002A0106 00010000 00000000 05420000 00005410 00160102 000E5465 72726920 47726973 736F6D20 Current Version

  32. DSN1PRNT After Reorg RECORD: XOFFSET='0014'X PGSFLAGS='00'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F 2A001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 00000000 00450000 B0010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00770104 00030000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4A313245 00002A01 06000100 00000000 00054200 00000054 10001601 02000E54 65727269 20477269 73736F6D 20 RECORD: XOFFSET='010F'X PGSFLAGS='00'X PGSLTH=251 PGSLTH='00FB'X 00800000 00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5 A6001D00 1E020010 00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000 00000000 00450000 B0010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00770104 00030000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4B313345 00002A01 06000100 00000000 00054200 00000054 10001601 02000E54 65727269 20477269 73736F6D 20 RECORD: XOFFSET='020A'X PGSFLAGS='02'X PGSLTH=292 PGSLTH='0124'X 00800000 00000000 01C9C8A7 587FA5E5 A6C9C8A7 6EF923E8 A3001D00 1E020010 00010000 00000000 00000000 440000F2 01020001 00000000 31000004 ED780000 00000000 00450000 D9010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00A00104 00040000 00000000 053F0000 00004110 001A0102 00000000 00000544 00000000 00064D6F 6E646179 4500001F 01040001 00000000 000003F3 00000000 5410000B 01020003 4B313345 00002902 05800001 00000000 00000543 00000000 54100014 0102000C 424D4320 536F6674 77617265 4500002A 01060001 00000000 00000542 00000000 54100016 0102000E 54657272 69204772 6973736F 6D20 RECORD: XOFFSET='032E'X PGSFLAGS='02'X PGSLTH=266 PGSLTH='010A'X 00800000 00000000 01C9C8A7 6EF923E8 A3FFFFFF FFFFFFFF FF001D00 1E020010 00010000 00000000 00000000 440000D8 01020001 00000000 31000004 ED780000 00000000 00450000 BF010200 02000000 00000005 3D000000 00450000 25010200 01000000 00000005 3E000000 00541000 11010200 09454D45 41203230 31324500 00860104 00030000 00000000 053F0000 00004500 001F0104 00010000 00000000 03F30000 00005410 000B0102 00034B31 33450000 29020580 00010000 00000000 05430000 00005410 00140102 000C424D 4320536F 66747761 72654500 002A0106 00010000 00000000 05420000 00005410 00160102 000E5465 72726920 47726973 736F6D20 Reorg does not always delete old versions Current Version

  33. Native XML date and Time support: • Data types: ex: xs:date, xs:time, xs:duration • Comparison operators: ex: op:date-equal, op:duration-equal • Functions: fn:current-date, fn:current-time, fn:day-from-date • Arithmetic operators: op:subtract-dates • XML Index types added for date and timestamp • Generate Key Using Xmlpattern … As SQL DATE • Generate Key Using Xmlpattern … As SQL TIMESTAMP V10 is Date and Time friendly Prior to V10 only had: As SQL VARCHAR(x) As SQL DECFLOAT Create Index IDUG.PO_ORDERDT On IDUG.PURCHASEORD(PO) Generate Key Using Xmlpattern ‘/PO/order/orderdt’ AS SQL DATE;

  34. <?xml version=“1.0” encoding=“IBM037”?> <PO> <order> <orderno>1000</orderno> <custno>GR320</custno> <orderdt>2012-06-24</orderdt> <items> <item part=“2360”> <qty>3</qty> <shipdt>2012-07-02T14:00:00</shipdt> </item> <item part=“2370”> <qty>5</qty> <shipdt>2012-07-03T10:40:00</shipdt> </item> </items> </order> </PO> Document with Date and Timestamps Valid Date Format: yyyy-mm-dd Valid Timestamp format: yyyy-mm-ddThh:mm:ss

  35. XML DATE Index <PO> <order> <orderno>1000</orderno> <custno>GR320</custno> <orderdt>2012-01-31</orderdt> …. Create Index IDUG.PO_ORDERDT On IDUG.PURCHASEORD(PO) Generate Key Using Xmlpattern ‘/PO/order/orderdt’ AS SQL DATE; Format for SQL Date must be yyyy-mm-dd

  36. EXPLAIN Access Path using DATE Index XMLEXISTS: Enclose compare in brackets to avoid Boolean predicate Explain Plan Set Queryno = 1 For Select ORDERNO From IDUG.PURCHASEORD WHERE XMLEXISTS('/PO/order[orderdt>xs:date("2012-06-25")]‘ Passing PO); TableScan Explain Results Before Index:

  37. Create Index IDUG.PO_ORDERDT On IDUG.PURCHASEORD(PO) Generate Key Using Xmlpattern ‘/PO/order/orderdt’ AS SQL DATE; EXPLAIN Access Path using DATE Index XMLEXISTS: Enclose compare in brackets to avoid Boolean predicate Explain Plan Set Queryno = 1 For Select ORDERNO From IDUG.PURCHASEORD WHERE XMLEXISTS('/PO/order[orderdt>xs:date("2012-06-25")]‘ Passing PO); TableScan Explain Results Before Index: Index Access Explain Results After Index:

  38. Valid Timestamp format: yyyy-mm-ddThh:mm:ss.sssssssssssszzzzzz Example: 2012-07-02T08:00:00.123456789012+06:00 Where: yyyy four digit year mm two digit month dd two digit day T Time-of-day separator hh two digit hours mm two digit minutes ss two digit seconds ssssssssssss 12 digit fractional seconds (optional) zzzzzz time zone ((‘+’ or ‘-‘)hh:mm) or ‘Z’ (optional) XML Timestamp format Minimum required value: yyyy-mm-ddThh:mm:ss Ex: 2012-07-01T08:15:00

  39. XML TIMESTAMP Index <item part=“2360”> <qty>3</qty> <shipdt>2012-07-02T14:00:00 </shipdt> </item> …. Create Index IDUG.PO_ORDERDT On IDUG.PURCHASEORD(PO) Generate Key Using Xmlpattern ‘/PO/order/items/item/shipdt’ AS SQL TIMESTAMP; Format is yyyy-mm-ddThh:mm:ss.sssssssssssszzzzzz

  40. EXPLAIN Access Path Using Timestamp Index XMLEXISTS: Enclose compare in brackets to avoid Boolean predicate Explain Plan Set Queryno = 1 For Select ORDERNO From IDUG.PURCHASEORD WHERE XMLEXISTS('/PO/order/items/item [shipdt>xs:dateTime("2011-12-31T24:00:00")]‘ Passing PO); TableScan Explain Results Before Index:

  41. Create Index IDUG.PO_SHIPDT On IDUG.PURCHASEORD(PO) Generate Key Using Xmlpattern ‘/PO/order/items/item/shipdt’ AS SQL TIMESTAMP; EXPLAIN Access Path Using Timestamp Index XMLEXISTS: Enclose compare in brackets to avoid Boolean predicate Explain Plan Set Queryno = 1 For Select ORDERNO From IDUG.PURCHASEORD WHERE XMLEXISTS('/PO/order/items/item [shipdt>xs:dateTime("2011-12-31T24:00:00")]‘ Passing PO); TableScan Explain Results Before Index: Index Access Explain Results After Index:

  42. “Lean” or fully qualified indexes perform better than “heavy” indexes • Use /IDUG/session/speaker instead of //speaker • XML Index may be used to evaluate XPath predicates in the XMLEXISTS and XMLTABLE functions. • XML Index will NOT be exploited with XMLQUERY function • XMLEXISTS – enclose comparison in square brackets to avoid Boolean comparison • XML index is eligible to evaluate XMLEXISTS if type match and Node containment. • For a DECFLOAT, DATE, or TIMESTAMP index: • String from document ignored if it cannot be converted into the index data type. • For a VARCHAR index: • If key value exceeds limit, INSERT or CREATE INDEX will fail. XML Index Tips

  43. Terri GrissomBMC Software, Inc.Donna_Di_Carlo@bmc.com

More Related