A question recently came up on the Ingres forums regarding the ability to query IMA to get a count and list of out bound sessions in the GCC servers. The former can be done using an existing IMA table, ima_gcc_info, like so:
execute procedure ima_set_vnode_domain; select outbound_current from ima_gcc_info;
The latter is a bit more involved as there is no IMA table registered that exposes the relevant information. Browsing ima_mib_objects using:
select distinct(instance) from ima_mib_objects where instance like '%gcc%'
gives a nice list of GCC related entries we can query:
* select distinct(instance) from ima_mib_objects where instance like '%gcc%'\g Executing . . . ┌────────────────────────────────────────────────────────────────┐ │instance │ ├────────────────────────────────────────────────────────────────┤ │exp.gcf.gcc.conn │ │exp.gcf.gcc.conn.al_flags │ │exp.gcf.gcc.conn.al_proto_lvl │ │exp.gcf.gcc.conn.flags │ │exp.gcf.gcc.conn.gca_assoc_id │ │exp.gcf.gcc.conn.inbound │ │exp.gcf.gcc.conn.lcl_addr.node │ │exp.gcf.gcc.conn.lcl_addr.port │ │exp.gcf.gcc.conn.lcl_addr.protocol │ │exp.gcf.gcc.conn.pl_flags │ │exp.gcf.gcc.conn.pl_proto_lvl │ │exp.gcf.gcc.conn.rmt_addr.node │ │exp.gcf.gcc.conn.rmt_addr.port │ │exp.gcf.gcc.conn.rmt_addr.protocol │ │exp.gcf.gcc.conn.sl_flags │ │exp.gcf.gcc.conn.sl_proto_lvl │ │exp.gcf.gcc.conn.target │ │exp.gcf.gcc.conn.tl_flags │ │exp.gcf.gcc.conn.tl_lcl_id │ │exp.gcf.gcc.conn.tl_proto_lvl │ │exp.gcf.gcc.conn.tl_rmt_id │ │exp.gcf.gcc.conn.trg_addr.node │ │exp.gcf.gcc.conn.trg_addr.port │ │exp.gcf.gcc.conn.trg_addr.protocol │ │exp.gcf.gcc.conn.userid │ │exp.gcf.gcc.conn_count │ │exp.gcf.gcc.data_in │ │exp.gcf.gcc.data_out │ │exp.gcf.gcc.ib_conn_count │ │exp.gcf.gcc.ib_encrypt_mech │ │exp.gcf.gcc.ib_encrypt_mode │ │exp.gcf.gcc.ib_max │ │exp.gcf.gcc.msgs_in │ │exp.gcf.gcc.msgs_out │ │exp.gcf.gcc.ob_conn_count │ │exp.gcf.gcc.ob_encrypt_mech │ │exp.gcf.gcc.ob_encrypt_mode │ │exp.gcf.gcc.ob_max │ │exp.gcf.gcc.pl_proto_lvl │ │exp.gcf.gcc.protocol │ │exp.gcf.gcc.protocol.addr │ │exp.gcf.gcc.protocol.host │ │exp.gcf.gcc.protocol.port │ │exp.gcf.gcc.registry │ │exp.gcf.gcc.registry.addr │ │exp.gcf.gcc.registry.host │ │exp.gcf.gcc.registry.port │ │exp.gcf.gcc.registry_mode │ │exp.gcf.gcc.trace_level │ └────────────────────────────────────────────────────────────────┘ (49 rows)
The lcl_addr and rmt_addr entries look useful so let’s create a table with the node and port sub-fields:
drop table ima_gcc_sessions;
register table ima_gcc_sessions (
net_server varchar(64) not null not default
is 'SERVER',
local_node varchar(20) not null not default
is 'exp.gcf.gcc.conn.lcl_addr.node',
local_port varchar(20) not null not default
is 'exp.gcf.gcc.conn.lcl_addr.port',
remote_node varchar(20) not null not default
is 'exp.gcf.gcc.conn.rmt_addr.node',
remote_port varchar(20) not null not default
is 'exp.gcf.gcc.conn.rmt_addr.port',
inbound varchar(20) not null not default
is 'exp.gcf.gcc.conn.inbound'
) as import from 'tables'
with dbms = IMA,
structure = sortkeyed,
key = (net_server);
So to get a list of outbound server connections we can now use the following SQL:
select * from ima_gcc_sessions where inbound='N'
Giving:
* select * from ima_gcc_sessions where inbound='N'\g Executing . . . ┌────────────────────────────────────────────────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┐ │net_server │local_node │local_port │remote_node │remote_port │inbound │ ├────────────────────────────────────────────────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┤ │esva-ubuntu::/@52970 │192.168.10.20 │50064 │10.100.10.178 │21064 │N │ │esva-ubuntu::/@52970 │192.168.10.20 │49331 │192.168.10.20 │27008 │N │ └────────────────────────────────────────────────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┘ (2 rows) continue *
Note In order to query the IMA tables for Ingres/NET related information you must execute the procedure ima_set_vnode_domain:
execute procedure ima_set_vnode_domain;
A belated happy new year / feliz año nuevo to you all
