DSXchange

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

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

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.

Hmmm. Perhaps I was thrown by the Active Stage Finishing message . Sorry if I misled you.

Tony

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.