Transcript An XML Document's Life

An XML Document’s Life – Dr. Node!
Donna Di Carlo
Terri Grissom, Michael Murley
BMC Software, Inc.
Click to edit Master title style
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
2
Click to edit Master title style
Acknowledgements / Disclaimers
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.
3
Click to edit Master title style
XML Data Type
•
•
•
•
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
4
5
Click to edit Master title style
Create Table with XML Data Type
Create Table IDUG.SCHEDULE
( IDUGID
Integer,
SCHEDULE
XML)
Base Ts
Docid Ix
In XMLIDUG.CONF;
XML Ts
Or
Nodeid Ix
Alter Table IDUG.SCHEDULE
Add Column SCHEDULE XML;
Yes
No
XMLIDUG.CONF
XMLIDUG.IRDOCIDC
XMLIDUG.XCON0000
XMLIDUG.IRNODEID
ZPARM IMPDSDEF
All objects materialized
Only Base Ts materialized
6
Click to edit Master title style
SYSXMLRELS relates Base and XML table
Reference
Base table
with SQL not the XML
table
Select XMLTBOWNER, XMLTBNAME
From SYSIBM.SYSXMLRELS
Where TBOWNER = ‘IDUG’ AND
TBNAME = ‘CONFERENCE’ AND
COLNAME = ‘SCHEDULE’
XMLTBOWNER
XMLTBNAME
IDUG
XCONFERENCE
7
Click to edit Master title style
Detail of Implicitly Created Objects
Create Table IDUG.Conference
(IDUGID
Integer
Schedule XML)
Base Table: IDUG.Conference
Xml Table:IDUG.XConference
IDUGID
Integer
Docid
Bigint
Schedule
Varchar(14)*
Min_NodeID
Varbin(128)
DB2_Generated
_Docid_for_Xml
Bigint
Xmldata
Varbin(15850)
Start_Ts*
Binary(8)*
End_Ts*
Binary(8)*
Docid
Index
NodeID
Index
Docid
Docid
Xmldata
End_Ts*
Start_Ts*
* Indicates DB2 V10 Multi-Versioned lengths and columns
8
Click to edit Master title style
XML Space Determined by Base Space
Base
Simple
XML
Partition by
Growth
UTS
Base
Classic
Partitioned
XML
Range
Partitioned
UTS
Segmented
Partition By
Growth
UTS
XML tablespace and base
tablespace grow
independently.
Range
Partitioned
UTS
XML document in partition
number corresponding to
base row partition number.
9
Click to edit Master title style
XML Basics – Elements and Attributes
Start
Tag
<name>John Doe</name>
Elements can contain:
1) Text
2) Other Elements
3) Attributes
End
Tag
Attribute
<patient>
id and name
are siblings
<id>1050</id>
Root
Element
<name prefix=“MR”>
<last>Doe</last>
last, first, and
middle are
siblings
<first>John</first>
<middle></middle>
</name>
</patient>
Empty
Element
10
Click to edit Master title style
XML Document with all statement types
<?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>
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
Click to edit Master title style
Mapping of Statement Type to Node Type
Node Type
<?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>
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
Text
x’45’
x’54’
Comments
Comment
x’43’
Declaration
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
Click to edit Master title style
Document ‘Skeleton’ – Nodes
Node Types that correspond to your XML:
•
•
•
•
•
•
•
•
Document
Namespace
Doctype
Element
Attribute
Text
Comment
Processing
Instruction
x’44’
x’4E’
x’42’
x’45’
x’41’
x’54’
x’43’
Ascii D
Ascii N
Ascii B
Ascii E
Ascii A
Ascii T
Ascii C
x’50’ Ascii P
Node Types to span documents across records:
• Continue
x’55’ Ascii U
• Range Proxy x’52’ Ascii R
Stored in all Nodes:
Node Type byte
Flags:
Proxy Flag
Continued Flag
Spanned Flag
No Escape Flag
Node Length
Local Node id Length
Local Node id Value
Every Node is
assigned an ID
13
Click to edit Master title style
Initial Node ID Assignment Rules
•
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”
02
02
02
04
04
06
Local Id: 04
Absolute Id: 020204
02
ID of child
added to
0204?
02
02
ID of sibling
added after
020206?
04
04
06
08
02
14
Click to edit Master title style
An Example – Meet the Patient
<?xml version=“1.0” encoding=“IBM037”?>
<IDUG>
<conf>EMEA 2012</conf>
<session day=“Monday”>
<id>J12</id>
<speaker>Terri Grissom</speaker>
</session>
</IDUG>
Quiz:
What is the Root Element?
IDUG
IDUG has how many children?
2
Document
x’44’
Element
x’45’
Text
x’54’
Attribute
x’41’
1.0
1) IDUG
1) EMEA 2012
IBM037
2) conf
2) J12
1) day=
“Monday”
3) session
3) Terri
Grissom
Node
Type
Byte
4) id
5) speaker
10 nodes
15
Click to edit Master title style
Surgery (Parsing)
Node ID
02
Document
Root Node
02 Element
IDUG
02 Element
conf
02 Text
EMEA 2012
<?xml version=“1.0” encoding=“IBM037”?>
<IDUG>
<conf>EMEA 2012</conf>
<session day=“Monday”>
<id>J12</id>
<speaker>Terri Grissom</speaker>
</session>
</IDUG>
04 Element
session
02 Attribute
day=Monday
04 Element
id
06 Element
Speaker
02 Text
J12
02 Text
Terri Grissom
16
Click to edit Master title style
Surgery Assistant - SYSXMLSTRINGS
Select STRINGID, HEX(STRINGID), STRING
From SYSIBM.SYSXMLSTRINGS
Where STRINGID in (1261,1341,1342,1343,1011,1346,1348);
Stringid
stored
in node
in XML
table
SYSIBM.SYSXMLSTRINGS
STRINGID
HEX(STRINGID)
STRING
1261
000004ED
IBM037
Encoding Scheme
1341
0000053D
IDUG
Element
1342
0000053E
conf
Element
1343
0000053F
session
Element
1011
000003F3
id
Element
1346
00000542
speaker
Element
1348
00000544
day
Attribute
17
Click to edit Master title style
DSN1PRNT – 10 nodes
IDUG
Document
conf
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
.......Terri Grissom
10001601 02000E54 65727269 20477269 73736F6D 20
Emea
2012
Node type x’44’
Node type x’45’
02
Document
02 Element
IDUG
02 Element
conf
Node type x’54’
02 Text
EMEA 2012
day=
Monday
04 Element
session
02 Attribute
day=Monday
Node type x’41’
Node type x’45’
04 Element
id
06 Element
Speaker
02 Text J12
02 Text
Terri
Grissom
session
18
Click to edit Master title style
Nodes Under a Microscope
Node
Type
Node
Lgth
Node id
Lgth/
Value
#
Child
ren
Stringid
Sysxmlstrings
IBM037
Xml Version 1
Document Node: 44 00 00C9
01 02
0001 0000000031 000004ED 7800000000000000
IDUG
Element Node: 45 00 00B0
Element Node: 45 00 0025
01 02
0002 00000000
01 02 0001 00000000
Txt Lgth
Text Node: 54 10 0011
01 02 0009
0000053D 00000000
0000053E 00000000
conf
Text: EMEA 2012
454D4541 20323031 32
02
Document
RECORD:
00800000
00010000
00000000
01000000
00770104
00000000
5410000B
10001601
XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251
00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF
00000000 00000000 440000C9 01020001 00000000
00450000 B0010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4A313245 00002A01 06000100 00000000
02000E54 65727269 20477269 73736F6D 20
PGSLTH='00FB'X
FF001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00054200 00000054
02 Element
IDUG
02 Element
conf
02 Text
EMEA 2012
04 Element
session
19
Click to edit Master title style
Nodes Under a Microscope
Node
Type
Node
Lgth
Node id
Lgth/
Value
#
Child
ren
Stringid
Sysxmlstrings
session
Element Node: 45 00 0077
01 04
0003 00000000
0000053F 00000000
Stringid : day
Lgth
Value : Monday
Attribute Node: 41 10 001A 01 02 00000000 00000544 00000000 0006 4D6F6E646179
Element Node: 45 00 001F 01 04 0001
Txt Lgth
Text Node: 54 10 000B 01 02
RECORD:
00800000
00010000
00000000
01000000
00770104
00000000
5410000B
10001601
0003
id
00000000 000003F3 00000000
Value: J12
4A3132
XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251
00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF
00000000 00000000 440000C9 01020001 00000000
00450000 B0010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4A313245 00002A01 06000100 00000000
02000E54 65727269 20477269 73736F6D 20
PGSLTH='00FB'X
FF001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00054200 00000054
04 Element
session
02 Attribute
day=Monday
04 Element
id
06 Element
speaker
02 Text
J12
02 Text
Terri Grissom
Click to edit Master title style
Summary of how sample document was stored
• 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
20
Click to edit Master title style
Multi-Versioning – more versions of Dr. Node!
•
•
•
•
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
21
22
Click to edit Master title style
Introduction to Versioning
DSN1PRNT of XML Tablespace:
RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251 PGSLTH='00FB'X PGSOBD='0000'X
00800000 00000000 01C9C8A7 0A421AEE A4FFFFFF FFFFFFFF FF001D00 1E020010
PGSBID='01'X
00010000 00000000 00000000 440000C9 01020001 00000000 31000004 ED780000
Base Tablespace
XML Tablespace
Docid
008000000000000001
Docid
008000000000000001
XML
Indicator,
offset x’02’
X’0000’
# times XML document
was updated
Start_Ts
x’C9C8A70A421AEEA4’
End_Ts
x’FFFFFFFFFFFFFFFF’
XML
Indicator,
offset x’06’
x’C9C8A70A421AEEA4’
Points to current version
in the xml ts.
Min Node
ID
02
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
Click to edit Master title style
XMLMODIFY – Update
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;
04 Element
session
02 Text
K13
02 Attribute
day=Monday
04 Element
id
06 Element
speaker
02 Text J12
02 Text
Terri Grissom
24
Click to edit Master title style
XMLMODIFY – Insert
Insert node company:
Insert Before/After - -> Create sibling
Update IDUG.CONFERENCE
Insert Into
- -> Create child
Set SCHEDULE = XMLMODIFY (
‘insert node $co after /IDUG/session/id’,
XMLPARSE (document ‘<company>BMC Software</company>’)
as “co”)
session now has 4 children
Where IDUGID = 1000; 02 Element
IDUG
Node id 0580
between 04
and 06
02 Element
conf
04 Element
session
02 Attribute
day=Monday
04 Element
Id
06 Element
Speaker
0580
Element
company
02 Text
K13
02 Text
Terri Grissom
02 Text
BMC
Software
25
Click to edit Master title style
XMLMODIFY - Delete
Delete attribute day node:
Update IDUG.CONFERENCE
Set SCHEDULE = XMLMODIFY (
‘delete nodes /IDUG/session/@day’)
Where IDUGID = 1000;
@ is a
shortcut for
attribute::day
04 Element
session
02 Attribute
day=Monday
04 Element
id
0580 Element
company
06 Element
speaker
02 Text K13
02 Text BMC
Software
02 Text
Terri Grissom
Click to edit Master title style
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
26
27
Click to edit Master title style
XML TS after 1 XMLMODIFY command
START_TS
END_TS
Original XML document
RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251
00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F
00010000 00000000 00000000 440000C9 01020001 00000000
Document Node Length
After XMLMODIFY – Update id to K13
RECORD: XOFFSET='010F'X PGSFLAGS='02'X PGSLTH=251
00800000 00000000 01C9C8A7 4326026F 2AFFFFFF FFFFFFFF
00010000 00000000 00000000 440000C9 01020001 00000000
PGSLTH='00FB'X
2A001D00 1E020010
31000004 ED780000
PGSLTH='00FB'X PG
FF001D00 1E020010
31000004 ED780000
28
Click to edit Master title style
XML TS after 2 XMLMODIFY commands
START_TS
END_TS
Original XML document
RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251
00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F
00010000 00000000 00000000 440000C9 01020001 00000000
Document Node Length
After XMLMODIFY – Update id to K13
RECORD: XOFFSET='010F'X PGSFLAGS='02'X PGSLTH=251
00800000 00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5
00010000 00000000 00000000 440000C9 01020001 00000000
PGSLTH='00FB'X
2A001D00 1E020010
31000004 ED780000
PGSLTH='00FB'X
A6001D00 1E020010
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
29
Click to edit Master title style
XML TS after 3 XMLMODIFY commands
START_TS
END_TS
Original XML document
RECORD: XOFFSET='0014'X PGSFLAGS='02'X PGSLTH=251
00800000 00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F
00010000 00000000 00000000 440000C9 01020001 00000000
Document Node Length
After XMLMODIFY – Update id to K13
RECORD: XOFFSET='010F'X PGSFLAGS='02'X PGSLTH=251
00800000 00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5
00010000 00000000 00000000 440000C9 01020001 00000000
After XMLMODIFY – Insert company
RECORD: XOFFSET='020A'X PGSFLAGS='02'X PGSLTH=292
00800000 00000000 01C9C8A7 587FA5E5 A6C9C8A7 6EF923E8
00010000 00000000 00000000 440000F2 01020001 00000000
Document Node Length
After XMLMODIFY – Delete attribute
RECORD: XOFFSET='032E'X PGSFLAGS='02'X PGSLTH=266
00800000 00000000 01C9C8A7 6EF923E8 A3FFFFFF FFFFFFFF
00010000 00000000 00000000 440000D8 01020001 00000000
Document Node Length
PGSLTH='00FB'X
2A001D00 1E020010
31000004 ED780000
PGSLTH='00FB'X
A6001D00 1E020010
31000004 ED780000
PGSLTH='0124'X
A3001D00 1E020010
31000004 ED780000
PGSLTH='010A'X
FF001D00 1E020010
31000004 ED780000
30
Click to edit Master title style
Base Table Points to Current Version
Base Table
XML Table
XML Ind offset x’06’
# upd
Start_ts
End_ts
x’C9C8A76EF923E8A3’
0003
x’C9C8A70A421AEEA4’
x’C9C8A74326026F2A’
Original
x’C9C8A74326026F2A’
x’C9C8A7587FA5E5A6’
K13 update
x’C9C8A7587FA5E5A6’
X’C9C8A76EF923E8A3’
Insert company
x’C9C8A76EF923E8A3’
X’FFFFFFFFFFFFFFFF’
Delete attribute
31
Click to edit Master title style
DSN1PRNT Before Reorg – 4 records
RECORD:
00800000
00010000
00000000
01000000
00770104
00000000
5410000B
10001601
RECORD:
00800000
00010000
00000000
01000000
00770104
00000000
5410000B
10001601
RECORD:
00800000
00010000
00000000
01000000
00A00104
00000000
5410000B
54100014
00000542
RECORD:
00800000
00010000
00000000
01000000
00860104
03F30000
05430000
00010000
736F6D20
XOFFSET='0014'X PGSFLAGS='00'X PGSLTH=251
00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F
00000000 00000000 440000C9 01020001 00000000
00450000 B0010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4A313245 00002A01 06000100 00000000
02000E54 65727269 20477269 73736F6D 20
XOFFSET='010F'X PGSFLAGS='00'X PGSLTH=251
00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5
00000000 00000000 440000C9 01020001 00000000
00450000 B0010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4B313345 00002A01 06000100 00000000
02000E54 65727269 20477269 73736F6D 20
XOFFSET='020A'X PGSFLAGS='02'X PGSLTH=292
00000000 01C9C8A7 587FA5E5 A6C9C8A7 6EF923E8
00000000 00000000 440000F2 01020001 00000000
00450000 D9010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00040000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4B313345 00002902 05800001 00000000
0102000C 424D4320 536F6674 77617265 4500002A
00000000 54100016 0102000E 54657272 69204772
XOFFSET='032E'X PGSFLAGS='02'X PGSLTH=266
00000000 01C9C8A7 6EF923E8 A3FFFFFF FFFFFFFF
00000000 00000000 440000D8 01020001 00000000
00450000 BF010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004500 001F0104
00005410 000B0102 00034B31 33450000 29020580
00005410 00140102 000C424D 4320536F 66747761
00000000 05420000 00005410 00160102 000E5465
PGSLTH='00FB'X
2A001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00054200 00000054
PGSLTH='00FB'X
A6001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00054200 00000054
PGSLTH='0124'X
A3001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00000543 00000000
01060001 00000000
6973736F 6D20
PGSLTH='010A'X
FF001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00010000 00000000
00010000 00000000
72654500 002A0106
72726920 47726973
Current Version
32
Click to edit Master title style
DSN1PRNT After Reorg
RECORD:
00800000
00010000
00000000
01000000
00770104
00000000
5410000B
10001601
RECORD:
00800000
00010000
00000000
01000000
00770104
00000000
5410000B
10001601
RECORD:
00800000
00010000
00000000
01000000
00A00104
00000000
5410000B
54100014
00000542
RECORD:
00800000
00010000
00000000
01000000
00860104
03F30000
05430000
00010000
736F6D20
XOFFSET='0014'X PGSFLAGS='00'X PGSLTH=251
00000000 01C9C8A7 0A421AEE A4C9C8A7 4326026F
00000000 00000000 440000C9 01020001 00000000
00450000 B0010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4A313245 00002A01 06000100 00000000
02000E54 65727269 20477269 73736F6D 20
XOFFSET='010F'X PGSFLAGS='00'X PGSLTH=251
00000000 01C9C8A7 4326026F 2AC9C8A7 587FA5E5
00000000 00000000 440000C9 01020001 00000000
00450000 B0010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4B313345 00002A01 06000100 00000000
02000E54 65727269 20477269 73736F6D 20
XOFFSET='020A'X PGSFLAGS='02'X PGSLTH=292
00000000 01C9C8A7 587FA5E5 A6C9C8A7 6EF923E8
00000000 00000000 440000F2 01020001 00000000
00450000 D9010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00040000 00000000 053F0000 00004110 001A0102
00064D6F 6E646179 4500001F 01040001 00000000
01020003 4B313345 00002902 05800001 00000000
0102000C 424D4320 536F6674 77617265 4500002A
00000000 54100016 0102000E 54657272 69204772
XOFFSET='032E'X PGSFLAGS='02'X PGSLTH=266
00000000 01C9C8A7 6EF923E8 A3FFFFFF FFFFFFFF
00000000 00000000 440000D8 01020001 00000000
00450000 BF010200 02000000 00000005 3D000000
00000005 3E000000 00541000 11010200 09454D45
00030000 00000000 053F0000 00004500 001F0104
00005410 000B0102 00034B31 33450000 29020580
00005410 00140102 000C424D 4320536F 66747761
00000000 05420000 00005410 00160102 000E5465
PGSLTH='00FB'X
2A001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00054200 00000054
PGSLTH='00FB'X
A6001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00054200 00000054
PGSLTH='0124'X
A3001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00000000 00000544
000003F3 00000000
00000543 00000000
01060001 00000000
6973736F 6D20
PGSLTH='010A'X
FF001D00 1E020010
31000004 ED780000
00450000 25010200
41203230 31324500
00010000 00000000
00010000 00000000
72654500 002A0106
72726920 47726973
Reorg does
not always
delete old
versions
Current Version
33
Click to edit Master title style
V10 is Date and Time friendly
• 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
Create Index IDUG.PO_ORDERDT
On IDUG.PURCHASEORD(PO)
Generate Key Using Xmlpattern
‘/PO/order/orderdt’ AS SQL DATE;
Prior to V10 only had:
As SQL VARCHAR(x)
As SQL DECFLOAT
Click to edit Master title style
Document with Date and Timestamps
<?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>
Valid Date Format:
yyyy-mm-dd
Valid Timestamp format:
yyyy-mm-ddThh:mm:ss
34
Click to edit Master title style
XML DATE Index
<PO>
<order>
<orderno>1000</orderno>
<custno>GR320</custno>
<orderdt>2012-01-31</orderdt>
….
Included in Index:
2012-01-31
Create Index IDUG.PO_ORDERDT
On IDUG.PURCHASEORD(PO)
Generate Key Using Xmlpattern
‘/PO/order/orderdt’ AS SQL DATE;
Example of <orderdt> that
would not be included in
Index:
2012-31-01
01-31-2012
2012-01-31T08:00:00
Format for SQL Date must be yyyy-mm-dd
35
36
Click to edit Master title style
EXPLAIN Access Path using DATE Index
Explain Plan Set Queryno = 1 For
Select ORDERNO
From IDUG.PURCHASEORD
WHERE
XMLEXISTS('/PO/order[orderdt>xs:date("2012-06-25")]‘ Passing PO);
XMLEXISTS:
Enclose compare in
brackets to avoid
Boolean predicate
Explain Results Before Index:
QUERYNO
QBLOCKNO PLANNO TNAME
ACCESSTYPE ACCESSNAME
1
1
R
1
PURCHASEORD
Table
Scan
37
Click to edit Master title style
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);
Table
Scan
Explain Results Before Index:
QUERYNO
QBLOCKNO PLANNO TNAME
ACCESSTYPE ACCESSNAME
1
1
R
1
PURCHASEORD
Create Index IDUG.PO_ORDERDT
On IDUG.PURCHASEORD(PO)
Generate Key Using Xmlpattern
‘/PO/order/orderdt’ AS SQL DATE;
Index
Access
Explain Results After Index:
QUERYNO
QBLOCKNO PLANNO TNAME
ACCESSTYPE ACCESSNAME
1
1
DX
1
PURCHASEORD
PO_ORDERDT
Click to edit Master title style
XML Timestamp format
• Valid Timestamp format:
yyyy-mm-ddThh:mm:ss.sssssssssssszzzzzz
Example: 2012-07-02T08:00:00.123456789012+06:00
Where:
yyyy
four digit year
Minimum required value:
mm
two digit month
yyyy-mm-ddThh:mm:ss
dd
two digit day
Ex: 2012-07-01T08:15:00
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)
38
Click to edit Master title style
XML TIMESTAMP Index
<item part=“2360”>
<qty>3</qty>
<shipdt>2012-07-02T14:00:00
</shipdt>
</item>
….
Included in Index:
2012-07-02T14:00:00
2012-07-02T14:00:00-06:00
2012-07-02T14:00:00Z
2012-07-02T14:00:00.1234
Create Index IDUG.PO_ORDERDT
On IDUG.PURCHASEORD(PO)
Generate Key Using Xmlpattern
‘/PO/order/items/item/shipdt’
AS SQL TIMESTAMP;
Example of <shipdt> that
would not be included in
Index:
2012-07-02
2012-07-02T14
2012-07-02T14:00:123456789
Format is yyyy-mm-ddThh:mm:ss.sssssssssssszzzzzz
39
40
Click to edit Master title style
EXPLAIN Access Path Using Timestamp Index
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);
XMLEXISTS:
Enclose compare in
brackets to avoid
Boolean predicate
Explain Results Before Index:
QUERYNO
QBLOCKNO PLANNO TNAME
ACCESSTYPE ACCESSNAME
1
1
R
1
PURCHASEORD
Table
Scan
41
Click to edit Master title style
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);
Table
Scan
Explain Results Before Index:
QUERYNO
QBLOCKNO PLANNO TNAME
ACCESSTYPE ACCESSNAME
1
1
R
1
PURCHASEORD
Create Index IDUG.PO_SHIPDT
On IDUG.PURCHASEORD(PO)
Generate Key Using Xmlpattern
‘/PO/order/items/item/shipdt’ AS SQL TIMESTAMP;
Index
Access
Explain Results After Index:
QUERYNO
QBLOCKNO PLANNO TNAME
ACCESSTYPE ACCESSNAME
1
1
DX
1
PURCHASEORD
PO_SHIPDT
Click to edit Master title style
XML Index Tips
• “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.
42
Terri Grissom
BMC Software, Inc.
[email protected]