I can't run strored procedure in Command Editor DB2

[Ivan]

Hi I am new in DB2, and I have some problems when I try run stored
procedures, and others statements. I made one stored procedure very
simple, but this show different messages.

I have followed the indications that have said me in answer to topics,
but without positive results

This is the stored procedure

CREATE PROCEDURE TESTS ( )
-- DYNAMIC RESULT SETS 1
LANGUAGE SQL MODIFIES SQL DATA

P: BEGIN ATOMIC
FOR V1 AS
SELECT B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
B.STATUSID = 3 AND
B.CLIENTID != B.MAPPEDCLIENTID
DO
DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;
END FOR;
END P

This is the message

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID
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
"LIENTID
= IDCLIENTID". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=15. SQLSTATE=42601

END FOR
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
FOR". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

As an aid to the programmer, 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.

Thanks for its amiability.

Greetings

Iván

[Shashi Mannepalli]

Here is a sample

CREATE PROCEDURE shashi.test ( IN p1 int, IN p2 int, OUT p3 INT)
LANGUAGE SQL
BEGIN
SET p2=p2+1;
SET p3=2*p1;
END
@

Now save that in a file and run it like this

db2 -td@ -vf <file>

Now call it like this....

db2 "call shashi.test(1,2,?)"

cheers..
Shashi Mannepalli
Ivan wrote:

Hi I am new in DB2, and I have some problems when I try run stored
procedures, and others statements. I made one stored procedure very
simple, but this show different messages.

I have followed the indications that have said me in answer to topics,
but without positive results

This is the stored procedure

CREATE PROCEDURE TESTS ( )
-- DYNAMIC RESULT SETS 1
LANGUAGE SQL MODIFIES SQL DATA

P: BEGIN ATOMIC
FOR V1 AS
SELECT B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
B.STATUSID = 3 AND
B.CLIENTID != B.MAPPEDCLIENTID
DO
DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;
END FOR;
END P

This is the message

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID
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
"LIENTID
= IDCLIENTID". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=15. SQLSTATE=42601

END FOR
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
FOR". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

As an aid to the programmer, 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.

Thanks for its amiability.

Greetings

Iván

[Artur]

Ivan,

In command editor you should define terminator (look at the screen
bottom), different than semicomma, because semicomma is a part of SQL
PL language. In version 9 terminator can be double character, for
example @@

Shashi gave you the way to create stored procedure from command line
processor, but why not to look at DB2 Developer Workbench? It's
free.

http://www-306.ibm.com/software/data.../download.html

-- Artur Wronski

[Ivan]

Thanks Artur, but I don't know where define terminator, what is the
bottom?

Greetings

Iván

Artur wrote:

Ivan,

In command editor you should define terminator (look at the screen
bottom), different than semicomma, because semicomma is a part of SQL
PL language. In version 9 terminator can be double character, for
example @@

Shashi gave you the way to create stored procedure from command line
processor, but why not to look at DB2 Developer Workbench? It's
free.

http://www-306.ibm.com/software/data.../download.html

-- Artur Wronski

[Ivan]

Thanks Shashi.

I made the test but wasn't successful. Show diferentes messages like
this

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
"QL BEGIN
SET p2=p2+1". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601

If I change the ";" for "," or without nothing show others messages,
but don't make nothing.
Greetings!!!

Iván

Shashi Mannepalli wrote:

Here is a sample

CREATE PROCEDURE shashi.test ( IN p1 int, IN p2 int, OUT p3 INT)
LANGUAGE SQL
BEGIN
SET p2=p2+1;
SET p3=2*p1;
END
@

Now save that in a file and run it like this

db2 -td@ -vf <file>

Now call it like this....

db2 "call shashi.test(1,2,?)"

cheers..
Shashi Mannepalli
Ivan wrote:

Hi I am new in DB2, and I have some problems when I try run stored
procedures, and others statements. I made one stored procedure very
simple, but this show different messages.

I have followed the indications that have said me in answer to topics,
but without positive results

This is the stored procedure

CREATE PROCEDURE TESTS ( )
-- DYNAMIC RESULT SETS 1
LANGUAGE SQL MODIFIES SQL DATA

P: BEGIN ATOMIC
FOR V1 AS
SELECT B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
B.STATUSID = 3 AND
B.CLIENTID != B.MAPPEDCLIENTID
DO
DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;
END FOR;
END P

This is the message

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID
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
"LIENTID
= IDCLIENTID". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=15. SQLSTATE=42601

END FOR
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
FOR". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

As an aid to the programmer, 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.

Thanks for its amiability.

Greetings

Iván

[Serge Rielau]

Ivan, which tool are you using? Do you use CLP (e.e. you type db2 from a
shell), Command Editor, Developer Center, Developer Workbench?
Do you use a text editor?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Ivan]

Thanks Serge for answer.

I'm working with DB2 v8 and using Command Editor, and it is the stored
procedure that I made.

Greetings.
Iván

CREATE PROCEDURE TESTS ( )
-- DYNAMIC RESULT SETS 1
LANGUAGE SQL MODIFIES SQL DATA
P: BEGIN ATOMIC
FOR V1 AS
SELECT B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
B.STATUSID = 3 AND
B.CLIENTID != B.MAPPEDCLIENTID
DO
DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;
END FOR;
END P
Serge Rielau wrote:

Ivan, which tool are you using? Do you use CLP (e.e. you type db2 from a
shell), Command Editor, Developer Center, Developer Workbench?
Do you use a text editor?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

Ivan wrote:

Thanks Serge for answer.

I'm working with DB2 v8 and using Command Editor, and it is the stored
procedure that I made.

OK, at the bottom of the command editor window is a tiny textbox.
In English versions it says "termination character" in blue in front of
it. When you bring up command editor the text box should contain a
semicolon (;).
All you need to do is edit the textbox to be a "@" or perhaps a dollar
"$" sign.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Ivan]

Thanks Serge.

Finally I can run the stored procedure, by something so simple that
have taken weeks.

Grettings
Iván

Serge Rielau wrote:

Ivan wrote:

Thanks Serge for answer.

I'm working with DB2 v8 and using Command Editor, and it is the stored
procedure that I made.

OK, at the bottom of the command editor window is a tiny textbox.
In English versions it says "termination character" in blue in front of
it. When you bring up command editor the text box should contain a
semicolon (;).
All you need to do is edit the textbox to be a "@" or perhaps a dollar
"$" sign.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/