[SOLVED] ATOMIC INSERT in STORED PROCEDURE

ATOMIC INSERT in STORED PROCEDURE

I'm fairly new to stored procedure. I have to design a stored procedure for ATOMIC insert (Mass insert). I'm using COBOL program to call the stored procedure in DB2. I will store values in array and have to insert all at one shot. Below is the query we are using in COBOL program and which I have to convert to stored procedure.

INSERT INTO TABLE_NAME
(COLUMN1
,COLUMN2
,COLUMN3
,COLUMN4
,COLUMN5)
VALUES
(VALUE1
,VALUE2
,VALUE3
,VALUE4
,VALUE5)
FOR  WS-SUB   ROWS
ATOMIC

VALUE1,VALUE2,VALUE3,VALUE4,VALUE5 are array elements and WS-SUB is number of occurrence.

I want to know, If I can handle array in stored procedure or want to know if its possible to do ATOMIC insert in DB2 stored procedure.

Thanks in advance.

Solution

Following the documentation for DB2 on z/OS 12.0.0:

A DB2 Stored Procedure may be configured to use arrays as parameter types, see Example of using arrays in an SQL procedure.

However, if your intention is calling this from COBOL you may run into issues as the documentation for Supported SQL data types in COBOL embedded SQL applications indicates:

Arrays are not supported by the COBOL precompiler

Another approach would be to pass your data as something like a CLOB or VARCHAR delimited by a character and then parse it within your stored procedure.

By default DB2 Stored Procedures does not commit on return, so another option would be to iterate your COBOL tables and call the stored procedure repeatedly.