473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Writing a stored procedure in command editor

Hi,
I am new to DB2 and i just started worked on it a couple of days back. I have created basic EMPLOYEE table from control centre which has 2 fields: EmpNo, EmpName.

I am trying to write a simple stored proc in db2 command editor to insert values into these fields.

CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@

And when i execute this i get this error:



------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@;
------------------------------------------------------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ')
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 "E
VALUES(001, 'XYZ')". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601

END @
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

Can someone help me in solving this problem.

Thanks.
Aug 30 '07 #1
2 13045
Hi,

I think the problem is coming because of the delimeter.
There should be a ; after End if you are not changing the delimeter.

Try this code
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END;
@

Regards,
rinku

Hi,
I am new to DB2 and i just started worked on it a couple of days back. I have created basic EMPLOYEE table from control centre which has 2 fields: EmpNo, EmpName.

I am trying to write a simple stored proc in db2 command editor to insert values into these fields.

CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@

And when i execute this i get this error:



------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ');
END
@;
------------------------------------------------------------------------------
CREATE PROCEDURE EMP_PROC()
LANGUAGE SQL
BEGIN
INSERT INTO EMPLOYEE VALUES(001, 'XYZ')
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 "E
VALUES(001, 'XYZ')". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601

END @
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

Can someone help me in solving this problem.

Thanks.
Sep 7 '07 #2
Remi
9
Hi :

Send you a Proc Example
---------------------------------------

CREATE PROCEDURE RACE.SP_LISTARPAISES6(
IN OPCION VARCHAR(2) ,
IN CODPAIS VARCHAR(3) ,
IN DENPAIS VARCHAR(30),
IN TIP VARCHAR(1)
)
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE SQLSELE1 CHAR(512);
DECLARE C1 CURSOR WITH RETURN FOR S1;
-- DEBERAN PASAR EL VALOR DE CODPAIS INCLUYENDO EL PORCENTUAL '02%'--
IF TIP = '1' THEN
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ? FETCH FIRST 5000 ROWS ONLY ';
ELSE
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ?
FETCH FIRST 5000 ROWS ONLY';
END IF;
PREPARE S1 FROM SQLSELE1;
OPEN C1 USING CODPAIS;
END;
Sep 19 '07 #3

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

Similar topics

0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
1
by: Karl | last post by:
Hi, I am trying to put SQL Server Stored Procedures into Sourcesafe as per the Microsoft Knowledge Base article 818368, but have run into a problem. The web server is SQL Server 2000 running on...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
5
by: Nesa | last post by:
I have a stored procedure that wraps a moderately complex query over 5, 6 related tables. The performance of the procedure is unacceptably slow as it takes on average 5-10 min to complete. To...
11
by: db2admin | last post by:
hello, is it possible to write compound sql without stored procedure or trigger. can i just run in command center of db2. regards, jagdip singh
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
3
by: annoir | last post by:
I am trying to create a stored procedure using the DB2 Connect Development Center. I am able to create the project, connect to the remote database on the mainframe and create the stored procedure....
1
by: PVBHANU | last post by:
Hi, I am using DB2 V9.1 windows , can any one please tell me how to complile and execute a stored procedure. I followed Alldatabse->database->Application Object->stored procedure...But No...
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.