How to prevent skip in oracle sequence in PX Job?

[srini_ramesh]

Hi,
I have developed a parallel job in datastage that loads the target table and assigns an oracle sequence for every record inserted. During update, it only updated the record without incrementing the surrogate key sequence. Earlier the oracle sequence was created with a cache=20 but then the DBA recreated the sequence with cache=0. However, still there is a skip in the sequence assigned to surrogate key whenever the job is rerun for a new record insert.
I am using User defined Upsert in the target oracle enterprise stage. During insert i am assigning sequence.nextval to the surrogate key. However, during update I am not assigning any sequence to the surrogate key. I am just assigning the existing target sequence value to the target surrogate key.
Following is the Insert and Update statement that i am using in target Stage:
*************************************************************
INSERT
INTO
#$DS_IP_EDW_SCHEMA#.DIM_ASYS_CORP_PROD
(ASYS_CORP_PROD_SK, PROD_NBR, PROD_DESC, IFDA_CATG_CD, IFDA_CATG_DESC, IFDA_CLS_CD, IFDA_CLS_DESC, MFR_NBR, MFR_NM, PROD_LBL, SLS_PCK_SIZE, EACH_CONV_FCTR, NET_WGHT, GROSS_WGHT, LBL_IND, PROD_BRAND, MFR_PROD_NBR, PROD_ACTV_CD, PROD_CLS_CD, PROD_CLS_DESC, PROD_CATG_CD, PROD_CATG_DESC, PROD_GRP_CD, PROD_GRP_DESC, UPC_CASE, UPC_PCKG, PROD_REC_TYPE, CATCH_WGHT_IND, GOVT_UOM, GOVT_CONV_FCTR, BOL_CD, PRTRY_GRP_ID, PROD_COLOR, PROD_IMPRT_IND, KRAFT_MFR_IND, KRAFT_LBL_IND, MTRL_SFTY_SHEET_IND, MTRL_SFTY_SHEET_EXPRTN_DATE, PROD_SEG_LVL1, PROD_SEG_LVL2, PROD_SEG_LVL3, PROD_SEG_LVL_STRT_DATE, PROD_LONG_DESC)
VALUES
(Decode('1','2',ORCHESTRATE.ASYS_CORP_PROD_SK,#$DS_IP_EDW_SCHEMA#.SEQ_ASYSCORPSK.NEXTVAL), ORCHESTRATE.PROD_NBR, ORCHESTRATE.PROD_DESC, ORCHESTRATE.IFDA_CATG_CD, ORCHESTRATE.IFDA_CATG_DESC, ORCHESTRATE.IFDA_CLS_CD, ORCHESTRATE.IFDA_CLS_DESC, ORCHESTRATE.MFR_NBR, ORCHESTRATE.MFR_NM, ORCHESTRATE.PROD_LBL, ORCHESTRATE.SLS_PCK_SIZE, ORCHESTRATE.EACH_CONV_FCTR, ORCHESTRATE.NET_WGHT, ORCHESTRATE.GROSS_WGHT, ORCHESTRATE.LBL_IND, ORCHESTRATE.PROD_BRAND, ORCHESTRATE.MFR_PROD_NBR, ORCHESTRATE.PROD_ACTV_CD, ORCHESTRATE.PROD_CLS_CD, ORCHESTRATE.PROD_CLS_DESC, ORCHESTRATE.PROD_CATG_CD, ORCHESTRATE.PROD_CATG_DESC, ORCHESTRATE.PROD_GRP_CD, ORCHESTRATE.PROD_GRP_DESC, ORCHESTRATE.UPC_CASE, ORCHESTRATE.UPC_PCKG, ORCHESTRATE.PROD_REC_TYPE, ORCHESTRATE.CATCH_WGHT_IND, ORCHESTRATE.GOVT_UOM, ORCHESTRATE.GOVT_CONV_FCTR, ORCHESTRATE.BOL_CD, ORCHESTRATE.PRTRY_GRP_ID, ORCHESTRATE.PROD_COLOR, ORCHESTRATE.PROD_IMPRT_IND, ORCHESTRATE.KRAFT_MFR_IND, ORCHESTRATE.KRAFT_LBL_IND, ORCHESTRATE.MTRL_SFTY_SHEET_IND, ORCHESTRATE.MTRL_SFTY_SHEET_EXPRTN_DATE, ORCHESTRATE.PROD_SEG_LVL1, ORCHESTRATE.PROD_SEG_LVL2, ORCHESTRATE.PROD_SEG_LVL3, ORCHESTRATE.PROD_SEG_LVL_STRT_DATE, ORCHESTRATE.PROD_LONG_DESC)
*************************************************************
Update statement:

UPDATE
#$DS_IP_EDW_SCHEMA#.DIM_ASYS_CORP_PROD
SET
ASYS_CORP_PROD_SK=Decode('1','2',ORCHESTRATE.ASYS_CORP_PROD_SK,ASYS_CORP_PROD_SK),
PROD_DESC = ORCHESTRATE.PROD_DESC, IFDA_CATG_CD = ORCHESTRATE.IFDA_CATG_CD, IFDA_CATG_DESC = ORCHESTRATE.IFDA_CATG_DESC, IFDA_CLS_CD = ORCHESTRATE.IFDA_CLS_CD, IFDA_CLS_DESC = ORCHESTRATE.IFDA_CLS_DESC, MFR_NBR = ORCHESTRATE.MFR_NBR, MFR_NM = ORCHESTRATE.MFR_NM, PROD_LBL = ORCHESTRATE.PROD_LBL, SLS_PCK_SIZE = ORCHESTRATE.SLS_PCK_SIZE, EACH_CONV_FCTR = ORCHESTRATE.EACH_CONV_FCTR, NET_WGHT = ORCHESTRATE.NET_WGHT, GROSS_WGHT = ORCHESTRATE.GROSS_WGHT, LBL_IND = ORCHESTRATE.LBL_IND, PROD_BRAND = ORCHESTRATE.PROD_BRAND, MFR_PROD_NBR = ORCHESTRATE.MFR_PROD_NBR, PROD_ACTV_CD = ORCHESTRATE.PROD_ACTV_CD, PROD_CLS_CD = ORCHESTRATE.PROD_CLS_CD, PROD_CLS_DESC = ORCHESTRATE.PROD_CLS_DESC, PROD_CATG_CD = ORCHESTRATE.PROD_CATG_CD, PROD_CATG_DESC = ORCHESTRATE.PROD_CATG_DESC, PROD_GRP_CD = ORCHESTRATE.PROD_GRP_CD, PROD_GRP_DESC = ORCHESTRATE.PROD_GRP_DESC, UPC_CASE = ORCHESTRATE.UPC_CASE, UPC_PCKG = ORCHESTRATE.UPC_PCKG, PROD_REC_TYPE = ORCHESTRATE.PROD_REC_TYPE, CATCH_WGHT_IND = ORCHESTRATE.CATCH_WGHT_IND, GOVT_UOM = ORCHESTRATE.GOVT_UOM, GOVT_CONV_FCTR = ORCHESTRATE.GOVT_CONV_FCTR, BOL_CD = ORCHESTRATE.BOL_CD, PRTRY_GRP_ID = ORCHESTRATE.PRTRY_GRP_ID, PROD_COLOR = ORCHESTRATE.PROD_COLOR, PROD_IMPRT_IND = ORCHESTRATE.PROD_IMPRT_IND, KRAFT_MFR_IND = ORCHESTRATE.KRAFT_MFR_IND, KRAFT_LBL_IND = ORCHESTRATE.KRAFT_LBL_IND, MTRL_SFTY_SHEET_IND = ORCHESTRATE.MTRL_SFTY_SHEET_IND, MTRL_SFTY_SHEET_EXPRTN_DATE = ORCHESTRATE.MTRL_SFTY_SHEET_EXPRTN_DATE, PROD_SEG_LVL1 = ORCHESTRATE.PROD_SEG_LVL1, PROD_SEG_LVL2 = ORCHESTRATE.PROD_SEG_LVL2, PROD_SEG_LVL3 = ORCHESTRATE.PROD_SEG_LVL3, PROD_SEG_LVL_STRT_DATE = ORCHESTRATE.PROD_SEG_LVL_STRT_DATE, PROD_LONG_DESC = ORCHESTRATE.PROD_LONG_DESC
WHERE
PROD_NBR = ORCHESTRATE.PROD_NBR
*************************************************************

Please tell me what goof up is happening here. Is it because of oracle sequence or is it because datstage parallel extender is causing this abrupt behaviour.

[ray.wurlod]

Given that you are only using the NEXTVAL property of the sequence within the SQL there is no reason to believe it's anything within DataStage.

Could any other Oracle user - even you - be doing

Code: Select all

SELECT SEQ_ASYSCORPSK.NEXTVAL FROM DUAL;

to check what is happening? This also increments the sequence. If you want to see the current value you should be using

Code: Select all

SELECT SEQ_ASYSCORPSK.CURRVAL FROM DUAL;

This does not increment the sequence.

At the end of the day it does not matter that there are gaps; a surrogate key should be invisible, and its values provide only uniqueness.