DSXchange

Maybe no-one else is silly enough to work weekends.

It appears that the Search facility in DSXchange isn't working for you either.

Have you done the things we have already suggested? Somewhere sqlldr has reported an error. There will be a log file from this.

This status code means that the job is not in a runnable state. Aborted, stopped by operator or "unknown" (underlying process killed without DataStage being able to record the status) are some of these states. In this case you must reset the job before it is once again runnable. Recompiling is sligh...

RefDS | | V ----> insert source ----> Lookup ----> Transformer ----> update Loaded in an earlier job, RefDS contains keys and those columns from the target that you wish to compare. The Lookup stage "lookup failed" rule is Continue. The Transformer stage directs failed lookups to the insert link an...

What I was getting at in the first question is what happens if the same key arrives twice in the stream of input data? How do you need to play this?
You can only insert the first of them.

There's no such thing as "collect in Peek stage".

Your ETL stream should (must) check that RI will not be violated. End of story. What you do with those rows is immaterial, so long as you don't send them to the target table.

Prevention is better than cure. Rows that would cause a referential integrity violation should never reach Oracle.

Two questions:

1. Does the input data contain duplicate key values?

2. What alternative do you plan for detecting change when the row already exists in the target table?

It doesn't matter that it's not null. In a server job, if the lookup fails, NULL is returned. It's a left outer join.

Hashed file can be resized to use 64-bit addressing, then it will accept 4GB data, indeed up to 0.19 PB of data if the O/S permits.

If you can't find the log anywhere else, look on the scratch disk directory for the node that reported the error.

Please answer all the questions. Is the lookup always successful? (Where is it not NULL is returned, which will always fail constraint logic.)

A lookup to a Hashed File can only be equality. So how are you looking up latest_date? Is the lookup always successful?

Are both dates in the same format, and is this a comparable format for a "greater than" comparison? For example YYYY-MM-DD is always comparable, but MM/DD/YYYY is not.