DSXchange

By conditional compile I mean the code between the $IFDEF and $ENDIF as well as the $UNDEFINE TESTING line. It shouldn't make a difference, but will make the test routine smaller. Did Ray's suggestion of cut-and-paste to a new routine make a difference?

Yes.

Perhaps you are not passing the correct "JobName" to the routine. Add after your DSAttachJob() call to try to debug what is happening.

That was good work and means that what I wrote earlier in this thread was incorrect, that there is indeed a difference! This means that when the SQL is generated DataStage makes use of the innate parallelism in DB2 to optimize the query and data transport to DataStage, whereas user-defined queries e...

Ankar, since you have marked this as an "urgent" issue you should go to your support provider as the general reaction from the volunteers here to such demands is to ignore the post for a while, which I will do. Also add some details to your problem description since what you posted isn't s...

How do you compute the new end_date that is to be written back to the table and how does OCONV() come into play?

The transform stage was optimized and at Version 7.x was very efficient.

There should be no difference if the two statements are identical. Since you do have performance differences I would guess that your user-defined code does something more. Do you select columns in your user-defined SQL that are not used in the flow?

Most likely you have an invalid date somewhere.

Try

There is something more involved here, since I cut-and-pasted your code to my machine and it compiled without warnings or errors.

If you delete your includes and conditional compile code does the error persist?

Yes, there are various ways and which method is used depends upon how you read the files. Are the Control-M characters in strings in the job? If yes, then a simple Convert(Char(13),'',In.ColumnName) will do the trick.

Since we don't know the cause of the problem we can't give a solution to ensure that it doesn't happen again.

Have you made sure that there are no left-over processes which might still be attached to the FIFO file. The easiest guaranteed solution is to bounce the server, if that is possible. Otherwise check to see if you have any processes left running when nothing is happening on the machine. Alternately, ...

If it is a BASIC routine then it will be referred to in the DS_ROUTINES at any version of DataStage. Try a "LIST DS_ROUTINES" to see what the contents are.

The general approach is correct. How are you parsing the return string?