Logic in the job using the Lookup stage

[sarat.srinivas@tcs.com]

Hi All,

The following is the job details I have to impemented:
I have an input text file which has data of the tide-height value at that respective time and date.
I have to achieve that we will load the unique tide-height which are unique with respect to the date time values into the data table, and the redundant data to the error tables.
I have implemented this by creating 2 jobs. The job 1 loads the given input data to the output data table. The job2 has a lookup which compares the same input file which is given to the job1 and another input coming from the Data table using the DB2/UDB API. The lookup will compare the data in the input file and the rows of the data table.If there is a match it sends the respective rows to the error table.
If I am using this way, by performing Job1 the Data table will have the unique rows in it, and running Job2 the Error table will have the all of the rows which includes the redundante recrds as well the unique records.
But I need only the redundant data to be in the error table.
Pls help me out to know how to go ahead with the logic to achive the requirement.
Thanks in advance!

[JoshGeorge]

You can do this in one job without any look up. Pass the records coming from your input file thru a sort stage (sorted on keys) by checking the change key option in sort stage. Use a filter stage and pass all the records with change key column values as 1 to your data table and rest to your error table.

[sarat.srinivas@tcs.com]

You can do this in one job without any look up. Pass the records coming from your input file thru a sort stage (sorted on keys) by checking the change key option in sort stage. Use a filter stage and pass all the records with change key column values as 1 to your data table and rest to your error table.

Hi JoshGeorge. Thanks for the quick response.
Actually we have the input date, time and the place in different fileds. The date and time for the respective Place have to be unique.In the present case we have used the three columns in the look-up stage for comparision.
But using a sort stage we can only have a single field as key. So could not to go ahead using the sort stage.
Pls let me know in case of any other way.
Thanks!

[JoshGeorge]

You can have more than one column as key fields in sort stage.

[sarat.srinivas@tcs.com]

You can have more than one column as key fields in sort stage.

Hi JoshGeorge,
Ya. The idea of using a sort and a filter is surely seperating the redundant data.But, actually the requirement is that we need to compare the data in the input file and the files in the data tables before the redundant data rows are pushed into the error tables. For that sake a Lookup has been used. Pls let me know if ther is anyother way out.
Thanks!

[JoshGeorge]

Your requirement is to take lookup reject and load into data table and matched ones to error table.

This can also be helpful --> Search for "change capture" or "CDC" in the site.