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”)

Derivation

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”)

Expire

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.

Transformer

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