An accurate timestamp in a stored procedure

Hi All, the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach. BUT 1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 2. We can use select (dbinfo-utc_to_datetime sh_curtime) from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need? ------------------------------ Andrew Cilia ------------------------------

Andrew: Yes, creating and calling a UDR is the only solution. I have a UDR coded that I can give you. Reach out to me directly. Example: > execute function adbm_gettimeofday();(expression) 2025-02-06 10:54:14.73923 1 row(s) retrieved. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

mi_lvarchar *oni_gettimeofday(mi_lvarchar *dtestr, MI_FPARAM *fp) { struct timeval tv; struct tm *ptm; char format[40]; char str[40]; gettimeofday(&tv, NULL); ptm=localtime(&tv.tv_sec); if(mi_fp_argisnull(fp,0)==MI_TRUE) stcopy("%Y-%m-%d %H:%M:%S",format); else sprintf(format,"%.*s", mi_get_varlen(dtestr), mi_get_vardata(dtestr)); strftime(str, 40, format, ptm); sprintf(str, "%s.%05ld", str, tv.tv_usec / 1000); mi_set_varlen(dtestr, stleng(str)); mi_set_vardata(dtestr, str); return(dtestr); } On 2/6/2025 9:26 AM, Andrew Cilia via IBM TechXchange Community wrote: 01000194dbdeebfa-d1a2b7ee-8d74-4514-9531-748b31a5e4a5-000000@email.amazonses.com"> Hi All, the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution.... -posted to the "Informix" group

My standard time functions can be downloaded from here https://www.oninit.com/download/time_udr.c Cheers Paul On 2/6/2025 10:03 AM, Paul Watson via IBM TechXchange Community wrote: 01000194dc00c48c-56da6e99-176f-4cf3-b3be-6227a42b13d0-000000@email.amazonses.com"> mi_lvarchar *oni_gettimeofday(mi_lvarchar *dtestr, MI_FPARAM *fp) { struct timeval tv; struct tm ... -posted to the "Informix" group

Hello Andrew, It is easier. You can determine the current time with this select: define ts datetime year to fraction(5); select dbinfo('utc_to_datetime', sh_curtime) into ts from sysmaster:sysshmvals; Make sure that the variable USEOSTIME is set to 1 in the onconfig so that you get the full time resolution. regards Andreas ------------------------------ Andreas Seifert CURSOR Software AG http://www.admin-scout.com ------------------------------

Thanks Art/Paul/Andreas, let me try Andreas' solution first as it seems to be the simplest. If I don't get anywhere with that, I'll go for the UDR. Guys, all your help is truly appreciated. Cheers ------------------------------ Andrew Cilia ------------------------------

Hi Andreas, Computer says no. Although I get the requred accuracy on the timestamp, the value returned is always the same no matter how many times you execute it in a Stored Procedure. I wasn't sure what USEOSTIME was set to and that is why I didn't rule it out immediately. Thanks just the same. So, going down the UDR route and I'll see where it takes me. ------------------------------ Andrew Cilia ------------------------------

Hello Andrew, I have just tried it again. It works exactly the same. Here is a small example: drop procedure test_time; create procedure test_time() returning varchar(100); define ts datetime year to fraction(5); select dbinfo('utc_to_datetime', sh_curtime) into ts from sysmaster:sysshmvals; return 'Start: '||ts with resume; system('sleep 5'); select dbinfo('utc_to_datetime', sh_curtime) into ts from sysmaster:sysshmvals; return 'End: '||ts with resume; end procedure; execute procedure test_time();The result is: informix@ifx-beta5:/opt/informix$ dbaccess sysadmin ts Database selected. Routine dropped. Routine created. (expression) Start: 2025-02-07 10:29:32.00000 (expression) End: 2025-02-07 10:29:37.00000 2 row(s) retrieved. Database closed. informix@ifx-beta5:/opt/informix I was wrong with USEOSTIME because the value in the sysshmvals only contains the seconds but not the fraction. regards Andreas ------------------------------ Andreas Seifert CURSOR Software AG http://www.admin-scout.com ------------------------------

Hi Andrew, how about this guy: create procedure ostime(inout integer, inout integer) external name '(_ostime)' language C;Through its two inout params it will give you seconds and micro-seconds immediately from a call to gettimeofday(). To demonstrate: create procedure test_ostime() -- returning integer, integer;returning varchar(255); define secs integer; define usecs integer; define rets varchar(255); let secs = 0; let usecs = 0; call ostime(secs, usecs); -- works: -- return secs, usecs; -- convert to string: let rets = dbinfo('utc_to_datetime', secs) || '.' || lpad(usecs, 6, '0'); return rets; end procedure; execute procedure test_ostime();HTH, Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Andreas: Very cool function, but the underlying _ostime() function is an undocumented internal function. Also, it requires USEOSTIME to be set to 1 (and a server restart if it is not already set). FWIW, here's a version of your "test" program that returns only five subsecond digits so it can be cast to a datetime for other uses and resolutions: CREATE PROCEDURE IF NOT EXISTS ostime () -- returning integer, integer;returning varchar(255); define secs integer; define usecs integer; define rets varchar(255); let secs = 0; let usecs = 0; call ostime(secs, usecs); -- works: -- return secs, usecs; -- convert to string: let rets = dbinfo('utc_to_datetime', secs) || '.' || lpad(usecs, 5, '0'); return rets; end procedure; So: > select ostime()::datetime year to fraction(5), (ostime()::datetime year to fraction(5))::datetime hour to fraction(2); (expression) (expression) 2025-02-07 08:27:34.82682 08:27:34.82 1 row(s) retrieved. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Art, I don't think it depends on USEOSTIME, it works well also with USEOSTIME 0. And careful with the microseconds: e.g. a value of 153 first had to be left padded to full six digits 000153 before any decision on how many digits after the dot you're interested in. Also: OUT instead of INOUT would of course be good enough in this case. Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Just a different look at it ... sort of a "trace" file approach ... and not really thought through to any depth ... just add a few SP calls at points in the relevant places in the stored procedure in question and then review the trace file: create procedure time_to_ns(in_phase char(200));define cmd char(256); define phase char(200); let phase = in_phase; let cmd = 'echo ' || phase || ' >> /tmp/jj1; date +%s%N >> /tmp/jj1'; SYSTEM cmd; return; end procedure; database jj1; execute procedure time_to_ns("PhaseN"); execute procedure time_to_ns("PhaseO"); execute procedure time_to_ns("PhaseP"); execute procedure time_to_ns("PhaseQ");PhaseN 1738937044744704720 PhaseO 1738937044761379505 PhaseP 1738937044771827677 PhaseQ 1738937044782441966 ------------------------------ Jon Ritson ------------------------------