Search code examples
informixspl

Sleep from within an Informix SPL procedure

What's the best way to do the semantic equivalent of the traditional sleep() system call from within an Informix SPL routine? In other words, simply "pause" for N seconds (or milliseconds or whatever, but seconds are fine). I'm looking for a solution that does not involve linking some new (perhaps written by me) C code or other library into the Informix server. This has to be something I can do purely from SPL. A solution for IDS 10 or 11 would be fine.

@RET - The "obvious" answer wasn't obvious to me! I didn't know about the SYSTEM command. Thank you! (And yes, I'm the guy you think I am.)

Yes, it's for debugging purposes only. Unfortunately, CURRENT within an SPL will always return the same value, set at the entry to the call:

"any call to CURRENT from inside the SPL function that an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement invokes returns the value of the system clock when the SPL function starts."

—IBM Informix Guide to SQL

Wrapping CURRENT in its own subroutine does not help. You do get a different answer on the first call to your wrapper (provided you're using YEAR TO FRACTION(5) or some other type with high enough resolution to show the the difference) but then you get that same value back on every single subsequent call, which ensures that any sort of loop will never terminate.

Solution

  • There must be some good reason you're not wanting the obvious answer: SYSTEM "sleep 5". If all you're wanting is for the SPL to pause while you check various values etc, here are a couple of thoughts (all of which are utter hacks, of course):

    1. Make the TRACE FILE a named pipe (assuming Unix back-end), so it blocks until you choose to read from it, or
    2. Create another table that your SPL polls for a particular entry from a WHILE loop, and insert said row from elsewhere (horribly inefficient)
    3. Make SET LOCK MODE your friend: execute "SET LOCK MODE TO WAIT n" and deliberately requery a table you're already holding a cursor open on. You'll need to wrap this in an EXCEPTION handler, of course.

    Hope that is some help (and if you're the same JS of Ars and Rose::DB fame, it's the least I could do ;-)