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
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
Return to “IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration