DSXchange

DS.REINDEX ALL won't help. This error usually indicates a problem with one of the system tables UV.ACCOUNT or UV_SCHEMA. Did "they", as part of getting the system back up and running, re-locate any of your DataStage components, particularly the DSEngine directory or project directories? Can you conn...

That can occur simply by having too much demand for memory on the client machine (close some windows). Or it may be that the graphical components for the QualityStage stages have not been installed.

Once you post your analysis of the logic of the SQL I'm sure there will be those here who will be willing to help.

Why not simply run this query against the database from DataStage then have DataStage process the results?

Just curious; where does the variable called "i" get its value? You test it in

No, because you're not doing it in DataStage.

You are using a stored procedure to trigger the PL/SQL. This is too far removed from DataStage. You could always follow it up with DataStage; to have DataStage to read wherever it is that you've had the PL/SQL dump its messages.

Surely it's %mm for a numeric month?

DataStage BASIC expressions recognize three quote characters. If you need to quote a single-quote character surround it with either of the others. Therefore you can represent a single quote as "'" or as \'\. Hence, for example, Ereplace(Input_Str, "'", "'", -1, 0)...

Step 1 is to analyze, and document, the logic of the SQL. The main difference between a query and a job is that the job has to do something with the result.

Why not simply run this query against the database from DataStage then have DataStage process the results?

UV.ACCOUNT is not available (by default) from the Administrator client. UV_SCHEMA, however, is.

Whence arose that rather odd requirement?

What about all the other environment variables? Technically every environment variable is user-defined. The "User Defined" category in the Administrator client is just a nicety - you can create them anywhere (if you know how).

Plus you need some token designators ("%") in the format string. For example "%mm/%dd/%yyyy".

You are generating a string, not a date. You need to apply a StringToDate() conversion - with appropriate format string - to the result that your concatenation expression generates.

Indeed please explain your job design in detail as well.

Everything is a performance overhead, for some particular meaning of "performance".

Often this includes the component between the keyboard and the chair.

Welcome aboard. Why use DataStage? You can use ordinary operating system utilities with a great deal less effort. Unless it is as a learning exercise, why would you spend so much money on a mv command? (Or cp if your UNIX does not permit mv across file systems.) If this is part of some larger scheme...