DB2 9 A DBA Guide to Native XML

1
DB2 9 A DBA Guide to Native XML

2
Agenda and Purpose

• XML it looks easy enough
• XML in DB2 9
• XML testing 1 2 3
• XML and Performance
• Summary and Questions

• Understand XML and related technology
• DB2 9 implementation of native XML
• Have an idea how it looks in the real world
• Performance and design considerations for XML
• Be able to discuss options for DB2 XML
  applications

3
Why Pure XML

• 85 of information is unstructured
• 50 separate systems and 2 -3 ERP in average
  company
• 30 of peoples time is spent searching for
  relevant information

40 Exabyte's (4 x 10 to the 19th) of unique
information will be generated in 2007
4
Why XML
Information outlives technology
Information Outlives Technology

• 85 of information is unstructured
• XML can represent just about anything
• XML forces syntax-Level interoperability

5
XML Overview

• XML stands for Extensible Markup Language
• XML is a markup language much like HTML
• XML was designed to describe data
• XML tags are not predefined. You must define your
  own tags
• XML uses a XML Schema (XSD) to describe the data
  (DTD older technology)
• XML is a W3C Recommendation

6
XML Description - TAGS
lt?xml version"1.0"?gt ltcontact-infogt ltnamegtMike
Sniezeklt/namegt ltcompanygtBMC Softwarelt/companygt ltph
onegt(713) 918-8800lt/phonegt lt/contact-infogt
7
XML Validation
XML DOCUMENT lt?xml version"1.0" ?gt
ltcontact-infogt   ltnamegtMike Sniezeklt/namegt  
ltcompanygtBMC Softwarelt/companygt   ltphonegt(713)
918-8800lt/phonegt   lt/contact-infogt
XSD XML SCHEMA lt?xml version"1.0"
encoding"utf-8"?gt ltxsschema xmlnsmsdata"urnsc
hemas-microsoft-comxml-msdata" xmlns""
id"NewDataSet" xmlnsxs"http//www.w3.org/2001/X
MLSchema"gt ltxselement name"contact-info"gt
ltxscomplexTypegt ltxssequencegt
ltxselement minOccurs"0" name"name"
type"xsstring" /gt ltxselement
minOccurs"0" name"company" type"xsstring" /gt
ltxselement minOccurs"0" name"phone"
type"xsstring" /gt lt/xssequencegt
lt/xscomplexTypegt lt/xselementgt ltxselement
msdataIsDataSet"true" msdataUseCurrentLocale"t
rue" name"NewDataSet"gt ltxscomplexTypegt
ltxschoice minOccurs"0" maxOccurs"unbounded"gt
ltxselement ref"contact-info" /gt
lt/xschoicegt lt/xscomplexTypegt
lt/xselementgt lt/xsschemagt
8
XML Transformation

• XSL consists of three parts
• XSLT - a language for transforming XML documents
• XPath - a language for navigating in XML
  documents
• XSL-FO - a language for formatting XML documents

XML Document
XSL Style sheet
Parameters
XSL Transformation
Email
XML
Text
SQL
XHTML
WML
HTML
9
XML Data - Spread sheet used for examples
10
XML Data from Spreadsheet
lt?xml version"1.0" encoding"ISO-8859-1"?gt lt!--
Edited by XMLSpy --gt ltcataloggt ltcdgt lttitlegtEmp
ire Burlesquelt/titlegt ltartistgtBob
Dylanlt/artistgt ltcountrygtUSAlt/countrygt ltcompany
gtColumbialt/companygt ltpricegt10.90lt/pricegt ltyear
gt1985lt/yeargt lt/cdgt ltcdgt lttitlegtHide your
heartlt/titlegt ltartistgtBonnie Tylerlt/artistgt ltc
ountrygtUKlt/countrygt ltcompanygtCBS
Recordslt/companygt ltpricegt9.90lt/pricegt ltyeargt19
88lt/yeargt lt/cdgt
11
XML Display with XLS
lt?xml version"1.0" encoding"ISO-8859-1"?gt lt!--
Edited by XMLSpy --gt ltxslstylesheet
version"1.0" xmlnsxsl"http//www.w3.org/1999/XS
L/Transform"gt ltxsltemplate match"/"gt lthtmlgt
ltbodygt lttable border"1"gt lttr
bgcolor"9acd32"gt ltthgtTitlelt/thgt
ltthgtArtistlt/thgt lt/trgt ltxslfor-each
select"catalog/cdartist'Bob Dylan'"gt
lttrgt lttdgtltxslvalue-of select"title"/gtlt/t
dgt lttdgtltxslvalue-of select"artist"/gtlt/td
gt lt/trgt lt/xslfor-eachgt lt/tablegt
lt/bodygt lt/htmlgt lt/xsltemplategt lt/xslstyleshee
tgt
12
Using XML the Big Picture

