Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Call stored procedure

    I am trying to have one stored procedure call another stored procedure. I have the second stored procedure built without issue. When I build the first stored procedure I get a warning rc=4, stored procedure 2 does not exist -which is not true since I have built the stored procedure and the stored procedure resides in sysibm.sysroutines.

    I'm using the standard call statement: "Call XXXX.XXXXXX(YYY, YYY, YYY)"

    Any ideas would be helpful.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post both stored procedures along with your DB2 and OS version?

    Andy

  3. #3
    Join Date
    Aug 2007
    Posts
    56
    I'm on db2 v7.1 on z/os

    SP #1
    CREATE PROCEDURE SERDB.I21SPDD1 (IN CASE_ID INT,
    IN TYPE_UPD CHAR(2),
    IN NEWUSER CHAR(13),
    OUT CASE_ID1 INT,
    OUT TYPE_UPD1 CHAR(2),
    OUT NEWUSER1 CHAR(13),
    OUT FAILEDAT CHAR(6),
    OUT NOTFOUND CHAR(1),
    OUT SRVCOORLEV SMALLINT,
    OUT POSITION_TYPE CHAR(1),
    OUT SQLSTATE_OUT CHARACTER(5),
    OUT SQLCODE_OUT INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID SER
    COMMIT ON RETURN NO
    WLM ENVIRONMENT DDSNSPENV
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&10.81.128.205:*)'

    P1: BEGIN

    --Declare variables

    DECLARE SQLSTATE CHAR(5);
    DECLARE SQLCODE INT;
    DECLARE not_found CONDITION FOR '02000';
    DECLARE NOTFOUNDIND CHAR(1) DEFAULT 'N';
    DECLARE BEG_DT DATE;
    DECLARE BEG_DT1 DATE;
    DECLARE BEG_DT2 DATE;
    DECLARE PLAN_DUE_DT DATE;
    DECLARE ELIG_STAT CHAR(1);
    DECLARE POS_TYPE CHAR(1) DEFAULT 'Z';
    DECLARE POS_TYPE1 CHAR(1);
    DECLARE POS_TYPE2 CHAR(1);
    DECLARE SRV_COOR_LEV SMALLINT DEFAULT 9;
    DECLARE DETER_DT DATE;
    DECLARE SRVCOOR_BEG_DT DATE;
    DECLARE SIN_PLN_DT DATE;
    DECLARE PLAN_DT DATE;

    --Declare cursor
    -- DECLARE cursor1 CURSOR WITH RETURN FOR
    -- SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;

    --Declare handler

    -- DECLARE EXIT HANDLER FOR not_found
    -- SET SQLSTATE_OUT = SQLSTATE;
    -- SET NOTFOUNDIND = 'Y';

    DECLARE CONTINUE HANDLER FOR not_found
    SET NOTFOUNDIND = 'Y';

    --DECLARE EXIT HANDLER FOR NOT FOUND
    -- SET NOTFOUNDIND = 'Y';


    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    FROM SYSIBM.SYSDUMMY1;

    -- Cursor left open for client application
    -- OPEN cursor1;
    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;

    SET FAILEDAT = 'STEP01';

    SELECT I47_SRV_COOR_LEV,
    I48_POSITION_TYPE
    INTO SRV_COOR_LEV,
    POS_TYPE
    FROM SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I48_CONS_WORKER_T
    WHERE I47_CASE_ID = CASE_ID
    AND I47_CASE_ID = I48_CASE_ID
    AND I47_SRVCOOR_END_DT IS NULL
    AND I48_WKER_END_DT IS NULL
    AND I48_PRIMARY_OTHER = 'P';

    SET FAILEDAT = 'STEP02';
    IF TYPE_UPD IN ('EL','CM','CP','SL')
    AND (POS_TYPE NOT IN ('A','C','H') OR SRV_COOR_LEV <> 1)
    AND notfoundind <> 'Y'

    THEN

    SET FAILEDAT = 'STEP03';
    UPDATE SERDB.I21_CONS_CASE_T
    SET I21_SINGLE_PLAN_DT = NULL,
    I21_PLAN_DUE_DT = NULL,
    I21_USER = NEWUSER
    WHERE I21_CASE_ID = CASE_ID;

    ELSE CALL SERDB.I21SPDD2(CASE_ID1, TYPE_UPD1, NEWUSER1, FAILEDAT, NOTFOUND, SRVCOORLEV, POSITION_TYPE, SQLSTATE_OUT, SQLCODE_OUT);

    END IF;

    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;
    SET NOTFOUND = NOTFOUNDIND;
    SET SRVCOORLEV = SRV_COOR_LEV;
    SET POSITION_TYPE = POS_TYPE;
    SET CASE_ID1 = CASE_ID;
    SET TYPE_UPD1 = TYPE_UPD;
    SET NEWUSER1 = NEWUSER;

    END P1

    SP #2
    CREATE PROCEDURE SERDB.I21SPDD2 (IN CASE_ID1 INT,
    IN TYPE_UPD1 CHAR(2),
    IN NEWUSER1 CHAR(13),
    IN FAILEDAT CHAR(7),
    IN NOTFOUND CHAR(1),
    IN SRVCOORLEV SMALLINT,
    IN POSITION_TYPE CHAR(1),
    OUT SQLSTATE_OUT CHARACTER(5),
    OUT SQLCODE_OUT INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    COLLID SER
    COMMIT ON RETURN NO
    WLM ENVIRONMENT DDSNSPENV
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&10.81.128.205:*)'

    P1: BEGIN

    --Declare variables

    DECLARE SQLSTATE CHAR(5);
    -- DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT;
    -- DECLARE SQLCODE INT DEFAULT 0;
    DECLARE not_found CONDITION FOR '02000';
    DECLARE NOTFOUNDIND CHAR(1) DEFAULT 'N';
    -- DECLARE NOTFOUNDIND CHAR(1);
    DECLARE BEG_DT DATE;
    DECLARE BEG_DT1 DATE;
    DECLARE BEG_DT2 DATE;
    DECLARE PLAN_DUE_DT DATE;
    DECLARE ELIG_STAT CHAR(1);
    DECLARE POS_TYPE CHAR(1) DEFAULT 'Z';
    DECLARE POS_TYPE1 CHAR(1);
    DECLARE POS_TYPE2 CHAR(1);
    DECLARE SRV_COOR_LEV SMALLINT DEFAULT 9;
    DECLARE DETER_DT DATE;
    DECLARE SRVCOOR_BEG_DT DATE;
    DECLARE SIN_PLN_DT DATE;
    DECLARE PLAN_DT DATE;

    --Declare cursor
    -- DECLARE cursor1 CURSOR WITH RETURN FOR
    -- SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;

    --Declare handler

    -- DECLARE EXIT HANDLER FOR not_found
    -- SET SQLSTATE_OUT = SQLSTATE;
    -- SET NOTFOUNDIND = 'Y';

    DECLARE CONTINUE HANDLER FOR not_found
    SET NOTFOUNDIND = 'Y';

    --DECLARE EXIT HANDLER FOR NOT FOUND
    -- SET NOTFOUNDIND = 'Y';


    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    FROM SYSIBM.SYSDUMMY1;

    -- Cursor left open for client application
    -- OPEN cursor1;
    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;

    SET FAILEDAT = 'STPB01';


    IF TYPE_UPD1 = 'SL'
    AND SRV_COOR_LEV = 1
    AND POS_TYPE IN ('A','C','H')
    AND notfoundind <> 'Y'
    THEN
    SET FAILEDAT = 'STPB02';
    SELECT I47_SRVCOOR_BEG_DT
    INTO SRVCOOR_BEG_DT
    FROM SERDB.I21_CONS_CASE_T,
    SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I48_CONS_WORKER_T
    WHERE I21_CASE_ID = CASE_ID1
    AND I47_CASE_ID = I21_CASE_ID
    AND I48_CASE_ID = I47_CASE_ID
    AND I47_SRV_COOR_LEV = 1
    AND I47_SRVCOOR_END_DT IS NULL
    AND I48_POSITION_TYPE IN ('A','C','H')
    AND I48_WKER_END_DT IS NULL;

    SET PLAN_DUE_DT = SRVCOOR_BEG_DT + 45 DAY;
    SET FAILEDAT = 'STPB03';
    UPDATE SERDB.I21_CONS_CASE_T
    SET I21_PLAN_DUE_DT = PLAN_DUE_DT,
    I21_USER = NEWUSER1
    WHERE I21_CASE_ID = CASE_ID1;

    END IF;

    IF TYPE_UPD1 = 'EL'
    AND SRV_COOR_LEV = 1
    AND POS_TYPE IN ('A','C','H')
    AND notfoundind <> 'Y'
    THEN
    SET FAILEDAT = 'STPB04';

    SELECT I04_DETER_DT
    INTO DETER_DT
    FROM SERDB.I21_CONS_CASE_T,
    SERDB.I48_CONS_WORKER_T,
    SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I04_ELIGIBILITY_T,
    SERDB.I01_ELIG_REQUEST_T
    WHERE I21_CASE_ID = CASE_ID1
    AND I21_CASE_ID = I01_CASE_ID
    AND I21_CASE_ID = I47_CASE_ID
    AND I47_CASE_ID = I48_CASE_ID
    AND I01_ELIG_ID = I04_ELIG_ID
    AND I04_ELIG_ID = I21_CUR_ELIG_ID
    AND I21_PLAN_DUE_DT IS NULL
    AND I21_ELIG_STAT = 'E'
    AND I47_SRV_COOR_LEV = 1
    AND I47_SRVCOOR_END_DT IS NULL
    AND I48_PRIMARY_OTHER = 'P'
    AND I48_POSITION_TYPE IN ('A','C','H')
    AND I48_WKER_END_DT IS NULL;

    SET FAILEDAT = 'STPB05';
    SET DETER_DT = DETER_DT + 45 DAY;
    UPDATE SERDB.I21_CONS_CASE_T
    SET I21_PLAN_DUE_DT = DETER_DT,
    I21_USER = NEWUSER1
    WHERE I21_CASE_ID = CASE_ID1;
    END IF;


    IF TYPE_UPD1 = 'CM'
    AND SRV_COOR_LEV = 1
    AND POS_TYPE IN ('A','C','H')
    AND notfoundind <> 'Y'
    THEN
    SET FAILEDAT = 'STPB06';
    SELECT I48_POSITION_TYPE, I48_WKER_BEG_DT
    INTO POS_TYPE,
    BEG_DT
    FROM SERDB.I21_CONS_CASE_T,
    SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I48_CONS_WORKER_T
    WHERE I21_CASE_ID = CASE_ID1
    AND I21_CASE_ID = I47_CASE_ID
    AND I47_CASE_ID = I48_CASE_ID
    AND I47_SRV_COOR_LEV = 1
    AND I47_SRVCOOR_END_DT IS NULL
    AND I48_POSITION_TYPE IN ('A','C','H')
    AND I48_WKER_END_DT IS NULL;

    SELECT I48_POSITION_TYPE, I48_WKER_BEG_DT
    INTO POS_TYPE1,
    BEG_DT1
    FROM SERDB.I21_CONS_CASE_T,
    SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I48_CONS_WORKER_T
    WHERE I21_CASE_ID = CASE_ID1
    AND I21_CASE_ID = I47_CASE_ID
    AND I47_CASE_ID = I48_CASE_ID
    AND I47_SRV_COOR_LEV = 1
    AND I48_POSITION_TYPE NOT IN ('A','C','H')
    AND I48_WKER_END_DT = BEG_DT - 1 DAY;

    SELECT I48_POSITION_TYPE, I48_WKER_BEG_DT
    INTO POS_TYPE2,
    BEG_DT2
    FROM SERDB.I21_CONS_CASE_T,
    SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I48_CONS_WORKER_T
    WHERE I21_CASE_ID = CASE_ID1
    AND I21_CASE_ID = I47_CASE_ID
    AND I47_CASE_ID = I48_CASE_ID
    AND I47_SRV_COOR_LEV = 1
    AND I48_POSITION_TYPE NOT IN ('A','C','H')
    AND I48_WKER_END_DT = BEG_DT1 - 1 DAY;


    IF POS_TYPE IN ('A','C','H')
    AND POS_TYPE1 NOT IN ('A','C','H')
    AND POS_TYPE1 IS NOT NULL
    THEN
    SET FAILEDAT = 'STPB07';
    SET PLAN_DUE_DT = BEG_DT + 45 DAY;

    UPDATE SERDB.I21_CONS_CASE_T
    SET I21_PLAN_DUE_DT = PLAN_DUE_DT,
    I21_USER = NEWUSER1
    WHERE I21_CASE_ID = CASE_ID1;

    IF POS_TYPE IN ('A','C','H')
    AND POS_TYPE2 NOT IN ('A','C','H')
    THEN
    SET FAILEDAT = 'STPB08';
    SET PLAN_DUE_DT = BEG_DT + 45 DAY;

    UPDATE SERDB.I21_CONS_CASE_T
    SET I21_PLAN_DUE_DT = PLAN_DUE_DT,
    I21_USER = NEWUSER1
    WHERE I21_CASE_ID = CASE_ID1;

    END IF;
    END IF;
    END IF;

  4. #4
    Join Date
    Aug 2007
    Posts
    56

    part two of post

    IF TYPE_UPD1 = 'CP'
    AND SRV_COOR_LEV = 1
    AND POS_TYPE IN ('A','C','H')
    AND notfoundind <> 'Y'
    THEN
    SET FAILEDAT = 'STPB09';
    SELECT I49_PLAN_DT
    INTO PLAN_DT
    FROM SERDB.I49_CAP_MSTR_T,
    SERDB.I21_CONS_CASE_T,
    SERDB.I47_SRVCOOR_LVL_T,
    SERDB.I48_CONS_WORKER_T
    WHERE I21_CASE_ID = CASE_ID1
    AND I47_CASE_ID = I21_CASE_ID
    AND I48_CASE_ID = I47_CASE_ID
    AND I49_CASE_ID = I48_CASE_ID
    AND I47_SRV_COOR_LEV = 1
    AND I47_SRVCOOR_END_DT IS NULL
    AND I48_POSITION_TYPE IN ('A','C','H')
    AND I48_WKER_END_DT IS NULL;

    SET FAILEDAT = 'STPB10';
    SET PLAN_DUE_DT = PLAN_DT + 364 DAY;
    SET SIN_PLN_DT = PLAN_DT;

    UPDATE SERDB.I21_CONS_CASE_T
    SET I21_PLAN_DUE_DT = PLAN_DUE_DT,
    I21_SINGLE_PLAN_DT = SIN_PLN_DT,
    I21_USER = NEWUSER1
    WHERE I21_CASE_ID = CASE_ID1;

    SET FAILEDAT = 'STPB11';

    END IF;



    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;
    SET NOTFOUND = NOTFOUNDIND;
    SET SRVCOORLEV = SRV_COOR_LEV;
    SET POSITION_TYPE = POS_TYPE;

    END P1

    *There might be items that I need to clean up. Right now I'm more concerned about getting the stored procedure to work while getting the correct sqlstate.

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    check the definition of FAILEDAT.

    it is CHAR(6) in SP1 , but CHAR(7) in SP2.

    so "no procedure with matching arguments" can be found

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    check out the proc named SYSPROC.ADMIN_CMD()

    This proc will let you do comand line tasks like load from cursor inside a stored proc. I beleve that it will also let you call other procs within a proc.

    Below is how I use it to do a load from cursor, but im sure it can be modified with a bit of work.

    Code:
    SET V_STMT = 'Load From (SELECT * FROM CENSTG.STG_TPC_RKSA_SUBSET_VW) of cursor insert into CENSTG.STG_TPC_RKSA_SUBSET_TBL NONRECOVERABLE';
    CALL SYSPROC.ADMIN_CMD(V_STMT);
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

Posting Permissions

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