Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Calling Mainframe DB2 Stored Procedure from DB2 UDB

    Hi All,

    I am using DB2 UDB v9.7.2 on Windows Operating System.

    I want to call a mainframe stored procedure in my DB2 UDB stored procedure. I am not sure on: -
    1. How should I call it and,
    2. What declaration needs to be given in DB2 UDB for mainframe DB2 stored procedure.

    For example:
    In mainframe, PDS Exxxx.PDS.PROC has member SQL12345 which contains body of stored procedure like
    CREATE PROCEDURE <schema>.p_test_proc (IN P1 INTEGER, OUT P2 CHAR(1))
    RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    ....


    In DB2 UDB, I have written a stored procedure, lets say <scehma>.sp_proc
    Now I want to call p_test_proc in this.

    So shall I call like:
    CALL <schema>.p_test_proc(p1, p2) ;
    or
    CALL <schema>.SQL12345(p1, p2) ;

    Also, what needs to be done in DB2 UDB for mainframe stored procedure so that DB2 knows that when there is call on p_test_proc, it has to go to mainframe stored procedure.
    As per my understanding, we need to give some sort of declaration for mainframe procedure in DB2 UDB like below but I am not sure on this.

    CREATE PROCEDURE "SCHEMA"."P_TEST_PROC"
    (
    IN P1 INTEGER,
    OUT P2 CHAR(1)
    )
    DYNAMIC RESULT SETS 1
    SPECIFIC P_TEST_PROC
    EXTERNAL NAME 'SQL12345'
    LANGUAGE SQL
    PARAMETER STYLE GENERAL
    NOT DETERMINISTIC
    FENCED THREADSAFE
    MODIFIES SQL DATA
    NO DBINFO;

    I have tried this declaration but it's giving SQL code -104.

    Any help on this would be highly appreciated.

    Many Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You'll need to enable federation (if not already) and create a sourced procedure, as described in the manual: Federation: Federated procedures for DB2 data sources
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2013
    Posts
    14
    Thank you !!

Posting Permissions

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