Capturing Reject records

mydsworld · Post by **mydsworld** » Wed Jan 21, 2009 11:12 am

I have a job design like this :

Seq File -> Transformer -> DB2 API

While loading DB2 table, some records are getting dropped in DB2 API stage. I need to capture those Reject records in a file (not from Director) Now, DB2 API stage doesn't allow to have a Reject link.

When I use ODBC Ent stage instead of DB1 API and set Write Method = Upsert, I can have a Reject link, but getting the following error :

Sequential_File_20: Error when checking operator: Could not find input field "sqlcode". [api/interface_rep.C:2168]

As ODBC Ent stage adds a field 'sqlcode' in the rejected data set which is not coming from input.

Please advise.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 21, 2009 12:01 pm

Look carefully. The Sequential File stage is the one that has generated the error message. This stage type does not generate sqlcode - after all, it doesn't use SQL.

My guess is that you have tried to propagate sqlcode column through your job design. Get rid of it. If you create a proper reject link ("Convert to Reject") as output from your database stage, then sqlcode will appear there automatically.

mydsworld · Post by **mydsworld** » Wed Jan 21, 2009 4:06 pm

I introduced a Transformer in the Reject link between ODBC Ent and Seq file (to drop 'sqlcode'). Now it is giving the same error on that Transformer. So, there is some setting in the ODBC Ent stage that adds that 'sqlcode' field in the Reject link.

Please advice.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 21, 2009 4:27 pm

No. Simply create the reject link FROM THE ODBC ENTERPRISE STAGE.

mydsworld · Post by **mydsworld** » Wed Jan 21, 2009 7:27 pm

I created the Reject link from ODBC Ent to Seq File (directly without any Transformer etc).It is giving the error :

Sequential_File_20: Error when checking operator: Could not find input field "sqlcode". [api/interface_rep.C:2168]

When I place Peek stage instead of Seq File, the job works. But I would like to use Seq File to capture the reject records.

Please advise.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 21, 2009 9:48 pm

GET RID of the sqlcode column from the sequential file and transformer stages.

mydsworld · Post by **mydsworld** » Thu Jan 22, 2009 9:12 am

Thats the problem, the 'sqlcode' appears in grey (read-only), so can not be removed from Seq file or Transformer.Even if I remove it from Transormer right side it gives that same error.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 22, 2009 3:34 pm

The reject link should not have any connection to the Transformer stage. It is an output from the ODBC Enterprise stage. Its link type must be Reject (painted in long dashes in the design area).

mydsworld · Post by **mydsworld** » Thu Jan 22, 2009 3:48 pm

Ray,

Believe me, I am directly taking the Reject link from target ODBC Ent to Seq File (with no Transformer in between). It does give that error. This was my original design.

Now, if I replace Seq File (at the other end of Reject link) with Peek, it works. So, any idea why I am not able to use Seq file to capture the rejected records.

devanars · Post by **devanars** » Thu Jan 22, 2009 4:07 pm

I dont think so seqential file will work for rejected link. It will work fine if you use only Peak or Hash file.

mydsworld · Post by **mydsworld** » Thu Jan 22, 2009 4:12 pm

The requirement is to capture the Reject records (failing to get inserted in database table) in a file that can be shared with other application or can be mailed.Thats why I need to use File and not Peek.

devanars · Post by **devanars** » Thu Jan 22, 2009 4:16 pm

If you need a file for rejected records then use load option instead of using upsert then you can see a rejected file with date under scratch folder each time.

mydsworld · Post by **mydsworld** » Thu Jan 22, 2009 4:21 pm

I am using ODBC Ent stage that has write option 'Upsert' and 'Write'. I dont find any option called 'Load'.

paulistrate · Post by **paulistrate** » Tue Mar 03, 2009 2:40 pm

mydsworld wrote:Now, if I replace Seq File (at the other end of Reject link) with Peek, it works. So, any idea why I am not able to use Seq file to capture the rejected records.

Put another link from Peek to SeqFile. This will activate the columns again. Then remove "sqlcode".

ray.wurlod · Post by **ray.wurlod** » Tue Mar 03, 2009 3:23 pm

devanars wrote:I dont think so seqential file will work for rejected link. It will work fine if you use only Peak or Hash file.

We're in the parallel job world here!