Group Discounts available for 3+ students and Corporate Clients

Oracle Enterprise in Data Stage

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




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



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


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

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at, we will update the article in 24 hours.”

0 Responses on Oracle Enterprise in Data Stage"

Leave a Message

Your email address will not be published. Required fields are marked *


Please Enter Your Details and Query.
Three + 6