Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Angry Unanswered: How to create Stored Procedure in DB2?

    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

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

  3. #3
    Join Date
    Mar 2004
    Posts
    61
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    61

    Where can xlC be got?

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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Mar 2004
    Posts
    61
    OS AIX 5.2
    DB2 7.2.8

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Sorry, I cannot help with C compiler for AIX. I think if you search the archives here you might find the answer.

    Andy

  9. #9
    Join Date
    Dec 2003
    Posts
    78
    what is the result when you run the command under instance_home
    >db2set -all

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

  10. #10
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I little birdy told me that a C compiler will no longer be required for SQL stored procedures in v8, not sure which fixpack.
    --
    Jonathan Petruk
    DB2 Database Consultant

  11. #11
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Looks like it's part of Stinger:

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

    Click on "Key Messages & Activities"
    --
    Jonathan Petruk
    DB2 Database Consultant

  12. #12
    Join Date
    Jan 2010
    Posts
    26

    I am getting below error while trying to create a SP

    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

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Those type declarations are wrong. Integer types don't have a precision in SQL:
    Code:
    DECLARE USRID INTEGER(4);
    DECLARE DIV_ID SMLLINT(2);
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Dec 2014
    Posts
    1

    Create Stored Procedure

    Hi Everyone, i have a requirement can someone help me in writing the procedure for the same..!
    Here is the requirement..!
    We have Audit_Type , AUDIT_LOG and AUDIT_DETAIL Tables as per below screen shot.
    Click image for larger version. 

Name:	sp.jpg 
Views:	1 
Size:	47.1 KB 
ID:	16046

    There is list of Audit_Type IDs For Example
    1.IOG_ADT1
    2.IOG_ADT2
    3.IOG_ADT3
    4.IOG_ADT4

    In the Audit_ log table , We can have one log for each Audit_ Type_ID as FK with AUDIT_Status ( S or F ) .
    In the Audit_Detail table , we can have many Audit_Detail entry with the one Audit_Log_ID ( FK ).
    In our Interfaces , When any file comes , we make the following Audit entries in the DB.

    AUDIT_LOG Table.

    First Entry
    AUDIT_LOG Entry with Type IOG_ADT1 with S
    Corresponding in the AUDIT_DETAIL Table,
    We make entries
    DETAIL_NAME = FileName DETAIL_VALUE = <FileNameValue>
    Few more detail Entries with same AUDIT_LOG
    Second Entry
    AUDIT_LOG Entry with Type IOG_ADT2 with S/F
    Third Entry
    AUDIT_LOG Entry with Type IOG_ADT3 with S/F
    Fourth Entry
    AUDIT_LOG Entry with Type IOG_ADT4 with S/F

    Stored Procedure for Duplicate File Name Validation

    We need to extract the all Audit_LOG Entries
    Get List of CORRELID from AUDIT_LOG

    Based on below attributes
    Where Audit_DETAIL ( DETAIL_NAME = FileName and DETAIL_VALUE=<FileNameValue> and AUDIT_DETAIL .AUDIT_LOG_ID = AUDIT_LOG.AUDIT_LOG_ID)
    AndAUDIT_TYPE_ID= IOG_ADT1 and AUDIT_LOG.CORRELID != <ParameterPassed> and AUDIT_LOG.Status= S )

    LOOP based on CorrelIDArray
    Select Count(AUDIT_LOG_ID) Table AUDIT_LOG Where AUDIT_TYPE_ID = IOG_ADT4 and STATUS = 'S' and CORRELID= CorrelIDArray[Index]
    IF Count =1 ,
    SET DuplicateFileNameFound= True
    Then Exit from Loop
    ElseIF Count =0
    SET DuplicateFileNameFound= False
    Continue Loop

    END IF
    Increase Index
    End Loop

Posting Permissions

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