Detailed log of all requests actioned on DB

[HebieBug]

Have an interesting one at the moment.
We have a applicaiton that sits on Sco Unix, Informix with a client connect through 4 Jays.
I also have an in house appliction built on Microsoft SQL with VB exe front end.
We would like to display some of the data from the Informix server onto the VB app. I have got that part figured out.
The only issue that I have is that I need to find what SQL requests are coming to the Informix box. That way on the 4 jays app I can get to the screen see what requests arebeing made to the database. Then just into the informix tables confirm the fields..... create a Stored procedure to do the same job through Microsoft SQL.
In SQL Uncle Bill calls it "profiler" does anyone know if IBM have something similar ? The profiler basically gives an on screen log of all transactions to the database.

 

[NatAnderson]

This should give you at least part of the information you are looking for. Use

onstat -g sql

to get a list of sessions, current query type that is executing, and the database the session is connected to. To show the sql that was last executed for a particular session, use

onstat -g sql sessionid

where sessionid is the first column from the first command. This will show the most recently executed query for that session, as well as the last parsed query. If you are looking for a particular user's session, you can run

onstat -u

, which returns the session id in the third column and the username in the fourth column.

 

[HebieBug]

cool dude.
I have been getting deeper into the product and discovered some intesting ideas on the log.
I say on teh web that there was a dude who created a SQL string that reported who was logged on. He was heading to the sysmaster database and pulling the data from there.
The onstat commands looks like it is running straight to the sysmaster to get it's results.
The sysmaster tables looks like it is a transactional database.. Did some exploring into the sysmaster database through an ODBC connection linked therough Access.
Found that there was a table that contained the current logged o users and all the SQL strings that they had run against the database.
I do not know if this does damage to the sysmaster table. To be on the safe side anyone who wants to play cowboy do it on your teat enviroment first.
I can't be anymore specific unfortunatly that I am not a guiness on Informix it is software that I am not completly comfortable. It was only 2 weeks ago that I learnt how to shut the DB down and bring it back up.

 

[NatAnderson]

The sysmaster table is generated by the database at runtime. It is used by Informix to keep track of system statistics as well as provide information about tables, columns, chunks, dbspaces, etc. You should not be able to cause any problems to the database by playing with the sysmaster database, or the system tables in each database. The system tables in each database are those with a tabid of 99 or less. You can find out the names of these tables by using the following query:

SELECT tabname FROM systables WHERE tabid <= 99;

If you are new to Informix and need a good reference, I would recommend The Informix Handbook by Ron Flannery. It is $99.00, but well worth the price, in my opinion.