Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Unanswered: Not able to create ANY procedure on DB2 9.5

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by jfbaro
    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:

    Code:
    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@
    Andy

  3. #3
    Join Date
    Jun 2009
    Posts
    6
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •