Revision history [back]

To execute numerous insert and update SQL statements using a List array from a Java application in a DB2 stored procedure, you can follow these steps:

1. Create a stored procedure in DB2 that takes an input parameter of type VARCHAR representing JSON array data.

2. Convert the JSON array data to a Java List array in the stored procedure using a Java Object type using the following code:

CALL SQLJ.PARSE('import java.util.List; import com.ibm.db2.jcc.DB2TypeAccessor; import com.ibm.db2.jcc.DB2Struct;', 0);

DECLARE @arr OBJECT; SET @arr = CAST(json_input AS OBJECT);

CALL SQLJ.INSTALL_JAR('db2jcc4.jar', 'java');

CALL SQLJ.SET_CLASSPATH('db2jcc4.jar');

CREATE OR REPLACE JAVA CLASS "DB2StoredProc" AS 'public class DB2StoredProc { public static void executeStatements(List<string> statements) { for (String statement : statements) { DB2TypeAccessor.executeUpdate(statement); } } }';

1. Call the Java class and method from the stored procedure using the following code:

BEGIN DECLARE @list OBJECT; SET @list = new ArrayList(); SET @list[:]= CAST(JSONVALUE(jsoninput, CONCAT('$[', idx, ']')) AS VARCHAR(1000)) CALL "DB2StoredProc".executeStatements(@list); END

1. Call the stored procedure from your Java application and pass the List array of SQL statements as a JSON array parameter.

With these steps, your DB2 stored procedure can execute numerous insert and update SQL statements by utilizing a List array from a Java application.