The sysdbopen procedure is executed when an explicit connection is made to the database and is often used to set the environment, e.g. SET LOCK MODE TO WAIT 30, or to record information on the connection (user, connection time, etc) in a logging table, or to authorize the incoming connection.
A danger of using sysdbopen is that a failure in the procedure will prevent the database connection from succeeding, which could also make it impossible to fix the failure or modify the sysdbopen procedure. For that reason, we often create a “public” sysdbopen procedure that contains the connection logging and environment setting and an empty sysdbopen owned by the informix user that simply returns with no additional logic or SQL that could potentially fail. The “informix” owned procedure is executed when informix connects instead of the “public” procedure, allowing the informix user to still access the database and make corrections when needed. The downside to this approach is that we can no longer automatically set the environment for the informix user or log informix connections – a potential auditing concern.
To get around this restriction we have two options – ignore errors in the function and/or use an environment variable to bypass execution of sysdbopen.
Ignore Errors
Adding an “ON EXCEPTION” clause at the beginning of the sysdbopen procedure allows the procedure to exit cleanly should any error occur and allows the connection to the database to succeed. For example:
CREATE PROCEDURE public.sysdbopen()
ON EXCEPTION
RETURN;
END EXCEPTION;
— Environment and logging code goes here …
SET LOCK MODE TO WAIT 30;
END PROCEDURE;
If an error does occur, then any unexecuted statements will not run, and that may include connection logging or checking that the user is authorized.
Skip sysdbopen
We can prevent the execution of sysdbopen (and sysdbclose) by setting the environment variable IFX_NODBPROC, for example:
export IFX_NODBPROC=1
This will bypass the sysdbopen procedure, giving emergency access for the informix user to connect to the database and make required changes.
The value of this environment variable does not matter – if it is set to anything then the sysdbopen procedure will be skipped. As soon as the problems have been corrected, the environment variable needs to be unset to allow sysdbopen to be run automatically again, e.g.
unset IFX_NODBPROC
Adding the “on exception” to sysdbopen should be considered best practice, making the procedure more resilient when errors occur, although it could potentially mean that authorization or logging performed by sysdbopen will be skipped.
Using IFX_NODBPROC gives us an emergency bypass when the procedure is preventing connections.
From Mike Walker (mike@xdbsystems.com)