Sleep time syntax for db2 store procedure

user_6671627 0
Viewed 3232

For oracle we can use the following syntax for sleep

DBMS_LOCK.SLEEP(sleepTime);

For mysql we can use the following syntax for sleep

DO SLEEP(sleepTime);

For db2 how could I achieve this?. Following is part of my script.

REPEAT
IF rowCount > 0
THEN

DO SLEEP(sleepTime);

END IF;
DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE IN ('EXPIRED','INACTIVE','REVOKED') OR (TOKEN_STATE='ACTIVE');
GET DIAGNOSTICS rowCount = ROW_COUNT;
UNTIL rowCount=0 END REPEAT;

How can we do sleep with db2?.Any help on this would be appreciated

stored-functions db2 sleep syntax
2 Answers

At the present time IBM did not supply a DBMS_LOCK module for Db2-for-LUW although that may change in the future or you can implement your own if you have the skills.

But If you are using recent Db2 versions for Linux/Unix/Windows, then you can abuse the DBMS_ALERT.WAITONE procedure. It's not an exact match but may be good enough. The idea is to wait a specified time for an alert(signal) that's never going to be triggered (i.e. you have to ensure the code does not signal the specified alert unless you want to interrupt the wait).

for example, the block below will wait for 5 minutes:

--#SET TERMINATOR@
 BEGIN
     DECLARE v_outmessage VARCHAR(32672);
     DECLARE v_outstatus integer default 0;
     DECLARE v_seconds INTEGER default 300;
     CALL dbms_alert.waitone('whatever',v_outmessage ,v_outstatus,v_seconds);
 END@

There is also the option to implement a sleep function (as an external UDF or an external stored procedure) and that is described here (requires C compiler etc).

user_82906340

Try the undocumented call DBMS_ALERT.SLEEP(60)

user_95253440
Related