Handling database rejects for db2 api stage

[Inquisitive]

Hi,
I need to incorporate handling of database rejects. DB2 stage doesnot seem to be supporting the creation of reject link. The pdf help docs say that it is possible as given below,but I am not clear where to put the transformer stage. Has anyone successfully tried it before

Any help wud be appreciated in this regard
Thanks.
----------------------------------------
DataStage provides additional reject row handling. To use this capability:
1. Set the Parameter Array Size property to 1.
2. Use a Transformer stage to redirect the rejected rows.
You can then design your job by choosing an appropriate target for the rejected rows, such as a Sequential stage. You can then reuse this target as an input source once you resolve the issues with the offending row values.
----------------------------------------

[vmcburney]

Create a transformer with one link going to the DB2 stage and one a reject link with the reject check box checked. Any row sent down to the DB2 stage that fails will be handled as a rejected row back in the transformer.

[ray.wurlod]

How this is done varies between parallel and server jobs.
The piece you've quoted from the manual relates to server jobs, for which Vince provided the clarification that it is the Transformer stage in which rejected rows are identified and redirected.
Unfortunately I do not have access to PX at the moment, hopefully someone else will post the solution.

[vzoubov]

What method to load a table are you using: load, write or upsert? What type of rejects do you need to handle?

Vitali.

[Inquisitive]

Hi Ray,

Thanks for pointing out that the suggestion in help doc was for server job.
Kindly provide me pointers on how to incorporate this on px version 7.0

I would be wanting to do insert / updates and which to capture database related errors like unique constraint violated, not null error etc.

I would appreciate this very much

Thanks

[Teej]

I do not know DB2, but I do know that if Oracle experiences those errors you mentioned, the job will abort with that particular error. There is no way to 'reject' errors like that for Oracle.

-T.J.

[ogmios]

The DB2 stage will happily continue on serious errors, it will only issue warnings even e.g. when the tablespace you're trying to write to is full, as long as I know DataStage the error handling of the DB2 stage is not as good as an ODBC stage.

So I prefer to use an ODBC stage when writing to DB2. On the other hand I prefer to use an OCI stage to write to Oracle instead of an ODBC stage (as the ODBC stage has less meaningful database error messages).

It's a pity Ascential didn't require/make some uniformity among database stages, also for type conversions e.g.

Ogmios

[vzoubov]

I would be wanting to do insert / updates and which to capture database related errors like unique constraint violated, not null error etc.

In the upsert mode (try to insert first, if unique constraint is violated try to update) the db2 stage will log a summary that looks like:

DB2_3.DSLink4,0: DB2 Info :
Records Inserted: 0
Records Used for Update: 10
Records Rejected: 0
Total Records: 10

There's no information about what records have been updated (not inserted) because of the unique constraint violation.

In the write/append mode the job will abort with a massage similar to:

DB2_3.DSLink4,0: DB2 Query Embedded SQL: Execute failed
sqlcode = -803 sqlstate = 23505database_name
Message: SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "schema.table" from having duplicate rows for those columns.
SQLSTATE=23505

All said applies to DS 6.x however.
Hope it helps,
Vitali.

[Inquisitive]

Hi Vincent / Ray,

I am replying to the same issue as I wanted to discuss more.

Create a transformer with one link going to the DB2 stage and one a reject link with the reject check box checked. Any row sent down to the DB2 stage that fails will be handled as a rejected row back in the transformer.

I tried the approach suggested by Vincent. I had one link going to db2 and other going to a reject file. How do I specify the second link as a Reject link. I thought I need to put a constraint on the first link as "DSStageLastErr=0" and ticked the reject check box of second link.Am I correct ?

Since Ray infomed that it was for a server job, I then tried similar approach for Parallel job and used a Basic Transformer. I am sure that will behave the same way as the server job

Thanks...

[crouse]

As of DS 6.x, there is not a way to detect rows that reject via the DB2 (and I think even OCI) stages for PX.

The Server functionality for detecting rejects is non-existent in PX as of 6.x and I don't think it's made it into 7 yet.

The concept is that if there is a reject, the job aborts, you fix the problem and rerun ("because PX is so fast"). But, that strategy doesn't play out real well in the real world.

[Inquisitive]

But the help file for PX in the pdf doc says its is possible.