Using sysbdopen( ) and sysdbclose( ) Stored Procedures (XPS)

To set the initial environment for one or more sessions, create and install the sysdbopen( ) SPL procedure. The primary function of these procedures is to initialize properties of a session without requiring the properties to be explicitly defined within the session. These procedures are executed whenever users connect to a database where the procedures are installed. Such procedures are useful if users access databases through client applications that cannot modify application code or set environment options or environment variables.

You can also create the sysdbclose( ) SPL procedure which is executed when a user disconnects from the database.

You can include valid SQL or SPL language statements that are appropriate when a database is opened or closed. See the following sections for restrictions on SQL and SPL statements within SPL routines:

• Subset of SPL Statements Valid in the Statement Block.
• SQL Statements Not Valid in an SPL Statement Block.
• Restrictions on SPL Routines in Data-Manipulation Statements.

For example, you might create the following procedure, which sets the isolation level to Dirty Read and turns on the IMPLICIT_PDQ environment variable, to be executed when any user connect to the database:

create procedure public.sysdbopen()
   set role engineer;
end procedure;

Procedures do not accept arguments or return values. The sysdbopen( ) and sysdbclose( ) procedures must be executed from the connection coserver and must be installed in each database where you want to execute them. You can create the following four SPL procedures.

Procedure Name

Description

user.sysdbopen( )

This procedure is executed when the specified user opens the database as the current database.

public.sysdbopen( )

If no user.sysdbopen( ) procedure applies, this procedure is executed when any user opens the database as the current database. To avoid duplicating SPL code, you can call this procedure from a user-specific procedure.

user.sysdbclose( )

This procedure is executed when the specified user 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.

public.sysdbclose( )

If no user.sysdbopen( ) procedure applies, this procedure is executed when the specified user 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.

See also the section Transactions in SPL Routines.

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.

See also the section Support for Roles and User Identity.

Only a user with DBA privileges can install these procedures. For security reasons, non-DBAs cannot prevent execution of these procedures. For some applications, however, such as ad hoc query applications, users can execute commands and SQL statements that subsequently change the environment.

For general information about how to write and install SPL procedures, refer to the chapter about SPL routines in IBM Informix Guide to SQL: Tutorial.

Related Information