db2 procedure SYSPROC.ADMIN_DROP_SCHEMA using db2batch

Question

Calling the following from DB2 CMD successfully,

SYSPROC.ADMIN_DROP_SCHEMA('TESTSCHEMA', NULL, 'ERRORSCHEMA', 'ERRORTABLE')

Value of output parameters

Parameter Name : ERRORTABSCHEMA
Parameter Value : TESTSCHEMA

Parameter Name : ERRORTAB
Parameter Value : ERRORTABLE

Return Status = 0

But when running the same command through a Perl script using db2batch, it gives the following error,

CLI error in preparing the SQL statement:
(-469): [IBM][CLI Driver][DB2/NT] SQL0469N  The parameter mode OUT or INOUT is n
ot valid for a parameter in the routine named "ADMIN_DROP_SCHEMA" with specific
name "ADMIN_DROP_SCHEMA" (parameter number "3", name "ERRORTABSCHEMA").  SQLSTAT
E=42886

Any pointers? Working on DB2 for past few days only.

Answer 1

The parameters ERRORTABSCHEMA and ERRORTAB are INOUT variables and they would need to be declared before using them.

You can create a stored procedure instead and call the stored procedure:

CREATE OR REPLACE PROCEDURE sample.delete_schema() BEGIN
    declare varErrSchema varchar(128) default 'ERRORSCHEMA';
    declare varErrTable varchar(128) default 'ERRORTAB';
    call sysproc.admin_drop_schema ('SCHEMANAME', NULL, varErrSchema, varErrTable);
END@

You can then call the stored procedure call sample.delete_schema ()