Thursday, March 01, 2007

Cheetah spot by spot: sysdbopen/sysdbclose

Continuing the Cheeta's new features articles, it's time to talk about sysdbopen and sysdbclose.
These are DBA controlled procedures that are run by the users when the try to connect/disconnect to/from a database.

These procedures are regular SPL procedures but with a few particularities:

  • Only DBAs can create or drop them
  • The owner has more meaning than usual. If you create a procedure called myuser.sysdbopen, only "myuser" will run it when he connects to the database.
    If you create a procedure called public.sysdbopen, then all users that don't have a matching user.sysdbopen procedure, will run public.sysdbopen
So, why is this useful? Well... I can only point out several uses for it... But it will really depend on your environment:
  • Can be used to do some kind of logging for connects and disconnects
  • Can be used to change the isolation level when changing the application code is not an option
  • Can be used to change the LOCK MODE in the same situations as above
  • Can be used to stop the creation of new database sessions (inhibit connections during a maintenance period)
  • Can be used to restrict certain users from connecting at certain hours, or from certain hosts...
  • sysdbclose can be used to gather session statistics (and save them in some history table)
Some of the above are obviously very useful, some are less, and you can certainly find a lot more of useful actions that you can do with it.

This mechanism was made available in XPS (DW Informix engine) a long time ago, but it took really too much time to be ported to IDS. Flexibility is the keyword here. It improves usability and adds functionality.

One final note. There is an environment variable (IFX_NODBPROC) that can be used to stop the server from executing these functions, BUT it can only be used by DBAs. Since a DBA could even drop the function this is a nice feature.

It could be nice to have sysdbopen/sysdbclose procedures associated with database roles. Currently this is not possible, but you can use the generic "public.sysdb*" procedure and put some logic in it so that it behaves differently for different default roles (just an example).

For now, let's just appreciate the fact that this was finally implemented.

To summarize, this is really a simple feature, but one that really brings more flexibility and manageability for the DBAs.

Regards.

Posted by Fernando Nunes at 12:50 AM

1 comment:

Mark Tyrer said...

This is a fantastic feature. We are currently using version 10 FC5 on an HP box, however we are upgrading to 11.5 on an AIX box in 10 days time.

One of the challenges that we face is .net connections to the database are not being closed properly. We can get hundreds of connections from a single process which starts to impact on performance and eventually causes an assert failure.

I tested this simple procedure to limit the number of connections on 11.5 and works like a charm.

CREATE PROCEDURE username.sysdbopen()

DEFINE l_sid INTEGER; -- Session ID
DEFINE l_curr_sessions INTEGER;

LET l_sid = DBINFO('sessionid');

SELECT count(*)
INTO l_curr_sessions
FROM sysmaster:syssessions t1, sysmaster:syssessions t2
WHERE t1.sid = l_sid
AND t2.sid != l_sid
AND t1.username = t2.username
AND t1.uid = t2.uid
AND t1.pid = t2.pid
AND t1.hostname = t2.hostname
AND t1.tty = t2.tty;

IF l_curr_sessions > 50 THEN
RAISE EXCEPTION -746,0, 'Excessive Process Connections. Access Denied';
END IF;

END PROCEDURE;

Mark Tyrer

April 01, 2009 2:35 PM

Post a Comment

Subscribe to: Post Comments (Atom)