HANA SQLScript "Sleep" Command

Question

Is there an equivalent of a Sleep(1000) command in HANA SQLScript?

I'm not seeing anything in the HANA SQLScript reference, so I'm wondering if there might be a creative workaround that won't peg the CPU at 100%.

Answer 1

HANA does not support anything like this out of the box. There are two approaches that I can think of, if you absolutely HAVE to have this

Busy Waiting

As already suggested on the SCN, you could create a procedure that is stuck in a while loop until a certain time threshold is reached. Mr. Amid Das suggested something like this:

CREATE PROCEDURE PLEASE_SLEEP ( IN SLEEPING_TIME INTEGER, OUT WOKE_UP_AT TIME )
LANGUAGE SQLSCRIPT READS SQL DATA AS
  V_TIME TIME;
  V_TIME_TO_WAKE TIME;
BEGIN
  V_TIME := CURRENT_TIME;
  V_TIME_TO_WAKE < ADD_SECONDS ( TO_TIME( V_TIME ), SLEEPING_TIME );
  WHILE V_TIME != V_TIME_TO_WAKE DO
    V_TIME := CURRENT_TIME ;
  END WHILE;
  WOKE_UP_AT := V_TIME_TO_WAKE ;
END

I am not sure how heavy the CPU usage will be, but it is definitely not the best.

Using Table Lock Timeouts

This idea is a little bit crazy and complicated but might just work, without sacrificing too much CPU power.

The only SQL command that can "wait" is LOCK TABLE. This command will time out after a certain amount of time if a table lock could not be acquired. This timeout can be set using the SET TRANSACTION LOCK WAIT TIMEOUT [number of milliseconds].

Now we just need a table that can be locked by the procedure, but also a different transaction that is keeping that table locked. This is the tricky part. What might work is using the XS Engine to schedule a continuous XS Job that will keep said table locked for as long as possible. Alternatively, you could write a Python script that will keep the table locked.

This idea is very experimental and I havent tried it myself. But I believe that it should work if everything is set up correctly.

Answer 2

You can try to use procedure SLEEP_SECONDS from library SYNCLIB. I am using SAP HANA Platform 2.0 SPS 02 and its working.

Example of use:

BEGIN:
    USING SQLSCRIPT_SYNC AS SYNCLIB;
    CALL SYNCLIB:SLEEP_SECONDS(<TIME_IN_SECONDS>); 
END;

Documentation: HANA SQL Script Reference