& 1 2
TCO DB2 DB2 UDB DB DB V8.2 V8.2 DB2 DB2 UDB V8.1 V8.1 DB2 9 3 CLOB XML XML DB2 9 purexml XML XML DOC XML DOC XML DOC XML DOC VARCHAR/CLOB XML ( ) 4
XML & XML ( & ) DB2 XML SQL/XML DB2 DB2 : DB2 / DB2 XQuery XML XML 5 XML (W3C XDM ) create table dept (deptid char(8),, deptdoc xml); XML deptid deptdoc PR27 XML RDB <dept> <emp> </emp> </dept> DB2 6
XML <dept> <employee id=901> <name>john Doe</name> <phone>408 555 1212</phone> <office>344</office> <employee id=902> XML <name>peter Pan</name> <phone>408 555 9918</phone> <office>216</office> XML XML </dept> XML Load employee employee XML id=901 name phone office id=902 name phone office RDB XML API DB2 purexml SQL/XML XQuery dept DB2 purexml Storage 7 2 - - 987564331 A6789 A54 200 10-985 115 20 < ponum= 987564331 > - 987564331 A54 12 A54 1 987564331 A6789 987564331 985 1 A54 2 bort < venderid= A6789 > <name> </name> <address> </address> </ > <itemlist> <item> < >A54</ > < >12</ > </item> <item> < >985</ > < >1</ > </item> </itemlist> </ > < partno= A54 > A54 200 10 < id= 1 > < > </ > 985 115 20 </ > < id= 2 > < >bort</ > </ > </ > 8
CREATE create index idx1 on T(xmlcol) generate key using xmlpattern '/a/b/@c' as sql date; & prefix 9 UNIQUE index-name ON table-name (xml-column-name) GENERATE KEY USING xmlpattern AS SQL VARCHAR (integer) VARCHAR (HASHED) DOUBLE DATE xmlpattern: TIMESTAMP / // element-tag * INDEX / // xmlpattern = XPath, child axis (/) descendent-or-self axis (//) text() @attribute-tag @* XML : create table dept(deptid char(8) primary key, deptdoc xml); create unique index idx1 on dept(deptdoc) generate key using xmlpattern '/dept/@bldg' as sql double; create unique index idx2 on dept(deptdoc) generate key using xmlpattern '/dept/employee/@id' as sql double; create index idx3 on dept(deptdoc) generate key using xmlpattern '/dept/employee/name' as sql varchar(35); xmlpattern //name as sql varchar(35); xmlpattern //@* as sql double; <dept bldg=101> <employee id=901> <name>john Doe</name> <phone>408 555 1212</phone> <office>344</office> <employee id=902> <name>peter Pan</name> <phone>408 555 9918</phone> <office>216</office> </dept> ( name ) ( ) xmlpattern //text() as sql varchar(hashed); ( ) xmlpattern /dept//name' as sql varchar(35); xmlpattern /dept/employee//text() as sql varchar(128); ( employee ) xmlpattern 'declare namespace m="http://www.myself.com/"; /m:dept/m:employee/m:name as sql varchar(45); 10
XML DB2 Net Search Extender XML XML Create index myindex for text on mytable (xmlcol); select xmlcol from mytable where contains(xmlcol, sections( /book/section ) Brazil"')=1 DB2 11 : XQuery : SQL/XML XQuery XQuery XQuery : XQuery SQL SQL SQL SQL SQL/XML : SQL XQuery XQuery 12
FOR: LET: WHERE: ORDER: RETURN: FOR $movie in collection( movies ) LET $actors := $movie//actor <movie> WHERE $movie/duration > 90 <title>chicago</title> ORDER by $movie/@year <actor>renee Zellweger</actor> RETURN <movie> <actor>richard Gere</actor> {$movie/title, $actors} <actor>catherine Zeta-Jones</actor> </movie> </movie> 13 phone 415 010 1234 name XQuery SQL dept deptdoc <dept <dept bldg=101> bldg=101> <employee <employee id=901> id=901> <name>john <name>john Doe</name> Doe</name> <phone>408 <phone>408 555 555 1212</phone> 1212</phone> <phone>415 <phone>415 010 010 1234</phone> 1234</phone> <office>344</office> <office>344</office> <employee <employee id=902> id=902> <name>peter <name>peter Pan</name> Pan</name> <phone>408 <phone>408 555 555 9918</phone> 9918</phone> <office>216</office> <office>216</office> </dept> </dept> 14
XMLQUERY:SQL create table dept(deptid char(8) primary key, deptdoc xml); create table unit(unitid char(8), headcount integer, bldg integer); select deptid,xmlquery('for $d in $deptdoc/dept where $d/@bldg = 101 return $d/name' passing deptdoc as deptdoc") from dept where deptid <> PR27 ; select d.deptid, u.headcount, xmlquery( $deptdoc/dept/name passing d.deptdoc as deptdoc ) from dept d, unit u where d.deptid=u.unitid and u.headcount > 200 and xmlquery( $deptdoc/dept/@bldg passing d.deptdoc as deptdoc ) = u.bldg and xmlexists( $deptdoc/dept/employee/name passing d.deptdoc as deptdoc ) 15 Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates> <update action="replace" col= 1 1 path="/dept/employee[@id=301]/phone"> <phone>408-463-4963</phone> </update> ( ) </updates>', 'Select deptdoc from dept where deptid=1006', '',?,?); action = replace compute append delete 16 Update the phone number of employee 301
create table dept(deptid char(8), deptdoc xml); ( ): insert into dept values (?,?) insert into dept values (?, xmlvalidate(?) Shema location DB2 schema repository Schema : insert into dept values(?,xmlvalidate(? according to xmlschema id dept.schema1 )) identifier Schema insert into dept values(?,xmlvalidate(? according to xmlschema uri http://my.dept.com )) URI Schema Validation Validation 17 18
XML API : JDBC,.NET, ODBC/CLI, Embedded SQL SQL/XML API XQuery API 19 XML Import & Export XML Runstats XML HADR XML / XML DB2 Developer Workbench GUI Shredding XML schema GUI XML GUI XML 20
HW: HW: IBM IBM p5 p5 560Q 560Q 8 8 cores cores (1.5GHz) (1.5GHz) Memory: Memory: 32GB 32GB OS: OS: AIX AIX 5L 5L v5.3 v5.3 TL04 TL04 Storage: Storage: IBM IBM TotalStorage TotalStorage DS8100 DS8100 DB2: DB2: DB2 DB2 9 9 ESE ESE STMM( STMM( Insert Read-only Mixed XML Select:Insert:Update:Del 100 25-150 ete=70:12:6:12 Mixed 5300XML/second 5500query/second 30GB/hour Insert 2000transaction/sec 21 Justsystem Viper xfy Enterprise solution for DB2 9 22
E press-c XML DB2 Express DB2 -DB2 - http://www-06.ibm.com/jp/software/data/db2express-c 23