Configuring session properties

Only a DBA or user informix can create or alter sysdbopen( ) or sysdbclose( ) in the ALTER PROCEDURE, ALTER ROUTINE, CREATE PROCEDURE, CREATE PROCEDURE FROM, CREATE ROUTINE FROM, DROP PROCEDURE, or DROP ROUTINE statements of SQL.

You can set up sysdbopen( ) procedures that change the properties of a 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 environment variables or to include session-related SQL statements, for example, because the SQL statements contain vendor-acquired code.

Follow these steps to set up a sysdbopen() and sysdbclose() procedure to configure session properties:

1. Set the IFX_NODBPROC environment variable to any value, including 0, to cause the database server to bypass and prevent the execution of the sysdbopen( ) or sysdbclose( ) procedure.
2. Write the CREATE PROCEDURE or CREATE PROCEDURE FROM statement to define the procedure for a particular user or the PUBLIC group.
3. Test the procedure, for example, by using sysdbclose( ) in an EXECUTE PROCEDURE statement.
4. Unset the IFX_NODBPROC environment variable to enable the database server to run the sysdbopen( ) or sysdbclose( ) procedure.

CREATE PROCEDURE oltp_user.sysdbopen()
	    SET ROLE TO oltp;
	    SET PDQPRIORITY 5;
	    SET ENVIRONMENT NOVALIDATE '1';
END PROCEDURE;

	CREATE PROCEDURE PUBLIC.sysdbopen()
	   SET ROLE TO others;
	   SET PDQPRIORITY 1;
	   SET ENVIRONMENT 
        RETAINUPDATELOCKS 'CURSOR STABILITY';
	END PROCEDURE