I've recently shifted development from zDB2 to UDB and one of the differences was the lack of multiple row insertion.
- 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
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.
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:
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