DB2 How To

DB2 Batch separator

DB2 does not have fixed batch delimiter. IBM DB2 command line processor (CLP) and DB2BATCH utility have a command to set a delimiter. For example --# SET DELIMETER !. SqlDbx does recognizes CLP SET delimiter command but even without it present still can process most of the batches because it parses SQL statements and able to recognize end of statement, compound SQL or procedure. Following SQL statement will be successfully recognized by SqlDbx without need to explicitly set delimiter.

CREATE PROCEDURE (FUNCTION, TRIGGER) name

...

BEGIN

--Compound SQL statements

END

Default delimiter is ";". If SQL script sets delimiter to something else it will be reset to ";" after all statements in the batch processed.

Executing Stored Procedures

Below is the example of how to call stored procedure on DB2 z/OS which has input and output parameters. The below code fragment was generated by selecting "Script Execute..." menu item. Leading underscore indicates that output parameter value should not be part of returned results.

VARIABLE COMMANDS '-DISPLAY THREAD(*) TYPE(*)' VARCHAR
VARIABLE LEN_COMMANDS 200 INTEGER
VARIABLE PARSE_TYPE 'THD' VARCHAR
VARIABLE _COMMANDS_EX INTEGER OUT
VARIABLE _IFCA_RET INTEGER OUT
VARIABLE _IFCA_RES INTEGER OUT
VARIABLE _XS_BYTES INTEGER OUT
VARIABLE ERROR_MSG VARCHAR OUT

CALL SYSPROC.DSNACCMD (?_COMMANDS, ?_LEN_COMMANDS, ?_PARSE_TYPE, ?COMMANDS_EX, ?IFCA_RET, ?IFCA_RES, ?XS_BYTES, ?ERROR_MSG);

Parameters also can be specified inline. Below is modified version of the code above.

VARIABLE COMMANDS '-DISPLAY THREAD(*) TYPE(*)' VARCHAR
VARIABLE PARSE_TYPE 'THD' VARCHAR

CALL SYSPROC.DSNACCMD (?COMMANDS, ?LEN_COMMANDS$int$in$100, ?PARSE_TYPE, ?_COMMANDS_EX$int$out, ?_IFCA_RET$int$out, ?_IFCA_RES$int$out, ?_XS_BYTES$int$out, ?_ERROR_MSG$int$out);

Parameter format: NAME $ TYPE $ [IN | INOUT | OUT] $ VALUE.

In case of DB2 LUW or DB2 iSeries there's no need to have VARIABLE statement or specify types for IN parameters.

CALL SYSPROC.DSNACCMD ('-DISPLAY THREAD(*) TYPE(*)', 100, 'THD', ?_COMMANDS_EX$int$out, ?_IFCA_RET$int$out, ?_IFCA_RES$int$out, ?_XS_BYTES$int$out, ?_ERROR_MSG$int$out);