Extremely pureXML in DB2 10 for z/OS

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>