Home | Previous Page | Next Page   Tuning Database Server Performance > Managing Resource Use for Specific Sessions >

Specifying the Initial Environment for Sessions

To set the initial environment for one or more sessions, create SPL procedures that are executed whenever users connect to a specific database. Such procedures are most useful if users access databases through client applications for which you cannot set environment options.

You can also create an SPL procedure that is executed when a user disconnects from the database. Procedures do not accept arguments or return values. The sysdbopen() and sysdbclose() procedures must be executed from the connection coserver.

Important:
If you open a transaction in the SPL procedure, it must be committed before you end the procedure. In an ANSI-compliant database, all SQL statements are preceded by an implicit BEGIN WORK statement and must be closed explicitly with a COMMIT WORK or ROLLBACK WORK statement. In a non-ANSI-compliant database, Informix database servers enclose single SQL statements in a transaction with an implicit BEGIN WORK and an implicit COMMIT WORK statement. If you explicitly use the BEGIN WORK statement, you must explicitly close the transaction with the COMMIT WORK or ROLLBACK WORK statement. If an SPL procedure leaves a transaction open, the database server rolls it back before it opens or closes the database.

Only a user with DBA privileges can install these procedures. For security reasons, non-DBAs cannot prevent execution of these procedures. However, if their applications permit, users can execute commands and SQL statements that change the environment later.

You can create the following four SPL procedures, which are run as DBA.

Procedure Name
Description
user.sysdbopen()
This procedure is executed when the specified user explicitly opens the database as the current database.
public.sysdbopen()
If no user.sysdbopen() procedure applies, this procedure is executed when any user explicitly opens the database as the current database.
user.sysdbclose()
This procedure is executed when the specified user explicitly closes the database, disconnects from the database server, or the user session ends. If the sysdbclose() procedure did not exist when a session opened the database, however, it will not be executed when the session closes the database.
public.sysdbclose()
If no user.sysdbopen() procedure applies, this procedure is executed when the specified user explicitly closes the database, disconnects from the database server, or the user session ends. If the sysdbclose() procedure did not exist when a session opened the database, however, it is not executed when the session closes the database.

For example, you might create the following procedure, which sets the isolation level to dirty read and turns on the COMPUTE_QUOTA environment option, to be executed when any user connects to the database:

create procedure public.sysdbopen ()
   set isolation level to dirty read ;
   set environment compute_quota on;
   end procedure

If a sysdbopen() procedure fails, the database cannot be opened. If a sysdbclose() procedure fails, the failure is ignored. Failures from these procedures can be generated by the system or explicitly by the procedures with the SPL statement RAISE EXCEPTION.

Make sure that you set permissions appropriately to allow intended users to execute the SPL procedure statements. For example, if the SPL procedure executes a command that writes output to a local directory, permissions must be set to allow users to write to this directory. If you want the procedure to continue if permission failures occur, include an ON EXCEPTION error handler for this condition.

For general information about writing and installing SPL procedures, refer to the IBM Informix: Guide to SQL Syntax.

Warning:
While you are writing and debugging one of these procedures, set the DEBUG environment variable to NODBPROC before you connect to the database. When DEBUG is set to NODBPROC the procedure is not executed and failures cannot prevent the database from opening.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]