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:

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.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
------------------------------

Original Message:
Sent: Thu February 06, 2025 10:54 AM
From: Art Kagel
Subject: An accurate timestamp in a stored procedure

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
------------------------------

Original Message:
Sent: 2/6/2025 11:04:00 AM
From: Paul Watson
Subject: RE: An accurate timestamp in a stored procedure

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

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
------------------------------

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
------------------------------

Original Message:
Sent: Fri February 07, 2025 03:12 AM
From: Andreas Seifert
Subject: An accurate timestamp in a stored procedure

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
------------------------------

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:

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:

So:

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Fri February 07, 2025 07:27 AM
From: Andreas Legner
Subject: An accurate timestamp in a stored procedure

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
------------------------------

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
------------------------------

Original Message:
Sent: Fri February 07, 2025 08:28 AM
From: Art Kagel
Subject: An accurate timestamp in a stored procedure

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
------------------------------


Original Message:
Sent: Fri February 07, 2025 07:27 AM
From: Andreas Legner
Subject: An accurate timestamp in a stored procedure

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

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
------------------------------

Original Message:
Sent: Fri February 07, 2025 08:56 AM
From: Andreas Legner
Subject: An accurate timestamp in a stored procedure

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
------------------------------


Original Message:
Sent: Fri February 07, 2025 08:28 AM
From: Art Kagel
Subject: An accurate timestamp in a stored procedure

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


Original Message:
Sent: Fri February 07, 2025 07:27 AM
From: Andreas Legner
Subject: An accurate timestamp in a stored procedure

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