Stored procedure 1

[sjolin01]

We are new to loading DB2 stored procedures and are having difficulties with our first attempt. Here is the stored procedure we are trying to load...

CREATE PROCEDURE SC80_IP( IN TBL_NAME CHAR(18), IN DLR_NBR CHAR(5), OUT SEQ_NBR DECIMAL(31,0))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
UPDATE SC79 SET DB_TXN_SEQ_NBR = DB_TXN_SEQ_NBR + 1;
SELECT DB_TXN_SEQ_NBR INTO SEQ_NBR FROM SC79 ;
INSERT INTO GC01SCMY.SC80(
DB_TXN_ID,
DB_TXN_SEQ_NBR,
DB_TABLE_NAME,
DB_TXN_DAT_TIM,
PRIMARY_DLR_NBR,
REC_PROC_FLG)
VALUES (
' ',
SEQ_NBR,
TBL_NAME,
CURRENT TIMESTAMP,
DLR_NBR,
'N');
END

Our DBA gets the following error message when trying to load........

CREATE PROCEDURE GC01SCMY.SC80_IP
( IN TBL_NAME CHAR(18),
IN DLR_NBR CHAR(5),
OUT SEQ_NBR DECIMAL(31,0))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
UPDATE GC01SCMY.SC79 SET DB_TXN_SEQ_NBR = DB_TXN_SEQ_NBR + 1;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL &quot;<END-OF-STATEMENT>&quot;. SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: ;
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 641 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
Command ===> Scroll ===> CSR
X'00000281' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNT417I SQLWARN0-5 = W,,,,W, SQL WARNINGS
DSNT417I SQLWARN6-A = ,,,, SQL WARNINGS
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE614I AUTOCOMMIT IS NO; NO CHANGES COMMITTED
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 10
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 28
******************************** Bottom of Data ********************************


Any thoughts or suggestions on what is causing this problem would be appreciated

 

[scotdb]

I assume you are trying to create this in SPUFI ?

The problem is that you are using the default character to identify the end of an SQL statement (a semicolon).

Because the SQL stored procedure contains multiple &quot;SQL statements&quot; within it, when it hits the first semicolon it takes that as the end of the CREATE PROCEDURE statement and tries to parse it, of course failing miserably.

The solution is to change the SQL terminator recognized by SPFUI. This is done by select &quot;YES&quot; for option 5 (Change defaults) and specifying another character under option 1 (SQL terminator). I tend to use # here.

You then code a # at the end of the complete SQL procedure definition (and any other complete SQL statement in your SPUFI input stream such as SET CURRENT SQLID statements).

You keep semicolons for the end-of-statement terminators internally within the SQL procedure definition.

As far as I know there is no way to change the end of statement terminator used within the SQL procedure definition.

 

[sjolin01]

Thanks! This was exactly the problem. Found out you can also use the following statement to set the terminator to a
value other than the default semicolon.

--#SET TERMINATOR #

Thanks again for the help