Chapter 4. Creating and adding XML data 69
4.5 Display database command
If you issue the DB2 -DISPLAY DATABASE command, you can see all the base objects and
the XML objects that are associated with the database as shown in Figure 4-9.
TYPE is TS for a table space, IX for an index space, and XS for an XML table space.
PART is the partition number. Because we have one partition for the partition-by-growth table
space for both the base table and the XML table, PART shows 00001. Over time, the table
space can grow to more partitions and that is why there are two lines for each of the base and
XML table spaces, at present with no value.
For non-partitioned indexes, it is the logical partition number preceded by the character L (for
example, L0001). This is the case for the DOCID and NODEID indexes.
Figure 4-9 -DISPLAY DATABASE command output
4.6 Ingesting XML data
You can use various techniques to ingest XML data. Various forms of the INSERT statement
and the LOAD utility exist.
In this section, we show an example of the SQL INSERT statement that uses a string literal to
insert rows into a table that contains XML columns. This form of INSERT is suitable for small
documents. The host variable or file reference versions of INSERT are applicable for any
length.
Example 4-6 on page 70 shows the successful insertion of the shorter version of the
Message Received XML document for our application scenario shown in Example A-2 on
page 274.
-DISPLAY DB(DSN00242)
DSNT360I -DB0B ***********************************
DSNT361I -DB0B * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -DB0B ***********************************
DSNT362I -DB0B DATABASE = DSN00242 STATUS = RW
DBD LENGTH = 4028
DSNT397I -DB0B
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
BKRTORCS TS 0001 RW
BKRTORCS TS RW
XBKR0000 XS 0001 RW
XBKR0000 XS RW
IRDOCIDB IX L0001 RW
IRDOCIDB IX L* RW
IRNODEID IX L0001 RW
IRNODEID IX L* RW
******* DISPLAY OF DATABASE DSN00242 ENDED **********************
DSN9022I -DB0B DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
***
70 Extremely pureXML in DB2 10 for z/OS
Example 4-6 Using the SQL INSERT statement to insert XML document to an XML column
INSERT INTO XMLR4.BK_TO_CSTMR_STMT(BK_TO_CSTMR_STMT)
values('<?xml version="1.0" encoding="UTF-8" ?>
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
<BkToCstmrStmt>
<GrpHdr>
<MsgId>AAAASESS-FP-STAT001</MsgId>
<CreDtTm>2010-10-18T17:00:00+01:00</CreDtTm>
</GrpHdr>
<Stmt>
<Id>AAAASESS-FP-STAT001</Id>
<CreDtTm>2010-10-18T17:00:00+01:00</CreDtTm>
<FrToDt>
<FrDtTm>2010-10-18T08:00:00+01:00</FrDtTm>
<ToDtTm>2010-10-18T17:00:00+01:00</ToDtTm>
</FrToDt>
<Acct>
<Id>
<Othr>
<Id>50000000054910000003</Id>
</Othr>
</Id>
<Ownr>
<Nm>FINPETROL</Nm>
</Ownr>
<Svcr>
<FinInstnId>
<Nm>AAAA BANKEN</Nm>
</FinInstnId>
</Svcr>
</Acct>
<Bal>
<Tp>
<CdOrPrtry>
<Cd>OPBD</Cd>
</CdOrPrtry>
</Tp>
<Amt Ccy="SEK">500000</Amt>
<CdtDbtInd>CRDT</CdtDbtInd>
</Bal>
<Bal>
<Tp>
<CdOrPrtry>
<Cd>CLBD</Cd>
</CdOrPrtry>
</Tp>
<Amt Ccy="SEK">435678.50</Amt>
<CdtDbtInd>CRDT</CdtDbtInd>
</Bal>
<Ntry>
<Amt Ccy="SEK">105678.50</Amt>
<BookgDt>
<DtTm>2010-10-18T13:15:00+01:00</DtTm>
</BookgDt>
Chapter 4. Creating and adding XML data 71
<AcctSvcrRef>AAAASESS-FP-CN_98765/01</AcctSvcrRef>
</Ntry>
<Ntry>
<Amt Ccy="SEK">200000</Amt>
<BookgDt>
<DtTm>2010-10-18T10:15:00+01:00</DtTm>
</BookgDt>
<AcctSvcrRef>AAAASESS-FP-ACCR-01</AcctSvcrRef>
</Ntry>
<Ntry>
<Amt Ccy="SEK">30000</Amt>
<BookgDt>
<DtTm>2010-10-18T15:15:00+01:00</DtTm>
</BookgDt>
<AcctSvcrRef>AAAASESS-FP-CONF-FX</AcctSvcrRef>
</Ntry>
</Stmt>
<Stmt>
<Id>AAAASESS-FP-STAT002</Id>
<CreDtTm>2010-10-17T17:00:00+01:00</CreDtTm>
<FrToDt>
<FrDtTm>2010-10-17T08:00:00+01:00</FrDtTm>
<ToDtTm>2010-10-17T17:00:00+01:00</ToDtTm>
</FrToDt>
<Acct>
<Id>
<Othr>
<Id>50000000054910000004</Id>
</Othr>
</Id>
<Ownr>
<Nm>FINPETROL</Nm>
</Ownr>
<Svcr>
<FinInstnId>
<Nm>AAAB BANKEN</Nm>
</FinInstnId>
</Svcr>
</Acct>
<Bal>
<Tp>
<CdOrPrtry>
<Cd>OPAV</Cd>
</CdOrPrtry>
</Tp>
<Amt Ccy="SEK">500300</Amt>
<CdtDbtInd>CRDT</CdtDbtInd>
</Bal>
<Bal>
<Tp>
<CdOrPrtry>
<Cd>FWAV</Cd>
</CdOrPrtry>
</Tp>
<Amt Ccy="SEK">435478.50</Amt>

Get Extremely pureXML in DB2 10 for z/OS now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.