DSXchange

...Query looks something like "Select * from table".This Query takes 25 mins for execution... There has to be more there, since a SELECT * without other selection criteria would start pushing rows to DataStage almost immediately. It sounds like your query is doing a full-table-scan (or mo...

Before worrying about your query you need to ensure that the query is the bottleneck. Have you done so?

I just saw the "SH" (for "SH"ell) in the error message. Could it be that your job has a before-job call which might create this file using the mkdbfile command?

Normally a hashed file should not be in the root directory, and since you are using this file as a reference lookup it must exist at runtime.

DataStage is trying to open the file "/hILkp_CUSTOMERFORM". Does it exist right at the root level?

Is NLS involved?

Correct, the DS_JOBS file exists in each project and only contains jobs in the local project. You could use the UNIX command "dsjob -lprojects" then loop for each project and use "dsjob -ljobs {projectname}"

I might push the sort up front to Oracle - but again, without knowing the answers to questions posed earlier we are just guessing.

Craig - Yes. I should have explained it. There is a sporadic issue with enabled auto-purge at 8.x which causes jobs to abort. We have 4 multiinstance jobs, each running with several instances and the jobs start once a minute or so and sometimes nothing (bad) happens for a day or two, but once issues...

Sainath has already stated the next step. If the time still remains the same, then it isn't your transform or sort and that would mean your SELECT in Oracle is the bottleneck.

It would be best to turn off autopurge completely for the job.

You most likely have autopurge settings for this job. Go to the Director -> Clear Log and set the Autopurge to none.

Does resetting the job produce an entry in the log file with "From previous run..."

Replace your output oracle with a dummy copy or a sequential file stage going to /dev/null. Rerun the program. Does it still take 31 minutes? How busy is your CPU during this time? Is Oracle on the same machine as DataStage?

So, did you look at the program in this thread?