Stored Procedure Ouptut Parameter in Toad Editor

Toad for IBM DB2
1

Hello,

I’m using some DB2 maintenance procedures like SYSPROC.ADMIN_DROP_SCHEMA. If they have input/output parameters they do not work in Toad.

In DB2 Command Window/DB2 CLP it is ok:

CALL SYSPROC.ADMIN_DROP_SCHEMA(‘TESTF3’, NULL, ‘TESTF1’, ‘ERRORTABLE’)

Value of output parameters

Parameter Name : ERRORTABSCHEMA
Parameter Value : TESTF1

Parameter Name : ERRORTAB
Parameter Value : ERRORTABLE

Return Status = 0

If I run it in Toad Editor I got errors on the input/output parameter:
DB2 Database Error: ERROR [42886] [IBM][DB2/LINUXX8664] SQL0469N The parameter mode OUT or INOUT is not valid for a parameter in the routine named “ADMIN_DROP_SCHEMA” with specific name “ADMIN_DROP_SCHEMA” (parameter number “3”, name “ERRORTABSCHEMA”).

Is it possible to run this procedure in Toad Editor?
I’m using Toad for DB2 5.0.1.

Thanks,
Dirk

2

Hi Dirk,

There appears to be a problem in the script editor with INOUT parameters in stored procedures. I opened issue 90,040 to resolve the problem.

I could not find any alternate way of executing this SP in the editor.

Thanks,
Adam

3

Hello,

Out and inout parameters should be declared in one of the following formats:
CALL SYSPROC.ADMIN_DROP_SCHEMA(‘TESTF3’, NULL, ?, ?);
CALL SYSPROC.ADMIN_DROP_SCHEMA(‘TESTF3’, NULL, @param1, @param2);

Vlas