Not able to write reject record
Moderators: chulett, rschirm, roy
Not able to write reject record
I have the following scenario:
I have a sequential file which consist of a date field in yyyy-mm-dd format. The file has 2 rows as followed. Row 2 is an invalid date.
2005-10-14
2007-25-12
I want to insert the data into an oracle table which has a date field.
I passed the seq file straight through a transformer stage and for the invalid record I want to write it to a seqential file via the reject link.
The input date field is defined as varchar length 10.
The output date field is defined as DATE length 10.
seq file
|
|
V
transformer ---------> Oracle Enterprise Stage
|
| reject
V
seq file
When the job ran, it inserted the only the first row to the Oracle table as expected, the second row was rejected (confirmed by the sqlldr log).
However, I was not able to get the rejected row to write to my reject file.
I tried using StringToDate function but the results are the same.
1) Why did my job aborted and not writing the rejected record?
2) Ideally I want the job to do what I intended and give a warning msg in the log to indicate there are some rejects rather than give a fatal error and abort. Can it be done?
I have a sequential file which consist of a date field in yyyy-mm-dd format. The file has 2 rows as followed. Row 2 is an invalid date.
2005-10-14
2007-25-12
I want to insert the data into an oracle table which has a date field.
I passed the seq file straight through a transformer stage and for the invalid record I want to write it to a seqential file via the reject link.
The input date field is defined as varchar length 10.
The output date field is defined as DATE length 10.
seq file
|
|
V
transformer ---------> Oracle Enterprise Stage
|
| reject
V
seq file
When the job ran, it inserted the only the first row to the Oracle table as expected, the second row was rejected (confirmed by the sqlldr log).
However, I was not able to get the rejected row to write to my reject file.
I tried using StringToDate function but the results are the same.
1) Why did my job aborted and not writing the rejected record?
2) Ideally I want the job to do what I intended and give a warning msg in the log to indicate there are some rejects rather than give a fatal error and abort. Can it be done?
Thank you for the prompt response.
When I added a reject link from Oracle enterprise stage, i got the msg:
"Error: Reject Link Ora_ENT.DSLink9 is not allowed wiht the current setting (or lack ) of the 'Output Reject records' property".
I don't seem to be able to set any reject link property from the Oracle Ent stage.
Can you tell me where the settings are?
When I added a reject link from Oracle enterprise stage, i got the msg:
"Error: Reject Link Ora_ENT.DSLink9 is not allowed wiht the current setting (or lack ) of the 'Output Reject records' property".
I don't seem to be able to set any reject link property from the Oracle Ent stage.
Can you tell me where the settings are?
I found in the documentation that I have to change the Write Method to 'Upsert' to enable the reject link.ArndW wrote:One of the switches in the Oracle stage lets you enable the reject link, look for it towards the bottom of the list. ...
when I did that I am getting the following error:
Message:
Oracle_Ent,0: Un-handled conversion error on field "DATE_FLD " from source type "date" to dest type "raw[7]": source value="**********": Result is non-nullable
and there is no handle_null to specify a default value
Check the field DATE_FLD . It has been changed from Date data type do a different. And more over, it has been changed to Not nullable. Where a null value is comming and is converted to *'s by default. As specified, you can handle the null by using handle_null function in modify stage or the null handling functions in transformer stage.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
OK I tried to put a null handling function in the transformer stage, yet still getting the same error.
I used NullToValue(Lnk_in.Field1,'1999-01-01') in the transformer derivation.
I also changed all the fields to non nullable, but still no avail.
Could you explain why the DATE_FLD has been changed by dstage to be non nullable and what can i do?
I used NullToValue(Lnk_in.Field1,'1999-01-01') in the transformer derivation.
I also changed all the fields to non nullable, but still no avail.
Could you explain why the DATE_FLD has been changed by dstage to be non nullable and what can i do?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is an additional property that you have to set to allow the use of a reject link. However, such a reject link can not capture bad rows from sqlldr. DataStage creates the control and data files then invokes sqlldr; control is actually passed to sqlldr. To determine what rows were rejected you need to design to inspect the sqlldr "bad file".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.