Data set

In this job, Path the path is the file 11columns 11load 11(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);



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


117.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.


11SCD – 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

11we can pass the values with parameters

11Can pass the parameter in runtime

11Parameterization 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. Txt11Empty file

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

11Compile and RUN

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

11When it is RUN for the 2nd time

11Sequence status from 15, 16


To identify the last highest value




Job :-  (Surrogate  key Generator)

Source Type  = Data Sequence

11Source name = C :/Skid.txt 11 Empty  file name11last generated value is stored11recalls the last generated, it status increment value11process 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 object11Create sequence shilpa;   Sequence created11Output11Do mapping

For indepth understanding of DataStage click on