How to prevent skip in oracle sequence in DS Parallel 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.

[T42]

Is it really important to have the numbers uniformily in order with no skipping?

It's just a surrogate key.

You could also use the Surrogate Key Generator (or the Column Generator) to handle this.

[GIDs]

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.

Two things...
1. Try setting the target Db stage to run in sequential mode
2. Set the sequence cache to 1 instead of 0