Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130

    Unanswered: DB2 SQL Procedures in z/Os

    Dear friends,

    I am creating a SQL procedure in DB2 on z/Os and giving the syntax error though the SQL is correct. We are in Db2 V8. Does SQL procedures allowed to create on z/os? I heard some thing to use development center to create.
    Any ideas. My sql is as follws here.

    CREATE PROCEDURE DRUGLIST
    ( IN PHOSPNO CHAR(4)
    ,OUT PMNEMONIC CHAR(8)
    ,OUT PDRUGSTAT CHAR(1)
    ,OUT PDRUGNM CHAR(76)
    ,OUT PDURATION CHAR(2)
    ,OUT PFREQ CHAR(10)
    ,OUT PDOSE DECIMAL(5, 2)
    ,OUT PSQLCODE INTEGER
    ,OUT PSQLSTATE CHAR(5)
    ,OUT PSQLERRMC VARCHAR(250)
    )
    RESULT SETS 0
    MODIFIES SQL DATA
    EXTERNAL NAME DRUGLIST
    NO DBINFO
    WLM ENVIRONMENT TDB2SID1
    STAY RESIDENT NO
    COLLID ICVPC
    PROGRAM TYPE MAIN
    RUN OPTIONS 'TRAP(OFF),RPTOPTS(OFF)'
    COMMIT ON RETURN NO
    LANGUAGE SQL
    P1: BEGIN
    DECLARE SQLCODE INTEGER;
    DECLARE SQLSTATE CHAR(5);
    SELECT
    MNEMONIC
    ,DRUGSTAT
    ,DRUGNM
    ,DURATION
    ,FREQ
    ,DOSE
    INTO PMNEMONIC
    ,PDRUGSTAT
    ,PDRUGNM
    ,PDURATION
    ,PFREQ
    ,PDOSE
    FROM IDVPCS01.ITORDSET
    WHERE HOSPNO = PHOSPNO ;

    SET PSQLCODE = SQLCODE ;
    SET PSQLSTATE = SQLSTATE;
    SET PSQLERRMC = 'ADIOS' ;
    END P1
    I got the following error. I tried to work around to remove the semi colon and it didn't work.
    DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME
    SYMBOLS THAT MIGHT BE LEGAL ARE: ;
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

  2. #2
    Join Date
    Mar 2009
    Posts
    16
    Are you using
    END
    @

    as the statement terminator?

  3. #3
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Thank you for the reply.

    I use this way for the SQL code

    P1: BEGIN

    Sql statements;

    END P1
    ;

    Should I use different way?

    Thanks,
    Srini

  4. #4
    Join Date
    Mar 2009
    Posts
    16
    Here is a layout for Stored procedure

    CREATE PROCEDURE procedurename (IN tmp INTEGER)
    P1: BEGIN
    sql goes here
    P1:END

    @

  5. #5
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    Thank you. I tried with your suggestion. It didn't work. Same issue. Looks like SQL procedures syntax different from cobol procedure definitions on z/os environment.

  6. #6
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    It worked after removing the declare statements. Did this procedure create an entry in catalog tables like SYSIBM.SYSROUTINES/SYSPARMS tables. I did not see any entry in these tables after the SQL execution. I wonder where this procedure definition stores in catalog table for DB2 sql procedures?

  7. #7
    Join Date
    Mar 2009
    Posts
    16
    Not sure about DB2 z/Os. For DB2 UDB they are under application objects--> stored procedure

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    On DB2 z/OS V8, you have no real LANGUAGE SQL procedures available yet. You need to create a job that precompiles the stored proc (into some host language) and then use that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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