Re: TRACE CURRENT in SPL

Jeff Craig asked: >Date: Sat, 5 Jul 1997 19:29:08 -0600 >From: settler@rmy.emory.edu (Jeff Craig) >X-Informix-List-Id: <list.15309> > >I am trying to track the number of updates completed >by a stored procedure in a given time period, so I >tried to do the following: > >...snip > >SET DEBUG FILE TO '/tmp/trace.log'; >TRACE OFF; > >TRACE ":" || CURRENT || ": " || UpdateTotal || " rows updated so far..."; > >...snip > >The TRACE is getting written out, but CURRENT isn't getting the current >time, merely reports the time the procedure started. > >Is there anyway to output the actual current time? Greg answered: >From: Greg <greg@cyberramp.net> >Date: Sat, 05 Jul 1997 23:46:00 -0500 >X-Informix-List-Id: <news.40103> > >Nope. This is a "feature". I think it sucks. gm. > >p.s. BTW, trace really slows things down under v7.x (but not v5...), so >don't bother measuring the performance of anything where trace is on. >Yes. I think this sucks too. >[...] Roger Tomas suggested: >From: Roger Tomas <tomasr@agcs.com> >Date: Mon, 07 Jul 1997 06:17:36 -0700 >X-Informix-List-Id: <news.40147> > >[...] >Build a UNIX command string that you then execute using the >system call. Something like the following: > > let command = 'echo "`date`: ' || UpdateTotal || ' rows updated so >far..." >> somefile'; > system command; > >I haven't tested this but I've done something similar in the past. Greg is right. Time is frozen while you are running a stored procedure. This also applies to nested stored procedures -- the start time of the top-level procedure (or SQL statement) is the time which will be returned by CURRENT throughout the procedure execution. Even if you invoke an auxilliary stored procedure which evaluates CURRENT each time it is called, it will use the system time for the top-level procedure. Hence, you do not get accurate elapsed times even if you use: CREATE PROCEDURE current_time() RETURNING DATETIME YEAR TO FRACTION(5); RETURN CURRENT YEAR TO FRACTION(5); END PROCEDURE; And then, in the procedure being traced, using: TRACE ":" || current_time() || ": " || UpdateTotal || " rows updated so far..."; Greg's comment about performance is accurate, but when you are tracing a stored procedure, you are not (or should not) be seeking performance. This would only be an issue if you are running into a synchronization (timing) problems. Roger's solution will work, after a fashion, but is slow and won't give sub-second resolution. This is fundamentally irritating at times. The origin of this behaviour is to give the appearance that SQL statements execute instantaneously. This is sound (think about it, hard). Nevertheless, it would be helpful for tracing and performance monitoring to be able to get actual elapsed times. Add it to the wishlist! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: I decline to respond to messages with anti-spam in the return path. PPS: Someone with anti-spam in the return path for their email address asked whether I will reply if they post a message to c.d.i with anti-spam in their address. The answer is No. I read c.d.i via email. If I cannot reply to the message without editing the To list or the Cc list, then I'm not going to spend time on the answer. Since the default reply list includes 'informix-list@rmy.emory.edu' and 'someuser@anti-spam', I will get a bounce, and I object to anti-spam bounces even more than I object to spam. Yes, I get spammed. Yes, I have a delete key which gets rid of unwanted email. No, I am not going to make a direct reply to the questions asked by said gentleman (who can probably guess who he is). In fact, I've already deleted them. If he wishes to get a response, he will have to ask me without anti-spam in his email address. And yes, I'd rather type essays like this than fiddle with address lists!