Solved

Receiving error SQLSTATE=07004 in UDB stored procedure.

Posted on 2009-05-12
1
2,377 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:Amit_Pekamwar
1 Comment
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 24363594
does it work if you provide a value? for example
db2 +c -tv "Call fcmcore.EXTRACT_VRU(current timestamp )"
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wrong record extracting - DB2 SQL 10 175
Populating a generated column that has been added 5 88
iSeries DB2 Query 2 98
Fuzzy search functionality DB2 UDB 3 30
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question