DB2 10.5 for Linux, UNIX, and Windows

Customizing an application environment using the connect procedure

The connect procedure provides you a way to allow applications in your environment to implicitly execute a specific procedure upon connection. This procedure can allow you to customize an application environment to a database from a central point of control. For example, in the connect procedure you can set special registers such as CURRENT_PATH to non-default values by invoking the SET CURRENT PATH statement. This new CURRENT_PATH value will now be the effective default CURRENT_PATH for all applications.

Any procedure created in the database that conforms to the naming and parameter restrictions can be used as the connect procedure for that database. The customization logic is provided by you in the form of a procedure created in the same database and is allowed to do any of the usual actions of a procedure such as issue SQL statements.

The connect_proc database configuration parameter specifies the connect procedure to be used for all connections to the database. Update the connect_proc parameter to set the name of the connect procedure and enable it. A database connection is required to update a non-zero length value of the connect_proc parameter. After the connect_proc parameter is set, the session authorization ID of any new connection must have EXECUTE privilege on the specified connect procedure either directly or indirectly through one of its associated groups, roles, or PUBLIC.

The connect procedure is implicitly executed on the server at the end of successful connection processing and before processing any subsequent requests on the same connection. After the connect procedure runs successfully, the database manager commits any changes made by the connect procedure. If the connect procedure fails, any changes made by the connect procedure are rolled back and the connection attempt fails with an error.

Note: Any changes made to a special register in the connect procedure are reflected in the resulting session even after the procedure finishes.

Important: Any error returned by the connection procedure will fail an attempted connection. The error returned by execution of the connect procedure is returned to the application. If you want to modify the connect procedure and fix the error, you must unset the connect_proc parameter to allow connections to succeed until the problem is fixed.

Recommendations for connect procedure

To avoid problems with your connect procedure, ensure that your connect procedure complies with the following recommendations:

Keep the connect procedure logic simple.
Using a connect procedure affects the performance of CONNECT commands for every connection by introducing additional processing. The performance impact can be significant if the procedure is inefficient or experiences delays such as lock contention.

Ensure that the procedure is well tested before establishing it as a connect procedure.
Avoid accessing objects in the connect procedure that will be dropped or altered.
If a dependent object in the connect procedure is dropped or privileges to access dependent objects are revoked, the connect procedure might fail. An error returned from the procedure can block new connections to the database from being established based on the logic of your procedure.
Avoid calling another procedure from the connect procedure.
Procedures called by the connect procedure can be dropped or altered, unlike the connect procedure itself. If procedures called by the connect procedure are invalidated or dropped, the connect procedure might fail. An error returned from the connect procedure can block new connections to the database from being established based on the logic of your procedure. Also, note that special registers changed in procedures that are called from the connect procedure do not change the special registers of the calling environment (as opposed to special registers changed in the connect procedure itself which do take effect in the application).
Avoid specifying the COMMIT ON RETURN clause in the connect procedure.
An internal commit is processed after the implicit call of connect procedure. If the clause COMMIT ON RETURN YES is specified, the database manager processes multiple commit calls that can affect performance. Specifying COMMIT ON RETURN NO has no effect on connect procedure processing.
Free all resources and close all cursors before exiting the connect procedure.
Applications cannot access any resources left open (such as WITH HOLD cursors) by the connect procedure. The resources held by the connect procedure after the commit is processed can be freed only when the application finishes.
Grant EXECUTE privilege to PUBLIC for the connect procedure.
A connect procedure is not intended to control database access. Access control is done by granting database authorities to users.
Avoid using different values for the connect_proc parameter for different database partitions.
Using different connect procedures for various database partitions can produce inconsistent application behavior depending on the database partition to which users are connected to. It also makes the database environment more complex and difficult to manage.

Usage notes for connect procedure

Connect procedure has the following restrictions and limitations:

