Data Set with Oracle Enterprise in Data Stage

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