Cannot get rejected Oracle records to post to reject file

[jherr22]

I have an ETL job in which the destination stage is Oracle Enterprise (OE). There is a reject stage and reject link associated with the OE stage. My intent is that records which, for any reason, fail constraint checks within the Oracle table fall into the reject file. This concept works when key constraints cause rejections: for example, if a foreign key constraint check fails, the record is duly sent to the reject file. However, if the DDL has a range check constraint, or a value check constraint, or if the data type translation from DS to Oracle fails (for example, varchar to numeric), no records are sent to the reject file, and the job aborts. The "Output Reject Records" property on the Input/Properties tab in the OE stage is set to true. Are there any settings or parameters in DataStage which will cause records rejected from being written to an Oracle table for any and all reasons to be sent to the reject file?

Thanks in advance for any help with this issue.

[Krazykoolrohit]

I cant answer your original question but i can provid you with a work around.

Perform all range checks and validations and reject your records using a transformer just before you write into the oracle table.

[ArndW]

That is strange behaviour; you mean that you have "reject" enabled in your Oracle Enterprise stage and you have a reject link going out of the stage and you still have records that disappear on write - ending up neither in the table nor in the reject link?

[jherr22]

[quote="ArndW"]That is strange behaviour; you mean that you have "reject" enabled in your Oracle Enterprise stage and you have a reject link going out of the stage and you still have records that disappear on write - ending up neither in the table nor in the reject link?[/quote]


No. Nothing disappears. Nothing happens at all. Instead of sending the rows which fail the constraint test to the reject file, the job aborts. Nothing gets written to the database (apparently the routine executes a rollback). Nothing gets written to the reject file. What I want is for incoming records which fail any and all constraint tests to be written to the reject file. That is the behavior I see with values which fail foreign key constraint tests now.

[ArndW]

That behaviour makes more sense, since the "load" option executes externally to the DataStage. If you don't use the load method you will probably see the job execute as you expect.

[jherr22]

[quote="ArndW"]That behaviour makes more sense, since the "load" option executes externally to the DataStage. If you don't use the load method you will probably see the job execute as you expect.[/quote]

I'm not sure I know what you mean by "'load' option." Settings within the OE stage are as follows ...
Write Method = Upsert
Upsert Mode = Auto-generated Update & Insert
Upsert order = Insert then update

I don't see anything else I might change to effect different behavior.

[ArndW]

With the upsert method you will either get the data row to be inserted, or update the existing row or going down your defined reject link. If this is not happening then you need to submit it as a bug to your support provider.

[ghielmettil]

I have found exacty the same problem.
The reject link works only if the error is a violation of oracle costraint.
If the error concerns other reasons (example: wrong date) the job fails without producing discards.

does somoneone use the reject link, from a target stage oracle, in correct way also for these cases?

thanks

With the upsert method you will either get the data row to be inserted, or update the existing row or going down your defined reject link. If this is not happening then you need to submit it as a bug to your support provider.

[ghielmettil]

I have found exacty the same problem.
The reject link works only if the error is a violation of oracle costraint.
If the error concerns other reasons (example: wrong date) the job fails without producing discards.

does somoneone use the reject link, from a target stage oracle, in correct way also for these cases?

thanks

With the upsert method you will either get the data row to be inserted, or update the existing row or going down your defined reject link. If this is not happening then you need to submit it as a bug to your support provider.

[ArndW]

What do you mean by the example of "wrong date" - if it is an invalid string that fails conversion to date or a date constraint then the reject handling works as expected. I can't think of a case where a record wouldn't get inserted/updated into the table and also not go to the reject link.

[ghielmettil]

It is an invalid string that fails conversion to date (example 34-01-1978).

if the error is a violation of a constraint the job run and create the reject file,
if the error si an invaild string, the job fail without any string file.

The statemant that I have insert into stage:

INSERT INTO TEST_OUTPUT (C_1, D_2) VALUES (ORCHESTRATE.C_1, to_date(ORCHESTRATE.D_2, 'DD/MM/YYYY')).

If i run the job with a valid date in the source, it work fine and insert the record in the target table. If I add a wrong date (34/01/1978), I expect that the job insert the correct record in the target table and discard the wrong record in the error file, instead the job goes to error with the following messages:

Stg_Output,0: Array execute failed for insert:
insert is: INSERT INTO TEST_LORENZO_OUTPUT (C_1, D_2) VALUES ( :C_1, to_date( :D_2, 'DD/MM/YYYY'))
sqlcode is: -1847
esql complaint: ORA-01847: day of month must be between 1 and last day of month
.

Stg_Output,0: Unable to insert a record into the table.

Stg_Output,0: The runLocally() of the operator failed.

Stg_Output,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

main_program: APT_PMsectionLeader(1, node1), player 4 - Unexpected exit status 1.

main_program: Step execution finished with status = FAILED.

What do you mean by the example of "wrong date" - if it is an invalid string that fails conversion to date or a date constraint then the reject handling works as expected. I can't think of a case where a record wouldn't get inserted/updated into the table and also not go to the reject link.

[ghielmettil]

It is an invalid string that fails conversion to date (example 34-01-1978).

if the error is a violation of a constraint the job run and create the reject file,
if the error si an invaild string, the job fail without any string file.

The statemant that I have insert into stage:

INSERT INTO TEST_OUTPUT (C_1, D_2) VALUES (ORCHESTRATE.C_1, to_date(ORCHESTRATE.D_2, 'DD/MM/YYYY')).

If i run the job with a valid date in the source, it work fine and insert the record in the target table. If I add a wrong date (34/01/1978), I expect that the job insert the correct record in the target table and discard the wrong record in the error file, instead the job goes to error with the following messages:

Stg_Output,0: Array execute failed for insert:
insert is: INSERT INTO TEST_LORENZO_OUTPUT (C_1, D_2) VALUES ( :C_1, to_date( :D_2, 'DD/MM/YYYY'))
sqlcode is: -1847
esql complaint: ORA-01847: day of month must be between 1 and last day of month
.

Stg_Output,0: Unable to insert a record into the table.

Stg_Output,0: The runLocally() of the operator failed.

Stg_Output,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

main_program: APT_PMsectionLeader(1, node1), player 4 - Unexpected exit status 1.

main_program: Step execution finished with status = FAILED.

What do you mean by the example of "wrong date" - if it is an invalid string that fails conversion to date or a date constraint then the reject handling works as expected. I can't think of a case where a record wouldn't get inserted/updated into the table and also not go to the reject link.

[ArndW]

I can't check this now,but would be surprised if that failed conversion didn't go down the reject link. Perhaps the easiest solution is to use a date type in the DataStage and capture the invalid date in the job.

[ghielmettil]

I would like to leave the management of the discards to oracle, so that to produce 1 only file of errors
I believe is impossible that cannot be done, but from the few information that I have found on the use of the reject in the stage oracle it seems that it always involves this way

I can't check this now,but would be surprised if that failed conversion didn't go down the reject link. Perhaps the easiest solution is to use a date type in the DataStage and capture the invalid date in the job.