Data set

In this job, Path the path is the file columns load (SCD)

Oracle enterprise 

Table name =  dim 2

Write method  = upset (up date and insert)

Upset order = update then insert

Compile and Run

GO to SQL plus 

Select * from dim2;

TO insert new values 

• Delete SRC ;
• Insert into SRC values (111,”Smitha”);

Insert in to SRC values (444, “Anu”);

• Compile and RUN Job (a);

Compile and RUN Job  (a);

Output 

Skid                             S no                   S name                  Es – date       Ee- date        ACF

———————————————————————————————————–

1                                       111                    Shilpa                                                                Y

2                                         222                   Reruka                                                            Y

3                                         333                 Archana                                                          Y

4                                         111                 Sumathi                                                            Y

7.5 version

SCD 1 and 2

SCD’s are implemented in 7 using change capture  and change apply.  (SCD 1)

Change Capture 

Capture the changes between before data and After data and generates change code.

SCD 1

SCD – II :-  (7.5 version)

If code =1 ,ten ESS –date = current data

EE –date =”9999-12-31”

Code =2

ee-dab =current date -1

Surrogate key Stage

• Alternative primary key
• Generates Sequence number
• Surrogate key Stage differs completely in stage and version in 7.5 and 8.0.1

Q: Column Generator also  generates Sequence number, So why do we use Surrogate key ?

Sol:    Surrogate Key

we can pass the values with parameters

Can pass the parameter in runtime

Parameterization is possible

Interested in mastering DataStage Training?Enroll now for FREE demo on DataStage Training.

Column Generator 

• We can’t do
• version :-

1. How to create Sequence
2. How to restart Sequence

Q:  How can you update parameter values

Sol:-   APS, Routines

Job :-  (Source Type  = flat file)

Surrogate  key 

Generated output column name = SKID

Source Name  = C : /Skid. TxtEmpty file

Source Type  = flat file  output  DO mapping Drag SKID to top

Compile and RUN

Generate Sequence no. 1 to 14^th for 14 records

When it is RUN for the 2^nd time

Sequence status from 15, 16

To identify the last highest value

Job :-  (Surrogate  key Generator)

Source Type  = Data Sequence

Source name = C :/Skid.txt  Empty  file namelast generated value is storedrecalls the last generated, it status increment valueprocess is auto missedàlast generated value can also be stored in data base

Job:-   (Source type = Sequence Data base)

Source type  = data type Sequence

Data base type = oracle

Oracle server name = oracle

Password = tiger

Username = Scott

Source name = shilpa

In SQL plus,   create a data base Sequence objectCreate sequence shilpa;   Sequence createdOutputDo mapping

For indepth understanding of DataStage click on

• Introduction to DataStage
• Architecture of Data Stage
• Oracle Enterprise in Data Stage
• SCD(Slow changing Dimension) in Data Stage
• DataStage Tutorials