• lt?xml version"1.0"?gtltsoapEnvelopexmlnssoap"h
  ttp//www.w3.org/2001/12/soap-envelope"soapencod
  ingStylehttp//www.w3.org/2001/12/soap-encodinggt
  ltsoapBodygt  ltmGetPrice xmlnsm"http//www.w3sc
  hools.com/prices"gt    ltmItemgtAppleslt/mItemgt 
  lt/mGetPricegtlt/soapBodygtlt/soapEnvelopegt

miracle
lt?xml version"1.0"?gtltsoapEnvelopexmlnssoap"h
ttp//www.w3.org/2001/12/soap-envelope"soapencod
ingStyle"http//www.w3.org/2001/12/soap-encoding"
gtltsoapBodygt  ltmGetPriceResponse
xmlnsm"http//www.w3schools.com/prices"gt   
ltmPricegt1.90lt/mPricegt  lt/mGetPriceResponsegtlt/
soapBodygtlt/soapEnvelopegt
13
XML and DB2 v9
14
XML Data type / storage
CREATE TABLE FAVORITE_CDS (NAME CHAR(20) NOT
NULL, CDID BIGINT, CDINFO XML)
XML Tablespace Partitioned by growth, if the
base table space is not partitioned Partitioned
by range, if the base table space is partitioned
15
SYSXMLRELS

• TBOWNER TBNAME
  COLNAME XML TABLE
• MKTMBS CLIENTS
  CONTACTINFO XCLIENTS
• MKTMBS FAVORITE
  CDINFO XFAVORITE_CDS
• MKTMBS PURCHASEORDERS
  XMLPO XPURCHASEORDERS

16
SYSXMLSTRINGS

• SELECT
  
• FROM "SYSIBM".SYSXMLSTRINGS
  
• WHERE STRINGID gt 1142
  
• STRINGID STRING
• 1143 TITLE
  
• 1144 ARTIST
  
• 1145 COMPANY
  
• 1146 YEAR
  
• 1147 CD
  
• NUMBER OF ROWS SELECTED 5
  

• ltcdgt
• lttitlegtEmpire Burlesquelt/titlegt
• ltartistgtBob Dylanlt/artistgt
• ltcountrygtUSAlt/countrygt
• ltcompanygtColumbialt/companygt
• ltpricegt10.90lt/pricegt
• ltyeargt1985lt/yeargt
• lt/cdgt
• ltcdgt
• lttitlegtHide your heartlt/titlegt
• ltartistgtBonnie Tylerlt/artistgt
• ltcountrygtUKlt/countrygt
• ltcompanygtCBS Recordslt/companygt
• ltpricegt9.90lt/pricegt
• ltyeargt1988lt/yeargt
• lt/cdgt

• 1143Hide your heart1143

17
Just going through the basics
18
Getting to DB2 9
------------------------------ Commands Entered
------------------------------ connect to DEDK
user MVSMXS1 using ---------------------
--------------------------------------------------
------- connect to DEDK user MVSMXS1 using
Database Connection Information Database
server DB2 OS/390 9.1.5 SQL
authorization ID MVSMXS1 Local database
alias DEDK A JDBC connection to the target
has succeeded.
19
XML Data Type Purchase Orders

• lt?xml version"1.0"?gt
• ltpurchaseOrder orderDate"2008-01-20"gt
• ltshipTo country"US"gt
• ltnamegtAlice Smithlt/namegt
• ltstreetgt123 Maple Streetlt/streetgt
• ltcitygtMill Valleylt/citygt
• ltstategtCAlt/stategt
• ltzipgt90952lt/zipgt
• lt/shipTogt
• ltbillTo country"US"gt
• ltnamegtRobert Smithlt/namegt
• ltstreetgt8 Oak Avenuelt/streetgt
• ltcitygtOld Townlt/citygt
• ltstategtPAlt/stategt
• ltzipgt95819lt/zipgt
• lt/billTogt
• ltcommentgtHurry, my lawn is going
  wildlt/commentgt
• ltitemsgt
• ltitem partNum"872-AA"gt

20
DDL - Nothing To Worry About

• CREATE TABLESPACE relData pagesize 4K managed by
  automatic storage bufferpool bp4k
• DROP TABLE PURCHASEORDERS
• CREATE TABLE PurchaseOrders (ponumber varchar(10)
  not null,
• podate date not null,
• status char(1),
• XMLpo xml,
• primary key (ponumber))
• CREATE TABLE PO LIKE PurchaseOrders
• CREATE VIEW ValidPurchaseOrders as
• SELECT ponumber, podate, XMLpo
• FROM PurchaseOrders
• WHERE status A
• ALTER TABLE PurchaseOrders
• ADD revisedXMLpo xml

21
Manipulation

