Hi every one,

When the following script of creating a stored proc is run from DB2 Command Center, the error message is showed as below. What wrong with it? How is a stored proc created in DB2? Can Command Center be used to create stored proc? Thanks.

CREATE PROCEDURE LBU_INV.SITEM
(IN id INT)
LANGUAGE SQL
BEGIN
select GAME_NO from INVENTORY_ITEM where INVENTORY_ITEM_ID=id;
END

Error Message:

---------------------------------- Script -----------------------------------
Untitled1
-----------------------------------------------------------------------------
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 "PROCEDURE" was found following " CREATE ".

Expected tokens may include: "SCHEMA". LINE NUMBER=1. SQLSTATE=42601



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 "id" was found following "(IN ". Expected

tokens may include: "JOIN <joined_table>". SQLSTATE=42601



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 "LANGUAGE

SQL". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601



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 "BEGIN".

Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601



SQL0104N An unexpected token ";" was found following "INVENTORY_ITEM_ID=id".

Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601



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




---------------------------------- Script -----------------------------------
Untitled1
-----------------------------------------------------------------------------
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 "SITEM" was found following "E PROCEDURE

LBU_INV.". Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=1.

SQLSTATE=42601



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 "id" was found following "(IN ". Expected

tokens may include: "JOIN <joined_table>". SQLSTATE=42601



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 "LANGUAGE

SQL". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601



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 "BEGIN".

Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601



SQL0104N An unexpected token ";" was found following "INVENTORY_ITEM_ID=id".

Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601



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

1) change your script like this:

CREATE PROCEDURE LBU_INV.SITEM
(IN id INT)
LANGUAGE SQL
BEGIN
select GAME_NO from INVENTORY_ITEM where INVENTORY_ITEM_ID=id;
END@

2) run it like this:
db2 -td@ -svf <script file>


Note: if you are trying to return that result set of the query,
you will need to use a cursor. Look in you samples directory if you need help (...\sqllib\samples\sqlproc\ )

Andy

I tried it and got the error:

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 "select GAME_NO from INVENTO" was found
following " LANGUAGE SQL BEGIN ". Expected tokens may include:
"<psm_labellable_stmt>". LINE NUMBER=5. SQLSTATE=42601

Thanks

That is because it is invalid syntax. You cannot just do a select in a SP. You need to use a CURSOR to process or return a result set. Look at the examples that I pointed to earlier.

Andy

Hi Andy,

Thanks. But another problem is occured when I tried again as the following. Where can xlC be got?

Thanks agian, Steven

*** BIND /../tmp/P1537700.sqc ***

LINE MESSAGES FOR P1537700.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.

*** COMPILE /.../tmp/P1537700.c ***

#!/bin/sh
PATH=$PATH:/usr/vacpp/bin
PATH=$PATH:/usr/ibmcxx/bin
PATH=$PATH:/usr/lpp/xlC/bin
export PATH

SQLROUTINE_FILENAME=P1537700
export SQLROUTINE_FILENAME
export SQLROUTINE_ENTRY=pgsjmp

xlC -I/.../include P1537700.c -bE:P1537700.exp -e pgsjmp -o P1537700 -L/.../sqllib/lib -lc -ldb2

*** /.../tmp/P1537700.exp ***
pgsjmp


sh: xlC: not found.

----------------------------------------------------------------------------

SQL7032N SQL procedure "" not created. Diagnostic file is "".

It looks like you either do not have a C compiler on the DB2 server, or it is not configured properly.

What version of DB2 and Which OS?

Andy

OS AIX 5.2
DB2 7.2.8

Sorry, I cannot help with C compiler for AIX. I think if you search the archives here you might find the answer.

Andy

what is the result when you run the command under instance_home
>db2set -all

????
you may have to change compile variables.

I little birdy told me that a C compiler will no longer be required for SQL stored procedures in v8, not sure which fixpack.

Looks like it's part of Stinger:

http://www-306.ibm.com/software/data/db2/stinger/

Click on "Key Messages & Activities"

Hi,
My script spcrterr.db2 contains
CREATE PROCEDURE pq5.spcrterr
(
IN incdnt_id char(20),
IN Err_msg_cd integer,
IN Chg_sts_cd smallint(2),
IN Cmt_id smallint,
IN Cmt_lang_cd CHAR(2),
OUT SQL_ERROR_CD INTEGER,
OUT APP_ERROR_CD INTEGER
)
SPECIFIC pq5.spcrterr
LANGUAGE SQL
/* This stored procedure updates status code in pq5.incdnt_all, add insert one row in pq5.incdnt_chg_hist and pq5.incdnt_chg_err_msg_rltn.*/

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE error_cd INTEGER DEFAULT 0;
DECLARE currentTSP timestamp;
DECLARE USRID INTEGER(4);
DECLARE DIV_ID SMLLINT(2);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET SQL_ERROR_CD = SQLCODE;
SET currentTSP = current timestamp;
SET SQL_ERROR_CD = 0;
set APP_ERROR_CD = 500;
update pq5.incdnt_all set STS_CD = 2064 where INCDNT_ID = incdnt_id;
if SQL_ERROR_CD<>0 then
return 1;
end if;
DECLARE Cs1 CURSOR for select usrid,div_id from pq5.incdnt_all where INCDNT_ID=incdnt_id;
OPEN Cs1;
FETCH Cs1 INTO USRID,DIV_ID;
INSERT into pq5.incdnt_chg_hist (incdnt_id,changing_usrid,changing_div_id,cmt_id,c mt_lang_cd)
values(incdnt_id,USRID,DIV_ID,Cmt_id,Cmt_lang_cd);
CLOSE Cs1;

if SQL_ERROR_CD<>0 then
return 1;
end if;
update pq5.incdnt_chg_err_msg_reltn set incdnt_id = incdnt_id,incdnt_chg_tmstmp = currentTSP,err_msg_cd = Err_msg_cd
where incdnt_id = incdnt_id;
if SQL_ERROR_CD<>0 then
return 2;
end if;
set APP_ERROR_CD = 0;
END@

on command prompt mode I tried to execute the below command
db2 -td@ -svf spcrterr.db2
but it throws below erro message:
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 "smallint" was found following "r, IN
Chg_sts_cd". Expected tokens may include: "CHAR". LINE NUMBER=5.
SQLSTATE=42601

please hep me on this.

Thanks
Surjya

First, start a new thread when asking a new question. This one is nearly 6 years old.

Look at the line: IN Chg_sts_cd smallint(2),

It is wrong.

Andy

Those type declarations are wrong. Integer types don't have a precision in SQL: