Hi, I am trying to get a list of DBMS connections using the IMA
mechanism. The problem I'm getting is that I'm running 5 DBMS servers
but the IMA script only returns the sessions for the DBMS that I'm
connected to. The (sparse) manual documentation says I need to update
ima_mib_objects to see the other DBMS servers, but I can't get it to
work. My code is below, does anyone have any ideas?

I'm running II 9.2.1 (su9.us5/103)

Thanks,

Matt.

sql imadb <<END

UPDATE ima_mib_objects
SET value = DBMSINFO('IMA_VNODE')
WHERE classid = 'exp.gwf.gwm.session.control.add_vnode'
AND instance = '0'
AND server = DBMSINFO('IMA_SERVER')
\p\g


select server,session_id,real_user,db_name
,client_host, client_user
from ima_server_sessions
where db_name<>''\g


END

Matt Charles wrote:

Start with:

execute procedure ima_set_vnode_domain \g

--
Roy

UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Register at http://www.regonline.co.uk/ukiua2011

Add "execute procedure ima_set_vnode_domain;" to the top of your script.
That expands your visible "horizon" to all the servers on the current
"vnode" (an unfortunately overloaded term which isn't really related to
an Ingres Net vnode but which refers to the current Ingres
installation).

HTH
Paul

Thanks, trying that gives me some errors (that procedure isn't
mentioned in the 9.2 manuals as far as I could see) :

execute procedure ima_set_vnode_domain
Executing . . .

E_GW8045 GWM error-- GCM completion error to 'devhost' (class '' flags
x00000000)
(Thu May 19 09:16:29 2011)

E_GC0132 User provided a vnode as part of the database name
(vnode::dbname), but connection information for that vnode is
missing. Enter
connection information for the vnode using NETUTIL.
(Thu May 19 09:16:29 2011)

E_GW8081 GWM error-- Error querying name server 'devhost::/IINMSVR'
(Thu May 19 09:16:29 2011)



Looking at that procedure it's defined as follows:

* help procedure ima_set_vnode_domain\g
Executing . . .

Procedure: ima_set_vnode_domain
Owner: $ingres
Procedure Type: native
Object type: user object
Created: 13-dec-2010 10:48:02

Procedure Definition:
/* 1 */ create procedure ima_set_vnode_domain as begin update
"$ingres". ima_mib_objects set value =dbmsinfo('ima_vnode') where
classid = 'exp.gwf.gwm.session.control.add_vnode' and instance = '0'
and server = dbmsinfo('ima_server');
/* 2 */ end

So I think it's equivalent to the original update statement stated in
the manual? Strange that this errors though.

What does dbmsinfo('ima_vnode') return? And does it match the machine
name you're running on? Are you using a fully qualified hostname
(devhost.something.com)?

I've seen similar errors before, this could be bug 125093. If so, we
found a workaround is to ensure a vnode exists for the FQDN of the local
host.


--
geraintjones

This is weird, sometimes it errors sometimes it doesn't. I just ran
it 4 times, twice it produced the errors, twice it didn't. But even
when it didn't error it still didn't produce the output from all the
servers, just the server it was connected to. (It was the same server
all 4 times).

On 19/05/2011 09:44, Ingres Forums wrote:
just hangs (won't connect and doesn't timeout or error). Destroying
the vnode allows me to connect again. The vnode itself passed the
connection test in netutil so I think it was okay.

I'm wondering if some of these problems may be due to the fact that this
is actually part of a cluster setup.

the hostname is devhost-1 but have a cluster name of devhost for the
installation and that's what II_HOSTNAME is set to. There is a
devhost-2 that is currently the inactive node (and has no attached disks
and isn't running ingres)

Something's getting confused along the way I think.

On 19/05/2011 09:42, Paul Mason wrote:
select dbmsinfo('ima_vnode')\g

Returns: devhost (which is what II_HOSTNAME is set to)
The actual hostname is devhost-1 as this is part of a cluster and
devhost-1 is actually the active node.


Ah trying this:

UPDATE ima_mib_objects
SET value = 'devhost-1'
WHERE classid = 'exp.gwf.gwm.session.control.add_vnode'
AND instance = '0'
AND server = DBMSINFO('IMA_SERVER')


Has made my session query return the rows for more than one DBMS, but
the query has now just hung and hasn't finished running.

This is now all working. It turned out that one of the 4 DBMS servers
had hung in the installation (clocking up lots of CPU time), restarting
the installation and performing the following query to set my IMA Vnode
and it now works fine:

UPDATE ima_mib_objects
SET value = 'devhost-1' /* current host name, not cluster name */
WHERE classid = 'exp.gwf.gwm.session.control.add_vnode'
AND instance = '0'
AND server = DBMSINFO('IMA_SERVER')\g

select server,session_id,real_user,db_name
,client_host, client_user
from ima_server_sessions
where db_name<>''\g

It's a little slow as I suppose it has to connect to each server in turn
to get the information, but at least it works now.

Thanks for the input everyone.