11. OpenSQL Standard Catalogs : Standard Catalog Interface : The iitables Catalog
 
Share this page                  
The iitables Catalog
The iitables catalog contains an entry for each queryable object in the database (table, view, or index). To find out what 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 = (select dba_name from iidbconstants))
Column Name
Data Type
Description
table_name
char(256)
The object's name. Must be a valid object name.
table_owner
char(32)
The owner's user name. Generally, the creator of the object is the owner.
create_date
char(25)
The object's creation date. Blank if unknown.
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.
table_type
char(1)
Type of 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(1)
Specifies the type of table or view. Possible values are:
N (native) - For standard Ingres databases.
L (links) - For Ingres Star.
I (imported tables) - For Enterprise Access products.
(blank) - If unknown
table_version
char(5)
Version of the object. Enables the user interfaces 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 Ingres tables, the value for this field is II2.5.
system_use
char(1)
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 use the naming convention of “ii” for tables to distinguish between system and user catalogs. In addition, any table beginning with ii_ is assumed to be a user interface object, rather than a DBMS system object. The standard system catalogs themselves must be included in the iitables catalog and are considered system tables.
tups_per_page
integer
Maximum tuples per data page.
keys_per_page
integer
Maximum keys per index page for ISAM and BTREE tables.
keys_per_leaf
integer
Maximum keys per leaf for BTREE tables.
The following columns in iitables have values only if table_type is T (table) or I (index).
Enterprise Access products that do not supply this information set these columns to -1 for numeric data types, blank for character data types.
Column Name
Data Type
Description
table_stats
char(1)
Contains Y if this object has entries in the iistats table, N if this object does not have entries. If this field is blank, then query iistats to determine if statistics exist.
This column is used for optimization of Ingres databases.
table_indexes
char(1)
Contains 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 query iiindexes on the base_table column.
This field is used for optimization of Ingres databases.
is_readonly
char(1)
Contains one of these values:
N - If updates are physically allowed
Y - If no updates are allowed
Blank - If unknown
Used for tables that are defined to the Enterprise Access product 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.
concurrent_access
char(1)
Y if concurrent access is allowed.
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, btree, or isam. Blank if unknown.
is_compressed
char(1)
Contains Y if the table is stored in compressed format, N if the table is uncompressed, blank if unknown.
key_is_compressed
char(1)
Contains Y if the table uses key compression, N if no key compression, or blank if unknown.
duplicate_rows
char(1)
D - If the table allows duplicate rows.
U - If the table does not allow duplicate rows.
Blank - If unknown.
The table storage structure (unique or non-unique keys) can override this setting.
unique_rule
char(1)
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 unique storage structure key. 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.
Blank - If uniqueness is unknown or does not apply.
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.
phys_partitions
smallint
For a partitioned table, this is the number of physical partitions. For a physical partition, this is the partition number.
partition_dimensions
smallint
For a partitioned table, this is the number of dimensions (partitioning levels) in the table's partitioning scheme. In all other cases, this is zero.
row_width
integer
The size, in bytes, of the uncompressed binary value for a row of this query object. For encrypted tables, the width of the encrypted row.
unique_scope
char(8)
R if this object is row-level, S if statement-level, blank if not applicable.
allocation_size
integer
The allocation size, in pages. Set to -1 if unknown.
extend_size
integer
The extend size, in pages. Set to -1 if unknown.
allocated_pages
integer
Total number of pages allocated to the table.
label_granularity
char(1)
Empty string.
This column is deprecated.
row_security_audit
char(1)
Y if row-level security auditing is enabled, N if not.
security_label
char(8)
Empty string.
This column is deprecated.
table_pagesize
integer
Page size of a table.
The following columns are used by the Ingres DBMS Server. If Enterprise Access does not supply this information, it will set these columns to the default values: -1 for numeric columns and a blank for character columns. The information in the following section is not duplicated in iiphysical_tables.
Column Name
Data Type
Description
expire_date
integer
Expiration date of table. This is an Ingres _bintim 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.
location_name
char(24)
The first location of the table. If there are additional locations for a table, they are shown in the iimulti_locations table and multi_locations is set to Y.
table_integrities
char(8)
Contains Y if this object has Ingres style integrities. If the value is blank, query the iiintegrities table to determine if integrities exist.
table_permits
char(8)
Contains Y if this object has Ingres style permissions.
all_to_all
char(8)
Contains Y if this object has Ingres permit all to all, N if not.
ret_to_all
char(8)
Contains Y if this object has Ingres permit retrieve to all, N if not.
is_journalled
char(8)
Contains Y if Ingres journaling is enabled on this object, N if not.
view_base
char(8)
Contains Y if object is a base for a view definition, N if not, or blank if unknown.
multi_locations
char(8)
Contains 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 command in the nonleaffill clause, expressed as a percentage (0 to 100).
Used for Ingres btree structures to rerun the last modify command.
table_dfillpct
smallint
Fill factor for the data pages used on the last modify command in the fillfactor clause, expressed as a percentage (0 to 100).
Used for Ingres btree, hash, and isam structures to rerun the last modify command.
table_lfillpct
smallint
Fill factor for the leaf pages used on the last modify command in the leaffill clause, expressed as a percentage (0 to 100).
Used for Ingres btree structures to rerun the last modify command.
table_minpages
integer
Minpages parameter from the last execution of the modify command.
Used for Ingres hash structures only.
table_maxpages
integer
Maxpages parameter from the last execution of the modify command.
Used for Ingres 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.
table_relversion
integer
Stores the version of table.
table_reltotwid
integer
The width of the table, including all deleted columns. For encrypted tables, the width of the encrypted row.
table_reltcpri
integer
Indicates a table's priority in the buffer cache. Values can be between 0 and 8. Zero is the default, and 1-8 can be specified using the priority clause in create table or modify table.
table_reldatawid
integer
The size in bytes of the uncompressed binary value for a row of this query object (for encrypted tables, the width of the decrypted row).
table_reltotdatawid
integer
The width of the table, including all deleted columns (for encrypted tables, the width of the decrypted row).
encrypted_columns
char(1)
Y if the table contains encrypted columns, N if not.
encryption_version
smallint
Internal version number of column encryption for the table (0 if there is no encryption).
encryption_type
varchar
Column encryption type: NONE, AES128, AES192, or AES256.