Using COBOL with pureXML on DB2 for z/OS Information Management 1

Using COBOL with pureXML on DB2 for
z/OS
Information Management
1
© 2012 IBM Corporation
Information Management
Agenda
2
■
Introduction
■
XML Host Variables in COBOL
■
SQL/XML Functions
■
Insert and Retrieval XML Data
■
Update and Delete XML Data
■
Trigger to Extract Relational Data from XML Data
■
Analyzing Access Plan with or without XML Indexes
■
DESCRIBE Statement Referring to an XML Column
■
FETCH WITH CONTINUE and FETCH CURRENT
CONTINUE on XML Data
■
XML Sub-document Update
© 2012 IBM Corporation
Information Management
Introduction
 Minimum Requirement:
– DB2 9 for z/OS
– Enterprise COBOL for z/OS V3R4
 Recommend Requirement:
– DB2 10 for z/OS
• New features (Sub-document update, XQuery, Binary XML, XML
support in SQL PL and UDF, etc)
• Enhancement on performance and storage
– Enterprise COBOL for z/OS V4R1
 DB2 Precompiler or DB2 Coprocessor
 Purpose of COBOL samples:
– Problems from users
– Illustrate DB2 for z/OS pureXML features in COBOL
 Where are the COBOL Samples?
http://www.ibm.com/developerworks/wikis/display/db2xml/DB2+for+zOS+pureXML
3
© 2012 IBM Corporation
Information Management
DDL for COBOL Samples
CREATE TABLE PURCHASE_ORDER ( PODOCID INTEGER,
PODOC XML );
CREATE INDEX POINDEX1 ON PURCHASE_ORDER (PODOC)
GENERATE KEY USING XMLPATTERN
'/purchaseOrder/items/item/productName'
AS SQL VARCHAR(30)
4
© 2012 IBM Corporation
Information Management
XML Storage
DocID index
NodeID index
B+tree
B+tree
5
DocID
B+tree
XML Table
PURCHASE_ORDER
PODOCID
XML index
(POINDEX1)
DOCID
MIN_NODEID
XMLDATA
PODOC
© 2012 IBM Corporation
Information Management
An Example of PURCHASE ORDER DOCUMENT
<purchaseOrder orderDate="2010-08-18">
<shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street>
<city>San Jose</city><state>California</state><zip>95123</zip>
</shipTo>
<billTo country="US">
<name>Robert Smith</name>
<street>8 Oak Avenue</street>
<city>San Jose</city><state>California</state><zip>95123</zip>
</billTo>
<comment>Hurry, my lawn is going wild!</comment>
<items>
<item partNum="872-AA">
<productName>Lawnmower</productName>
<quantity>1</quantity><USPrice>149.99</USPrice>
<comment>Confirm this is gas powered</comment>
<shipDate>2010-08-20</shipDate>
</item>
</items>
</purchaseOrder>
6
© 2012 IBM Corporation
Information Management
XML Host Variables
01 XMLCLOB
USAGE IS SQL TYPE IS XML AS CLOB(10K).
01 XMLBLOB
USAGE IS SQL TYPE IS XML AS BLOB(10K).
01 XMLDBCLOB USAGE IS SQL TYPE IS XML AS DBCLOB(10K).
Modified Source by DB2 Precompiler:
01 XMLCLOB.
02 XMLCLOB-LENGTH PIC 9(9) COMP-5.
02 XMLCLOB-DATA.
49 FILLER PIC X(10240).
01 XMLBLOB.
02 XMLBLOB-LENGTH PIC 9(9) COMP-5.
02 XMLBLOB-DATA.
49 FILLER PIC X(10240).
01 XMLDBCLOB.
02 XMLDBCLOB-LENGTH PIC 9(9) COMP-5.
02 XMLDBCLOB-DATA.
49 FILLER PIC G(10240) USAGE DISPLAY-1.
7
© 2012 IBM Corporation
Information Management
Avoiding Data Conversion
8
© 2012 IBM Corporation
Information Management
More XML Host Variables
01 BLOB-XML-FILE USAGE IS SQL TYPE IS XML AS BLOB-FILE.
01 CLOB-XML-FILE USAGE IS SQL TYPE IS XML AS CLOB-FILE.
01 DBCLOB-XML-FILE USAGE IS SQL TYPE IS XML AS DBCLOB-FILE.
01 BLOB-XML-FILE.
49 BLOB-XML-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
49 BLOB-XML-FILE-DATA-LENGTH PIC S9(9) COMP-5.
49 BLOB-XML-FILE-FILE-OPTION PIC S9(9) COMP-5.
49 BLOB-XML-FILE-NAME PIC X(255).
01 CLOB-XML-FILE.
49 CLOB-XML-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
49 CLOB-XML-FILE-DATA-LENGTH PIC S9(9) COMP-5.
49 CLOB-XML-FILE-FILE-OPTION PIC S9(9) COMP-5.
49 CLOB-XML-FILE-NAME PIC X(255).
9
01 DBCLOB-XML-FILE.
49 DBCLOB-XML-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
49 DBCLOB-XML-FILE-DATA-LENGTH PIC S9(9) COMP-5.
49 DBCLOB-XML-FILE-FILE-OPTION PIC S9(9) COMP-5.
49 DBCLOB-XML-FILE-NAME PIC X(255).
© 2012 IBM Corporation
Information Management
SQL/XML Functions

