How do I run multiple CALL statements in DB2

Question

I'm trying to run multiple statements from DB2 (Windows 11.5) using remote SQL. So far I have:

BEGIN
CALL SYSPROC.SYSTS_CREATE('my_schema', 'my_index', 'my_schema.my_table (column1)', '', 'en_us', ?)
CALL SYSPROC.SYSTS_CREATE('my_schema', 'my_index', 'my_schema.my_table (column2)', '', 'en_us', ?)
END;

This gives me the following error:

SQL Error [07004]: The number of variables in the EXECUTE statement, the number of variables in the OPEN statement, or the number of arguments in an OPEN statement for a parameterized cursor is not equal to the number of values required.. SQLCODE=-313, SQLSTATE=07004, DRIVER=4.26.14

This is running from DBeaver but ultimately I want to run this as one command from C#, just trying to get the syntax right before I code it into the application. Note that each of the CALL lines work fine on their own.

Answer 1

The SYSTS_CREATE procedure has an output parameter, and you must use the corresponding variable in a compound statement in your application.

BEGIN
  DECLARE V_MSG VARCHAR (32672);
  CALL SYSPROC.SYSTS_CREATE('my_schema', 'my_index', 'my_schema.my_table (column1)', '', 'en_us'
    , V_MSG);
  CALL SYSPROC.SYSTS_CREATE('my_schema', 'my_index', 'my_schema.my_table (column2)', '', 'en_us'
    , V_MSG);
END

To run it in some tool like DBeaver you must change the statement delimiter from the default one (;) to some other like @ and place it at the end of the statement.

The statement delimiter is changed in DBeaver in the Window -> Preferences -> Editors -> SQL Editor -> SQL Processing -> Delimiters -> Statement Delimiter field.