Overview

Starting with Informix V11.10, a pair of procedures called sysdbopen() and 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 individual 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 is performed.

At the time when a session disconnects from the server (even if the administrator terminates the session by issuing onmode -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 sysdbopen() und 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 each user.

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.

Back to top

System requirements

The usage of sysdbopen() and sysdbclose() is not restricted to a specific edition of the Informix® server. The minimum requirement is V11.10.

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.

Back to top

Preparation

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 sqexplain. 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.

        
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 sqexplain. In 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 sysdbopentab.

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.

        
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 sysdbopen() und 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.

Back to top

Create sysdbopen procedure

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 procedure.

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.

        
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 (LastCommitted) read.

  
        
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 sysdbopentab table.

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().

Back to top

Create sysdbclose procedure

The procedure 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.

        
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 sysdbopen() and sysdbclose(), the table sysdbopenuser records the information about recent connects to and disconnects from the database.

        
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 

Back to top

Reset values

In addition, you can create a small procedure to reset the content of the table sysdbopenuser. Using the reset feature, you will be able to record the values for a determined period of time.

         
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.

        
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(); 

Back to top

Conclusion

Informix provides a powerful tool named sysdbopen() and 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.

Back to top

Downloads

Information about download methods

Note

1. The scripts in informix_sysdbopen2.zip are for evaluation and reset.

Resources

Learn

• 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.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Gerd Kaluzinski is a Certified IT Specialist who works with Informix since 1992. He works as consultant at customer sites in IBM Softwaregroup Services and as trainer for IBM education. His specialties are Informix replication (HDR/RSS/ER/GRID), Datablades (for example, TimeSeries, Excalibur) and Stored Procedures. He is the editor of the monthly "German IBM Informix Newsletter."

Rate this article

Comments

Back to top

Table of contents

• Overview
• System requirements
• Preparation
• Create sysdbopen procedure
• Create sysdbclose procedure
• Reset values
• Conclusion
• Downloads
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers