Configure session properties at connection or access time

You can use a sysdbopen( ) procedure to change the properties of a database server session at connection or access time without changing the application that the session runs. This is useful if you cannot modify the source code of an application to set environment options or session variables, or to include session-related SQL statements, for example, because the SQL statements contain vendor-acquired code.

To change the properties of a session, design custom sysdbopen( ) and sysdbclose( ) procedures for various databases to support the applications of specific users or of the PUBLIC group. The sysdbopen( ) and sysdbclose( ) procedures can contain a sequence of SET, SET ENVIRONMENT, SQL, or SPL statements that the database server executes for the user or for the PUBLIC group when the database opens or closes.

For example, for user1, you can define procedures that contain SET PDQPRIORITY, SET ISOLATION LEVEL, SET LOCK MODE, SET ROLE, or SET EXPLAIN ON statements that execute whenever user1 opens the database with a DATABASE or CONNECT TO statement.

Any settings of the session environment variables PDQPRIORITY and OPTCOMPIND that are specified by SET ENVIRONMENT statements within sysdbopen( ) procedures persist for the duration of the session. SET PDQPRIORITY and SET ENVIRONMENT OPTCOMPIND statements, which are not persistent for regular procedures, are persistent when sysdbopen( ) procedures contain them.

The user.sysdbclose( ) procedure runs when the user who is the owner of the procedure disconnects from the database (or else when PUBLIC.sysdbclose( ) runs, if it exists and no sysdbclose( ) procedure is owned by the current user).

In custom sysdbopen( ) and sysdbclose( ) procedures, HCL OneDB™ does not ignore the name of the owner of a UDR when a routine is invoked in a database that is not ANSI-compliant.