XML Consortium & XML Consortium 1 XML Consortium XML Consortium 2

Size: px

Start display at page:

Download "XML Consortium & XML Consortium 1 XML Consortium XML Consortium 2"

れんまたつざわ
5 years ago
Views:

1 & 1 2

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

3 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

4 XML <dept> <employee id=901> <name>john Doe</name> <phone> </phone> <office>344</office> <employee id=902> XML <name>peter Pan</name> <phone> </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 A6789 A < ponum= > A54 12 A A A54 2 bort < venderid= A6789 > <name> </name> <address> </address> </ > <itemlist> <item> < >A54</ > < >12</ > </item> <item> < >985</ > < >1</ > </item> </itemlist> </ > < partno= A54 > A < id= 1 > < > </ > </ > < 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

5 CREATE create index idx1 on T(xmlcol) generate key using xmlpattern 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 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> </phone> <office>344</office> <employee id=902> <name>peter Pan</name> <phone> </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=" /m:dept/m:employee/m:name as sql varchar(45); 10

6 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

7 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 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> </phone> 1212</phone> <phone>415 <phone> </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> </phone> 9918</phone> <office>216</office> <office>216</office> </dept> </dept> 14

8 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 = 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> </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(?

9 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 )) URI Schema Validation Validation 17 18

10 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

11 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 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

12 E press-c XML DB2 Express DB2 -DB

IBM Software Group DB2 Information Management Software DB2 V8 XML SQL/XML 2 XML XML UDF XMLExtender XML XML XMLCollection, XMLColumn XML UDF Informati

IBM Software Group XML Features in DB2 UDB V8 IBM Software Group DB2 Information Management Software DB2 V8 XML SQL/XML 2 XML XML UDF XMLExtender XML XML XMLCollection, XMLColumn XML UDF Information Integrator