IBM Software Group pure XML IBM EEA NikolayKulikovru
® IBM Software Group Разработка Приложений с помощью pure. XML Николай Куликов, IBM EE/A Nikolay_Kulikov@ru. ibm. com
IBM Software Group | DB 2 Information Management Software XML-Enabled Databases: две опции Shredding CLOB/Varchar XML DOC Извлечение определенных элементов/ атрибутов XML DOC Фиксированное отображение “Decompositon” Shredder Сторонние таблицы XML DOC (regular tables for faster lookup) varchar CLOB Обычные таблицы 3
IBM Software Group | DB 2 Information Management Software Таблицы строк и путей Paths table dept employee id name phone 901 John Doe 408 -555 1212 0 1 2 3 4 5 6 employee office 344 id name phone 902 Peter Pan 408 -555 9918 office 216 / /0 /0/4/5 /0/4/1 /0/4/2 /0/4/3 Paths table § Strings & Paths table per database § Database wide dictionary… § …for all documents in all XML columns Strings table 0 4 1 5 2 3 dept employee name id phone office SYSIBM. SYSXMLSTRINGS 5 0 1 2 3 4 5 6 / /dept/employee/@id /dept/employee/name /dept/employee/phone /dept/employee/office SYSIBM. SYSXMLPATHS
IBM Software Group | DB 2 Information Management Software dept employee id name John Doe 901 phone 408 -555 1212 “Компрессия" employee office 344 id name 902 Peter Pan phone office 408 -555 9918 Strings table 0 4 1 5 2 3 dept employee name id phone office 0 4 5 901 6 216 1 John Doe 4 2 408 -555 1212 3 5 344 902 1 Peter Pan 2 408 -555 9918 3 216
IBM Software Group | DB 2 Information Management Software DDL for Index on XML column create index idx 1 on T(xmlcol) generate key using xmlpattern '/a/b/@c' as sql date INDEX CREATE index-name ON table-name UNIQUE (xml-column-name) AS GENERATE KEY USING XMLPATTERN SQL xmlpattern-constant xmlpattern = XPath without predicates, only child axis (/) and descendent-or-self axis (//) VARCHAR (integer) VARCHAR (HASHED) DOUBLE DATE TIMESTAMP xmlpattern-constant: / // element-tag * / // text() @attribute-tag @* § Declaration & use of namespace prefix supported (not shown above) 7
IBM Software Group | DB 2 Information Management Software Интеграция в среды Eclipse 9
IBM Software Group | DB 2 Information Management Software Интеграция в среды. Net 10
IBM Software Group | DB 2 Information Management Software DB 2 data provider for. NET Version 2. 0 § Поддержка базовых классов System. Data. Common § Эквивалентность типов данных DB 2 и. NET § Поддержка 64 -bit § Scrollable and updateable result sets § Data Paging 4 Класс DB 2 Command теперь имеет Execute. Page. Reader § Bulk Data Copy § Update batch size 4 Позволяет приложению определять сколько операторов за раз будет отправлятся на сервер DB 2 для обработки 12
IBM Software Group | DB 2 Information Management Software <ipo: purchase. Order …. . order. Date="1999 -12 -01“ order. ID=“ 19991201 -AZFG”> ………. . <item part. Num="833 -AA"> <product. Name>Phone </product. Name> <quantity>1</quantity> <USPrice>132. 95</USPrice> <ship. Date>1999 -12 -05</ship. Date> </item> <item part. Num=“ 533 -AC"> <product. Name>Cycle</product. Name> <quantity>1</quantity> <USPrice>149. 95</USPrice> <ship. Date>1999 -12 -05</ship. Date> </item></items></ipo: purchase. Order> …<sequence> <element name="product. Name“ type="string“/> <element name="quantity“ type=“ipo: derived. Positive. Integer. Type” db 2 -xdb: row. Set=“ELECTRONICITEMS” db 2 -xdb: column=“QTY”/> ……. <!– ignoring mapping of PRICE --> …. <attribute name="part. Num" type="ipo: SKU“ db 2 -xdb: row. Set =” ELECTRONICITEMS” db 2 -xdb: column=“PARTNUM” db 2 -xdb: expr =“udf_convert. To. Internal. Part($SELF)” db 2 -xdb: cond=“udf_is. Electronic. Item($SELF) = ‘true’/>……. . 14 ELECTRONICITEMS ORDERID PARTNUM QTY PRICE 19991201 AZFG 833 -AA 1 132. 95 <attribute name="order. ID" type=“xs: string“ db 2 -xdb: row. Set = “PURCHASE_ORDER” db 2 -xdb: column = “ORDERID” > <annotation> <appinfo> <db 2 -xdb: table. Mapping> <db 2 -xdb: row. Set>ELECTRONICITEMS</db 2 -xdb: row. Set> <db 2 -xdb: column>ORDERID</db 2 -xdb: column> </db 2 -xdb: table. Mapping> </appinfo> </annotation> </attribute>
IBM Software Group | DB 2 Information Management Software Concurrent Data Readers //Open connection to the database DB 2 Connection conn = new DB 2 Connection ("database=sample; "); conn. Open(); //Create 2 commands from the same connection DB 2 Command cmd 1 = conn. Create. Command(); DB 2 Command cmd 2 = conn. Create. Command(); cmd 1. Command. Text = "SELECT SALES_PERSON, SALES FROM SALES"; cmd 2. Command. Text = "SELECT DEPTNUMB, DEPTNAME FROM ORG" //Execute the first query DB 2 Data. Reader dr 1 = cmd 1. Execute. Reader(); //Execute the second query while the first one is still open DB 2 Data. Reader dr 2 = cmd 2. Execute. Reader(); //Get results from both data readers while (dr 1. Read()) { Console. Write. Line(" Sales Person {0}, Sales {1}" dr 1. Get. String(0), dr 1. Get. Int 16(1)); } while (dr 2. Read()) { Console. Write. Line(" Department Number {0}, Department Name {1}" dr 2. Get. Int 16(0), dr 2. Get. String(1)); } //Close both data readers dr 1. Close(); dr 2. Close(); conn. Close(); 15
IBM Software Group | DB 2 Information Management Software JDBC и XML обзор § JDBC и XML сегодня § Расширения DB 2 для JDBC XML § JDBC и XML Завтра (JDBC v 4) § Java's XML APIs 4 DOM, SAX, St. AX, and Transform (XSLT) 16
IBM Software Group | DB 2 Information Management Software JDBC краткий обзор 1. Connection – соединение с БД connection = Driver. Manager. get. Connection(url, user, pass); 2. Statement – Оператор для выполнения Prepared. Statement stmt = connection. prepare. Statement(sql); 3. Result. Set – результат выполнения Result. Set result. Set = stmt. execute. Query(); 4 Stream – значение XML Input. Stream input. Stream = result. Set. get. Binary. Stream(1); 4 или DB 2 XML или SQLXML DB 2 Xml db 2 xml = (DB 2 Xml) result. Set. get. Object(1); 18
IBM Software Group | DB 2 Information Management Software Выботка из XML столбца String sql = "SELECT PID, DESCRIPTION from XMLPRODUCT where PID = ? "; Prepared. Statement stmt = connection. prepare. Statement(sql); stmt. set. String(1, "100 -105 -09"); Result. Set result. Set = stmt. execute. Query(); String xml = result. Set. get. String("DESCRIPTION"); // or Input. Stream input. Stream = result. Set. get. Binary. Stream("DESCRIPTION"); // or Reader reader = result. Set. get. Character. Stream("DESCRIPTION"); // or DB 2 Xml db 2 xml = (DB 2 Xml) result. Set. get. Object("DESCRIPTION"); 20
IBM Software Group | DB 2 Information Management Software Вставка из файла XML String sql = "INSERT INTO xmlproduct VALUES(? , ? )"; Prepared. Statement stmt = connection. prepare. Statement(sql); stmt. set. String(1, "100 -105 -09"); File bin. File = new File("product. Bin. In. xml"); Input. Stream in. Bin = new File. Input. Stream(xml. File); stmt. set. Binary. Stream(2, in. Bin, (int) bin. File. get. Length()); stmt. execute(); 21
IBM Software Group | DB 2 Information Management Software JDBC и XML завтра (JDBC v 4) (JSR 221) § SQLXML объект добавлен к спецификации JDBC 4 get. SQLXML() возвращает SQLXML объект 4 get. Object() возвращает SQLXML объект 4 Объекты SQLXML являются также объектами DB 2 XML § Представляет XML значение § Похоже на BLOB/CLOB § Доступ из Result. Set 4 SQLXML get. SQLXML(int column. Index) 4 SQLXML get. SQLXML(String column. Name) 4 void update. SQLXML(int column. Index, SQLXML xml. Object) 4 void update. SQLXML(String column. Name, SQLXML xml. Object) 22
IBM Software Group | DB 2 Information Management Software Source and Result Examples § DOM get a Document 4 DOMSource dom. Source = sqlxml. get. Source(DOMSource. class); 4 Document document = (Document) dom. Source. get. Node(); § DOM set a Document 4 DOMResult dom. Result = sqlxml. set. Result(DOMResult. class); 4 dom. Result. set. Node(my. Node); § Run an XSLT on an XML result 4 File xslt. File = new File("my. xslt"); 4 File my. File = new File("result. xml"); 4 Transformer xslt = Transformer. Factory. new. Instance(). new. Transformer(new Stream. Source(xslt. File)); 4 Source source = sqlxml. get. Source(null); 4 Result result = new Stream. Result(my. File); 4 xslt. transform(source, result); 23
IBM Software Group | DB 2 Information Management Software New Snapshot Monitor Counters for XML Buffer Buffer Buffer pool pool pool data logical reads data physical reads temporary data logical reads temporary data physical reads data writes index logical reads index physical reads temporary index logical reads temporary index physical reads index writes xda logical reads = 253 = 70 = 145 = 0 = 17275 = 0 = 0 = 2837 Buffer pool xda physical reads = 174 Buffer pool temporary xda logical reads = 0 Buffer pool temporary xda physical reads = 0 Buffer pool xda writes = 0 Data Counters (relational) Relational and XML Index Counters XML Data Counters Проверяйте XDA счетчики для оценки XML активности ! Активности с XML Regions включается в index counters. 27 New !
IBM Software Group | DB 2 Information Management Software Проверка на основе XML схем create table dept(dept. ID char(8), deptdoc xml); Validation is optional, and per document (per row): insert into dept values (? , ? ) No Validation insert into dept values (? , xmlvalidate(? )) With Validation Валидация увеличивает процессорное время CPU для вставок, и уменьшает пропускную способность. 28
IBM Software Group | DB 2 Information Management Software Примеры XML индексов create table customer( info XML); create unique index idx 1 on customer(info) generate key using xmlpattern '/customerinfo/@Cid' as sql double; create index idx 2 on customer(info) generate key using xmlpattern '/customerinfo/name' as sql varchar(40); create index idx 3 on customer(info) generate key using xmlpattern '//name' as sql varchar(40); 29 <customerinfo Cid="1004"> <name>Matt Foreman</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Toronto</city> <state>Ontario</state> <pcode>M 3 Z-5 H 9</pcode> </addr> <phone type="work">905 -555 -4789</phone> <phone type="home">416 -555 -3376</phone> <assistant> <name>Peter Smith</name> <phone type="home">416 -555 -3426</phone> </assistant> </customerinfo>
IBM Software Group | DB 2 Information Management Software XML Indexing Examples create table customer( info XML); create unique index idx 1 on customer(info) generate key using xmlpattern '/customerinfo/@Cid' as sql double; create index idx 2 on customer(info) generate key using xmlpattern '/customerinfo/name' as sql varchar(40); create index idx 3 on customer(info) generate key using xmlpattern '//name' as sql varchar(40); create index idx 4 on customer(info) generate key using xmlpattern '//text()' as sql varchar(40); 30 Не индексируйте все! Слишком дорого для insert, update, delete ! <customerinfo Cid="1004"> <name>Matt Foreman</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Toronto</city> <state>Ontario</state> <pcode>M 3 Z-5 H 9</pcode> </addr> <phone type="work">905 -555 -4789</phone> <phone type="home">416 -555 -3376</phone> <assistant> <name>Peter Smith</name> <phone type="home">416 -555 -3426</phone> </assistant> </customerinfo>
IBM Software Group | DB 2 Information Management Software Рекомендации по XML Запросам § Если возможно полностью указывайте точный XPath, а не шаблон. 4 /customerinfo/phone вместо //phone 4 /customerinfo/addr/state вместо /customerinfo/*/state <customerinfo Cid="1004"> <name>Matt Foreman</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Toronto</city> <state>Ontario</state> <pcode>M 3 Z-5 H 9</pcode> </addr> <phone type="work">905 -555 -4789</phone> <phone type="home">416 -555 -3376</phone> </customerinfo> <assistant> <name>Peter Smith</name> <phone type="home">416 -555 -3426</phone> </assistant> </customerinfo> 32
IBM Software Group | DB 2 Information Management Software SQL/XML with XMLQUERY Без индекса! select xmlquery(‘$i/customerinfo[phone = “ 905 -555 -4789”]/name’ passing c. info as “i”) from customer c; customer table: <customerinfo> <name>Matt Foreman</name> <phone>905 -555 -4789</phone> </customerinfo> <name>Matt Foreman</name> <customerinfo> <name>Peter Jones</name> <phone>905 -123 -9065</phone> </customerinfo> 3 record(s) selected <customerinfo> <name>Mary Poppins</name> <phone>905 -890 -0763</phone> </customerinfo> <name>Matt Foreman</name> 1 record(s) selected Индекс! select xmlquery(‘$i/customerinfo/name’ passing c. info as “i”) from customer c where xmlexists(‘$i/customerinfo[phone = “ 905 -555 -4789”]’ passing c. info as “i”) 34
IBM Software Group | DB 2 Information Management Software Viper II § Ограничения целостности на XML § Передача параметров в SQLQuery 4 –XQuery нет возможности передачи параметров в SQL XQUERY for $docid in (1, 2, 3), $j in db 2 -fn: sqlquery('select xmlcol from t 1 where docid = parameter(1)', $docid)/bib/book where count($j/author) > 1 return $j; 35
IBM Software Group | DB 2 Information Management Software Viper II § Репликация XML § Поддержка триггеров для XML 4 Before Trigger сможет работать перед валидацией на вставку § Обновление части документа update T set doc = XMLQuery (' transform copy $r : = $doc do delete {$r/score}, replace. Value of {$r/salary} with $r/salary * 1. 1, insert {<d>Ph. D. </d>} into $r//emp, rename ($r/status[1]) to "state" return $r' passing doc as "doc", cast (? as double) as "raise", id as "i"); 36
IBM Software Group | DB 2 Information Management Software Questions? Nikolay_Kulikov@ru. ibm. com 37 Matthias Nicola, IBM SVL
IBM Software Group | DB 2 Information Management Software New XML Statistics § Gathered by runstats when run on XML column and XML index § Cannot be modified via update § For each of the most frequent paths in an XML column we collect - The total number of times the path is seen - Top-k Pathid node counts - In how many documents it is seen - Top-k Pathid doc counts § Same is collected for most frequent paths that lead to values including the value itself - Top-k Pathid-Value node counts - Top-k Pathid-Value doc counts § Catch all stats for paths that are not frequent § New registry variables for fine tuning, db 2 cat for inspection 38
IBM Software Group | DB 2 Information Management Software XANDOR Join Example: New ! Query: /doc/A[B=5 and C/F=6] XML Index 1 /doc/A/B = 5 ? … B=5, docid=3 B=5, docid=4 B=5, docid=5 B=5, docid=7 B=5, docid=8 B=5, docid=9 B=5, docid=10 B=5, docid=11 B=5, docid=15, node. ID=1. 1. 1 B=5, docid=16 B=5, docid=17 B=5, docid=19 node. ID=1. 1. 1 … 39 XML Index 2 /doc/A/C/F = 6 ? … F=6, docid=1 F=6, docid=2 F=6, docid=6 F=6, docid=15, node. ID=1. 2. 1. 1, F=6, docid=12 F=6, docid=13 F=6, docid=19 node. ID=1. 1. 2. 1 … doc. ID=15 <doc> <A> <B>5</B> </A> <C> <F>6</F> </C> </A> <doc> doc. ID=19 <doc> <A> <B>5</B> <C> <F>6</F> </C> </A> <doc> IBM Confidential
- Slides: 39