DSXchange

This is my point. If you haven't changed your reference data between runs, then the different results must be due to changed input data. Perhaps the 2nd run lookup keys have extra trailing spaces?

Sachin - that is the exact point I made in my post. Now we need to know what the original poster really wishes to do.

Yes, this can be done using the ICONV() and OCONV() functions. First, use ICONV() to convert your date into then internal format, which is an integer number of days since Dec 31, 1967. Once you have this internal format you have various options, for instance adding or subtracting 182 days and then c...

It the same query works one run and not another with the same data, I would start looking for the error in the incoming data. Or do you modify the table used for the lookup?

The point of the reject link is that it supplies you with the SQL error code, which can then be used to try to figure out why this statement isn't working.

Jagan617 - thanks for the additional explanation, that has cleared things up. There are 2 answers to your question:

(a) 42
or
(b)

Not datastage licenses, but perhaps (on AIX) the concurrent compile license.

The Hashed File limit is due to internal pointers and can be avoided on large files by declaring them as 64BIT; this 2Gb limitation has nothing to do with the internal buckets and overflow groups, just pointer sizes.

"u" is not a valid English word, try using "you".

If you activate a reject link and output that to a file, what is the SQL error code you get?

No problem combining the canvas lookups into one stage, this might end up executing the same way as a job with several lookup stages due to the combination that the framework tries to do at runtime.
The memory required for one vs. several lookup stages is the same.

Are you connected to your server with a fast internet connection? Are you using Citrix? We have a job here that takes 45 minutes to compile, except on weekends when the time goes down to 44 minutes (no, I'm not kidding). It is a large job, though. Are you on AIX? If so, getting the latest patch rele...

The environment variables, which you can include in the job parameters and change the defaults values, contain:

$APT_ORAUPSERT_COMMIT_ROW_INTERVAL (5000 row default)
$APT_ORAUPSERT_COMMIT_TIME_INTERVAL (2 second default)

Are you writing to the same Oracle instance?

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.

Are you reading through the database? If yes, then in the database input stage specify "ShiftJIS" instead of UTF8.