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.
How to prevent skip in oracle sequence in DS Parallel Job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 13
- Joined: Fri Oct 08, 2004 6:19 am
Re: How to prevent skip in oracle sequence in DS Parallel Jo
Two things...srini_ramesh wrote: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.
1. Try setting the target Db stage to run in sequential mode
2. Set the sequence cache to 1 instead of 0