Data Set with Oracle Enterprise in Data Stage
Data setIn 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 :-
- How to create Sequence
- 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 14th for 14 records
When it is RUN for the 2nd 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