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

Can you post both stored procedures along with your DB2 and OS version?

Andy

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;

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.

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

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.