Ramblings from the bit bucket

This has been a long running pun within Ingres development. The Ingres Management Architecture (IMA) is an intrinsic part of the processes that compose the Ingres DBMS, yet no one claims responsibility for it.

For the uninitiated, IMA is a powerful mechanism where the values from elements of a programming structure within an Ingres process can be exposed as attributes in a table, giving a direct snapshot of Ingres. This makes customizing applications for monitoring and maintenance easier, for example, Visual DBA uses this mechanism to obtain its information.

The weakness of IMA is the lack of documentation and the reason? There are over 1700 parameters, maybe more, that cover many aspects of the internal control structures. The meaning of each of the values and their inter-relationship is usually locked within the mind of the person who decided to exposed it. The strength of IMA is the ability to extract these values as queries into the DBMS. This means that DBMS values can be exposed via any client technology that can issue queries.

There have been a few attempts to start the documentation of each parameter yet the projects have been stifled by the sheer volume of information and knowledge that is required.

Some attempts have been made to define a standard schema with the assistance of thrid party vendors and partners whilst maintaining some degree of client confidentiality. Inevitably, with each new version of Ingres some subtle change in the control structure reduces one or more of these monitoring queries to return zero rows.

I shall leave the vagaries of registering IMA tables for someone else or to a later post (much later).

For the inquisitive, this sample query returns a list of the sessions that are using an Ingres lock, those that are waiting and the one that is granted together with the query text. I’ve only tried it on Ingres 2006. Hope you find it useful, usual disclaimers apply.
select
a.server, a.session_id, db_name, _bintim() - session_time as duration,
session_cpu as cpu, session_query, session_lquery, resource_id, lock_state from
ima_server_sessions a, ima_server_sessions_extra b,
ima_server_sessions_desc e, ima_locklists c, ima_locks d, ima_server_sessions_query q
where
a.session_id = q.session_id and
resource_id in
(select
b.resource_id
from
ima_locklists a, ima_locks b
where
a.locklist_wait_id = b.lock_id)
and c.locklist_id = d.locklist_id
and a.session_id = c.locklist_session_id
and a.session_id = b.session_id
and a.session_id = e.session_id \p\g

In order to see the last query a table is added to include a field that is included in Ingres but is not yet exposed. The session_lquery field provides the text of the previously executed query for the session.
/*
** Add ima_server_sessions_query table.
**
** This script should be run as $ingres.
** e.g.
** sql -u"\$ingres" imadb
**
** Add session_lquery. For imadb created with 4k pages the field could
** fit in the ima_server_sessions table.
*/
drop table ima_server_sessions_query;
\p\g
register table ima_server_sessions_query(
server varchar(64) not null not default is
'SERVER',
session_id varchar(32) not null not default is
'exp.scf.scs.scb_index',
session_lquery varchar(1000) not null not default is
'exp.scf.scs.scb_lastquery' )
as import from 'tables' with dbms = IMA,
structure = unique sortkeyed,
key = (server, session_id)
\p\g
grant select on ima_server_sessions_query to public with grant option;
\p\g


[tags]ima, ingres, management[/tags]