Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>DB2 has decent support for XML data, so you have a few options on how to shred XML and apply it to one or more tables.</p> <p>The function you're looking for is <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/c0023903.html" rel="nofollow">XMLTABLE</a>. It is by far the most flexible way to shred XML into a single tabular result set.</p> <p>When your incoming XML document contains data that needs to go to multiple tables, the <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0022721.html" rel="nofollow">DECOMPOSE XML DOCUMENT</a> statement is powerful, but it requires a pre-registered XML Schema Document with special annotations that dictate the mapping of each XML node to its relational target. This statement doesn't allow a <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0003272.html" rel="nofollow">declared global temporary table</a> (DGTT) to serve as a target table, which has been a problem when the incoming XML data requires additional processing in a staging DGTT before it can be written to its final destination.</p> <p>As for your cursor-based approach to apply the shredded data, you could replace the entire loop with a single <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0010873.html" rel="nofollow">MERGE</a> statement that uses the XMLTABLE result set for the input expression. There are a lot of examples online of using DB2's MERGE statement for "upsert" processing, in which each incoming row could require an INSERT or an UPDATE depending the presence or absence of that primary key in the destination table.</p>
    singulars
    1. This table or related slice is empty.
    1. PODB2 UPDATE stored proc looping through INPUT XML
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USFred Sobotka
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODB2 UPDATE stored proc looping through INPUT XML
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks alot! I got it working as per your valuable suggestion.
      singulars
      1. PO
      1. UStechknowfreak
    2. COTo update 100 such xml records, sp takes around 1.5 minutes. I think it's too long..what could be the reasons?..
      singulars
      1. PO
      1. UStechknowfreak
    3. COEnsure the target table is properly indexed for the updates you are performing, and that the table has accurate statistics (via RUNSTATS). DB2 has an explain utility that shows the access plan that the optimizer has chosen for a query. You will probably have an easier time using the explain utility on just the merge statement itself than on the entire stored procedure. The statement you run through the explain utility will benefit from a hardcoded XML document that contains valid data for the query.
      singulars
      1. PO
      1. USFred Sobotka
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload