Page 1 of 1
Transformer Stage Vs Filter Stage
Posted: Wed Sep 29, 2004 7:39 am
by vigneshra
Hi
In our project, we have a requirement like we need to forward records from one table to 7 other tables based on the value of a date column. If date falls in range1 then it should go to table 1 or if it falls in range2 then it should go to table 2 and so on. Which stage performs well in this case
TRANSFORMER or FILTER ??
The data we are handling is in the range nearing billion. All your suggestions welcome.
Vignesh.
Posted: Wed Sep 29, 2004 8:04 am
by dsxdev
For small operations like this Filter would be a better option.
Transformer is more robust than a Filter Stage. :D
Posted: Wed Sep 29, 2004 8:08 am
by dsxdev
Hi,
if you already have a transformer doing the transformations
added to those you need to split the records based on the range of a column value then better you implement this in transformer using Link Constraints and Stage Variables.
Posted: Wed Sep 29, 2004 11:45 pm
by vigneshra
Infact no other transformations involved. It is a straight pull from source table to 7 target tables based on the date range. So can I assume that filter stage gives a better performance.
Vignesh.
Posted: Thu Sep 30, 2004 12:57 am
by vigneshra
I tried using filter stage for doing this but ended up in the job abortion due to incorrect syntax in where clause. I explain you li'l bit more. Here in this job, we are passing the date parameter values to the job through a unix script. One of the columns input to the filter stage is a date column which is compared against these parameters. The input column is TRANSACTION_DATE and it is compared against the parameters like (TABLE1_START_DATE and TABLE1_END_DATE), (TABLE2_START_DATE and TABLE2_END_DATE) and so on.
Can I know the exact syntax for putting in where clause..
I put like
TRANSACTION_DATE BETWEEN #TABLE1_START_DATE# AND #TABLE1_END_DATE#
TRANSACTION_DATE BETWEEN #TABLE2_START_DATE# AND #TABLE2_END_DATE#
and so on.........
But the job got aborted. Please throw me some light on this.
Posted: Thu Sep 30, 2004 1:07 am
by vigneshra
I got it folks !! I put the syntax here. May be it will be of use to someone.
TRANSACTION_DATE BETWEEN '#TABLE1_START_DATE#' AND '#TABLE1_END_DATE#'
Thanks !!
Vignesh.
Posted: Thu Sep 30, 2004 2:42 am
by mandyli
Hi Vignesh,
The ans is simple but great .
Posted: Fri Nov 05, 2004 12:06 am
by vigneshra
Still the question remains. If there is a situation where either transformer or filter can be used, which one to choose ?? I came across a document stating that using transformer is expensive. If so, in what way is it expensive ?? Ray, Craig, Burney, Bland, Duke and other DataStage Gurus, can you give a pleasing answer to this question ??