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.