Starting with Informix V11.10, a pair of procedures called
sysdbclose() will be executed by the database server at
connection/disconnection time of a session, if the procedures are created
by the database administrator.
This feature was implemented similarly to existing solutions in other database products. There is no need to change or recompile applications to utilize these procedures. The database server executes them behind the scenes, and the user even does not need to be aware of this.
If a procedure called
public.sysdbopen is present, it is called every
time a new connection to the database is opened by an user (if an
sysdbopen procedure for a specific user exists, that procedure
is called instead on behalf of that user). This procedure can be used to
run SQL statements at connection time of a session for things like
dynamically setting session environment variables or activating the
sqexplain trace for specific user names. As the procedure is executed
immediately when starting the connection to the database, the statement in
the procedure will affect the session before the first user SQL statement
At the time when a session disconnects from the server (even if the
administrator terminates the session by issuing
-z), the procedure
sysdbclose() is called. If no individual procedure for the disconnecting
user name is present, the public procedure
sysdbclose() is called.
This article illustrates how SQL manipulation using
sysdbclose() can be implemented avoiding the need of recompiling
applications and the recreation of the
sysdbopen procedure in case of
changes in functionality. The only additional structure needed
is a simple table to hold the control information and instructions for
As an additional benefit, the created procedures can record the latest timestamps of the user's connects and disconnects, the total number of connects and disconnects of a user, and the maximum number of simultaneous connections to the database originated by the user.
The usage of
sysdbclose() is not restricted
to a specific edition of the Informix® server. The minimum requirement is
This example was created and tested on Linux® using Informix Ultimate
Edition V11.70.UC3. It can be used without changes on all Linux and
UNIX® platforms. On Windows®, the path of the
sqexplain output has to be changed
to Windows syntax.
In our example, the control table is designed to hold only one row for each
user name (column "who" in the example below). So this user name is
created as the primary key of the control table. The second column,
expln, in the table is a flag, which can be set if the user's SQL
statements should be monitored using
The third field, wait_sec, is reserved for the lock wait time. It is kept in a separate
column to be able to change it independently of other instructions and
settings. For individual SQL statements, a field called
cmd_txt is available and can hold up to 128 characters for the instruction.
Listing 1. Example of a
sysdbopentab table to hold the instructions
create table sysdbopentab ( who varchar(32), expln char(1), wait_sec int, cmd_txt varchar(128) ); -- Make sure to have only one instruction for each user alter table sysdbopentab add constraint primary key (who) constraint sysdbopentab_p; -- All users must be able to read this table grant select on sysdbopentab to public;
To prepare a set of first tests of the procedure, we insert values for
some users: User "kalu" should be monitored using
addition, this user will get a lock wait time of 42.
insert into sysdbopentab values ("kalu","1",42,'');
User "carmen" should get a lock wait time of 23.
insert into sysdbopentab values ("carmen","0",23,'');
User "marion" should work with committed read last committed.
insert into sysdbopentab values ("marion",null,null,'set isolation to committed read last committed');
Hint: Create the procedures and table as user
"informix." During the
tests, avoid inserting commands for user "informix" in the
To record the connect and disconnect information for the users, we have to create a second table to hold these values. The recorded values are the last connect time, the time of the last disconnect, the name of the database (this is always the current database, it is provided to make it easier to join this information with the database sysmaster for all databases within this server), the number of connects and disconnects, and the maximum number of simultaneous connections of one user to this database.
Listing 2. Example of a
sysdbopenuser table to record the information
drop table if exists sysdbopenuser; create table if not exists sysdbopenuser ( who varchar(32), last_conn datetime year to second, last_disconn datetime year to second, db_name varchar(128), num_conn int, num_disconn int, max_conn int ); -- Make sure to have only one counter for each user alter table sysdbopenuser add constraint primary key (who) constraint sysdbopenuser_p; -- All users must be able to read, insert and update this table grant select,insert,update on sysdbopenuser to public;
After the preparations are done, the procedures
sysdbclose() should be implemented.
Look at this sample of the
sysdbopenuser table as a draft for your own
implementation of this feature. There is a lot of information within
the sysmaster database that could be worth recording before a session
disconnects. For example the number of deadlocks, the number of sequential
scans, the lockwaits or logpages used by a session are available from
sysmaster:syssesprof. The output of most
onstat commands are also
available via the tables of the sysmaster database and can be selected
from every database within that server.
When creating the procedure
public.sydbopen(), it is important that
the identifier "public" is given. Without this, the
procedure would only be executed for the user that created the
In the following section, you can see an example of a public
sysdbopen() procedure. This is meant for use as a (working) draft that should be
adapted to the needs of your specific database environment.
Listing 3. Example of the procedure
CREATE PROCEDURE public.sysdbopen() DEFINE stmt1 varchar(128); DEFINE stmt2 varchar(128); DEFINE expln_file varchar(128); DEFINE cmd_txt_x varchar(128); DEFINE my_database varchar(128); DEFINE sess_id int; DEFINE who_x varchar(32); DEFINE expln_x char(1); DEFINE wait_sec_x int; DEFINE flag int; DEFINE num_conn_x int; DEFINE max_conn_x int; DEFINE act_conn_x int; ON EXCEPTION --if we insert statements that are invalid, --DO NOT PREVENT USERS FROM WORKING !!! --keep on working in case of an error within this procedure. END EXCEPTION WITH RESUME; --only set this tracing to test changes of this procedure - NOT for normal work --SET DEBUG FILE TO "/tmp/sysdbopen.out"; --TRACE ON; --All sqexplain output should be collected in "/tmp" as username.session_id SELECT "/tmp/"||USER||"."||DBINFO( 'sessionid' ) INTO expln_file FROM systables WHERE tabid = 1; --See, if the user has already an entry in sysdbopentab: SELECT who, expln, wait_sec, cmd_txt INTO who_x, expln_x, wait_sec_x, cmd_txt_x FROM sysdbopentab WHERE who = USER; IF (who_x is not null) THEN IF (expln_x = "1") THEN -- activate sqexplain for that user LET stmt1 = 'set explain file to "'||expln_file||'";'; LET stmt2 = 'set explain on;' ; EXECUTE IMMEDIATE stmt1; EXECUTE IMMEDIATE stmt2; END IF; IF (wait_sec_x is not null) THEN -- set lock wait time for that user LET stmt1 = 'set lock mode to wait '||wait_sec_x||';'; EXECUTE IMMEDIATE stmt1; END IF; IF (cmd_txt_x is not null AND cmd_txt_x != '') THEN -- execute further SQL statements LET stmt1 = cmd_txt_x||';'; EXECUTE IMMEDIATE stmt1; END IF; END IF; -- Now let's update the user's last connection time -- or insert the user at the first connection -- first determine the current database-- SELECT odb_dbname INTO my_database FROM sysmaster:sysopendb WHERE odb_sessionid = DBINFO( 'sessionid' ) AND odb_iscurrent = 'Y'; -- Now let's see if we already know this user SELECT count(*) INTO flag FROM sysdbopenuser WHERE who = USER AND db_name = my_database; IF flag = 0 -- new entry THEN BEGIN INSERT INTO sysdbopenuser VALUES (USER,CURRENT year to second,null,my_database,1,0,1); END ELSE BEGIN SELECT count(*) INTO act_conn_x FROM sysmaster:syssessions s, sysmaster:sysopendb o WHERE s.username = USER AND o.odb_dbname = my_database AND o.odb_sessionid = s.sid; SELECT num_conn + 1, max_conn -- increase connections by one INTO num_conn_x,max_conn_x FROM sysdbopenuser WHERE who = USER; IF (act_conn_x > max_conn_x) -- new maximum connections THEN let max_conn_x = act_conn_x; END IF; UPDATE sysdbopenuser SET (last_conn, num_conn, max_conn) = (CURRENT year to second,num_conn_x,max_conn_x) WHERE who = USER AND db_name = my_database; END END IF END PROCEDURE;
Using the example above, you can see that user "kalu" is traced by
sqexplain and has the lock mode set to 42 seconds. The other sessions
do not write
sqexplain information. User "carmen" has lock mode set
to wait 23 seconds and the isolation level of user "marion" is set to LC
Listing 4. Check session parameters with
onstat -g sql: IBM Informix Dynamic Server Version 11.70.UC3 -- On-Line -- Up 01:42:23 ... Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 117 - stores LC Not Wait 0 0 9.24 Off ← marion 116 - stores CR Wait 23 0 0 9.24 Off ← carmen 115 - stores CR Wait 42 0 0 9.24 On ← kalu
Be careful and test the statements inserted into the control table. Most SQL errors are hidden by the exception clause of the procedure, so users normally should not see an error, if your SQL statement contains a mistake. However, you could write a SQL statement that causes bad performance or a delay at the start of the database session.
Now we have a working
sysdbopen() procedure and are able to influence the
SQL behavior by simply modifying data records within the
The only thing missing after that step is a way to record the last
disconnect timestamp of each user. This can be implemented by
creating a procedure called
sysdbclose() ist called when a session disconnects from the
database. As mentioned, the creation of the
sysdbopen() procedure, it is very important that the owner "public" is determined at
creation time so the procedure is called by everyone.
Listing 5. Example of the procedure
CREATE procedure public.sysdbclose() DEFINE cmd_txt_x varchar(128); DEFINE my_database varchar(128); DEFINE sess_id int; DEFINE who_x varchar(32); DEFINE act_conn_x int; DEFINE max_conn_x int; DEFINE num_disconn_x int; DEFINE flag int; ON EXCEPTION --IF we insert statements that are invalid, --DO NOT PREVENT USERS FROM WORKING !!! --keep on working in case of an error within this procedure. END EXCEPTION with resume; --only SET this tracing to test changes of this procedure - NOT for normal work --SET debug file to "/tmp/sysdbclose.out"; --trace on; -- Now let's UPDATE the user's disconnection time -- determine my database SELECT odb_dbname into my_database FROM sysmaster:sysopendb WHERE odb_sessionid = DBINFO( 'sessionid' ) AND odb_iscurrent = 'Y'; -- see if the user hal already an entry in sysdbopenuser SELECT count(*) into flag FROM sysdbopenuser WHERE who = USER AND db_name = my_database; IF flag = 0 THEN BEGIN insert into sysdbopenuser values (USER,null,CURRENT year to second,my_database,1,0,1); END ELSE BEGIN -- how many sessions are active ? SELECT count(*) into act_conn_x FROM sysmaster:syssessions s, sysmaster:sysopendb o WHERE s.username = USER AND o.odb_dbname = my_database AND o.odb_sessionid = s.sid; -- increase the value of disconnects by one SELECT num_disconn + 1, max_conn into num_disconn_x,max_conn_x FROM sysdbopenuser WHERE who = USER; -- record id number of actual connections exceeds maximum recorded IF (act_conn_x > max_conn_x) THEN let max_conn_x = act_conn_x; END IF; -- update the values in sysdbopenuser UPDATE sysdbopenuser SET (last_disconn, num_disconn, max_conn) = (CURRENT year to second,num_disconn_x,max_conn_x) WHERE who = USER AND db_name = my_database; END END IF END procedure;
From now on, for all users that have no individual procedure
sysdbclose(), the table
sysdbopenuser records the information about
recent connects to and disconnects from the database.
Listing 6. Example of output information recorded in
select * from sysdbopenuser who kalu ← username last_conn 2011-08-11 10:18:20 ← latest connect time last_disconn 2011-08-10 11:26:08 ← latest disconnect time db_name stores ← current database num_conn 12 ← number of connects num_disconn 10 ← numer of disconnects max_conn 2 ← max. parallel connections who carmen last_conn 2011-08-11 10:40:25 last_disconn 2011-08-09 15:05:29 db_name stores num_conn 5 num_disconn 2 max_conn 3 who marion last_conn 2011-08-11 10:19:04 last_disconn db_name stores num_conn 1 num_disconn 0 max_conn 1
In addition, you can create a small procedure to reset the content of the
sysdbopenuser. Using the reset feature, you will be able to record
the values for a determined period of time.
Listing 7. Example of a procedure
CREATE procedure public.sysdbopenreset() ON EXCEPTION --IF we insert statements that are invalid, --DO NOT PREVENT USERS FROM WORKING !!! --keep on working in case of an error within this procedure. END EXCEPTION with resume; DELETE FROM sysdbopenuser WHERE 1 = 1; END procedure;
Using the feature of overloaded functions, we can create another procedure
with the same name and different signature to be able to reset only the
content of the table
sysdbopenuser for a single user name.
Listing 8. Example of the procedure
sysdbopenreset() with same name and different signature
CREATE procedure public.sysdbopenreset(x_user varchar(32)) ON EXCEPTION END EXCEPTION with resume; DELETE FROM sysdbopenuser WHERE who = x_user; END procedure;
The reset for a single user or for all users can be executed as a simple procedure call.
EXECUTE PROCEDURE sysdbopenreset('kalu'); EXECUTE PROCEDURE sysdbopenreset();
Informix provides a powerful tool named
sysdbclose() to be
used by the database administrator to influence session behavior. Once
implemented, no further change to DDL structures or applications is needed
to adjust parameters or set tracing on. This sample code of a parameter-driven procedure is even prepared for possible future session options, by
only inserting one row into a control table. Before you start implementing
this feature on a production environment, first download the free Informix
developer edition and start your tests on that platform. If you verified
that your setting fits the needs of your system, you can transfer the
settings to your production environment.
|Sample SQL scripts for the procedures||informix_sysdbopen.zip||3KB|
|Sample SQL scripts for reset procedure1||informix_sysdbopen2.zip||1KB|
- The scripts in informix_sysdbopen2.zip are for evaluation and reset.
- Visit the IBM Informix 11.70 Information Center.
- Learn more in the Using SYSDBOPEN and SYSDBCLOSE Procedures.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.