Abstract
There is no built-in way to view the history of connections to an IBM Informix Dynamic Server (IDS) database once they have terminated. Doing so can be the key to solving questions such as where a user is located or who has been performing particular SQL actions, whether it be for auditing, application improvement, or user training.
This article outlines a simple solution that can be implemented to append connection details to an external text file as a permanent record, and goes further to describe how that can then be maintained and accessed with SQL.
Content
From IDS 11.10 onwards, stored procedures named “sysdbopen” are executed automatically when a user connects to a the database containing it, as documented here. There is a special interpretation of the owner of such a procedure: it’s invoked only for that user, except for “public” which applies to all who don’t have their own.
This makes it possible for us to save details to a file immediately after successful connection, using the following code which must be executed in each database to be monitored:
CREATE PROCEDURE public.sysdbopen() -- Log session details on database connection -- Doug Lawry, Oninit Consulting, August 2020 DEFINE session, process INTEGER; DEFINE dbname, client, program VARCHAR(255); -- Continue calling session after any error ON EXCEPTION RETURN; END EXCEPTION; -- Ignore high frequency or system users IF USER IN ('no_exceptions_for now') THEN RETURN; END IF LET session = DBINFO('sessionid'); LET dbname = TRIM(DBINFO('dbname')); SELECT pid, TRIM(hostname), TRIM(feprogram) INTO process, client, program FROM sysmaster:syssessions WHERE sid = session; SYSTEM 'echo ' || '"' || CURRENT :: DATETIME YEAR TO SECOND || '|' || session || '|' || dbname || '|' || USER || '|' || client || '|' || process || '|' || program || '|"' || ' >> $INFORMIXDIR/logs/connection.log';END PROCEDURE;- The ON EXCEPTION block ensures that the calling session is unaffected if anything goes wrong.
- A placeholder is provided to insert a list of user’s names to be excluded as necessary.
- The DBINFO function and “syssessions” system view provide the details we want to record.
- The “program” value is likely to be empty for APIs compiled with Client SDK earlier than 3.70.
- Change the output location as preferred, or create a symbolic link pointing elsewhere.
- If separate logs per database are preferred, include “dbname” in the output file name.
Make sure the file exists and is accessible and writable for all users. For example, shell commands to create a connection log in the same location as the IDS message log are:
DIR=$(dirname $(onstat -c MSGPATH))LOG=connection.logcd $INFORMIXDIRln -s $DIR logscd logstouch $LOGchmod 666 $LOG
You will need to repeat the above to create empty writable log files on HDR/RSS/SDS replica servers. The “sysdbopen” procedure will already exist once created on the primary and will try to record connections to each replica instance in its local file system. An advantage of the solution in this article is that it will work just fine with UPDATABLE_SECONDARY 0 in the IDS configuration, as the data is not being written to a standard table.
Opening the database with “dbaccess” as user “informix” in Docker container “ifx” appended this line:
2020-10-09 15:22:30|65|stores_demo|informix|ifx|1607|/opt/ibm/IDS/14.10.FC4W1/bin/dbaccess|
Note that the process ID will be “-1” if not applicable (typically Java) and the “program” field can be very long. For example, connecting with AGS Server Studio running on IP address 172.17.0.1 appended this line:
2020-10-09 15:28:39|66|stores_demo|informix|172.17.0.1|-1|/C:/Users/Doug/AGS/common/drivers/informix/jdbc/jdbc.4.10.jc1/ifxjdbc.jarjava.awt/EventDispatchThread|
You can create an external table to make the log file accessible in SQL, perhaps in the “sysadmin” database:
CREATE EXTERNAL TABLE connection_log( connected DATETIME YEAR TO SECOND, sid INTEGER, -- Informix session ID dbname VARCHAR(128), -- Database name contaning sysdbopen() username VARCHAR(32), -- User name in connection details client VARCHAR(255), -- IP address or host name if resolved pid INTEGER, -- Process ID on client machine program VARCHAR(255) -- Full program path on client machine)USING( DATAFILES ( 'DISK:/opt/ibm/informix/logs/connection.log' ), ESCAPE OFF -- Windows program paths contain backslashes!);REVOKE ALL ON connection_log FROM PUBLIC;
Example output follows:
$ dbaccess -e sysadmin connections.sql 2>/dev/nullSELECT FIRST 2 * FROM connection_log;connected 2020-10-09 15:22:30sid 65dbname stores_demousername informixclient ifxpid 1607program /opt/ibm/IDS/14.10.FC4W1/bin/dbaccessconnected 2020-10-09 15:28:39sid 66dbname stores_demousername informixclient 172.17.0.1pid -1program /C:/Users/Doug/AGS/common/drivers/informix/jdbc/ jdbc.4.10.jc1/ifxjdbc.jarjava.awt/EventDispatchThread
For long term monitoring, you may need to cycle round a set of files to limit disk space usage. A complete solution using Linux “logrotate” follows:
$ cat logrotate.config/opt/ibm/informix/logs/connection.log { rotate 9 weekly create 666 informix informix}$ cat logrotate.shcd $(dirname $(which $0))logrotate -s logrotate.status logrotate.config$ crontab -l | grep logrotate0 0 * * * . ./.profile ; $INFORMIXDIR/logs/logrotate.sh
That configuration keeps 9 previous files per week plus the current one. Resulting files might be:
$ ls -otr connection.*-rw-rw-rw-. 1 informix 38811164 Aug 16 00:00 connection.log.9-rw-rw-rw-. 1 informix 112669233 Aug 23 00:00 connection.log.8-rw-rw-rw-. 1 informix 113626513 Aug 30 00:00 connection.log.7-rw-rw-rw-. 1 informix 113222376 Sep 6 00:00 connection.log.6-rw-rw-rw-. 1 informix 115349172 Sep 13 00:00 connection.log.5-rw-rw-rw-. 1 informix 123526199 Sep 20 00:00 connection.log.4-rw-rw-rw-. 1 informix 131300768 Sep 27 00:00 connection.log.3-rw-rw-rw-. 1 informix 120945157 Oct 4 00:00 connection.log.2-rw-rw-rw-. 1 informix 102190765 Oct 11 00:00 connection.log.1-rw-rw-rw-. 1 informix 53568616 Oct 14 10:47 connection.log- The above is based on an actual production system.
- The external table would need to include all 10 files in the DATAFILES clause to cover the whole history.
Once you have an external table, it can be combined with the SQLTRACE facility described here to give important information otherwise missing regarding SQL statements that may be of interest, but for which there is no longer any entry in “syssessions” if already disconnected. The main system pseudo-table “syssqltrace” contains columns listed here and doesn’t include the following which we do have in our connection log:
- User name
- Client
- PID
- Program
Note that “syssqltrace” only contains an integer user ID column, so you would have to look up the user name from /etc/passwd which will only work for local operating system accounts: the user ID is that of the “mapped user” when relevant as described here and does not distinguish between multiple names mapped to it. Our log has the more meaningful actual user name specified in the connection process.
Our external table adds the missing data. For example, the following lists recent failed SQL statements:
SELECT con.*, DBINFO('utc_to_datetime',sql_finishtime) AS sql_finishtime, sql_sqlerror, sql_isamerror, TRIM(sql_statement) AS sql_statementFROM sysmaster:sysshmvalsJOIN sysadmin:connection_log AS con ON connected > DBINFO('utc_to_datetime',sh_boottime)JOIN sysmaster:syssqltrace ON sql_sid = sid AND sql_sqlerror < 0;- TRIM is essential to reduce the data returned with “sql_statement” being CHAR(16000).
- Only connections since Informix was started are included to avoid duplicate session IDs.
Example output follows:
connected 2020-10-14 18:18:27sid 61dbname stores_demousername informixclient ifxpid 16217program /opt/ibm/IDS/14.10.FC4W1/bin/dbaccesssql_finishtime 2020-10-14 18:18:43sql_sqlerror -255sql_isamerror 0sql_statement ROLLBACKConclusion
Full details of clients connecting to a database can easily be captured for subsequent analysis, without requiring any changes to application code, with no impact on database storage, and no measurable effect on server load.
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