• UPDATE PurchaseOrders SET XMLpo XMLpo_revised
• WHERE ponumber 12345
• INSERT INTO PurchaseOrders VALUES
  (200300001,CURRENT DATE, A, xmlPo)
• INSERT INTO PurchaseOrders VALUES (200300003,
  CURRENT DATE, A, XMLPARSE(DOCUMENT vchar
  PRESERVE WHITESPACE) )
• INSERT into PurchaseOrders VALUES( '200300004',
  CURRENT DATE, 'A',DSN_XMLValidate(lobPo,
  SYSXSR.myPOSchema))
• DELETE FROM PurchaseOrders WHERE ponumber
  12345

22
Retrieval

• SELECT XMLpo INTO xmlPo
• FROM PurchaseOrders
• WHERE ponumber 200300001
• SELECT XMLPO
• FROM PurchaseOrders
• WHERE XMLEXISTS(//items/itemproductName Baby
  Monitor PASSING XMLpo)
• SELECT XMLQUERY(//items/item/quantity PASSING
  XMLpo)
• FROM PurchaseOrders WHERE

23
Indexes

• CREATE INDEX ON
• PurchaseOrders(XMLPO)
• Generate Keys Using
• XMLPATTERN
• /purchaseOrder/items/item/productName
• as SQL VARCHAR(100)
• Index will be used for this query.
• SELECT XMLPO
• FROM PurchaseOrders
• XMLEXISTS(/purchaseOrder/items/itemproductName
  Lawnmower passing XMLPO)

24
Validation XML Schema Support

• DB2 requires a SQL identifier for identification.
• REGISTER XMLSCHEMA
• http//www.test.com/order.xsd
• FROM file//C/xmlschema/order.xsd
• AS ORDERSCHEMA
• COMPLETE ENABLE DECOMPOSITION
• REMOVE XMLSCHEMA ORDERSCHEMA

25
XML Performance

• How are you going to use the XML?

26
INSERT Performance

• INSERT
• The obvious the larger and more complex the XML
  column is the more expensive the insert.
• The more indexes the more overhead
• INSERT with VALIDATE is at least double the
  overhead
• Use host variables rather than Literals
• Use LOAD instead of SQL INSERT (30 to 40 percent)

27
Update

• When updating an XML document, an SQL UPDATE
  statement is equivalent to performing an SQL
  DELETE and INSERT and the performance will be
  about the same.
• If this is going to happen allot then consider
  splitting the XML into smaller pieces

28
Select Performance

• Performance for SQL no real change, the size and
  complexity of the XML document will determine
  overhead. Well coded SQL proper indexes and
  physical design still make the big difference.

• XML Indexes are different and good ones make a
  great deal of difference.
• CREATE INDEX ON
• PurchaseOrders(XMLPO)
• Generate Keys Using
• XMLPATTERN
• /purchaseOrder/items/item/productName
• as SQL VARCHAR(100)

29
Data Considerations
Native XML is good when you need Schema
flexibility Search Performance Partial document
retrieval
CREATE TABLE CD_CATALOG (TITLE
VARCHAR(30), ARTIST VARCHAR(30) , COUNTRY
VARCHAR(25) , COMPANY VARCHAR (25) PRICE
DECIMAL(5,2) , YEAR SMALINT)
CREATE TABLE CD_CATALOG (PID VARCHAR(10) NOT NULL
, OWNER VARCHAR(30) , DESCRIPTION XML)
CREATE TABLE CD_CATALOG (NAME CHAR(20) NOT NULL,
CDID BIGINT, CDINFO XML, PRIMARY KEY (NAME))
30
Summary

• XML is the current standard for sharing data
• How your organization is exploiting XML or will
  be exploiting XML should be understood by
  Database Administrators
• You should be familiar of how DB2 9 stores and
  catalogs the XML data type
• You dont have to be an SQL GURU but be aware of
  performance characteristics
• Even if your organization has no immediate plans
  to use the XML data type your understanding maybe
  key to the future of new applications. Try it!
• If you have questions mike_sniezek_at_bmc.com

31
Some useful links

• http//www.eccnet.com/acronyms/acronyms.php -
  related acronyms
• http//www.w3schools.com/xml/default.asp - XML
  Tutorial
• http//www.w3.org/TR/xml/ - XML 1.0 Standard
• http//www.w3.org/TR/xpath-datamodel/ - XPATH and
  XQUERY
• http//www.w3.org/TR/xmlschema-0/ - Schema
• http//xml.coverpages.org/xmlApplications.html -
  XML applications and standards
• http//www.w3schools.com/xpath/default.asp -XPATH
  Tutorial
• http//www.redbooks.ibm.com/redbooks/pdfs/sg247330
  .pdf -Red Book
• http//www.w3schools.com/xml/xml_examples.asp -
  examples