How to collect information for active SQL sessions on an Informix Dynamic server 7.31 database

Question

This article provided details of how to write an awk script that can collect information about all active SQL sessions running on an IBM® Informix® Dynamic Server 7.31 database.

Answer

INTRODUCTION

IBM® Informix® Dynamic Server (IDS) version 9.x introduced the ability to collect information for all active SQL sessions by using the command onstat -g sql 0. This functionality can be replicated for IDS version 7.31 databases by using the following awk script.

STEPS

Create an awk script that contains the following text:

BEGIN {system(":> onstat_g_sql_0")}
{
if ($NF > 0) {
my_string= onstat -g sql " $1 "  >> onstat_g_sql_0; echo \"----------------\" >> onstat_g_sql_0"
system(sprintf(my_string))
}
}
END {}

Note: This script directs the SQL information to a file called onstat_g_sql_0 in the current working directory. 

Run this script using the command 

onstat -g sql|tail +6|awk -f <scriptname> 

where scriptname is the name of the awk file created in the previous step. To improve usability, this command could be written as a shell script (with execute permissions) and located in a directory referenced by your PATH environment variable. 

Display the contents of the output file to view the details of all the sql in execution in the database. 

Example 

SQL running on database server

IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:54 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
13    SELECT         informix_log       CR  Not Wait   0    0    7.31
11    -              informix_log       CR  Not Wait   0    0    7.31

Sample output from awk script

IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:20 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
13    SELECT         informix_log       CR  Not Wait   0    0    7.31
Current statement name : slctcur
Current SQL statement :
  select * from test2
Last parsed SQL statement :
  select * from test2
----------------
IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:20 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
11    -              informix_log       CR  Not Wait   0    0    7.31
Last parsed SQL statement :
  UPDATE   test2 set str1="bbb" where key=0
----------------

http://www-01.ibm.com/support/docview.wss?