I'm running an Informix Dynamic Server Version 11.50 on AIX with multiple DB Server Aliases listening on different tcp ports.
This is basically what I have in my sqlhosts file:
# dbservername nettype hostname servicename options
# DBSERVERNAME
infx_1 onsoctcp host port1
# DBSERVERALIASES
infx_2 onsoctcp host port2
infx_3 onsoctcp host port3
I want to log connections to the database, so I'm going to use the procedure below which I found here
This gets me most of the information I want, but it doesn't tell me whether the DB Server Name, or one of the DB Server Aliases was used when connecting.
Is there any way to get that so I can add it to the log?
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:informix.syssessions
WHERE sid = session;
SYSTEM
'echo ' || '"' ||
CURRENT :: DATETIME YEAR TO SECOND || '|' ||
session || '|' ||
dbname || '|' ||
USER || '|' ||
client || '|' ||
process || '|' ||
program || '|"' ||
' >> $INFORMIXDIR/logs/connection.log';
END PROCEDURE;
I tried using DBSERVERNAME/SITENAME, but it always gives the DB Server Name, even if the connection was to one of the SB Server Aliases.