Receiving error SQLSTATE=07004 in UDB stored procedure.

Hi UDB experts -
I am trying to call sp ( mentioned in code section) with below command
db2 +c -tv "Call fcmcore.EXTRACT_VRU(?)"
& receiving below mentioned error -
Call fcmcore.EXTRACT_VRU(?)
SQL0313N  The number of host variables in the EXECUTE or OPEN statement is not
equal to the number of values required.  SQLSTATE=07004

Please advise what can be cause.

SET SCHEMA = FCMCORE@
DROP PROCEDURE FCMCORE.EXTRACT_VRU@
CREATE PROCEDURE FCMCORE.EXTRACT_VRU ( IN TS TIMESTAMP )
  SPECIFIC EXTRACT_VRU
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  MODIFIES SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
P1: BEGIN 
 
--- Start variables for fields used in cursor srv_alert_cur
        DECLARE V_ACCT_CMS_CARD_ORG CHARACTER(3) DEFAULT NULL;
        DECLARE V_ACCT_CMS_CARD_LOGO CHARACTER(3) DEFAULT NULL;
        DECLARE V_ACCT_MULTI_ORG_ID INT;
        DECLARE V_ACCT_CMS_OWNING_branch CHARACTER(9) DEFAULT NULL; 
        DECLARE V_ACCT_CMS_HOME_PH_1 CHARACTER(15) DEFAULT NULL;
        DECLARE V_ACCT_CMS_WORK_PH_1 CHARACTER(15) DEFAULT NULL;
        DECLARE V_ACCT_CMS_CH_1_MOB_1 CHARACTER(15) DEFAULT NULL;
        DECLARE V_ACCT_CMS_HOME_PH_2 CHARACTER(15) DEFAULT NULL;
        DECLARE V_ACCT_CMS_WORK_PH_2 CHARACTER(15) DEFAULT NULL;
        DECLARE V_ACCT_CMS_CH_1_MOB_2 CHARACTER(15) DEFAULT NULL;
        DECLARE V_ACCT_CMS_ADDR_POSTAL_CODE CHARACTER(9) DEFAULT NULL; 
---     DECLARE V_ALERT_HWM_FRAUD_SCORE INT;
        DECLARE V_ALERT_HWM_FRAUD_SCORE INT;
        DECLARE V_ALERT_QUEUE_ID INT;
        DECLARE V_ALERT_ACCT_NUM  CHARACTER(20) DEFAULT NULL;
        DECLARE V_ALERT_ALERT_ID INT;
        DECLARE V_ALERT_ALERT_STATUS CHARACTER(10) DEFAULT NULL;
 
--- End variables for fields used in cursor srv_alert_cur
 
--- Start variables for fields used in cursor checkout_cur
        DECLARE V_chkout_HTS_ACCT_NUM CHARACTER(20);
        DECLARE V_chkout_HTS_QUEUE_ID INT DEFAULT NULL;
        DECLARE V_chkout_HTS_ALERT_STATUS CHARACTER(1);
---     DECLARE V_chkout_HTS_REQUEST_COUNTER INT DEFAULT NULL;
        DECLARE V_chkout_HTS_REQUEST_COUNTER INT;
        DECLARE V_chkout_HTS_CREATE_TIMESTAMP TIMESTAMP DEFAULT NULL;
        DECLARE V_chkout_HTS_UPDT_TIMESTAMP TIMESTAMP DEFAULT NULL;
 
        DECLARE V_TIME_DIFFERENCE INT DEFAULT 30;
--- End variables for fields used in cursor checkout_cur
        
        DECLARE V_CHECKOUT_CNT INT;
        DECLARE VAR_CURRENT_TIMESTAMP TIMESTAMP DEFAULT NULL;
        DECLARE V_INSERT_CHECKOUT_FLAG int DEFAULT 0; 
 
