Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    8

    Question Unanswered: Help on Stored Procedures

    Hi,

    I am using Mainframes DB2 UDB V7.1. We want to develop a SQL stored procedure, the syntax for which is given below:

    CREATE PROCEDURE TSTPROC2
    (
    IN PROV_ID_IN INTEGER,
    OUT PROV_ID INTEGER,
    OUT LST_NM CHAR(40),
    OUT FST_NM CHAR(20),
    OUT EFF_DT DATE
    )
    LANGUAGE SQL
    COLLID NOCOLLID
    NO WLM ENVIRONMENT
    BEGIN
    DECLARE SQLCODE INT DEFAULT 0;
    SELECT PROV_ID, LST_NM, FST_NM, EFF_DT
    FROM PROV WHERE PROV_ID = PROV_ID_IN;
    END

    We are able to create an entry in the SYSIBM.SYSROUTINES by executing the above statement in the SPUFI.

    But I have the following Queries:

    1) Do we need to compile the SQL stored procedure to execute it...???

    If we are supposed to compile it, please give the details of the compilation. The manual says if we compile with the program DSNHPC with the HOST(SQL) option, it converts the SQL statements in to equivalent 'C' statements. Then we need to compile & link-edit the generated 'C' statements.

    In mainframes, I am not aware of the JCL to compile the 'C' program.

    2) When we are trying to invoke the stored procedure from the COGNOS, IMPROPTU tool it is throwing the error message as "Data types not compatiable".

    Any information on this regard will be greatly helpful.


    Thanks,
    Manoj Kumar

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would try to execute the SP via SPUFI first.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2005
    Posts
    8
    Hi Marcus,

    Could you please let me know how to execute a SP via SPUFI.

    When I tried to execute in the spufi as
    "CALL TEST_PROC (IN , OUT)" it is showing an error message as
    "SQLCODE = -084, ERROR: UNACCEPTABLE SQL STATEMENT"

    Also could you please let me know whether we need to compile the SQL stored procedure to execute it...???


    Thanks,
    Manoj

Posting Permissions

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