DS job taking long time to finish

kishorchitra · Post by **kishorchitra** » Mon May 02, 2005 1:08 pm

Hi,

I am fairly new to datastage and don't really know much, but I am supposed to do production support for few handful data stage jobs. I have a simple data stage job which has insert/update/delete logic. The data it deals with is in the order of few thousands only. But, It runs for more than an hour after the above message comes up (CTransformerStage53: DSD.StageRun Active stage finishing.). I want to know whether any one else encountered the same problem and is there any fix to it.

Below is the last few steps in the log file:

DsHcFrmlyAccessStCs..ORAOCI854: UPDATE HIST_HC_ACCOUNT_ACCESS SET ACCESS_LEVEL = :3, RECORD_EFFECTIVE_THRU_DT_TIME = TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'), CRTD_BY_USR = :6, UPD_BY_USR = :7, UPD_DT = TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'), DEL_FLG = :9, CURRENT_FLG = :10 WHERE ACCOUNT_ID = :1 AND PRODUCT_ID = :2 AND RECORD_EFFECTIVE_FROM_DT_TIME = TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS')

DsHcFrmlyAccessStCs..CTransformerStage53: DSD.StageRun Active stage finishing.

Please let me know.

Thanks,
KH

tonystark622 · Post by **tonystark622** » Mon May 02, 2005 4:08 pm

You might want to check the &PH& directory in your project directory. When there are a lot of files in this directory it can take a job a loooonnnng time to finish. If no DataStage jobs are running (ps -ef |grep -i dsd) you can delete all the files in this directory.

Also, you may have an after job routine that runs after the job finishes. I don't know of a way to see if an After Job Routine is running. Just check Job Properties to find out if there is an After Job Routine defined.

Tony

kcbland · Post by **kcbland** » Mon May 02, 2005 9:02 pm

You need to look at the explain plan. If it's a full table scan of a partitioned table and you're not supplying the partitioning key with a parallel query, then you are in the worst situation possible on the database side. Start with the database and have a DBA look at the activity.

tonystark622 · Post by **tonystark622** » Tue May 03, 2005 7:00 am

Hmmm. Perhaps I was thrown by the Active Stage Finishing message

. Sorry if I misled you.

Tony

kishorchitra · Post by **kishorchitra** » Tue May 03, 2005 10:13 am

Thanks guys for all your help. Yes, I also figured that it is the problem with the first SQL, the way the data is being pulled from the data base and it is being fixed.