DECLARE at_end int default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE dup_rec CONDITION FOR SQLSTATE '23505';
DECLARE dup_flag int default 0;
        
        DECLARE srv_alert_cur CURSOR FOR 
            Select b.cms_card_org,b.cms_card_logo,b.multi_org_id,b.cms_owning_branch,
            b.CMS_HOME_PHONE_1,b.CMS_WORK_PHONE_1,b.CMS_CH_1_MOBILE_1,
            b.CMS_HOME_PHONE_2,b.CMS_WORK_PHONE_2,b.CMS_CH_1_MOBILE_2,
            b.CMS_ADDR_POSTAL_CODE,a.hwm_fraud_score,a.queue_id,a.account_num,
            a.alert_id,a.alert_status from fcm_alert a,fcm_account b 
            Where 
--          a.QUEUE_ID=    Business will provide the queue ids   and                 
            a.Result_id is null and
            a.ALERT_STATUS='ACTIVE' and
            a.ACCOUNT_NUM=b.CMS_ACCT_NUM;
 
        DECLARE CONTINUE HANDLER FOR not_found
                SET at_end=1;
        DECLARE CONTINUE HANDLER FOR dup_rec
                SET dup_flag=1;
 
-- Opening service alert cusror
        open srv_alert_cur;     
        fetch srv_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT_CMS_CARD_LOGO,V_ACCT_MULTI_ORG_ID,V_ACCT_CMS_OWNING_branch,V_ACCT_CMS_HOME_PH_1,V_ACCT_CMS_WORK_PH_1,V_ACCT_CMS_CH_1_MOB_1,V_ACCT_CMS_HOME_PH_2,V_ACCT_CMS_WORK_PH_2,V_ACCT_CMS_CH_1_MOB_2,V_ACCT_CMS_ADDR_POSTAL_CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_ALERT_STATUS;
                
        WHILE at_end = 0 DO
                SET V_CHECKOUT_CNT=0;
                SET V_INSERT_CHECKOUT_FLAG=0;
                --SELECT count(1) into V_CHECKOUT_CNT from hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_NUM and HTS_QUEUE_ID=V_ALERT_QUEUE_ID;
                SELECT count(1) into V_CHECKOUT_CNT from hts_checkout where HTS_ACCT_NUM=V_ALERT_ACCT_NUM; 
--              SELECT current timestamp into VAR_CURRENT_TIMESTAMP FROM sysibm.sysdummy1;
                
                IF (V_CHECKOUT_CNT=0) THEN
---                     insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ALERT_QUEUE_ID,V_ALERT_ALERT_STATUS,'H',0,current timestamp,current timestamp);
                        SET V_INSERT_CHECKOUT_FLAG=1;
--                      GOTO RECORD_NEXT;
                ELSEIF (V_CHECKOUT_CNT>0) THEN
---                        select HTS_ALERT_STATUS into V_chkout_HTS_ALERT_STATUS, HTS_REQUEST_COUNTER into V_chkout_HTS_REQUEST_COUNTER from fcmcore.HTS_CHECKOUT where HTS_ACCT_NUM=V_ALERT_ACCT_NUM;
                        select HTS_ALERT_STATUS into V_chkout_HTS_ALERT_STATUS from fcmcore.HTS_CHECKOUT where HTS_ACCT_NUM=V_ALERT_ACCT_NUM;
                        select HTS_REQUEST_COUNTER into V_chkout_HTS_REQUEST_COUNTER from fcmcore.HTS_CHECKOUT where HTS_ACCT_NUM=V_ALERT_ACCT_NUM;
 
