Customizing an application environment using the connect procedure
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.
Recommendations for connect procedure
- 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
- 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.
- Define an SQL procedure NEWTON.CONNECTPROC to set a special register
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.
- Set up and invoke a procedure for new connections in order to
customize their initial special register values.