10. Understanding Ingres Management Architecture : MIB Creation : Registration of IMA Tables : IMA Table Types
 
Share this page                  
IMA Table Types
There are two types of IMA tables:
Flat tables
Cross-tab tables
Flat Tables
Flat tables:
Have one object instance per row
Can be updated
Have the clause: IMPORT FROM 'objects'
An example of a flat table registration is:
REGISTER TABLE ima_mib_objects (
     server        VARCHAR(64)  NOT NULL NOT DEFAULT IS 'SERVER',
     classid       VARCHAR (64) NOT NULL NOT DEFAULT IS 'CLASSID',
     instance      VARCHAR (64) NOT NULL NOT DEFAULT IS 'INSTANCE',
     value         VARCHAR(64)  NOT NULL NOT DEFAULT IS 'VALUE',
     perms         INTEGER2     NOT NULL NOT DEFAULT IS 'PERMISSIONS'
)
AS IMPORT FROM 'objects'
WITH UPDATE,
DBMS = IMA,
STRUCTURE = UNIQUE SORTKEYED,
KEY = (server, classid, instance)
;
Sample output from ima_mib_objects table may look like this:
SELECT server, classid, instance, value
FROM   ima_mib_objects;
server
classid
instance
value
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_id
00000000001
00000000032
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_id
00000000002
00000000020
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_id
00000000003
00000000003
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_id
00000000004
00000000031
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_id
00000000005
00000000030
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_id
00000000006
00000000010
. . .
 
 
 
Another 32175 rows (approx)
 
 
The above SQL by default will give only the available IMA classid values from the DBMS server to which the SQL session is currently connected.
To see all the IMA classid values available in the VNODE (that is, all Ingres servers running in that domain), it is necessary to extend the domain (see page Management Domains).
Cross-tab Tables
Cross-tab tables:
Have the clause: IMPORT FROM 'tables'
The columns in the table registration can be in any order
A place column is optional. If present, it is identified by having an "IS ext_format" of value VNODE or SERVER. If omitted the place defaults to the current server.
All other columns must have an "IS ext_format" of classid.
There are objects that describe IMA; these objects can be seen by registering a cross-tab table as follows:
REGISTER TABLE ima_mo_meta
(
     server       VARCHAR(64) NOT NULL NOT DEFAULT IS 'SERVER',
     classid      VARCHAR(64) NOT NULL NOT DEFAULT IS 'exp.glf.mo.meta.classid',
     oid          VARCHAR(8) NOT NULL NOT DEFAULT IS 'exp.glf.mo.meta.oid',
     perms        INTEGER2 NOT NULL NOT DEFAULT IS 'exp.glf.mo.meta.perms',
     size         INTEGER2    NOT NULL NOT DEFAULT IS 'exp.glf.mo.meta.size',
     xindex       VARCHAR(64) NOT NULL NOT DEFAULT IS 'exp.glf.mo.meta.index'
)
AS IMPORT FROM 'tables'
WITH DBMS = IMA,
STRUCTURE = UNIQUE SORTKEYED,
KEY = (server, classid);
Note:  The table and column names do not have to be as shown; the table name ima_mo_meta is used for convenience only.
Selecting from ima_mo_meta as created above will by default only give the objects for the DBMS to which the SQL session is currently connected.
To see all the IMA objects available in the VNODE, it is necessary to extend the domain (see page Management Domains).
Sample output from ima_mo_meta table may look like this:
SELECT server, classid, xindex
FROM   ima_mo_meta;
server
classid
xindex
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_id
exp.adf.adg.dt_ix
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_ix
exp.adf.adg.dt_ix
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_name
exp.adf.adg.dt_ix
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.dt_stat
exp.adf.adg.dt_ix
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.fi_dtarg1
exp.adf.adg.fi_ix
GRFR6::/@II\\INGRES\\db0
exp.adf.adg.fi_dtarg2
exp.adf.adg.fi_ix
. . .
 
 
Another 1150 rows (approx.)
 
 
Another example of a cross-tab table registration is:
UNIX:
REGISTER TABLE ima_dbms_servers (
server               VARCHAR(64) NOT NULL NOT DEFAULT IS
                     'SERVER',
listen_address       VARCHAR(64) NOT NULL NOT DEFAULT IS
                     'exp.gcf.gca.client.listen_address',
max_connections      INTEGER4 NOT NULL NOT DEFAULT IS
                     'exp.scf.scd.server.max_connections',
num_connections      INTEGER4 NOT NULL NOT DEFAULT IS
                     'exp.scf.scd.server.current_connections’,
server_pid           INTEGER4 NOT NULL NOT DEFAULT IS
                     'exp.clf.unix.cs.srv_block.pid'
)
AS IMPORT FROM 'tables'
WITH DBMS = IMA,
STRUCTURE = SORTKEYED,
KEY = (server);
Windows:
REGISTER TABLE ima_dbms_servers (
server                   VARCHAR(64) NOT NULL NOT DEFAULT IS
                         'SERVER',
listen_address           VARCHAR(64) NOT NULL NOT DEFAULT IS
                         'exp.gcf.gca.client.listen_address',
max_connections          INTEGER4 NOT NULL NOT DEFAULT IS
                         'exp.scf.scd.server.max_connections',
num_connections          INTEGER4 NOT NULL NOT DEFAULT IS
                         'exp.scf.scd.server.current_connections',
server_pid               INTEGER4 NOT NULL NOT DEFAULT IS
                         'exp.clf.nt.cs.srv_block.pid'
)
AS IMPORT FROM 'tables'
WITH DBMS = IMA,
STRUCTURE = SORTKEYED,
KEY = (server);
Check to see that the classids belong either to the same “xindex” or to no index. If the classids come from a mix of xindexes, probably no rows will be returned.
SELECT
classid,
xindex
FROM
ima_mo_meta
WHERE
classid in
(
     'exp.gcf.gca.client.listen_address',
     'exp.scf.scd.server.max_connections',
     'exp.scf.scd.server.current_connections',
     'exp.clf.nt.cs.srv_block.pid'
)
classid
xindex
exp clf.nt.cs.srv_block.pdf
 
exp.gcf.gca.client.listen_address
exp.gcf.gca.client
exp.scf.scd.server.current_connections
 
exp.scf.scd.server.max_connections
 
Check to see that the classids exist in the MIB server domain. If a table is registered with invalid classids, no error message is generated (at both registration and run time). If an invalid classid is used, no rows are returned from the registered table.
This can be demonstrated from the above SQL, if the REGISTER TABLE with classid ‘exp.clf.nt.cs.srv_block.pid' is registered on an installation on UNIX or Linux and a SELECT statement run against the table, no rows are returned.
The same applies if the 'exp.clf.unix.cs.srv_block.pid' classid is registered against an installation on Windows.
Executing the following SQL:
SELECT *
FROM ima_dbms_servers
on an installation on Windows where the domain has not been extended, gives:
Server
Listen address
Max connections
Num connections
Server PID
GRFR6::/@II\\INGRES\\fe0
II\\INGRES\\fe0
500
2
4064
In the Ingres home directory under (depending on the version of Ingres) ingres/bin or ingres/vdba, there are scripts that are used to create the database objects (such as tables, views, and database procedures) within the IMADB database. These are:
makimau.sql (for UNIX)
makiman.sql (for Windows)
makimav.sql (for VMS)
There are also two example applications in the SIG directory, in the directories
IMA (An IMA-based example program)
IMP (An IMA-based IPM application)
Both of these applications come with SQL scripts to create IMA-based tables.