|
|
The formats of the Standard Catalog Interface for Ingres Star are described in detail in tables in the following sections. All database users can read the Standard Catalog Interface, but users should never update the catalogs.
|
|
For each alternate key, any columns defined as part of the key have an entry in iialt_columns.
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The table to which column_name belongs |
table_owner |
char(32) |
The table owner |
key_id |
integer |
The number of the alternate key for this table |
column_name |
char(32) |
The name of the column |
key_sequence |
smallint |
Sequence of column within the key, numbered from 1 |
|
|
For each querytable object in the iitables catalog, there are one or more entries in the iicolumns catalog. Each row in iicolumns contains the logical information on a column of the query object. The iicolumns catalog is used by user interfaces and user programs to perform dictionary operations and dynamic queries.
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The name of the table. Must be a valid name. |
table_owner |
char(32) |
The owner of the table. Must be a valid username. |
column_name |
char(32) |
The column's name. Must be a valid name. |
column_datatype |
char(32) |
The column's data type name returned to users and applications:
|
column_length |
integer |
The length of the column returned to users and applications. If a data type contains two length specifiers, this column uses the first length. Set to zero for the data types which are specified without length (money and date). This length is not the actual length of the column's internal storage. |
column_scale |
smallint |
The second number in a two-part user length specification; for typename (len1, len2) it will be len2. |
column_nulls |
char(8) |
Y if the column can contain null values, N if not. |
column_defaults |
char(8) |
Y if the column is given a default value when a row is inserted. N if not. |
column_sequence |
smallint |
The number of this column in the corresponding table's create statement, numbered from 1. |
key_sequence |
smallint |
The order of this column in the primary key, numbered from 1. For a table, this indicates the column's order in the primary storage structure key. If 0, then this column is not part of the primary key. This is unique if the unique_rule column for the table's corresponding entry in iitables is set to U. |
Sort_direction |
char(8) |
Defaults to A for ascending when key_sequence is greater than 0. Otherwise, this value is a blank. |
Column_ingdatatype |
smallint |
Contains a value that indicates the data type of the column. If the value is positive then the column is not nullable; if the value is negative, then the column is nullable. The data types and their corresponding values are:
|
|
|
The iidbcapabilities catalog contains information about the capabilities provided by the DBMS. The following table describes the columns in the iidbcapabilities catalog:
Column Name |
Data Type |
Description |
|---|---|---|
cap_capability |
char(32) |
Contains one of the values listed in the Capability column of the following table. If the cap_capability has a value, it will be activated by the value in the cap_value column. |
Cap_value |
char(32) |
Set to the value of the capability. This is usually the string Y or N. See the Values column in the following table for possible values of each capability. |
The cap_capability column in the iidbcapabilities catalog contains one or more of the following values:
Capability |
Value |
|---|---|
OPEN/SQL_LEVEL |
Version of OpenSQL supported by the DBMS. Use this name in preference to the older COMMON/SQL_LEVEL. Default setting is 00605. |
COMMON/SQL_LEVEL |
Version of OPEN/SQL support provided by the DBMS. Maintained for backward compatibility. (Use OPEN/SQL_LEVEL instead.) Examples:
|
DB_NAME_CASE |
Case mapping semantics of the database with respect to regular identifiers for database objects: LOWER for lowercase is the Ingres setting. |
DB_DELIMITED_CASE |
Case mapping semantics of the database with respect to delimited identifiers for database objects: LOWER for lowercase is the Ingres setting. MIXED for mixed case is set for an ISO Entry SQL92 compliant installation. If the value is MIXED, an identifier must be enclosed in double quotes to maintain case as originally defined. Otherwise it is treated as a regular identifier (converted to uppercase). |
DB_REAL_USER_CASE |
Case mapping of user names as retrieved by the operating system: LOWER for lowercase is the Ingres setting. MIXED for mixed case or UPPER for uppercase is set as specified during installation. |
DBMS_TYPE |
The type of DBMS the application is communicating with. For a Star Server, the value is always STAR. |
DISTRIBUTED |
Y since the DBMS is distributed. |
INGRES |
Set to Y if the DBMS supports in all respects 100 percent of Ingres Release 6. Otherwise N. For Ingres Star this is set to N since it does not support QUEL. |
INGRES/SQL_LEVEL |
Version of SQL supported by the DBMS. These include:
|
INGRES/QUEL_LEVEL |
Version of QUEL supported by the DBMS. These include:
|
OWNER_NAME |
Contains N if schema.table table name format is not supported. Contains Y if schema.table format is supported; contains QUOTED if schema.table is supported with optional quotes ("schema".table). The default is QUOTED. |
PHYSICAL_SOURCE |
T indicates that both iitables and iiphysical_tables contain physical table information. P indicates that only iiphysical_tables contains the physical table information. |
SAVEPOINTS |
Y if savepoints behave exactly as in Ingres, else N. Default is Y. |
STANDARD_CATALOG _LEVEL |
Version of the standard catalog interface supported by this database. Should be 00602 (the default) for Ingres Star. |
UNIQUE_KEY_REQ |
Set to Y if the database service requires that some or all tables have a unique key. Set to N or not present if the database service allows tables without unique keys. |
|
|
The iidbconstants catalog contains values required by the Ingres tools. The following table describes the columns in the iidbconstants catalog:
Column Name |
Data Type |
Description |
|---|---|---|
user_name |
varchar(32) |
The name of the current user. |
dba_name |
varchar(32) |
The name of the db's owner. |
system_owner |
varchar(32) |
The name of the catalog owner (for example, $ingres). |
|
|
The iihistograms table contains histogram information used by the optimizer:
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The table for the histogram. Must be a valid name. |
table_owner |
char(32) |
The table owner. Must be a valid name. |
column_name |
char(32) |
The name of the column. Must be a valid name. |
text_sequence |
Integer |
The sequence number for the histogram, numbered from 1. There may be several rows in this table, used to order the optdata data when histogram is read into memory. |
text_segment |
char(228) |
The encoded histogram data, created by optimizedb. |
|
|
For indexes, any columns that are defined as part of the primary index key will have an entry in iiindex_columns. For a full list of all columns in the index, use the iicolumns catalog.
Column Name |
Data Type |
Description |
index_name |
char(32) |
The index containing column_name. Must be a valid name. |
index_owner |
char(32) |
The index owner. Must be a valid username. |
column_name |
char(32) |
The name of the column. Must be a valid name. |
key_sequence |
smallint |
Sequence of column within the key, numbered from 1. |
sort_direction |
char(8) |
Defaults to A for ascending. |
|
|
Each table with a table_type of I in the iitables table has an entry in iiindexes. All indexes also have an entry in iiphysical_tables.
Column Name |
Data Type |
Description |
|---|---|---|
index_name |
char(32) |
The index name. Must be a valid name. |
index_owner |
char(32) |
The index owner. Must be a valid username. |
create_date |
char(25) |
Creation date of index. This is a date field. |
base_name |
char(32) |
The base table name. Must be a valid name. |
base_owner |
char(32) |
The base table owner. Must be a valid username. |
storage_structure |
char(16) |
The storage structure for the index: heap, hash, isam, or B-tree. Set to blank if unknown. |
is_compressed |
char(8) |
Y if the table is stored in compressed format, N if the table is uncompressed, blank if unknown. |
unique_rule |
char(8) |
U if the index is unique, D if duplicate key values are allowed, or blank if unknown. |
|
|
Iiintegrities contains one or more entries for each integrity defined on a table. Because the text of the integrity definition can contain more than 240 characters, iiintegrities may contain more than one row for a single integrity. The text may contain newlines and may be broken mid-word across rows.
Note: Ingres Star does not support integrities, so there are no rows in this catalog.
This table is keyed on table_name and table_owner:
Column Name |
Data Type |
Description |
table_name |
char(32) |
The table name. Must be a valid name. |
table_owner |
char(32) |
The table owner. Must be a valid name. |
create_date |
char(25) |
The integrity's creation date. This is a date field. |
integrity_number |
smallint |
The number of this integrity. |
text_sequence |
smallint |
The sequence number for the text, numbered from 1. |
text_segment |
varchar (240) |
The text of the integrity definition. |
|
|
For tables located on multiple volumes, this table contains an entry for each additional location on which a table resides. The first location for a table can be found in the iitables catalog.
Note: Ingres Star does not currently populate this table.
This table is keyed on table_name and table_owner:
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The table name. Must be a valid name. |
table_owner |
char(32) |
The table's owner. Must be a valid username. |
sequence |
integer |
The sequence of this location in the list of locations, as specified in the modify statement. This is numbered from 1. |
location_name |
char(32) |
The name of the location. |
|
|
The iipermits catalog contains one or more entries for each permit defined. Because the permit definition can contain more than 240 characters, iipermits can contain more than one row for a single permit. The text may contain newlines and may be broken mid-word across rows.
Note: Ingres Star does not currently support permits, so there are no rows in this catalog.
This table is keyed on object_name and object_owner:
Column Name |
Data Type |
Description |
|---|---|---|
object_name |
char(32) |
The table or procedure name. Must be a valid name. |
object_owner |
char(32) |
The owner of the table or procedure. Must be a valid name. |
object_type |
char(8) |
The type of the object: T for a table or view; P for a database procedure. |
create_date |
char(25) |
The permit's creation date. This is a date field. |
permit_user |
char(32) |
The username to which this permit applies. |
permit_number |
smallint |
The number of this permit. |
text_sequence |
smallint |
The sequence number for the text, numbered from 1. |
text-segment |
varchar (240) |
The text of the permission definition. |
|
|
The information in the iiphysical_tables catalog overlaps with some of the information in iitables. You can query the physical_source column in iidbcapabilities to determine whether you must query iiphysical_tables. If you do not want to make this check, then you must always query iiphysical_tables to be sure of getting the correct information.
If a queryable object is type T (table), then it is a physical table and may have an entry in iiphysical_tables as well as iitables.
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The table name. This is an name. |
table_owner |
char(32) |
The table owner's username. |
table_stats |
char(8) |
Y if this object has entries in the iistats table, N if it does not. If blank, it is undetermined if the object has entries in iistats and you should check iistats directly. |
table_indexes |
char(8) |
Y if this object has entries in the iiindexes table that refer to this as a base table, N if not. If blank, it is undetermined if the object has entries in the iiindexes table that refer to it as a base table, and you should check iiindexes directly. This field is only used for optimization for Ingres databases, as other Enterprise Access products cannot automatically supply this information. |
is_readonly |
char(8) |
Y if updates are physically allowed on this object, N if not. The field is blank if this is unknown. This is used for tables that are defined to the Enterprise Access for retrieval. If this field is set to Y, updates will not be allowed regardless of what permissions might be set for the table. |
num_rows |
integer |
The estimated number of rows in the table. Set to -1 if unknown. |
storage_structure |
char(16) |
The storage structure of the table. Possible values are: heap, B-tree, isam, or hash. Set to blank if the structure is unknown. |
is_compressed |
char(8) |
Indicates if the table is stored in compressed format. Y if it is compressed, N if not compressed, blank if unknown. |
duplicate_rows |
char(8) |
Indicates if duplicate rows are allowed in the table. Set to U if rows must be unique, D if duplicates are allowed, or blank if unknown. |
unique_rule |
char(8) |
Indicates if the storage structure key is unique. Set to U if the storage structure is unique, D if duplicates are allowed, blank if unknown or inapplicable. |
number_pages |
integer |
The estimated number of physical pages in the table. Set to -1 if unknown. |
overflow_pages |
integer |
The estimated number of overflow pages in the table. Set to -1 if unknown. |
row_width |
integer |
The size (in bytes) of the uncompressed binary value for a row in the object for Ingres. Set to -1 if this is unknown. |
|
|
The iiprocedures catalog contains one or more entries for each procedure defined. Because the procedure definition can contain more than 240 characters, iiprocedures can contain more than one row for a single procedure. The text may contain newlines and may be broken mid-word across rows. The text segment contains the procedure registration text, not the actual procedure definition text.
This table is keyed on procedure_name and procedure_owner:
Column Name |
Data Type |
Description |
|---|---|---|
procedure_name |
char(32) |
The table or procedure name. Must be a valid name. |
procedure_owner |
char(32) |
The owner of the table or procedure. Must be a valid name. |
create_date |
char(25) |
The permit's creation date. This is a date field. |
proc_subtype |
varchar(1) |
The type of procedure, which is one of the following values:
|
text_sequence |
integer |
The sequence number for the text, numbered from 1. |
text-segment |
varchar (240) |
The text of the procedure definition. |
|
|
The iiregistered_objects catalog is an Ingres Star-only catalog. It resides in all distributed databases, but is not available in local databases. This catalog ties registered objects that Ingres Star may acquire when the user registers a table to the underlying objects in the local database. The registered objects are tables, views, secondary indexes, and procedures.
Column Name |
Data Type |
Description |
|---|---|---|
ddb_object_name |
char(32) |
The name of the Ingres Star-registered object. Must be a valid name. |
ddb_object_owner |
char(32) |
The name of the owner of the Ingres Star-registered object. Must be a valid username. |
register_date |
char(25) |
The date the object was registered. This is a date field. |
ldb_database |
char(32) |
The name of the local database in which the registered object resides. |
ldb_node |
char(32) |
The node on which the ldb_database resides. |
ldb_dbmstype |
char(32) |
The type of the ldb_database. These are the same types used by iinamu (INGRES, RMS, DB2, RDB, and so on). |
ldb_object_name |
char(32) |
The name that the local database uses for the registered object. |
ldb_object_owner |
char(32) |
The name of the owner of the registered object in the local database. |
ldb_object_type |
char(8) |
The type of local object. The values are T if the object is a table, V if it is a view, I if the object is an index, or P if the object is a procedure. |
|
|
The iiregistrations catalog contains the text of register statements.
Column Name |
Data Type |
Description |
|---|---|---|
object_name |
char(32) |
The name of the registered table, view, or index. |
object_owner |
char(32) |
The name of the owner of the table, view, or index. |
object_dml |
char(8) |
The language used in the registration statement. S for SQL. |
object_type |
char(8) |
Describes the object type of object_name. The values are T if the object is a table, V if it is a view, I if the object is an index, or P if the object is a registered procedure. |
object_subtype |
char(8) |
Describes the type of table or view created by the register statement. For Ingres Star, this will be L for a link. |
text_sequence |
integer |
The sequence number of the text field, numbered from 1. |
Text_segment |
varchar(240) |
The text of the register statement. |
|
|
The iistats catalog contains the following information.
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The name of the table. Must be a valid name. |
table_owner |
char(32) |
The table owner. Must be a valid username. |
column_name |
char(32) |
The column name to which the statistics apply. Must be a valid name. |
create_date |
char(25) |
The date on which statistics were gathered. This is a date field. |
num_unique |
float4 |
The number of unique values in the column. |
rept_factor |
float4 |
The repetition factor, or the inverse of the number of unique values (number of rows/ number of unique values). |
has_unique |
char(8) |
Y if the column has unique values, N otherwise. |
pct_nulls |
float4 |
The percentage (fraction of 1.0) of the table which contains NULL for the column. |
num_cells |
smallint |
The number of cells in the histogram. |
column_domain |
smallint |
A user-specified number signifying the domain from which the column draws its values; default is 0. |
is_complete |
char(8) |
Y if the column contains all possible values in the domain, N otherwise. |
stat_version |
char(8) |
The version of the statistics for this column. |
hist_data_length |
smallint |
The length of the histogram boundary values, either the user-specified length or optimizedb's computed length. |
|
|
The iitables catalog contains an entry for each queryable object in the database (table, view, or index). To find out which tables, views, and indexes are owned by you or the DBA, you can query this catalog; for example:
select * from iitables
where (table_owner = user
or table_owner = dba())
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The object's name. Must be a valid name. |
table_owner |
char(32) |
The object's owner. Must be a valid username. Generally, the creator of the object is the owner. |
create_date |
char(25) |
The object's creation date. Blank if unknown. This is a date field. |
alter_date |
char(25) |
The last time this table was altered. This date is updated whenever the logical structure of the table changes, either through changes to the columns in the table or changes to the primary key. Physical changes to the table, such as changes to data, secondary indexes, or physical keys, do not change this date. Blank if unknown. This is a date field. |
table_type |
char(8) |
Type of the query object:
|
table_subtype |
char(8) |
Specifies the type of table or view. Possible values are:
|
table_version |
char(8) |
Version of the object; enables the tools to determine where additional information about this particular object is stored. This reflects the database type, as well as the version of an object within a given database. For tables, the value for this field is ING6.0. |
system_use |
char(8) |
Contains S if the object is a system object, U if user object, or blank if unknown. Used by utilities to determine which tables need reloading. If the value is unknown, the utilities will use the naming convention of ii for tables in order to distinguish between system and user catalogs. Also, any table beginning with ii_ is assumed to be a tool object, rather than a DBMS system object. The system catalogs themselves must be included in the iitables catalog and are considered system tables. |
The following information may also be present in iiphysical_tables but not present in this catalog:
Column Name |
Data Type |
Description |
|---|---|---|
table_stats |
char(8) |
Y if this object has entries in the iistats table, N if this object does not have entries. If this field is blank, then you must query iistats to determine if statistics exist. This column is used only for optimization of databases. |
table_indexes |
char(8) |
Y if this object has entries in the iiindexes table that refer to this as a base table, or N if this object does not have entries. If the field is blank, then you must query iiindexes on the base_table column. This field is used only for optimization of databases. |
is_readonly |
char(8) |
N if updates are physically allowed, Y if no updates are allowed, or blank if unknown. Used for tables that are defined to the Enterprise Access only for retrieval, such as tables in hierarchical database systems. If this field is set to Y then no updates will work, independent of what permissions might be set. If it is set to N, updates may be allowed, depending on whether the permissions allow it or not. |
num_rows |
integer |
The estimated number of rows in the table. Set to -1 if unknown. |
storage_structure |
char(16) |
The storage structure for the table: heap, hash, B-tree, or isam. Blank if the table structure is unknown. |
is_compressed |
char(8) |
Y if the table is stored in compressed format, N if the table is uncompressed, blank if unknown. |
duplicate_rows |
char(8) |
D if the table allows duplicate rows, U if the table does not allow duplicate rows, blank if unknown. The table storage structure (unique vs. non-unique keys) can override this setting. |
unique_rule |
char(8) |
The value may be U (unique key), D (duplicate key) or blank if unknown or does not apply. D indicates that duplicate physical storage structure keys are allowed. (A unique alternate key may exist in iialt_columns and any storage structure keys may be listed in iicolumns.) U: If the object is an Ingres object, indicates that the object has a unique storage structure key(s); if the object is not an Ingres object, then it indicates that the object has a unique key, described in either iicolumns or iialt_columns. |
number_pages |
integer |
The estimated number of physical pages in the table. Set to -1 if unknown. |
overflow_pages |
integer |
The estimated number of overflow pages in the table. Set to -1 if unknown. |
row_width |
integer |
The size, in bytes, of the uncompressed binary value for a row of this query object. |
The information in the following table is not duplicated in iiphysical_tables:
Column Name |
Data Type |
Description |
|---|---|---|
expire_date |
integer |
Expiration date of table. This is a _bintime date. |
modify_date |
char(25) |
The date on which the last physical modification to the storage structure of the table occurred. Blank if unknown or inapplicable. This is a date field. |
location_name |
char(32) |
The first location of the table. If there are additional locations for a table, they will be shown in the iimulti_locations table and multi_locations will be set to Y. |
table_integrities |
char(8) |
Y if this object has Ingres-style integrities. If the value is blank, you must query the iiintegrities table to determine if integrities exist. |
table_permits |
char(8) |
Y if this object has Ingres-style permissions. |
all_to_all |
char(8) |
Y if this object has permit all to all, N if not. |
ret_to_all |
char(8) |
Y if this object has permit retrieve to all, N if not. |
is_journalled |
char(8) |
Y if journaling is enabled on this object, N if not. Set to C if journaling will be enabled at the next checkpoint. This will be blank if journaling does not apply. |
view_base |
char(8) |
Y if object is a base for a view definition, N if not, or blank if unknown. |
multi_locations |
char(8) |
Y if the table is in multiple locations, N if not. |
table_ifillpct |
smallint |
Fill factor for the index pages used on the last modify statement in the nonleaffill clause, expressed as a percentage (0 to 100). Used for |
table_dfillpct |
smallint |
Fill factor for the data pages used on the last modify statement in the fillfactor clause, expressed as a percentage (0 to 100). Used for |
table_lfillpct |
smallint |
Fill factor for the leaf pages used on the last modify statement in the leaffill clause, expressed as a percentage (0 to 100). Used for |
table_minpages |
integer |
Minpages parameter from the last execution of the modify statement. Used for hash structures only. |
table_maxpages |
integer |
Maxpages parameter from the last execution of the modify statement. Used for hash structures only. |
table_relstamp1 |
integer |
High part of last create or modify timestamp for the table. |
table_relstamp2 |
integer |
Low part of last create or modify timestamp for the table. |
table_reltid |
integer |
The first part of the internal relation ID. |
table_reltidx |
integer |
The second part of the internal relation ID. |
|
|
The iiviews catalog contains one or more entries for each view in the database. (Views are indicated in iitables by table type = V.) Because the text_segment column is limited to 256 characters per row, a single view can require more than one row to contain all its text; in this case, the text will be broken in mid-word across the sequenced rows. The text column is text and may contain newline characters.
Column Name |
Data Type |
Description |
|---|---|---|
table_name |
char(32) |
The view name. Must be a valid Ingres name. |
table_owner |
char(32) |
The view owner's Ingres username. |
view_dml |
char(8) |
The language in which the view was created: S (for SQL). |
check_option |
char(8) |
Y if the check option was specified in the create view statement, or N if not. Set to blank if unknown. |
text_sequence |
integer |
The sequence number for the text field, starting with 1. |
text_segment |
varchar(256) |
The text of the view definition. |