Informix - get DB Server Name / Alias for connected Session

50 views Asked by At

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.

0

There are 0 answers