An Ingres Blog

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