You cannot create a procedure with the same name as a connect procedure while the connect_proc parameter is set.
Only a procedure with exactly zero parameters can be used as a connect procedure. No other procedure sharing the same two-part name can exist in the database as long as the connect_proc parameter is set.
The connect procedure name (both schema and procedure name) can only contain the following characters:
- A-Z
- a-z
- _ (underscore)
- 0-9
In addition, the schema and procedure name need to follow the rules of an ordinary identifier.
You cannot drop or alter the connect procedure while the connect_proc parameter is set.
To alter or drop the connect procedure, change the connect_proc parameter to null or the name of a different procedure.
A connect procedure cannot use client information fields set by the sqleseti API or the SQLSetConnectAttr CLI functions.
The special register for these fields contains their default server value before the connect procedure runs. The client information fields or SQL special register set by calling the sqleseti API, SQLSetConnectAttr CLI function, or SQLSetEnvAttr CLI function (for example, CLIENT USERID, CLIENT ACCTNG, CLIENT APPLNAME, and CLIENT WRKSTNNAME) are not yet updated when the connect procedure runs.
The client information fields or SQL special register set by calling the sqleseti API, the SQLSetConnectAttr CLI function, or the SQLSetEnvAttr CLI function, IBM® Data Server Driver for JDBC and SQLJ method set ClientAccountingInformation take precedence and override the special register values set in the connect procedure.
Only special registers that are set directly by the connect procedure will remain set after returning from the connect procedure. The nested routine calls within the connect procedure do not change the settings of the special registers in the calling environment.

Examples of implementing connect procedure

The following examples show you some samples of connect procedure and how the connect procedure is enabled in the database:

Example 1

Define an SQL procedure NEWTON.CONNECTPROC to set a special register based on SESSION_USER.

CREATE PROCEDURE NEWTON.CONNECTPROC ( )
READS SQL DATA
LANGUAGE SQL 
   BEGIN 

   --set the special register based on session user id
      CASE SESSION_USER 
        WHEN 'USERA' THEN
         SET CURRENT LOCALE LC_TIME 'fr_FR';

        WHEN 'USERB' THEN
         SET CURRENT LOCALE LC_TIME 'de_DE';
   ELSE
         SET CURRENT LOCALE LC_TIME 'au_AU';

   END CASE;

   END %

This procedure establishes a setting for the CURRENT LOCALE LC_TIME special register with special case values for users USERA and USERB.

Grant EXECUTE privilege on the connect procedure to the group PUBLIC:
```
GRANT EXECUTE ON PROCEDURE NEWTON.CONNECTPROC TO PUBLIC
```
.
Update the connect_proc parameter to indicate that this new procedure is to be invoked for any new connection:
```
db2 update db cfg using connect_proc "NEWTON.CONNECTPROC"
```
The NEWTON.CONNECTPROC connect procedure is now automatically invoked for any subsequent CONNECT request for a new connection. The special register CURRENT LOCALE LC_TIME is set based on SESSION USER.

Example 2

Set up and invoke a procedure for new connections in order to customize their initial special register values.
```
   CREATE PROCEDURE MYSCHEMA.CONNECTPROC
     (   )
     EXTERNAL NAME 'parts!connectproc'
     DBINFO
     READS SQL DATA
     LANGUAGE C
     PARAMETER STYLE SQL
```
This procedure reads from a database table, MYSCHEMA.CONNECTDEFAULTS, to determine what values to set in the CURRENT SCHEMA, CURRENT PATH, and CURRENT QUERY OPTIMIZATION special registers based on the groups associated with the authorization ID of the new connection. It also sets the value of the global variable, MYSCHEMA.SECURITY_SETTING, based on information in the same table.
Grant EXECUTE privilege on the connect procedure to the group PUBLIC:
```
GRANT EXECUTE ON PROCEDURE MYSCHEMA.CONNECTPROC TO PUBLIC
```
.
Update the connect_proc parameter to indicate that this new procedure is to be invoked for any new connection:
```
db2 update db cfg using connect_proc "MYSCHEMA.CONNECTPROC"
```
The MYSCHEMA.CONNECTPROC connect procedure is now automatically invoked for any subsequent CONNECT request for a new connection.