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.
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.
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.
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.
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 ??