Db2 13 Function Level 506: a Nice Multi-Row Insert Enhancement

Recently (via function level 506, which came out in October 2024), Db2 13 for z/OS got a multi-row INSERT enhancement that surprised me. Why was I surprised? Because - embarrassing admission - I thought the functionality had already been available in a Db2 for z/OS system. My confusion in this case stemmed largely from terminology, as I'll explain.

There's (now) more than one kind of multi-row INSERT

A colleague of mine, in the IBM Db2 for z/OS development organization, explained to me that with function level V13R1M506 activated in a Db2 13 system, the statement below can be successfully executed (provided the APPLCOMPAT value for the Db2 package associated with the statement is at least V13R1M506):

INSERT INTO EMPLOYEE 

(EMPNO, FIRSTNAME, LASTNAME, WORKDEPT)

VALUES

  ('000206', 'ELIZABETH', 'GRACE', 'A11'), 

  ('000207', 'JACK', 'JOHNSON', 'B13'),    

  ('000208', 'JENNIFER', 'WHITE', 'D15');

My initial response: "But, we can already do that." The truth: no, we couldn't. I got mixed up because of two things. First, I knew that Db2 for z/OS had multi-row insert functionality, and that this feature was introduced with Version 8, back in 2004 (and I'm not talking about an INSERT with a subselect, which is a form of multi-row insert that has been around for I don't know how long). Second reason for my confusion: in my mind, I thought that the multi-row INSERT enhancement delivered with Db2 V8 for z/OS enabled the syntax of the green-highlighted statement shown above. Not so. What Db2 V8 made possible was this kind of process:

1. In your row-insert program, declare several host variable arrays - one for each column of the table into which rows will be inserted.
2. Load the host variable arrays with the values that will be inserted into columns of the target tables (so, referencing the green-highlighted INSERT statement shown above, the first host variable array could hold values '000206', '000207' and '000208'; the second host variable array could hold values 'ELIZABETH', 'JACK' and 'JENNIFER'; and so on).
3. Once the host variable arrays have been loaded, issue the INSERT statement in the form shown below (this example is of a static INSERT statement, and it is assumed that the host variable array names are :hva1, :hva2, :hva3 and :hva4).

INSERT INTO EMPLOYEE (EMPNO, FIRSTNME, LASTNAME, WORKDEPT) 

  VALUES (:hva1, :hva2, :hva3, :hva4) FOR 3 ROWS 

  NOT ATOMIC CONTINUE ON SQLEXCEPTION; 

Obviously, if I'd ever had a need to code a multi-row INSERT statement prior to the advent of Db2 13 function level 506, I would have known right away that the new multi-row INSERT syntax provided with FL506 really was new; but, I never had that need (mostly I work with a test Db2 for z/OS system, and I often need only 3 or 4 rows in a table to try out a certain query syntax, and I always found it pretty easy to just insert those 3 or 4 rows individually - plus, I could often use already-populated tables in this system). Being quite familiar with single-row INSERT syntax, I had assumed that the Db2 V8-introduced "multi-row INSERT" capability of which I'd heard involved an extension of traditional single-row INSERT syntax. Invalid assumption.

What I really like about the new multi-row INSERT syntax provided by Db2 13 FL506

Here's the thing: it's always been pretty easy to programmatically insert a lot of rows into a Db2 for z/OS table using single-row INSERT syntax: you just specify host variables in the VALUES clause of the INSERT statement, and you place the values for row 1 into the appropriate host variables and execute the INSERT, then place the values for row 2 into the host variables and execute the INSERT, then place the values for row 3 into the host variables and execute the INSERT, and so on. The value of multi-row INSERT, then, is often related to enhanced CPU efficiency and throughput for high-volume INSERT processes. For organizations with a need for high levels of INSERT throughput, especially when the INSERTs are driven by a relatively smaller number of batch processes versus a large number of transactional processes, the new multi-row INSERT syntax provided by Db2 13 FL506 means that the CPU-efficiency and throughput benefits of multi-row INSERT can now be achieved in a way that is more programmer-friendly than before (referring the the older array-based Db2 multi-row INSERT capability).

[Added bonus: the programmer-friendly multi-row INSERT syntax delivered for Db2 13 for z/OS via function level 506 was already supported in a Db2 for Linux/UNIX/Windows (LUW) environment. This consistency between Db2 for z/OS and Db2 for LUW is really helpful for developers who work with both of these members of the Db2 database family.]

Shoot, I might even change my INSERT ways, thanks to Db2 13 FL506. I believe that old dogs can learn new tricks. Next time I'm working with one of our test Db2 for z/OS systems, and I need to put a few rows into a table I've created, I'm going to use the new multi-row INSERT syntax made possible by function level V13R1M506, versus my old "code single-row INSERT, execute it, edit the statement with different VALUES specifications, execute it again, and so on" approach. There's a Db2-geek New Year's resolution for you. Happy New Year to all.