So have you ever wanted to have an easy way to know how long you SQL waited on I/O? What about the actually number of sequential scans for an individual query? How about the average execution time of a query without running a script and using time() or timex() as part of the equation. I know I have. And until We got to informix 11.10 and above, we didn't have that opportunity, at least not natively. Technically we had an old IBM/Informix product called I-SPY that offered most of the functionality that you might want, but it was :

1. High Overhead
2. A separate application to manage

Beginning in version 11.10 we have the ability to handle that information natively. It handled by a new ONCONFIG variable called SQLTRACE. SQLTRACE can be set like the following:

# SQLTRACE     - Configures SQL tracing. The format is:

#                SQLTRACE level=(low|med|high),ntraces=<#>,size=<#>,

SQLTRACE level=high,ntraces=1000,size=2,mode=global

I pulled that out of one of my test boxes, and you can see I have mine set to high , that mode is slightly more overhead, but not a huge amount, however it gives you a lot more diagnostic information.

The best thing about SQLTRACE is you can set it dynamically. You can use OAT to set it, or you can set it yourself using the sysadmin api. The syntax is fairly easy, so to mimic what I have above it would be

EXECUTE FUNCTION TASK ("SET SQL TRACING ON", 1000, 2, "high", "global");

The next question of course is how do you access this information. You have two ways, plus OAT, to look at the info, the first is through onstat.

In this case it's onstat -g his and has the following type of output:

This one is just showing a DATABASE connection so nothing particularly noteworthy, but it still shows you the format that you will see for all queries.

You can also see that like an onstat -g sql, we trap the error number. And yes it looks like I have ER turned on somewhere, but didn't actually create the syscdr database.

If you look a little closer though, this output will also show you the caveat to this functionality, namely the info is in the equivalent of a circular linked list. So looking at the above, trace number 1001 will overwrite your first entry here. Note that OAT comes with a function that will let you write this info to disk, thus saving the info to do historical information, or a poor man's auditing of queries. 

The other option to gather info is by way of SQL, specifically querying the syssqltrace table. The output is not as pretty, but it allows you to search on particular session ID's, or most anything in the above output.

All in all this is a great advancement if you are trying to track down poor performing queries. 

-Mark