How to capture reject records in ETL process?

swathi.mekala · Post by **swathi.mekala** » Mon Dec 11, 2006 11:07 pm

I have 174 records in input file but i am getting only 173 records as output and 1 record is rejected.I want to capture the reject record or display the reject record.
reject record is displayed as one column as {1a} but i want the whole record to be displayed.I am capturing the reject record in sequential file
Input,ouptut file is Sequential file

Thanks
swathi

narasimha · Post by **narasimha** » Mon Dec 11, 2006 11:21 pm

Welcome Aboard :D

Swathi,

Your question is a little vague.

swathi.mekala wrote:How to capture reject records in Datastage(ETL process)

We have a search option, make use of it.
There are many topics relating to capturing rejects.
If you have any specific question (not found by a search) get back, there are many people here to help you out.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 11, 2006 11:49 pm

For example, and especially in parallel jobs, you need to define what you mean by the term. Most stages support a reject-handling link, but what it means is different in different stage types.

samsuf2002 · Post by **samsuf2002** » Tue Dec 12, 2006 3:26 pm

what are the stages you are using in your job and at what point you want to reject the records?

ray.wurlod · Post by **ray.wurlod** » Tue Dec 12, 2006 3:42 pm

Who is "u" and what is "r"? Is "wantt" related to "Gantt"?

Please use English on this forum.

Nageshsunkoji · Post by **Nageshsunkoji** » Wed Dec 13, 2006 11:43 am

Hi Swathi,

There are so many stages in Parallel to support reject process.

Lookup ----- Lookup fails (capture the driving link)

Merge-------- join fails (Capture the refrence/updated records)

Tranformer -------- caputure the records which are not satifying the requirements (Like null handling)

Sequential file : Reject the records if data is not satisfying the DataTypes.

Like some other stages are there........

You have to clear about your requirements........

If Its a Interview Question.......... DSXchange will not help you......

swathi.mekala · Post by **swathi.mekala** » Wed Dec 27, 2006 12:33 am

Hi all;

I am sorry ,i posted it uncorrectly. what i intention is to capture the rejected records which are of incorrect format or datatypes. for example we are using the sequential file in our job to import the data . and if the data is of in correct format then we are capturing the records in to another sequential file by using the "reject mode" property as "output" and then when the job is run then we are getting the reject records into the sequential file . but we cant able to see the reject record exactly as it is. please help me regarding this query,its urgent.

Cheers;
Swathi.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 27, 2006 1:01 am

We don't do "urgent" - we are all volunteers who post as and when we can. If you want "urgent" sign up with your support provider for premium services, and learn the true cost of urgent.

The column sent to the reject link by the Sequential File stage is in Raw format. You can feed it to a Peek stage, and see it in the log entry produced You can write it to a sequential file, and read that with any utility external to DataStage.

Or, if you know its layout, you can parse the raw (or VarBinary) string into columns using a Column Import stage.

battaliou · Post by **battaliou** » Tue Mar 06, 2007 2:46 am

Try rejecting your data into a dataset instead of a sequential file. Sounds like your delimiter settings in your sequential file are not initialised properly. Also be aware of nulls and use an explicit NullToValue conversion on nullable fields.

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 4:58 am

As noted, you can try writing into dataset, and later you can convert Varbinary into varchar in Sequential file.

Karine · Post by **Karine** » Thu Mar 08, 2007 5:29 am

Hi Kumar,
I have a similar requirement where I need to identify the rejected rows with its column values so that the data steward can analyze the error. Can you please explain how I can convert varbinary into varchar or its columns ?

Karine

kumar_s · Post by **kumar_s** » Thu Mar 08, 2007 5:50 am

I think Swathi might have tried to view data from the Sequential stage.
Though it is mentioned as Varbinary datatype, the reject records are stored fully. Try to check the data of the rejected file from unix using cat.

Karine · Post by **Karine** » Thu Mar 08, 2007 6:03 am

I'm also want to "deconstruct" the varbinary field into its columns, but when I tried to link the rejected seq file or dataset (from a reject link) to a transformer stage, I'm getting the error message - The source stage does not support non-reject output links whilst it has input links.
Am I missing something????

kumar_s · Post by **kumar_s** » Thu Mar 08, 2007 6:08 am

Perhaps, you can process the reject file in separate job. Read it as varchar and "deconstruct" as you wish. But still the records which got reject might be due to metadata mismatch, hence you may not parse correctly.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 08, 2007 8:29 am

Karine wrote:I'm also want to "deconstruct" the varbinary field into its columns, ...
Am I missing something????

The Column Import stage can deconstruct a raw (VarBinary) string.

How to capture reject records in ETL process?

How to capture reject records in ETL process?

Re: How to capture reject records in ETL process?