Topic
  • 1 reply
  • Latest Post - ‏2012-06-07T14:16:00Z by MatthiasNicola
Sigmazen
Sigmazen
19 Posts

Pinned topic Multiple Row INSERT on UDB

‏2012-05-25T21:15:28Z |
Hi there
I've recently shifted development from zDB2 to UDB and one of the differences was the lack of multiple row insertion.
Problem Statement

  • Assume I have 50 items to insert for a given Customer
  • Assume 1ms insert time on the DB and 25ms network time each route (exaggerated to make the point)
  • Standard single-row INSERT: 50 * (25 + 1 + 25) = 2,550ms
  • Possible Multiple row INSERT: 25 + (50 * 1) + 25 = 100ms
So, as you can see, based on this hypothetical example the elapsed times saves are considerable.

The obvious candidate is using:
INSERT INTO my_table VALUES
(123, 1, '2012-05-01', 10)
,(123, 2, '2012-05-02', 100)
...
,(123, 50, '2012-05-06', 900)
which allows for a single network journey, but unfortunately our standards do not allow for this type of dynamically generated statement; it has to be done within the confines of a stored procedure (SP).

I looked through various forums but couldn't see an equivalent so came up with the following solution driven by pureXML which I thought I'd get feedback on.

Potential Solution

The table, my_table, already exists and is NOT xml based so I'm not looking for a 'create or alter the table to include an xml column' option ;-)

My new SP will have input as IN inITEMS varchar(xxxxx) which the application will populate with xml-formatted items:
<Item><Customer>123</Customer><ItemId>1</ItemId><ItemDt>2012-05-01</ItemDt><ItemAmt>10</ItemAmt></Item>
<Item><Customer>123</Customer><ItemId>2</ItemId><ItemDt>2012-05-02</ItemDt><ItemAmt>100</ItemAmt></Item>
...
<Item><Customer>123</Customer><ItemId>50</ItemId><ItemDt>2012-05-06</ItemDt><ItemAmt>900</ItemAmt></Item>

The embedded SQL looks like this:

INSERT INTO my_table (CUST_ID, ITEM_ID, ITEM_DT, ITEM_AMT)

WITH IN_ITEMS (INCOMING_ITEMS) AS (SELECT '<Items>' || inITEMS || '</Items>' FROM SYSIBM.SYSDUMMY1)
, MY_INSERT (XML_ITEMS) AS (SELECT XMLPARSE(DOCUMENT(SELECT INCOMING_ITEMS FROM IN_ITEMS)) FROM SYSIBM.SYSDUMMY1)

SELECT B.CUST_ID, B.ITEM_ID, B.ITEM_DT, ITEM_AMT
FROM MY_INSERT AS A
, XMLTABLE ('$xml/Items/Item' passing A.XML_ITEMS as "xml"
COLUMNS "CUST_ID" CHAR(03) PATH './Customer'
, "ITEM_ID" INTEGER PATH './ItemId'
, "ITEM_DT" DATE PATH './ItemDt'
, "ITEM_AMT" DECIMAL(15,0) PATH './ItemAmt' ) AS B

Note: The reason for the two CRTs was because I wasn't sure whether it was possible to put inITEMS directly within the xmlparse(document( .. )) statement, but I'll tweak that as necessary.

Anyway, it seems to me quite an elegant solution.
Any comments or alternative solutions for multiple row insertion techniques within a Stored Procedure?
Thanks in advance
Cheers
Simon
Updated on 2012-06-07T14:16:00Z at 2012-06-07T14:16:00Z by MatthiasNicola