Previous Topic

Catalogs

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.

Previous Topic

The iialt_columns Catalog

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

Previous Topic

The iicolumns Catalog

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:

  • integer
  • smallint
  • int
  • float
  • real
  • double precision
  • char
  • character
  • varchar
  • c
  • text
  • date
  • money
  • decimal
  • user data types (UDTs)

    For details on UDTs, see the Object Management Extension User Guide.

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:

  • integer -30/30
  • float -31/31
  • c -32/32
  • text - 37/37
  • date -3/3
  • money -5/5
  • char -20/20
  • varchar -21/21
  • decimal -10/10

Previous Topic

The iidbcapabilities Catalog

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:

  • 00600 Version 6.0
  • 00601 Version 6.1
  • 00602 Version 6.2

    Default is 00600.

DB_NAME_CASE

Case mapping semantics of the database with respect to regular identifiers for database objects:

LOWER for lowercase is the Ingres setting.
UPPER for uppercase is set for an ISO Entry SQL92 compliant installation.

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:

  • 00600 Version 6.0
  • 00601 Version 6.1
  • 00602 Version 6.2
  • 00603 Version 6.3
  • 00604 Version 6.4
  • 00605 Ingres
  • 00000 DBMS does not support SQL

    Default is 00600.

INGRES/QUEL_LEVEL

Version of QUEL supported by the DBMS. These include:

  • 00600 Version 6.0
  • 00601 Version 6.1
  • 00602 Version 6.2
  • 00603 Version 6.3
  • 00604 Version 6.4
  • 00605 Ingres
  • 00000 DBMS does not support QUEL

    Default is 00600.

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.

Previous Topic

The iidbconstants Catalog

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

Previous Topic

The iihistograms Catalog

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.

Previous Topic

The iiindex_columns Catalog

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.

Previous Topic

The iiindexes Catalog

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.

Previous Topic

The iiintegrities Catalog

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.

Previous Topic

The iimulti_locations Catalog

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.

Previous Topic

The iipermits Catalog

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.

Previous Topic

The iiphysical_tables Catalog

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.

Previous Topic

The iiprocedures Catalog

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:

  • N (native) = the database supports standard Ingres database procedures
  • I (import) = for Enterprise Access products, the database supports host DBMS procedures
  • E (external) = for Enterprise Access products, the database supports procedures external to the database
  • L (link) = a Ingres Star registered procedure

text_sequence

integer

The sequence number for the text, numbered from 1.

text-segment

varchar (240)

The text of the procedure definition.

Previous Topic

The iiregistered_ objects Catalog

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.

Previous Topic

The iiregistrations Catalog

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.

Previous Topic

The iistats Catalog

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.

Previous Topic

The iitables Catalog

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:

  • T table
  • V view
  • I index

    Further information about tables can be found in iiphysical_tables; further information about views can be found in iiviews.

table_subtype

char(8)

Specifies the type of table or view. Possible values are:

  • N (native) for Ingres Star-level table (created by create table or create view statement issued from Ingres Star)
  • L (links) for Ingres Star
  • " " (blank) if unknown

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
B-tree structures in order to rerun the last modify statement.

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
B-tree, hash, and isam structures in order to rerun the last modify statement.

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
B-tree structures in order to rerun the last modify statement.

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.

Previous Topic

The iiviews Catalog

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.


© 2007 Ingres Corporation. All rights reserved.