Strange production scenario

Tools & Utilities
Enthusiast

Strange production scenario

Hi , 

We have encountered a strange data issue in Prod. We are trying to load a history table using BTEQ. 

The history table has 

PRIMARY INDEX( ACCT_NB )

UNIQUE INDEX( ACCT_NB ,EFF_DT )

A volatile table with the exact structure as the history table including indexes is created and data is manipulated and loaded into this staging volatile table. Then SCD2 is implemented by comparing this staging and the actual history table. ie old rows are expired and updates and new rows are inserted with expiry_dt = NULL.

the last few days especially on alternate days the job is failing with unique index violation , when it inserts from the staging into the history . The code populates

eff_DT=Current_Date , EXPIry_dt=current_date -1. 

for may31st run we are seeing the following which is very strange. since this has been failing , we checked the history table for rows with eff_dt=5/31 before we submitted the load. The history table load failed with unique index violation and we saw the following rows in the table even though the load failed.







SRC_SYS_ACCT_NB EFF_DT EXPIR_DT LSTUPDT_DT
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016
  5/31/2016 5/31/2016 5/31/2016

History table update code

UPDATE  STG

    FROM STAGING1 STG,

              HISTORY_DTL V500

     SET ACTION_CD='C'

    WHERE V500.ACCT_NB    =  STG.ACCT_NB 

      AND V500.EXPIR_DT           IS NULL

      AND (V500.EFF_DT            <> STG.EFF_DT    

       OR V500.NON_KEY_Col1          <> STG.NON_KEY_Col1

       OR V500.NON_KEY_Col1  <> NON_KEY_Col1);

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

/*____________________________________________________________________*/

/* UPDATE ACTION_CD='A' FOR STAGING TABLE ACCT_NB THAT ARE    */

/* NOT PRESENT IN THE HISTORY TABLE.                                  */

/*____________________________________________________________________*/

 UPDATE STAGING1

    SET ACTION_CD='A'

   WHERE (SRC_SYS_ACCT_NB)

   NOT IN (SELECT ACCT_NB

             FROM HISTORY_DTL

            WHERE EXPIR_DT IS NULL);

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

/*____________________________________________________________________*/

/* UPDATE HISTORY TABLE FOR ACCT_NB THAT HAVE CHANGES IN THE  */

/* STAGIN TABLE.                                                      */

/*____________________________________________________________________*/

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

UPDATE V500                                         

  FROM HISTORY_DTL V500   

      ,STAGING1 STG            

   SET EXPIR_DT   = CURRENT_DATE - 1                

      ,LSTUPDT_DT = DATE                            

      ,LSTUPDT_USER_ID_CD = USER                    

  WHERE V500.ACCT_NB = STG.ACCT_NB  

    AND V500.EXPIR_DT IS NULL                       

    AND STG.ACTION_CD = 'C'                         

   ;                                                

   INSERT INTO History_TBL 

           from STAGING; 


we dont understand why the code updating the same date for both eff_dt and expir_dt. and why is the staging table load successful but the history tbl is failing with unque index violation. 

Please advise

1 REPLY
Enthusiast

Re: Strange production scenario

sorry about the data alignment . 

 

SRC_SYS_ACCT_NB
EFF_DT



5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016
EXPIR_DT



5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016

LSTUPDT_DT




5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016
5/31/2016