DB2 stored procedure gave syntax errors

Question

I am creating a stored procedure for db2. But it is giving an error saying that

"SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "SS_TOKEN_BAK". Expected tokens may include: " END IF".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.23.42".

Following is my stored procedure.

CREATE OR REPLACE PROCEDURE TOKEN_CLEANUP_SP

BEGIN

DECLARE batchSize INTEGER;

-- ------------------------------------------
-- CONFIGURABLE ATTRIBUTES
-- ------------------------------------------
SET batchSize = 10000;      -- SET BATCH SIZE FOR AVOID TABLE LOCKS    [DEFAULT : 10000]

-- ------------------------------------------------------
-- BACKUP IDN_OAUTH2_ACCESS_TOKEN TABLE
-- ------------------------------------------------------
IF EXISTS (SELECT TABLE_NAME FROM TABLES WHERE TABLE_NAME = 'IDN_OAUTH2_ACCESS_TOKEN_BAK')
THEN
    DROP TABLE IDN_OAUTH2_ACCESS_TOKEN_BAK;
END IF;     

END/

Is anyone face this type of issue. Any help on this would be much appreciated.

mao mao · Accepted Answer · 2018-08-28T10:43:08

Verify that you have the end-of-statement delimiter configured correctly for whatever tool submits the 'CREATE OR REPLACE' procedure. Different tools have different ways to configure the block terminator (alternatively known end of statement delimiter). For command line scripts, use --#SET TERMINATOR / at the start of the file, other ways are possible.

Your code sample shows / as the block terminator, so you might want to use that character as the block delimiter. The semi-colon terminates statements inside the block.

Separately you should see that your code won't compile if the specified table does not exist in the implied schema at compilation time, because you are using static SQL. You may want to use dynamic SQL instead for the drop table statement (search for 'EXECUTE IMMEDIATE' examples).

DB2 stored procedure gave syntax errors

1 Answers