Was this helpful?
Registration of IMA Tables
To define an SQL schema for a MIB database, IMA tables (and the IMA catalogs if they do not exist) must be created by the $ingres user in a database owned by $ingres.
The SQL statement REGISTER TABLE...AS IMPORT is used to register IMA tables.
REGISTER TABLE Statement--Register IMA Table
The REGISTER TABLE...AS IMPORT statement has the following syntax:
REGISTER TABLE tablename
(col_name format [IS 'ext_format'] {, col_name format [IS 'ext_format']})
AS IMPORT FROM 'source'
WITH DBMS = IMA
[, STRUCTURE = NONE | [UNIQUE] SORTEDKEY]
[, KEY = (col_name [ASC] {, col_name})]
[, ROWS = nnnnnnnn]
[, [NO]DUPLICATES]
[, [NO]UPDATE]
[, [NO]JOURNALING]
;
where:
tablename
(Required) Is the name of the table, which must follow the standard naming convention.
col_name
(Required) Is the name of the column, which must follow the standard naming convention.
format
(Required) Is the data type of the column.
IS 'ext_format'
Specifies the management object being referenced, by extended format string name. This can be:
Classid
A reserved name identifying metadata.
VNODE
Column is vnode places only char type.
SERVER
Column is server places only char type.
CLASSID
Column is the classid name char type.
INSTANCE
Column is the instance value char type.
VALUE
Column is the char value of the {place, classid, instance} object.
PERMISSIONS
Column is the integer management object permission mask for the classid.
AS IMPORT FROM 'source'
(Required) Indicates that the table being registered is for a gateway product. The value of source must be enclosed in single quotation marks, and is either "objects" or "tables," as described in IMA Table Types:
IMPORT FROM 'objects'
IS 'ext_format' clause values can be any of the following:
'VNODE'
'SERVER'
'CLASSID'
'INSTANCE'
'VALUE'
'PERMISSIONS'
These values are case sensitive and must be uppercase.
IMPORT FROM ‘tables’
IS 'ext_format' clause values can be any of the following:
'VNODE' or 'SERVER'
classid
These values are case sensitive: 'VNODE' or 'SERVER' must be uppercase and the classids must be in lowercase.
WITH
(Required) Introduces additional information about the table being imported.
DBMS = IMA
(Required) Indicates that the table is registered with IMA. The only possible value is IMA.
STRUCTURE =
(Required) Specifies whether the table is keyed or not. For IMA tables, the only valid structure is either:
SORTKEYED
UNIQUE SORTKEYED
KEY =
(Required) Specifies the column name(s) to use as the key.
For tables with IMPORT FROM 'objects', the key must contain a maximum of three columns from the following: VNODE or SERVER, CLASSID, INSTANCE, and the order must follow the same sequence.
For tables with IMPORT FROM 'tables', the key must contain a maximum of two columns. If two columns are specified, the first must be either VNODE or SERVER, both VNODE and SERVER cannot be specified.
ROWS = nnnnnnnn
(Optional) Specifies the approximate number of rows in the table. If omitted, a default of 1000 is used, which is used by the query optimizer as the number of rows in the table.
[NO]DUPLICATES
(Optional) Indicates whether the table can contain duplicate rows. If this parameter is omitted, duplicates will be considered.
[NO]UPDATE
(Optional) Indicates whether the table can be updated.
For tables with IMPORT FROM 'objects' can be UPDATE or NOUPDATE.
For tables with IMPORT FROM 'tables' must be NOUPDATE.
If this parameter is omitted, the default is NOUPDATE (read-only).
[NO]JOURNALING
(Optional) Indicates whether journaling is allowed. For IMA tables, only NOJOURNALING is allowed.
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 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 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.
REMOVE TABLE Statement--Remove Table Registrations
Use the SQL statement REMOVE TABLE to remove the definition of an IMA table. This statement removes all catalog entries for the registered table, including any related views, integrities, and permits.
This statement has the following syntax:
remove table tablename;
where:
tablename
Is the table name for a registered IMA table.
Last modified date: 01/30/2023