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 "<END-OF-STATEMENT>". 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
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 "<END-OF-STATEMENT>". 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