Ora Script in PX

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chou
Participant
Posts: 14
Joined: Wed Dec 10, 2003 12:19 pm

Ora Script in PX

Post by chou »

CREATE TABLE TB_GP_AGG_99950B1_&MMID
TABLESPACE &TBSP UNRECOVERABLE PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 10M NEXT 4M PCTINCREASE 0)
AS SELECT * from TB_GP_PREAGG_99950B1_&MMID;

CREATE INDEX IX1_GP_AGG_99950B1_&MMID ON TB_GP_AGG_99950B1_&MMID
(SK_CU_L9_ID,
PER_CCYYMM,
BAL_CD,
SEG3_CD,
SRCE_SYS_CD,
SK_PR_L5_ID,
BAL_AM)
TABLESPACE &IXSP UNRECOVERABLE PCTFREE 0
STORAGE (INITIAL 10M NEXT 4M PCTINCREASE 0);

@newtab TB_GP_AGG_99950B1_&MMID

CREATE TABLE TB_GP_AGG_8B850B1_&MMID
TABLESPACE &TBSP UNRECOVERABLE PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 6M NEXT 3M PCTINCREASE 0)
AS SELECT /*+ RULE */
b.SK_CU_L8_ID,
b.SK_CF_LB_ID,
b.SK_BR_L8_ID,
a.SK_PR_L5_ID,
a.PER_CCYYMM,
a.SEG3_CD,
a.SRCE_SYS_CD,
a.BAL_CD,
SUM(a.BAL_AM) BAL_AM
FROM TB_GP_AGG_99950B1_&MMID a,
TB_GP_DIM_CU_L9 b
WHERE a.SK_CU_L9_ID = b.SK_CU_L9_ID
GROUP BY
b.SK_CU_L8_ID,
b.SK_CF_LB_ID,
b.SK_BR_L8_ID,
a.SK_PR_L5_ID,
a.PER_CCYYMM,
a.SEG3_CD,
a.SRCE_SYS_CD,
a.BAL_CD;

CREATE INDEX IX1_GP_AGG_8B850B1_&MMID
ON TB_GP_AGG_8B850B1_&MMID
(SK_CU_L8_ID,
SK_BR_L8_ID,
SK_CF_LB_ID,
PER_CCYYMM,
BAL_CD,
SEG3_CD,
SRCE_SYS_CD,
SK_PR_L5_ID,
BAL_AM)
TABLESPACE &IXSP UNRECOVERABLE PCTFREE 0
STORAGE (INITIAL 6M NEXT 3M PCTINCREASE 0);

CREATE INDEX IX2_GP_AGG_8B850B1_&MMID
ON TB_GP_AGG_8B850B1_&MMID
(SK_CF_LB_ID,
SK_BR_L8_ID,
SK_CU_L8_ID,
PER_CCYYMM,
BAL_CD,
SEG3_CD,
SRCE_SYS_CD,
SK_PR_L5_ID,
BAL_AM)
TABLESPACE &IXSP UNRECOVERABLE PCTFREE 0
STORAGE (INITIAL 6M NEXT 3M PCTINCREASE 0);

CREATE INDEX IX3_GP_AGG_8B850B1_&MMID
ON TB_GP_AGG_8B850B1_&MMID
(SK_BR_L8_ID,
SK_CF_LB_ID,
SK_CU_L8_ID,
PER_CCYYMM,
BAL_CD,
SEG3_CD,
SRCE_SYS_CD,
SK_PR_L5_ID,
BAL_AM)
TABLESPACE &IXSP UNRECOVERABLE PCTFREE 0
STORAGE (INITIAL 6M NEXT 3M PCTINCREASE 0);

@newtab TB_GP_AGG_8B850B1_&MMID


I have a Script like above. i want a clarification that when i took the

OCI ----> XFM -----> OCI from this target OCI becomes my source to another and i have to lookup with table TB_GP_DIM_CU_L9 and to get the agg level2. if this is the case i have to take 2 tables in OCI and with the same tab2 using as lookup. i am simply confused, coz very new to PX and 7x too. i know this is a silly question, but need help from any one of gurus.

what are all the stages to use to design the above job. i have like 9 levels of Agg, above one is the first level the o/p of the first level goes to Input to Next level with joins of 2 more tables.

Thanks in Advance
Post Reply