Solved

Receiving error SQLSTATE=07004 in UDB stored procedure.

Posted on 2009-05-12
1
2,296 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
Comment Utility
does it work if you provide a value? for example
db2 +c -tv "Call fcmcore.EXTRACT_VRU(current timestamp )"
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now