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;
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;
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'
)
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:
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.