Wednesday, 4 August 2021
Thursday, 23 May 2013
Wednesday, 22 May 2013
Free DataStage Lab Exercises
Hey Guys Download Free DataStage Lab Exercises
DataStage+Lab+Exercises+Part-1
DataStage+Lab+Exercises+Part-2
DataStage+Lab+Exercises+Part-1
DataStage+Lab+Exercises+Part-2
Tuesday, 21 May 2013
Transformer Stage using Stripwhitespaces Function
Stripwhitespaces is the function used for the remove before,after and middle of the characters.
Some times we get the data as below
e_id,e_name
10,em y
20, j ul y
30,re v o l
40,w a go n
Take Job Design as
Se.File ------ Tx------D.s
Read and load the data in Sequential file stage
Go to Transformer stage
Here, we use stripwhitespaces function in the required column derivation.
You can write expression as below
Stripwhitespaces(e_name) for e_name
Click ok
Compile and Run the data
You will get the data after removal of all the spaces between the characters,
before and after spaces also.
Some times we get the data as below
e_id,e_name
10,em y
20, j ul y
30,re v o l
40,w a go n
Take Job Design as
Se.File ------ Tx------D.s
Read and load the data in Sequential file stage
Go to Transformer stage
Here, we use stripwhitespaces function in the required column derivation.
You can write expression as below
Stripwhitespaces(e_name) for e_name
Click ok
Compile and Run the data
You will get the data after removal of all the spaces between the characters,
before and after spaces also.
Transformer-Stage-Example
If our requirement is to filter the data department wise from the file below
samp_tabl
1,sam,clerck,10
2,tom,developer,20
3,jim,clerck,10
4,don,tester,30
5,zeera,developer,20
6,varun,clerck,10
7,luti,production,40
8,raja,priduction,40
And our requirement is to get the target data as below
In Target1 we need 10th & 40th dept employees.
In Target2 we need 30th dept employees.
In Target1 we need 20th & 40th dept employees.
Take Job Design as below
Read and Load the data in Source file
In Transformer Stage just Drag and Drop the data to the target tables.
Write expression in constraints as below
dept_no=10 or dept_no= 40 for table 1
dept_no=30 for table 1
dept_no=20 or dept_no= 40 for table 1
Click ok
Give file name at the target file and
Compile and Run the Job to get the Output
Read more about Transformer Stage with example
samp_tabl
1,sam,clerck,10
2,tom,developer,20
3,jim,clerck,10
4,don,tester,30
5,zeera,developer,20
6,varun,clerck,10
7,luti,production,40
8,raja,priduction,40
And our requirement is to get the target data as below
In Target1 we need 10th & 40th dept employees.
In Target2 we need 30th dept employees.
In Target1 we need 20th & 40th dept employees.
Take Job Design as below
Read and Load the data in Source file
In Transformer Stage just Drag and Drop the data to the target tables.
Write expression in constraints as below
dept_no=10 or dept_no= 40 for table 1
dept_no=30 for table 1
dept_no=20 or dept_no= 40 for table 1
Click ok
Give file name at the target file and
Compile and Run the Job to get the Output
Read more about Transformer Stage with example
What is Lookup stage? Uses of Look up stage?
It performs on dataset read into memory from any other parallel job stage that can
output data.
The main uses of the lookup stage is to map short codes in the input dataset onto
expanded information from a look up table which is then joined to the data coming from
input. For example, some we get the data with customers name and address. Here the
data identifies state as a two letters or three letters like mel for melbourne or
syd for sydney. But you want the data to carry the full name of the state by
defining the code as the key column. In this case lookup stage used very much.
It will reads each line, it uses the key to look up the stage in the lookup table.
It adds the state to the new column defined for the output link.
So that full state name is added to the each row based on codes given.
If the code not found in the lookup table, record will be rejected.
Lookup stage also performs to validate the row.
Look Up stage is a processing stage which performs horizontal combining.
Lookup stage Supports
N-Inputs ( For Norman Lookup )
2 Inputs ( For Sparse Lookup)
1 output
And 1 Reject link
Up to Datastage 7 Version We have only 2 Types of LookUps
a) Normal Lookup and b) Sparse Lookup
But in Datastage 8 Version, enhancements has been take place. They are
c) Range Look Up And d) Case less Look up
Normal Lookup:-- In Normal Look, all the reference records are copied to the memory and the primary records are cross verified with the reference records.
Sparse Lookup:--In Sparse lookup stage, each primary records are sent to the Source and cross verified with the reference records.
Here , we use sparse lookup when the data coming have memory sufficiency
and the primary records is relatively smaller than reference date we go for this sparse lookup.
Range LookUp:--- Range Lookup is going to perform the range checking on selected columns.
For Example: -- If we want to check the range of salary, in order to find the grades of the employee than we can use the range lookup.
output data.
The main uses of the lookup stage is to map short codes in the input dataset onto
expanded information from a look up table which is then joined to the data coming from
input. For example, some we get the data with customers name and address. Here the
data identifies state as a two letters or three letters like mel for melbourne or
syd for sydney. But you want the data to carry the full name of the state by
defining the code as the key column. In this case lookup stage used very much.
It will reads each line, it uses the key to look up the stage in the lookup table.
It adds the state to the new column defined for the output link.
So that full state name is added to the each row based on codes given.
If the code not found in the lookup table, record will be rejected.
Lookup stage also performs to validate the row.
Look Up stage is a processing stage which performs horizontal combining.
Lookup stage Supports
N-Inputs ( For Norman Lookup )
2 Inputs ( For Sparse Lookup)
1 output
And 1 Reject link
Up to Datastage 7 Version We have only 2 Types of LookUps
a) Normal Lookup and b) Sparse Lookup
But in Datastage 8 Version, enhancements has been take place. They are
c) Range Look Up And d) Case less Look up
Normal Lookup:-- In Normal Look, all the reference records are copied to the memory and the primary records are cross verified with the reference records.
Sparse Lookup:--In Sparse lookup stage, each primary records are sent to the Source and cross verified with the reference records.
Here , we use sparse lookup when the data coming have memory sufficiency
and the primary records is relatively smaller than reference date we go for this sparse lookup.
Range LookUp:--- Range Lookup is going to perform the range checking on selected columns.
For Example: -- If we want to check the range of salary, in order to find the grades of the employee than we can use the range lookup.
Subscribe to:
Posts (Atom)