472,992 Members | 3,476 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,992 software developers and data experts.

Error in Stored Procedure (DB21034E)

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
Aug 1 '08 #1
4 8081
Hi
Rather than using END! at the and of procedure try END@

Morarji
Aug 8 '08 #2
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
Aug 11 '08 #3
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
Aug 11 '08 #4
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
Jun 15 '09 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Alex | last post by:
Hi all, I am trying to install a java stored procedure via the windows development centre. The linux box is running 8.1 FP4 as is the windoze platform. If I am on the linux box i can install...
5
by: Lili | last post by:
I'm having problems creating a simple stored procedure in DB2. Can someone help? Here is the screen dump when I tried to load the stored procedure. Thanks for any help. Create procedure...
2
by: Solomon Obi | last post by:
I am running DB2 Express V 8.1 on Windows XP I have installed the Visual C/C++ compiler the command to invoke the compiler is cl I am attempting to install the stored procedure defined below ...
3
by: Bob Gabor | last post by:
Running 8.1 w/FP5 on Windows 2000. Everytime I try to create a stored procedure, I get the following error saying that my database is damaged: DB21034E The command was processed as an SQL...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
1
by: UDBDBA | last post by:
Hi: How can one call SYSPROC.DB2LOAD procedure within a SQL stored procedure. I get the following error: DB21034E The command was processed as an SQL statement because it was not a valid...
4
by: Pakna | last post by:
Hi, is there any way to call a JAVA stored procedure from a SQL Trigger? We are having difficulties with this and cannot verify whether DB2 even *has* this capability? Thank you very much....
0
by: Johan Neidenmark | last post by:
When i try to run this SQL statements in iSeries Access for windows (against my customers db2) i get: SQL State: 42904 Vendor Code: -7032 Message: SQL procedure, function, or trigger...
2
by: gjain12 | last post by:
When I am trying to declare a cursor over a temproay table in my stored procedure I am getting some errors. Below are the piece of code from my stored procedure in which I am getting error. ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.