How to Ouput Reject Records from DB2 Connector to Flat File

jweir · Post by **jweir** » Mon Oct 31, 2011 1:18 pm

Hello all,

I am trying to capture reject records from a DB2 Connector stage in a Sequential file. However, I just want the records in the file, not any error codes or error text. When I try to output to Sequential File, I get below fatal error:

Code: Select all

Sequential_File_34: A schema must be set in APT_FileExportOperator before use.

I have tried clicking different combinations of the two Reject conditions on the reject link, however no luck. When I click ERRORCODE or ERRORTEXT, the job runs fine, but only outputs the Error Code and not the actual record.

Is it possible to output the reject records to a flat file?

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 31, 2011 9:02 pm

Do you have any columns defined on your reject link?

Is it possible to drag all the input columns for the stage onto the reject link?

jweir · Post by **jweir** » Tue Nov 01, 2011 6:30 am

Thanks for getting back to me Ray.

No, I do not have any columns defined on the reject link. I cannot load a table definition, due to the 'Load' button being disabled.

I have been trying to figure out a way to bring the columns down the reject link, but have been unsuccessful.

I replaced the Sequential File with a Dataset, and it was able to output the reject records into the Dataset successfully. However, I need it to be in a flat file so the business can see those records.

Is there a workaround that someone has come across? Any suggestions would be apprecaited.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 01, 2011 7:47 am

Try including a Copy stage ahead of the Sequential File stage.

jweir · Post by **jweir** » Tue Nov 01, 2011 10:51 am

Thanks Ray.

I have tried implementing what you suggested, but in the Copy stage, there are no input columns. So I cannot drag them to the Output link to load the Sequential File.

Is there a property on the reject link inside the DB2 Connector I am missing?

I have also tried importing the target table definition into the Sequential File, but then in the Copy, it still does not recognize any input columns.

BradMiller · Post by **BradMiller** » Tue Nov 01, 2011 12:10 pm

If your DB2 Connector stage is a target, you should be able to have a reject link pointing to a sequential file and have the reject records written to the file. If you only want the record written to the file with out the error code or error text, you would not check either of the check boxes. If it is configured correctly, you should see the same columns in the sequential file's metadata that are in your DB2 Connector metadata. The columns in the file will be grayed-out. I have never attempted to put a reject link onto a source DB2 Connector stage if that is what you are trying to do, so I don't know how to configure it.

jweir · Post by **jweir** » Tue Nov 01, 2011 12:55 pm

I agree Brad; the metadata in the Sequential file should be grayed-out. However, it is missing.

BradMiller · Post by **BradMiller** » Thu Nov 03, 2011 9:40 am

What version are you using? As I remember, the DB2 Connector was first available in 8.1, but had bugs until FP1 came out.

jweir · Post by **jweir** » Fri Nov 04, 2011 7:14 am

I'm using v8.5.

qt_ky · Post by **qt_ky** » Fri Nov 04, 2011 10:25 am

I see the same thing on 8.5, except that I don't get any metadata even when the reject link goes to a dataset. I have RCP turned off in this case. Do you have RCP enabled? Do you see the metadata defined on the reject link when you switch it to a dataset?

As a workaround, can you send reject rows to a dataset and have a subsequent job export the dataset to a sequential file?

jweir · Post by **jweir** » Fri Nov 04, 2011 12:25 pm

I do not have RCP enabled. But when I output it to a dataset, it does not have any metadata defined, but the columns are there in the dataset when ran.

I suppose I could always have a seperate job, having the dataset written to a flat file, however, I would need to make another 40 or so jobs, and I really rather not do that, especially because I am in a time crunch as it is anyways

qt_ky · Post by **qt_ky** » Fri Nov 04, 2011 1:08 pm

Now I'm curious. I searched the fix list for 8.5 FP1 and did not see anything about this exact problem there.

I know the workaround is not pretty. With that workaround, you could possibly setup a single multi-instance job with parameters to dump a dataset to a sequential file and call it as needed.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 04, 2011 3:31 pm

jweir wrote:I do not have RCP enabled. But when I output it to a dataset, it does not have any metadata defined, but the columns are there in the dataset when ran.

This is because the operator that writes to a Data Set is copy - it simply makes a copy of the virtual Data Set in the job. And virtual Data Sets do have metadata defined.

qt_ky · Post by **qt_ky** » Sat Nov 05, 2011 7:07 am

If you're DB2 Connector reject link goes to a Sequential File then you can navigate to the reject link properties from within the DB2 Connector, Columns tab, turn on RCP. On the Reject tab you can check the reject condition options and leave the errorcode and errortext addtion options unchecked. Then it will write any rejected rows to the file.

jweir · Post by **jweir** » Sat Nov 05, 2011 9:01 am

qt_ky, I have went to the reject link in the DB2 Connector, and to the Columns tab, but how do I turn RCP on?

Ray, Thanks for the post, but I am not a premium member.