Data stage job stats (counts) stopped updating

gouraram · Post by **gouraram** » Tue Sep 30, 2003 9:39 am

Data stage job stats (counts) stopped updating counts once it reached running time(elapsed time) of 24 hours, but it is till extrating records and writing into output file, only counts are not getting updated.
Any idea why it is stopped updating counts after 24 hours mark reached, is it because counts are based on time not on timestamp?

ds_developer · Post by **ds_developer** » Tue Sep 30, 2003 10:59 am

I saw this in 6.0.1 on Windows 2000 and reported it to Ascential. They did not offer any explanation or solution. Later on the counts in the monitor started again.
John

Neil C · Post by **Neil C** » Thu Oct 28, 2004 9:42 pm

Has there ever been a solution found for this problem? I have a job in the same situation currently. Running for 25:30 hours and the stats stopped updating some time back. It SEEMS to be still running, but I would like to know how far it has got. As far as I can see the two Oracle databases involved in the process do not have a problem.

kcbland · Post by **kcbland** » Thu Oct 28, 2004 9:55 pm

I'm going to avoid asking why you find a runtime of anything measured in days to be acceptable performance.

You may consider that your process died and doesn't know it. It could be that you exceeded a 2.2 gigabyte limit for your particular release of DS and OS if you're constrained by 32-bit files. Could you please give us a little more info about your platform?

Neil C · Post by **Neil C** » Thu Oct 28, 2004 10:11 pm

In excess of 24 hours run time is in no way acceptable. It is doing a lot of work (many rows to process) but it should run quicker than it seems to have been over the last day. It was running at about 8 rows per second when the counts stopped. It usually does run quicker than this. Maybe up to 100 rps.

It is transfering data from several tables in an Oracle 9.2.0.5 system (the SQL is narly and needs looking at. It does outer joins on 4 tables etc.). It then writes it to a second database, also on 9.2.0.5. The insert is done using an Oracle procedure, and this may also update records that are allready there. This process also needs looking at, as it could be better, IMO.

I would be worried about the size of the Oracle undo files at this stage, but can not see any sign of there being problems in this area. Don't ask me why the job has been written the way that it has, and does not commit frequently. I know that it needs rewriting...

kcbland · Post by **kcbland** » Thu Oct 28, 2004 10:16 pm

Ayyyeeesh. 24+ hours??? 8 row/s???

ray.wurlod · Post by **ray.wurlod** » Thu Oct 28, 2004 10:19 pm

I suspect that the display of elapsed time uses Oconv(seconds, "MTS"), which is written so as to return a value in the range 00:00:00 through 23:59:59.

You can test it yourself; Oconv(Iconv("28:00:00","MTS"), "MTS:") returns "04:00:00")

Neil C · Post by **Neil C** » Mon Nov 01, 2004 6:52 pm

Yay! It has ended OK. I was not looking forward to the rollback if it crashed....

Have now dissabled 24 RI constraints on the target table and it is running again. Lot less rows today, but a lot faster as well. Now to see how many other tables have useless constraints eneabled on them...

Thanks guys.