Error in Stored Procedure (DB21034E)

[saravananmc]

Hi All,

I am new to Stored Procedures, Cursors. I am getting an error "DB21034E The command was processed as an SQL statement because it was invalid Command Line Processor command. During SQL processing it returned SQL0104N An unexpected token "FOR" was found following "FOR P_DYN_STMT1” Expected tokens may include: "FROM". LINE NUMBER=13. SQLSTATE=42601" when I try to create this stored procedure in DB2 UDB

CREATE PROCEDURE CER_MKTG.P_CHNL_FLG_SET_ALL_LOB()
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
DECLARE P_STMT1 VARCHAR(1000);
DECLARE P_STMT2 VARCHAR(1000);
DECLARE P_CHNL VARCHAR(100);
DECLARE P_LOB VARCHAR(100);
DECLARE A VARCHAR(100);
DECLARE P_RC INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR P_DYN_STMT1;
FOR LOBF, CHANNELF AS SELECT DISTINCT LOB, CHANNEL_FLAG FROM CER_MKTG.SUPPRESSION_GRID
DO
SET P_STMT1 = '';
SET P_CHNL = '';
SET P_LOB = '';
SET P_STMT1 = 'UPDATE CER_MKTG.LOB_SUPPRESSION_TEST SET ';
SET P_CHNL = CHANNELF;
SET P_LOB = LOBF;
SET P_STMT1 = P_STMT1 || P_CHNL || '_FLG = ''Y'' WHERE SUPPRESSION_TYPE = '''|| P_LOB ||''' AND (';
SET P_STMT2 = 'SELECT SCRUB_NAME FROM CER_MKTG.SUPPRESSION_GRID WHERE LOB = '''|| P_LOB || ''' AND
CHANNEL_FLAG = ''' || P_CHNL || '''';
SET P_RC = (SELECT COUNT(*) FROM CER_MKTG.SUPPRESSION_GRID WHERE LOB = P_LOB AND CHANNEL_FLAG = P_CHNL);
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(CHAR(P_RC));
PREPARE P_DYN_STMT1 FROM P_STMT2;
OPEN C1;
SET A = '';
FETCH C1 INTO A;
WHILE (SQLCODE=0) DO
SET P_STMT1 = P_STMT1 || ' ' ||A || ' +';
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(CHAR(P_RC));
SET P_RC = P_RC - 1;
SET A = '';
FETCH C1 INTO A;
END WHILE;
CLOSE C1;
SET P_STMT1 = LEFT(P_STMT1,LENGTH(P_STMT1)-1) || ' ) = 0';
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(P_STMT1);
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(P_STMT2);

PREPARE P_DYN_STMT2 FROM P_STMT1;
SET P_STMT2 = '';
SET P_STMT2 = 'ALTER TABLE CER_MKTG.LOB_SUPPRESSION_TEST ACTIVATE NOT LOGGED INITIALLY';
PREPARE P_DYN_STMT1 FROM P_STMT2;
EXECUTE P_DYN_STMT1;
EXECUTE P_DYN_STMT2;
SET P_STMT2 = '';
SET P_STMT2 = 'COMMIT';
PREPARE P_DYN_STMT1 FROM P_STMT2;
EXECUTE P_DYN_STMT1;
END FOR;
END!

Any thoughts why I am getting this error and how to resolve it?

Thanks in advance,
Saravanan

[kalyan14]

Hi
Rather than using END! at the and of procedure try END@

Morarji

[saravananmc]

Hi Morarji,

I tried with END @, it did not work. so I replaced the for statement with a cursor which now works fine. Thanks for your help!

Saravanan

[kalyan14]

Hi saravana.....

When the same problem came for me i did this......and solved..... any way you got the solution in another way..... cheer up.......


Can you suggest me..... How to improve my DB2 server speed.......

Thanks is advance

Morarji

[arpitha2009]

CREATE PROCEDURE SHREYADB.PAYMENT1()
BEGIN
UPDATE CUSTOMER SET CUSTOMERADDRESS='DARBYTOWNPLACE' WHERE CUSTOMERID='cust222';
END!

Hi I am new to storedprocedure when try to run the above code it is giving the below error code.
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601