SLEEP function clarification

vivekgadwal · Post by **vivekgadwal** » Mon Jun 29, 2009 4:31 pm

Gurus,

First of all, thank you for your help in resolving my earlier query about Trans() function.

I am using the SLEEP function to suspend the DataStage jobs from executing if, for a particular row (the latest row) in a Control Table, a couple of fields are not populated. These fields are to be updated by a different process, which utilizes the data we have loaded initially (into a Work table) and transforms the data and populates these two fields in the Control table as a signal that our ETL can be triggered. Hence, the requirement is for our process to wait until the process on the other team's end to be complete, without any manual intervention.

Just now, I came to know from one of my colleagues, that the SLEEP function has caused trouble for his team earlier (this was in earlier versions...presumably 7.0). He claims that it is not reliable as sometimes the process did not sleep which resulted in them developing a different mechanism instead of using SLEEP function. Even IBM acknowledged that there might be a minor Bug in the function at that time.

My questions are:
> Do you know of any such issues with SLEEP function?
> If so, do you know if that has been solved?

Please advise.
I am continuing my development as if SLEEP should not cause any trouble for me. If there is an alternate approach that I can take, kindly let me know.

Regards,

chulett · Post by **chulett** » Mon Jun 29, 2009 4:39 pm

This is regarding the DataStage SLEEP function rather than the one at the operating system level, yes?

ArndW · Post by **ArndW** » Tue Jun 30, 2009 4:23 am

I had issues with the DataStage SLEEP function several years ago with V7, it would usually function but sometimes, for no immediately obvious reason, stop working. I recall having opened up a call with support at the time, but as a sporadic problem it was almost impossible to track down. We modified our jobs to shell out to UNIX and issue a SLEEP there. I have not had any problems since that one installation and have used SLEEP a lot. Perhaps it was a combination of version and AIX...

chulett · Post by **chulett** » Tue Jun 30, 2009 6:08 am

Was wondering... don't think I've ever actually used the internal one but have shelled out and used the O/S sleep function without issue many (many) times and places.

vivekgadwal · Post by **vivekgadwal** » Tue Jun 30, 2009 8:56 am

ArndW wrote:I had issues with the DataStage SLEEP function several years ago with V7, it would usually function but sometimes, for no immediately obvious reason, stop working. I recall having opened up a call with support at the time, but as a sporadic problem it was almost impossible to track down.

This is exactly the problem faced and they opened up a ticket with IBM too. It seems they have run some tests on their side and confirmed that SLEEP (in DataStage) has some issues. They said that this might be fixed for later releases. Does anybody know if this has been fixed?
I, so far, didn't have any issues (I just started development last week and have done, may be, four or five runs using SLEEP). But, this issue concerns me.

ArndW wrote: We modified our jobs to shell out to UNIX and issue a SLEEP there. I have not had any problems since that one installation and have used SLEEP a lot. Perhaps it was a combination of version and AIX...

I don't think this problem is unique to the combination of version and AIX as we are SOLARIS based shop and we had issues with the function. I believe the way ArndW mentioned is the alternate path they have taken...i.e., to use OS level SLEEP functionality, although I haven't looked at the code in those jobs though (we are currently going through migration to a new box...so cannot access some projects!)

If I have to take up this route of using UNIX SLEEP, then does it suspend the whole process for the duration input to the SLEEP command? Does this have any effect on other jobs...both dependent to the process that calls the SLEEP command and independent to it?

Thanks for you suggestions...

Regards,

DSguru2B · Post by **DSguru2B** » Tue Jun 30, 2009 10:39 am

What about the NAP command. Did you look into it. I do not seem to recall ever having issues with the NAP command.

vivekgadwal · Post by **vivekgadwal** » Tue Jun 30, 2009 1:32 pm

DSguru2B wrote:What about the NAP command. Did you look into it. I do not seem to recall ever having issues with the NAP command.

NAP sounds like a viable option.
> Does it have any limitations...like the max. amount of time (in milliseconds) that we can enter?
> Also, in the DS Help, it says..."will slow down the performance of your job"!
I was wondering if waiting for 15 minutes to 30 minutes (wait time is not yet determined) and checking the database if the other process finished doing its job and wait for some more time if the process is not finished might be very very slow if I am using this NAP function! What are your comments on that?

Regards,

DSguru2B · Post by **DSguru2B** » Tue Jun 30, 2009 2:32 pm

Well, performance should really not be a deciding factor here as you are just checking for a value in a column. The job is goign to be running long anyways as you will beoing going sleep every few mins untill your condition is met.

ArndW · Post by **ArndW** » Wed Jul 01, 2009 12:35 am

I really don't know if NAP ends up executing anything other than an OS sleep call (well, "nanosleep" or the like) at UNIX. So I wouldn't rely on NAP not showing the same sporadic issues.