• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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

Summary
Review Date
Reviewed Item
Oracle Enterprise in Data Stage
Author Rating
5

“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 info@tekslate.com, 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 *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.