One of the new features in Cheetah is the ability to enable tracing for SQL. While this has been available in limited method though the use of the IBM product I-SPY, the drawback before has always been the performance hit to actually monitor this information. That limitation is effectively eliminated with this feature in Cheetah. So how is SQL Tracing enabled? It's actually pretty simple, one way is to just put SQLTRACE in your ONCONFIG. One configuration I use most often is:

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

This allows me to keep track of the last 1000 sql statements on the instance.

However ONCONFIG is not the only way to enable tracing, you can also do this through the sysadmin database by running the following:

EXECUTE FUNCTION task("set sql tracing on", 1000, 2,"low","global");


So by using either of the above methods you now have enabled SQLRACE, but how do you validate it. The easiest method is by running :

onstat -g his

Below is a sample of onstat -g his output.

In Part 2 we will look at how to read this in SQL, and possible options for the data.

Author: cheetahblog

Find more about it here.

Find out more about Informix Dynamic Server team.

Copyright © 2007. All Rights Reserved.Copyright 2007