--                              SET V_TIME_DIFFERENCE=TIMESTAMPDIFF(4,V_chkout_HTS_CREATE_TIMESTAMP, V_chkout_HTS_UPDT_TIMESTAMP);
                                SET V_TIME_DIFFERENCE=30;
                                IF (V_chkout_HTS_ALERT_STATUS='X' or V_chkout_HTS_ALERT_STATUS='V' or V_chkout_HTS_ALERT_STATUS='S' or V_chkout_HTS_REQUEST_COUNTER = 6) THEN
                                        GOTO INTERNAL_LOOP;
                                END IF;
                                IF (V_TIME_DIFFERENCE < 30 ) THEN               --- Threshold needs to confirm & set from parameter
                                        GOTO INTERNAL_LOOP;
                                ELSE
                                        SET V_chkout_HTS_REQUEST_COUNTER=V_chkout_HTS_REQUEST_COUNTER+1;
                                        
                                        UPDATE HTS_CHECKOUT SET
                                                HTS_REQUEST_COUNTER=V_chkout_HTS_REQUEST_COUNTER,
                                                HTS_UPDT_TIMESTAMP=current timestamp,
                                                HTS_ALERT_STATUS='X' 
                                        WHERE HTS_ACCT_NUM=V_ALERT_ACCT_NUM;
                                        
                                        insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
                                                                V_ACCT_CMS_CARD_ORG,
                                                                V_ACCT_CMS_CARD_LOGO,
                                                                V_ACCT_MULTI_ORG_ID,
                                                                V_chkout_HTS_ACCT_NUM,
                                                                V_ACCT_CMS_OWNING_branch,
                                                                V_chkout_HTS_QUEUE_ID,
                                                                0,
                                                                CHAR(V_ALERT_HWM_FRAUD_SCORE),
                                                                '1',
                                                                '1',
                                                                V_ACCT_CMS_HOME_PH_1,
                                                                V_ACCT_CMS_WORK_PH_1,
                                                                V_ACCT_CMS_CH_1_MOB_1,
                                                                V_ACCT_CMS_HOME_PH_2,
                                                                V_ACCT_CMS_WORK_PH_2,
                                                                V_ACCT_CMS_CH_1_MOB_2);
                                END IF;
 
                                IF (V_INSERT_CHECKOUT_FLAG=1) THEN
                        --              RECORD_NEXT:
                                        ---  Insert dialer out
                                        insert into HTS_DIALER_OUT values(V_ALERT_ALERT_ID,
                                                                V_ACCT_CMS_CARD_ORG,
                                                                V_ACCT_CMS_CARD_LOGO,
                                                                V_ACCT_MULTI_ORG_ID,
                                                                V_chkout_HTS_ACCT_NUM,
                                                                V_ACCT_CMS_OWNING_branch,
                                                                V_chkout_HTS_QUEUE_ID,
                                                                0,
                                                                CHAR(V_ALERT_HWM_FRAUD_SCORE),
                                                                '1',
                                                                '1',
                                                                V_ACCT_CMS_HOME_PH_1,
                                                                V_ACCT_CMS_WORK_PH_1,
                                                                V_ACCT_CMS_CH_1_MOB_1,
                                                                V_ACCT_CMS_HOME_PH_2,
                                                                V_ACCT_CMS_WORK_PH_2,
                                                                V_ACCT_CMS_CH_1_MOB_2);
                                        --- Insert HTS_CHECKout
                                        
                                        insert into fcmcore.hts_checkout values(V_ALERT_ACCT_NUM,V_ALERT_QUEUE_ID,V_ALERT_ALERT_STATUS,'H',1,current timestamp,current timestamp);
                                ELSE
                                        update hts_checkout set HTS_REQUEST_COUNTER=V_chkout_HTS_REQUEST_COUNTER, HTS_UPDT_TIMESTAMP=current timestamp,HTS_ALERT_STATUS='X' where HTS_ACCT_NUM=V_chkout_HTS_ACCT_NUM;
                                END IF;
                END IF;
                INTERNAL_LOOP:
        fetch srv_alert_cur into V_ACCT_CMS_CARD_ORG,V_ACCT_CMS_CARD_LOGO,V_ACCT_MULTI_ORG_ID,V_ACCT_CMS_OWNING_branch,V_ACCT_CMS_HOME_PH_1,V_ACCT_CMS_WORK_PH_1,V_ACCT_CMS_CH_1_MOB_1,V_ACCT_CMS_HOME_PH_2,V_ACCT_CMS_WORK_PH_2,V_ACCT_CMS_CH_1_MOB_2,V_ACCT_CMS_ADDR_POSTAL_CODE, V_ALERT_HWM_FRAUD_SCORE, V_ALERT_QUEUE_ID, V_ALERT_ACCT_NUM, V_ALERT_ALERT_ID,V_ALERT_ALERT_STATUS;
        END WHILE;
CLOSE srv_alert_cur;
--COMMIT;
END P1
@

Open in new window

Amit_PekamwarAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
momi_sabagConnect With a Mentor Commented:
does it work if you provide a value? for example
db2 +c -tv "Call fcmcore.EXTRACT_VRU(current timestamp )"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.