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

amos_s12@zahav.net.il wrote:
[color=blue]
> 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[/color]

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

amos_s12@zahav.net.il wrote:[color=blue]
> 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
>[/color]
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