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

Data Set with Oracle Enterprise in Data Stage

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

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

 

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.

SCD 1

11SCD – II :-  (7.5 version)

 

Screenshot_77

 

 

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)

Screenshot_78

 

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

Screenshot_79

 

 

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)

Screenshot_80

 

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

Summary
Review Date
Reviewed Item
Data Set with 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 Data Set with 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.