DSXchange

Phil - it is correct; I just wrote a test job to make sure that it is so. The SQL logic of a left outer states that for each left input row there will be an output row, those fields coming from the right side of the join when unmatched are set to <null>. If, in DataStage, this field is not nullable ...

If you declare your numeric columns coming from the sequential files as being nullable (even though they aren't), then after your left outer join you will get null values for the joined columns when the join doesn't find a match.

That isn't much in the way of error messages. Try a "reset" of your job and then see if you find an entry in your log file with the text "from previous run..."

How are you calling up this .bat file in the after-job?

You determine the return value for "ErrorCode" in your after-job routine. If the return value is nonzero then the job will consider that as an error and then abort.

Is your data being sorted on the CDC Key(s) in the database? In the job (explicitly)? or in the job (implicitly)?. You can turn on $APT_DUMP_SCORE to see if there are inserted sort operators which may be slowing your job down.

You've hit upon the reason. Since this is a system routine, it is stored and cataloged as DSX.KEYMGTGETNEXTVALUECONCURRENT and when you compile and catalog from DataStage you will get an entry for DSU.KEYMGTGETNEXTVALUECONCURRENT and for this reason you are still getting the original code at runtime...

While there is usually just one way for a job to run correctly, there are very many ways for it not to run. Without stating what happened and how the job failed (error messages, for example) there is little chance of getting a good answer that goes beyond guesswork.

There is a "promote subrecord" in the "Restructure" category.

There should be no problem. Are you having issues?

Does the script also work as a 1-liner in the command line?

Can you write the script to accept arguments and pass only the arguments to the after-job routine? That way would certainly work.

You need to recompile and catalog the routine, don't worry - the BASIC compiler is present on your production machine.

Generally one must always assume that every step can somehow go wrong.

But if you are doing update-then-insert then you shouldn't be getting rejects due to primary key constraints (but you would get a rejected record if a column that was non-nullable were updated with a null value).

If it is the same file and the same record and the same user then I'd see if the hashed file might be broken at that address. Try a "RESIZE filename * * *" from the DataStage command line when the file isn't being used and see if the error subsequently goes away. If the RESIZE shows proble...

I wonder if escaping might help here:

In that case MSQUERY will help you quicker.