MIB Creation

The IMA's SQL MIB is the binding of the MIB object space into the SQL name space. Tables containing MIB objects can be registered into any database owned by the user "$ingres". The binding is done by IMA, which converts between rows and individual MIB objects.

Registration of IMA Tables

To define an SQL schema for a MIB database, IMA tables must be created by the $ingres user in a database owned by $ingres, as must the IMA catalogs, if they do not already exist.

The SQL statement REGISTER TABLE...AS IMPORT is used to register IMA tables.

Register Table Statement

The REGISTER TABLE...AS IMPORT statement has the following syntax:

(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 'object', 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 object 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:

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 will by default only give the available IMA classid values from the DBMS server to which the SQL session is currently connected.

To see all the IMA class id values available in the VNODE (that is, all Ingres servers running in that domain), it is necessary to extend the domain (see the section on extending the domain).

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:

(

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 name of the table and columns 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.

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:

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:

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 class ids belong either to the same "xindex" or to no index. If the classid come from a mix of xindexes, the most probable outcome is that 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 class ids exist in the MIB server domain. If a table is registered with invalid class ids, no error message is generated (this applies to both registration and run time). If an invalid class id 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 a UNIX or Linux based installation of Ingres 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 a windows based installation of Ingres.

Executing the following SQL:

SELECT *

FROM ima_dbms_servers

on an Ingres 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 Ingres uses 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)
makimag.sql (generic )

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.

Removing 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.