ODBC Reject link to sequential file

[VCInDSX]

My job design is as follows. This is a PX, RCP enabled job.

Sequential File ===> Transformer ==> ODBC Enterprise
||
||
||
\/
Rejects (Seq File)

In one particual file that has 75K records, the data gets loaded but i see a "Fatal" log entry
ODB_AsCollected,0: [DataDirect][ODBC SQL Server Driver]Datetime field overflow

The job did not abort, but the "fatal" log entry clearly appears to be some data that might have been in an out-of-range value or incorrect format.

I wanted to capture the erring record into a reject file and tried to add a link from the ODBC stage to a Seq File or PEEK. Is this valid?
I got the following error "Error: Reject Link 'ODBC_Enterprise_1.DSLink4' is not allowed with the current setting (or lack) of the 'Write Method' property"

I came across one post viewtopic.php?t=96043&highlight=odbc+reject
where ODBC reject link was discussed. I would appreciate your help if you could provide some pointers on how one could get the reject link from ODBC stage.

If this is not allowed from ODBC stage, is there a recommended approach on troubleshooting this?

Thanks in advance for your time and help,

[sud]

Did you set the Output Reject Records property to true? And, what is the write method used by you?

[VCInDSX]

Hi Sud,
Thanks for the followup.
I don't see a direct option for "Output Reject Records" in the stage properties tabs - (Options branch or other places).
The items that i can see are
- Truncate Column Names
- Close Command
- Convert to Native form
- Drop Unmatched Column
- Insert Array Size
- Isolation Level
- Length to truncate
- Open Command
I am wondering if I might have missed some configuration/setting in the ODBC stage that is not letting me set the rejects property.

As for the ODBC stage properties for Target
Table=#TABLE_P#
Write Method=Write
Write Mode=Append

I put a PEEK after the ODBC stage to check if I might have additional property items, but no luck there either. The output tab says that it is already a reject link and no settings can be done. Whatever columns were mapped in the ODBC stage (apart from RCP columns) are automatically propagated to the output link.

I am also reviewing the data (manually) by loading into MS-Access to find out what might be triggering this error. I will post an update on that soon.

Let me know if any other information from my side will help you help me.

Thanks again for your time

[VCInDSX]

To followup on my earlier post.....
The data analysis showed year values upto 2199. I then tried a few tests with year values ranging from 2000 to 2090 and finally arrived at a tipping point of 2079-June-06.
Searching for this range in the web, the following MSDN page http://msdn2.microsoft.com/en-us/librar ... L.80).aspx says MS SQL Server has 2 datatime types and the simpler version (smalldatetime) has a range of January 1, 1900, through June 6, 2079.

It appears the the "date" type in Datastage is internally mapped to the smalldatetime range. I tried the timestamp type and ran into a different set of errors with buffer overrun issues in the RCP job.

Submitted a ticket to IBM and the response was that they were able to reproduce this in their test environment and has been submitted to engineering department..... Has been a couple of days since....

Meanwhile, the workaround we did was to load the data into a text column and then write a post-job that invokes a DB Stored procedure that tansfers the data from the text column into the DataTime column in the SQL Server 2005 table.

If I ever hear back from IBM i will post the feedback to this group.

Thanks,