Oracle Enterprise in Data Stage

  • Share this blog:

Oracle Enterprise

Table name = dim 1

Write method = upset Upset order  = update then insert Compile and RUN 11In SQL plus, Select * from dim 1;   Skip      

S no       S            name

1             111          shilpa

2             222        Renuka

3             333       Archama  

To insert new values 11delete SRC;

  • Insert in to SRC Values (222, ‘Anil’); Insert in to SRC Values (444, ‘Sravani’);
  • Commit;
  • Compile and Run job (a)
  • Compile and Run Job (b)
  • Select * from dim 1;
  • Skip S no S name

1             111               shilpa

2              222              Renuka

3               333             Archama

4                 444           Sravani    

SCD – type 2 Table 1 Create table SRC  (S no  number  (3), S name  varchar2  (25)  ); Table 2  Create Table dim 2 (skid number  (2), S no number  (3), S name varchar2 (25), Es – date data, EE – data date, ACF varchar2 (2)); 5 types for SCD – Type 2 S 1 :- Select output link as fact link S2 :- Map the look up key S No S3 :-Source type  = Flat file =Source Name  = (Empty file name) S4:-   Identify  the purpose of each field Skid = Surrogate key S no= business Key S name = type 2 Es –date = Effective date EE – date  = Expiration date ACF  = Current Indicator  (“Y”)


Skid = Surrogate key S no=  S no S name = S name Es –date = Current date EE – date  = “9999 – 12-31”  (default DB/2  format) ACF  = Current Indicator  (“Y”)


EE – date   = Date from Julian Day ((Julian Day from Date  (Current Date  ())  -1)) ACF = “N” S5 :- Map required fields to fact table

Load properties

S1 :- Set Data set properties S2 :-Set oracle properties Table Name  = dim 2 Write method = upset (update and insert) Upset order = update then Insert   Screenshot_76  

Oracle enterprise 0 Load dim 2 table à column à change date to Times stamp  

Oracle enterprise 1  Load SRC table  

S no              S name 111               shilpa 222                 Renuka 333                 Archama

Learn DataStage by Tekslate - Fastest growing sector in the industry.
Explore Online DataStage Training and course is aligned with industry needs & developed by industry veterans.
Tekslate will turn you into DataStage Expert.


Drag and drop the fields Times stamp to date   (before. Es. date)   = Es – date Times stamp to date   (before. Es. date)   = Ee – date In before  11 change Es – date, Ee – date to date   SCD 1)  Select fact link 2)  Map the look up Key SNO 3) Source type = flat file Source name = E:/shilpa/SCD 2. Txt; 4)Select  all 11Right click 11 clear derivation   Derivation               key               purpose                     expire Double click  Surrogate key ()        Skid               Surrogate key   S no                business key S name          type 2   Current date ()         Es – date      effective date “9999 – 12 - 31”       Ee –date       Expiration date “Y”                               ACF               Current indicator   Ee – date = Date from Julian Day   ((Julian Day from Date  (Current Date  ())  -1))  

  1. Map required fields to fact table

Compile and Run Output Skid            S no                   S name                  Es – date       Ee- date        ACF 1                     111                    Shilpa                                                                         Y 2                    222                   Reruka                                                                      Y 3                     333                 Archana                                                                     Y   (b)  Data set ---------------à oracle enterprise Copy the path of DFM Data set  (a)  -11 Columns -11Save --11(SCD) For indepth understanding of DataStage click on

You liked the article?

Like : 0

Vote for difficulty

Current difficulty (Avg): Medium

Recommended Courses


About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.

Stay Updated

Get stories of change makers and innovators from the startup ecosystem in your inbox