DB2 stored procedure to run multiple insert & update SQL statements using List array from Java application

Question

I want to use a DB2 stored procedure to run multi insert & update SQL statements from my Java application. I have prepared below procedure that contains error.

In this implementation, I first want to create a temporary table to store the SQL statements. Then use a loop to insert the SQL statements into the temporary table using a prepared statement. Finally, we execute the SQL statements in batch mode.

To call this stored procedure from my Java application, I want to pass the list of SQL statements as a List array of strings.

Here is my procedure:

CREATE OR REPLACE PROCEDURE multiInsertAndUpdate(IN sqlStatements VARCHAR(2000) ARRAY)
BEGIN
DECLARE batchStmt VARCHAR(2000);
DECLARE i INTEGER DEFAULT 0;
-- Create a temporary table to store the SQL statements
CREATE TEMPORARY TABLE temp_sql_statements (
id INTEGER GENERATED ALWAYS AS IDENTITY,
statement VARCHAR(2000)
);
-- Insert the SQL statements into the temporary table using a prepared statement
DECLARE insertStmt STATEMENT;
SET insertStmt = 'INSERT INTO temp_sql_statements(statement) VALUES(?)';
FOR i IN 1..CARDINALITY(sqlStatements) DO
   EXECUTE IMMEDIATE insertStmt USING sqlStatements[i];
END FOR;
-- Execute the SQL statements in batches of 1000
SET i = 0;
WHILE i >= 0 DO
DECLARE stmtCursor CURSOR WITH HOLD FOR
SELECT statement FROM temp_sql_statements ORDER BY id FOR UPDATE SKIP LOCKED FETCH FIRST 1000 ROWS ONLY;
OPEN stmtCursor;
FETCH FROM stmtCursor INTO batchStmt;
IF batchStmt IS NOT NULL THEN
  DECLARE batchStmts VARCHAR(20000);
  SET batchStmts = batchStmt;
  SET i = i + 1;
  WHILE FETCH FROM stmtCursor INTO batchStmt DO
    SET batchStmts = batchStmts || batchStmt || ';';
    SET i = i + 1;
    IF i % 1000 = 0 THEN
      EXECUTE IMMEDIATE batchStmts;
      SET batchStmts = '';
    END IF;
  END WHILE;
  IF batchStmts <> '' THEN
    EXECUTE IMMEDIATE batchStmts;
  END IF;
ELSE
  SET i = -1;

END IF;
CLOSE stmtCursor;
END WHILE;
-- Drop the temporary table
DROP TABLE temp_sql_statements;
END

Please assist align the procedure

// JAVA Call the stored procedure with the array of SQL statements

    static void executeStatements(Connection conn, List<String> sqlStatements)
{

    try {
        CallableStatement cstmt = conn.prepareCall("{CALL multiInsertAndUpdate(?)}");

        // Convert the List<String> to a SQL Array
        Array sqlArray = conn.createArrayOf("VARCHAR", sqlStatements.toArray());

        cstmt.setArray(1, sqlArray);
        cstmt.execute();

        sqlArray.free();
        cstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The procedure can't execute due to errors

Answer 1

    CREATE PROCEDURE BatchProcess()
LANGUAGE SQL
BEGIN
    DECLARE v_batch_size INT DEFAULT 100;
    DECLARE v_records_processed INT DEFAULT 0;
    DECLARE v_control_number INT DEFAULT 1;
    DECLARE v_error_message VARCHAR(255);
    DECLARE v_continue INT DEFAULT 1;

    -- Declare cursor for selecting records in batches
    DECLARE cursor_batch CURSOR WITH HOLD FOR
        SELECT A.col1, A.col2, B.col3, B.col4
        FROM TableA A
        INNER JOIN TableB B ON A.col1 = B.col1 AND A.col2 = B.col2;

    -- Declare exception handler
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS EXCEPTION 1 v_error_message = MESSAGE_TEXT;
            INSERT INTO AuditTable (timestamp, records_updated, control_number, error_message)
            VALUES (CURRENT TIMESTAMP, v_records_processed, v_control_number, v_error_message);
            SET v_continue = 0;
        END;

    -- Create global temporary table
    DECLARE GLOBAL TEMPORARY TABLE SessionTable (
        col1 INT,
        col2 INT,
        col3 INT,
        col4 INT
    ) WITH REPLACE ON COMMIT PRESERVE ROWS;

    -- Create index on session table
    CREATE INDEX idx_session ON SESSION.SessionTable (col1, col2);

    -- Open the cursor
    OPEN cursor_batch;

    -- Loop to process records in batches
    my_loop: LOOP
        -- Fetch records into session table
        INSERT INTO SESSION.SessionTable (col1, col2, col3, col4)
        SELECT col1, col2, col3, col4
        FROM cursor_batch
        FETCH FIRST v_batch_size ROWS ONLY;

        -- Check if any records were inserted
        GET DIAGNOSTICS v_records_processed = ROW_COUNT;
        IF v_records_processed = 0 THEN
            LEAVE my_loop;
        END IF;

        -- Update TableB using session table
        UPDATE TableB B
        SET B.col3 = S.col3, B.col4 = S.col4, B.col5 = S.col5, B.col6 = S.col6
        FROM SESSION.SessionTable S
        WHERE B.col1 = S.col1 AND B.col2 = S.col2;

        -- Get number of records processed
        GET DIAGNOSTICS v_records_processed = ROW_COUNT;

        -- Write to audit table
        INSERT INTO AuditTable (timestamp, records_updated, control_number, error_message)
        VALUES (CURRENT TIMESTAMP, v_records_processed, v_control_number, NULL);

        -- Increment control number
        SET v_control_number = v_control_number + 1;

        -- Clear session table for next batch
        DELETE FROM SESSION.SessionTable;
    END LOOP my_loop;

    -- Close the cursor
    CLOSE cursor_batch;
END;

Answer 2

Interesting procedure. I assume your usage is such that you are not worried what SQL gets injected by the Java program?

I've been using arrays a lot recently, typically with row types. For a working (but more complex) example see my SAVE_ATTRIBUTES procedure at https://github.com/easydataservices/db2-auth/blob/main/db2/schema/module_attributes.sql, and then look at the Java code for saveAttributes(String sessionId, List sessionAttributes) at https://github.com/easydataservices/db2-auth/blob/main/java/src/open/auth/AuthAttributesDao.java

Note line 116:

Struct[] attributeStructs = new Struct[sessionAttributes.size()];

Also line 141:

attributeArray = connection.createArrayOf(schemaName + "ATTRIBUTES.SESSION_ATTRIBUTE", attributeStructs);

You see that is what I am passing to createArrayOf(...), Struct[] not Object[]. In JDBC any row type is a Struct, so an array of a row type is a Struct[].

In your case, VARCHAR is a String, so for a VARCHAR array you should be passing String[].

Hope this helps.