How to obtain the actual date and time in a stored procedure

Troubleshooting

Problem

This article describes a problem which occurs when CURRENT does not recalculate the actual date and time when called more than once in a stored procedure.

Resolving The Problem

PROBLEM

Repeated calls to the CURRENT function within a stored procedure always returns the same value.

This problem is typically seen when the stored procedure uses a cursor that loops through large quantities of data, such that by the time the final row is reached, the actual time has moved on and CURRENT no longer hold the true system datetime.


CAUSE

If using CURRENT in a stored procedure, the datetime value is set to the current system datetime at the time the procedure is called. CURRENT is not updated while the stored procedure is running.


SOLUTION

The system table sysmaster:sysshmvals.sh_curtime stores the exact system datetime. Run the following query within your stored procedure to return the actual time and store it into the local variable current_time.

select DBINFO('utc_to_datetime', sh_curtime) into current_time
from sysmaster:sysshmvals;

The following example demonstrates the different results returned when comparing the use of the CURRENT function with that of the sysshmvals query shown above.


Example:

create database currdemo;

create table dates (text char(20),
                    time datetime year to second);

create procedure test()
define i int;
define current_time datetime year to second;


insert into dates values ("current 1st run", CURRENT);

select DBINFO('utc_to_datetime', sh_curtime) into current_time
from sysmaster:sysshmvals;
insert into dates values ("realtime 1st run", current_time);

system "sleep 5" ;

insert into dates values ("current 2nd run", CURRENT);

select DBINFO('utc_to_datetime', sh_curtime) into current_time
from sysmaster:sysshmvals;
insert into dates values ("realtime 2nd run", current_time);

end procedure;

execute procedure test();

select * from dates order by text;


The output looks as follows:

text                 time

current 1st run      2006-10-31 17:02:11
current 2nd run      2006-10-31 17:02:11
realtime 1st run     2006-10-31 17:02:11
realtime 2nd run     2006-10-31 17:02:16

 

출처 : https://www.ibm.com/support/pages/how-obtain-actual-date-and-time-stored-procedure