How to sleep/pause in a Stored Procedure?

Database

How to sleep/pause in a Stored Procedure?

I (geniunely) need to sleep inside a stored procedure as part of a loop which checks table contents and exits the loop based on content. Let's say for 5 minutes at a time.

I don't *think* there is such a command (even though there d*mn well should be :) )

Has anybody written any code which emaulates such a command?
5 REPLIES
N/A

Re: How to sleep/pause in a Stored Procedure?

It should be easy to write a C-UDF which only executes a sleep(x).

But could you elaborate on what you actually try to accomplish?
"a loop which checks table contents" sounds like you're waiting for an insert into that table.
A different approach might be a QUEUE table.

Dieter

Re: How to sleep/pause in a Stored Procedure?

You could create a stored procedure below and call it However you need to create a set of stored procedure if you need to have sleep for various times. Below is an example for delay for 1 minute

REPLACE PROCEDURE CDW_SANDBOX.DELAY_1minute()

BEGIN

DECLARE

v_delaycounter TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

    REPEAT

    SET v_delaycounter=v_delaycounter;

    UNTIL CURRENT_TIMESTAMP=v_delaycounter+ INTERVAL '1' MINUTE

    END REPEAT;

END;

thanks

rama

Teradata Employee

Re: How to sleep/pause in a Stored Procedure?

Please don't do the looping that Rama suggested. That will cause DBQL tables to fill up if DBQL is turned on. Sorry Rama.

May I suggest this:

Create a c file with the following source code:

----------------------------------

#define SQL_TEXT Latin_Text

#include "sqltypes_td.h"

#include <unistd.h>

void SleepInSeconds(int *SleepTimeInSeconds, char sqlState[6])

{

 sleep(*SleepTimeInSeconds);

}

----------------------------------

Then execute this SQL:

REPLACE PROCEDURE SYSLIB.SleepInSeconds(IN SleepTimeInSeconds INT)

LANGUAGE C

NO SQL

PARAMETER STYLE SQL

EXTERNAL NAME 'CS!SleepInSeconds!C:\SleepInSeconds.c';

Teradata Employee

Re: How to sleep/pause in a Stored Procedure?

I wonder why Rama's suggestion would fill up the DBQL.  I don't see any queries being run in his implementation of sleep.  Please let us know which DBQL table will be affected due to a loop which does not involve queries in the Stored  procedure.

N/A

Re: How to sleep/pause in a Stored Procedure?

It's probably not filling the Query Log or Access Log, but it's a waste of resources, it's a loop possibly consuming 100% CPU during the sleep.