XMLEXISTS
SELECT COUNT(DISTINCT PODOCID) INTO :HV-COUNT
FROM PURCHASE_ORDER
WHERE XMLEXISTS ('//item[@partNum = "872-AA"]' PASSING PODOC)

XMLQUERY
SELECT XMLQUERY( '//item[@partNum = "872-AA"]' PASSING PODOC)
INTO :HV-XML-CLOB
FROM PURCHASE_ORDER WHERE PODOCID = 1

XMLTABLE
SELECT X.ORDERDATE, X.SHIPDATE INTO :HV-DATE1, :HV-DATE2
FROM PURCHASE_ORDER,
XMLTABLE('/purchaseOrder[.//item/@partNum="872-AA"]' PASSING PODOC
COLUMNS
ORDERDATE DATE PATH '@orderDate' ,
SHIPDATE DATE PATH 'items/item[@partNum = "872-AA"]/shipDate'
) AS X WHERE PODOCID = 1

XMLCAST
SELECT XMLCAST(XMLQUERY( '/purchaseOrder/@orderDate' PASSING PODOC)
AS DATE) INTO :HV-DATE
FROM PURCHASE_ORDER WHERE PODOCID = 1
10
© 2012 IBM Corporation
Information Management
Insert XML Data
•Insert from literal
INSERT INTO PURCHASE_ORDER (PODOCID, PODOC) VALUES (1,
'<purchaseOrder orderDate="2010-08-18">…</purchaseOrder>')
INSERT INTO PURCHASE_ORDER (PODOCID, PODOC) VALUES (2,
XMLPARSE(DOCUMENT '......' PRESERVE WHITESPACE))
•Insert from Host Variable
01 HV-CLOB1 SQL TYPE IS XML AS CLOB(3K).
01 HV-CLOB-LOCATOR01 SQL TYPE IS CLOB-LOCATOR.
INSERT INTO PURCHASE_ORDER (PODOCID, PODOC) VALUES(3, :HVCLOB1)
INSERT INTO PURCHASE_ORDER (PODOCID, PODOC) VALUES(4, :HVCLOB-LOCATOR01)
11
© 2012 IBM Corporation
Information Management
Insert XML Data (Cont.)
•Insert from File Reference Host Variable
01 HV-XML-CLOB-FILE SQL TYPE IS XML AS CLOB-FILE.
MOVE 23 TO HV-XML-CLOB-FILE-NAME-LENGTH.
MOVE "SYSADM.XMLSAMP.CLOBFILE" TO HV-XML-CLOB-FILE-NAME.
MOVE SQL-FILE-READ TO HV-XML-CLOB-FILE-FILE-OPTION.
EXEC SQL
INSERT INTO PURCHASE_ORDER (PODOCID, PODOC)
VALUES(5, :HV-XML-CLOB-FILE)
END-EXEC.
•FILE OPTIONS
•SQL-FILE-READ
: 2
•SQL-FILE-CREATE
: 8
•SQL-FILE-OVERWRITE : 16
•SQL-FILE-APPEND
12
: 32
© 2012 IBM Corporation
Information Management
Retrieving XML Data
• Simple select:
SELECT PODOC
FROM PURCHASE_ORDER
WHERE PODOCID = 1
• Select with condition:
SELECT PODOC
FROM PURCHASE_ORDER
WHERE XMLEXISTS('//item[@partNum = "872-AA"]' PASSING PODOC)
• Extract from a document:
SELECT XMLQUERY(‘/purchaseOrder/@orderDate’ PASSING PODOC)
FROM PURCHASE_ORDER
WHERE XMLEXISTS('//item[@partNum = "872-AA"]' PASSING PODOC)
13
© 2012 IBM Corporation
Information Management
Retrieving XML Data to File Reference Host Variable
01 HV-XML-CLOB-FILE SQL TYPE IS XML AS CLOB-FILE.
MOVE 17 TO HV-XML-CLOB-FILE-NAME-LENGTH.
MOVE "/tmp/xmldoc01.txt" TO HV-XML-CLOB-FILE-NAME.
MOVE SQL-FILE-OVERWRITE TO HV-XML-CLOB-FILE-FILE-OPTION.
EXEC SQL
SELECT PODOC INTO :HV-XML-CLOB-FILE
FROM PURCHASE_ORDER
WHERE PODOCID = 1
END-EXEC.
14
© 2012 IBM Corporation
Information Management
Update and Delete on XML Data
•Updating an XML Column
01 HV-CLOB1
SQL TYPE IS XML AS CLOB(2048).
EXEC SQL
UPDATE PURCHASE_ORDER SET PODOC = :HV-CLOB1
WHERE PODOCID = 1
END-EXEC.
•Delete Rows Based on XML Data
EXEC SQL
DELETE FROM PURCHASE_ORDER
WHERE XMLEXISTS ('//item[@partNum = "872-AA"]' PASSING PODOC)
END-EXEC.
15
© 2012 IBM Corporation
Information Management
Trigger to Extract Relational Data from XML Data
CREATE TRIGGER INS_CUST
AFTER INSERT ON CUSTOMER_INFO
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE CUSTOMER_INFO
SET (CUST_NAME, CUST_ADDR, CUST_CITY, CUST_ZIP) =
(SELECT X.NAME, X.ADDR, X.CITY, X.ZIP FROM
CUSTOMER_INFO AS C ,
XMLTABLE('customer' PASSING C.CUST_INFO
COLUMNS
NAME VARCHAR(30) PATH 'name',
CITY VARCHAR(30) PATH 'city',
ZIP VARCHAR(12) PATH 'zip' ,
ADDR VARCHAR(100) PATH 'street'
) AS X
WHERE C.CUST_ID = NEWROW.CUST_ID
)
WHERE CUST_ID = NEWROW.CUST_ID ;
END
16
© 2012 IBM Corporation
Information Management
Trigger to Extract Relational Data from XML Data (Cont.)
CREATE TABLE CUSTOMER_INFO (
CUST_ID INTEGER,
CUST_NAME VARCHAR(30),
CUST_ADDR VARCHAR(100),
CUST_CITY VARCHAR(30),
CUST_ZIP VARCHAR(12),
CUST_INFO XML
)
INSERT INTO CUSTOMER_INFO (CUST_ID, CUST_INFO) VALUES (1,
'<customer country="US"><name>Alice Smith</name>...</customer>')
SELECT CUST_NAME FROM CUSTOMER_INFO WHERE CUST_ID = 1
+--------------------------------+
| CUST_NAME
|
+--------------------------------+
1_| Alice Smith
|
+--------------------------------+
17
© 2012 IBM Corporation
Information Management
Create XML Indexes
CREATE INDEX POINDEX1 ON PURCHASE_ORDER (PODOC)
GENERATE KEY USING XMLPATTERN
'/purchaseOrder/items/item/productName'
AS SQL VARCHAR(30)
CREATE INDEX POINDEX2 ON PURCHASE_ORDER (PODOC)
GENERATE KEY USING XMLPATTERN
'/purchaseOrder/items/item/USPrice'
AS SQL DECFLOAT
18
© 2012 IBM Corporation
Information Management
Explain Queries with XML Indexes
EXPLAIN ALL SET QUERYNO = 1 FOR SELECT PODOCID FROM
PURCHASE_ORDER WHERE
XMLEXISTS('/purchaseOrder/items/item[productName
="Lawnmower"]' PASSING PODOC)
EXPLAIN ALL SET QUERYNO = 2 FOR SELECT PODOCID FROM
PURCHASE_ORDER WHERE
XMLEXISTS('/purchaseOrder/items/item[USPrice > 100]'
PASSING PODOC)
EXPLAIN ALL SET QUERYNO = 3 FOR SELECT PODOCID FROM
PURCHASE_ORDER WHERE
XMLEXISTS('/purchaseOrder/items/item[productName
="Lawnmower" and 'USPrice >100]' PASSING PODOC)
EXPLAIN ALL SET QUERYNO = 4 FOR SELECT PODOCID FROM
PURCHASE_ORDER WHERE
XMLEXISTS('/purchaseOrder/items/item[productName
="Lawnmower" or 'USPrice >100]' PASSING PODOC)
19
© 2012 IBM Corporation
Information Management
Explain Results with XML Indexes
+-----------------------------------------+
| QUERYNO | ACCESSTYPE |
ACCESSNAME
|
+-----------------------------------------+
|
0001 |
DX
| POINDEX1
|
|
0002 |
DX
| POINDEX2
|
|
0003 |
M
|
|
|
0003 |
DX
| POINDEX1
|
|
0003 |
DX
| POINDEX2
|
|
0003 |
DI
|
|
|
0004 |
M
|
|
|
0004 |
DX
| POINDEX1
|
|
0004 |
DX
| POINDEX2
|
|
0004 |
DU
|
|
+-----------------------------------------+
20
© 2012 IBM Corporation
Information Management
Explain Results without XML Indexes
DROP INDEX POINDEX1
DROP INDEX POINDEX2
+-----------------------------------------+
| QUERYNO | ACCESSTYPE |
ACCESSNAME
|
+-----------------------------------------+
|
0001 |
R
|
|
|
0002 |
R
|
|
|
0003 |
R
|
|
|
0004 |
R
|
|
+-----------------------------------------+
21
© 2012 IBM Corporation
Information Management
DESCRIBE Statement Referring to an XML Column
01 HV-STMT.
49 HV-STMT-LEN PIC S9(4) COMP.
49 HV-STMT-DATA PIC X(240).
MOVE 75 TO HV-STMT-LEN.
MOVE 'SELECT PODOC, XML2CLOB(PODOC) FROM ADMF001.PURCHASE_ORDER
WHERE PODOCID = 1' TO HV-STMT-DATA.
PREPARE STMT1 FROM :HV-STMT
DESCRIBE STMT1 INTO :SQLDA
After the Execution of DESCRIBE statement:
22
SQLTYPE(1) = 0989
(XML data type in DB2)
SQLTYPE(2) = 0409
(CLOB data type in DB2)
© 2012 IBM Corporation
Information Management
FETCH XML Data Based on the DESCRIBE SQLDA
SET SQLDATA(1)
TO ADDRESS OF HV-XML-CLOB.
SET SQLIND(1)
TO ADDRESS OF HV-XML-CLOB-IND.
SET SQLDATA(2)
TO ADDRESS OF HV-CLOB.
SET SQLIND(2)
TO ADDRESS OF HV-CLOB-IND.
FETCH C1 USING DESCRIPTOR :SQLDA
-804 AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT
PARAMETERS FOR THE SQL STATEMENT, REASON 16
RC16 : Unrecognized output data type.
MOVE 409 TO SQLTYPE(1).
MOVE 0
TO SQLLEN(1) .
MOVE 1048576
TO SQLLONGLEN(3).
FETCH C1 USING DESCRIPTOR :SQLDA
23
© 2012 IBM Corporation
Information Management
FETCH WITH/CURRENT CONTINUE on XML Data
01 HV-INTEGER PIC S9(4) COMP.
01 HV-XML-CLOB SQL TYPE IS XML AS CLOB(100).
DECLARE C1 CURSOR FOR SELECT PODOCID, PODOC FROM PURCHASE_ORDER
WHERE PODOCID = 1
FETCH C1 INTO :HV-INTEGER, :HV-XML-CLOB
==> SQLCODE = -433, ERROR:
VALUE <XML-Value> IS TOO LONG
FETCH WITH CONTINUE C1 INTO :HV-INTEGER, :HV-XML-CLOB
==> HV-XML-CLOB-LENGTH = 641
FETCH CURRENT CONTINUE C1 INTO :HV-INTEGER, :HV-XML-CLOB
==> HV-XML-CLOB-LENGTH = 541
HV-XML-CLOB-LENGTH = 441
HV-XML-CLOB-LENGTH = 341
24
......
© 2012 IBM Corporation
Information Management
XML Sub-document Update (DB2 10)
EXEC SQL
UPDATE PURCHASE_ORDER
SET PODOC = XMLMODIFY(
'insert nodes $item as last into /purchaseOrder/items',
XMLPARSE(DOCUMENT
'<item partNum="747-BB">
-
'
<productName>Ruby Ring</productName>
-
'
<quantity>1</quantity><USPrice>75.50</USPrice>
-
'
<shipDate>2012-08-21</shipDate>
-
'</item>') as "item")
WHERE PODOCID = 1001
END-EXEC
25
© 2012 IBM Corporation
Information Management
XML Sub-document Update (Cont.)
No easy way to update parts of a document in DB2 9

Only simple update: insert, replace, delete from XQuery

update facility
XMLMODIFY can only be used in RHS of UPDATE SET.

Only changed rows in XML table are updated

26
© 2012 IBM Corporation
Information Management
Conclusion
27
■
Introduction
■
XML Host Variables and SQL/XML Functions
■
Insert, Retrieval, Update and Delete XML Data
■
Trigger to Extract Relational Data from XML Data
■
Analyzing Access Plan with or without XML Indexes
■
DESCRIBE Statement Referring to an XML Column
■
FETCH WITH CONTINUE and FETCH CURRENT
CONTINUE on XML Data
■
XML Sub-document Update
© 2012 IBM Corporation