Hi,

We are trying to create a simple DB in DB2 9.5.400.576 Enterprise, but we had no joy so far.

We have used (IBM) Command Center, (IBM) Command Line and Toad for DB2.

That´s one procedure we tried to create (for testing purposes - "test" table exists):

SET SCHEMA = SA@

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SA"@

CREATE PROCEDURE "SA".PTest ( IN p_name VARCHAR(4) )

LANGUAGE SQL

NOT DETERMINISTIC

NO EXTERNAL ACTION

CONTAINS SQL

CALLED ON NULL INPUT

BEGIN

SELECT * FROM test WHERE name = p_name;

END@

It doesn't matter which Store Procedure we are trying to create, we always get the same fucking error:

ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "*" was found following "INPUT BEGIN SELECT". Expected tokens may include: "(". LINE NUMBER=8. SQLSTATE=42601


I found on many forums about the statement delimiter. We have also tried that (we choose @) but strangely enough we get the same error.
I have no idea what could be happening.

Any help will be much appreciate.

Cheers

That is not how you return a result from a Stored Procedure in DB2. Try this:

Andy

Thanks ARWinner,

I have tried that (on IBM Command Editor), but got a new error:

------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE "SA".PTest ( IN p_name VARCHAR(4) )

LANGUAGE SQL

NOT DETERMINISTIC

NO EXTERNAL ACTION

CONTAINS SQL

CALLED ON NULL INPUT

BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT * FROM test WHERE name = p_name;

OPEN CURSOR1;

END@
------------------------------------------------------------------------------
CREATE PROCEDURE "SA".PTest ( IN p_name VARCHAR(4) )

LANGUAGE SQL

NOT DETERMINISTIC

NO EXTERNAL ACTION

CONTAINS SQL

CALLED ON NULL INPUT

BEGIN
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT * FROM test WHERE name = p_name;

OPEN CURSOR1;

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:
SQL0579N Routine "PTEST" (specific name "") attempted to read data but was
not defined as READS SQL DATA or MODIFIES SQL DATA. LINE NUMBER=17.
SQLSTATE=42985

SQL0579N Routine "PTEST" (specific name " ") attempted to read data but was not defined as READS SQL DATA or MODIFIES SQL DATA.

Explanation:

The program used to implement the body of a routine is not allowed to
read SQL data.

User response:

Remove any SQL statements that read data then recompile the program.
Investigate the level of SQL allowed as specified when defining the
routine.

sqlcode: -579

sqlstate: 38004

sqlstate: 42985

Take a look at this example:

CREATE PROCEDURE EMP_NAME_SEARCH
( INOUT p_name VARCHAR(12), INOUT O_SQLSTATE CHAR (5))
SPECIFIC EMP_NAME_SEARCH
RESULT SETS 1
READS SQL DATA
LANGUAGE SQL

BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE C1 CURSOR WITH RETURN FOR
SELECT *
from employee
WHERE FIRSTNME = p_name;

---------------------------------------------
-- Declare exit handlers
---------------------------------------------

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE INTO o_sqlstate
FROM sysibm.sysdummy1;

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

DECLARE EXIT HANDLER FOR SQLWARNING
SELECT SQLSTATE INTO o_sqlstate
FROM sysibm.sysdummy1;

OPEN C1;
END
DB20000I The SQL command completed successfully.

call EMP_NAME_SEARCH ('JOHN', '00000')

Value of output parameters
--------------------------
Parameter Name : P_NAME
Parameter Value : JOHN

Parameter Name : O_SQLSTATE
Parameter Value : 00000


Result set 1
--------------

EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------
000050 JOHN B GEYER E01 6789 08/17/1979 MANAGER 16 M 09/15/1955 80175.00 800.00 3214.00
000290 JOHN R PARKER E11 4502 05/30/2006 OPERATOR 12 M 07/09/1985 35340.00 300.00 1227.00

2 record(s) selected.

Return Status = 0