Delay between SQL statements in DB2

[amos_s12]

Hello everybody

Is there a possibility to make a delay between two sql statements,
namely one sql statement is performed, then there is a delay of some
seconds and then rhe next statement is performed.
In sybase database, there is a possibility to do such thing by using
the statement waitfor delay 'hh:mm:dd'
for example:

while(...)
begin
update table
set field_name = <value>
where <condition>

waitfor delay 'hh:mm:dd'
end

If there is such possibility in DB2 I would like to know if it consumes CPU

Thank you ahead
Amos

[Knut Stolze]

am******@zahav.net.il wrote:

Hello everybody

Is there a possibility to make a delay between two sql statements,
namely one sql statement is performed, then there is a delay of some
seconds and then rhe next statement is performed.
In sybase database, there is a possibility to do such thing by using
the statement waitfor delay 'hh:mm:dd'
for example:

while(...)
begin
update table
set field_name = <value>
where <condition>

waitfor delay 'hh:mm:dd'
end

If there is such possibility in DB2 I would like to know if it consumes
CPU

If I got this right, then you want to have a delay _in_ your SQL statement,
right?

You could either write (a) a simple UDF (user-defined function) which does a
sleep, or (b) you add a loop that just counts something up.

Option (a) would not consume CPU cycles if you use an external programming
language like C/C++ or Java; if you use SQL, then it would consume CPU
cycles like option (b).

An SQL function could look like this:

CREATE FUNCTION delay(sec INTEGER)
RETURNS INTEGER
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE now TIMESTAMP;
DECLARE end TIMESTAMP;

SET now = TIMESTAMP(GENERATE_UNIQUE());
SET end = now + sec SECONDS;

WHILE ( now < end ) DO
SET now = TIMESTAMP(GENERATE_UNIQUE());
END WHILE;

RETURN 1;
END
@

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Jean-David Beyer]

am******@zahav.net.il wrote:

Hello everybody

Is there a possibility to make a delay between two sql statements,
namely one sql statement is performed, then there is a delay of some
seconds and then rhe next statement is performed. In sybase database,
there is a possibility to do such thing by using the statement waitfor
delay 'hh:mm:dd' for example:

while(...) begin update table set field_name = <value> where
<condition>

waitfor delay 'hh:mm:dd' end

If there is such possibility in DB2 I would like to know if it consumes
CPU

If you mean you are writing a program in some programming language, such
as C or C++, and running on a UNIX-like operating system (such as Linux),
you can do

while (...) {
EXEC SQL
UPDATE table
SET fieldname = :value
WHERE <condition>;
[ought to check return status here]
sleep (3600*hh + 60*mm + ss)
[or 86400*dd + 3600*hh + 60*mm, if that is what you meant]
}

This consumes very little CPU time as the OS will suspend your process
until the sleep() returns. You will need some testing, though, so read
about the sleep function before you use it (it can return before the time
is up under unusual conditions).

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 09:35:00 up 6 days, 19:16, 3 users, load average: 0.05, 0.08, 0.08