C
Data Dictionary Views

This appendix includes the gateway data dictionary views accessible to all users of an Oracle server. Most views can be accessed by any user with SELECT privileges for DB2/400 catalog tables.

A dictionary item described with N/A in this appendix means that the item is not available for the gateway. Such items default to zero for numeric columns, or to blanks or some fixed value for (VAR)CHAR columns.

This appendix contains the following sections:

The column names listed for the following views are the column names that the Oracle Database server presents to the client and may actually differ from the underlying views in DB2/400.

ALL_CATALOG

All tables, views, synonyms, and sequences accessible to the user.

OWNER	Schema of the object
TABLE_NAME	Name of the object
TABLE_TYPE	Type of object

ALL_COL_COMMENTS

Comments on columns of accessible tables and views.

OWNER	Schema of the object
TABLE_NAME	Object name
COLUMN_NAME	Column name
COMMENTS	Comments on the column

ALL_CONSTRAINTS

Constraint definitions on accessible tables.

OWNER	Owner of the constraint definition
CONSTRAINT_NAME	Name associated with the constraint definition
CONSTRAINT_TYPE	Type of constraint definition
TABLE_NAME	Name associated with the table with the constraint definition
SEARCH_CONDITION	Text of the search condition for the table check
R_OWNER	Owner of the table used in the referential constraint
R_CONSTRAINT_NAME	Name of the unique constraint definition for the referenced table
DELETE_RULE	Delete rule for the referential constraint
STATUS	Status of the constraint
DEFERRABLE	N/A
DEFERRED	N/A
VALIDATED	N/A
GENERATED	N/A
BAD	N/A
LAST_CHANGE	N/A

ALL_IND_COLUMNS

Columns of the indexes on the accessible tables.

INDEX_OWNER	Owner of the index
INDEX_NAME	Name of the index
TABLE_OWNER	Table owner
TABLE_NAME	Table name
COLUMN_NAME	Column name
COLUMN_POSITION	Position of column within the index
COLUMN_LENGTH	Indexed length of column

ALL_INDEXES

Description of indexes on tables accessible to the user.

OWNER	Owner of the index
INDEX_NAME	Name of the index
INDEX_TYPE	Type of the Index
TABLE_OWNER	Owner of the indexed object
TABLE_NAME	Name of the indexed object
TABLE_TYPE	Type of indexed object
UNIQUENESS	Uniqueness status of the index
TABLESPACE_NAME	Name of the tablespace containing the index
INI_TRANS	N/A
MAX_TRANS	N/A
INITIAL_EXTENT	N/A
NEXT_EXTENT	N/A
MIN_EXTENTS	N/A
MAX_EXTENTS	N/A
PCT_INCREASE	N/A
PCT_THRESHOLD	N/A
INCLUDE_COLUMN	N/A
FREELISTS	N/A
FREELIST_GROUPS	N/A
PCT_FREE	N/A
LOGGING	N/A
BLEVEL	Depth of the index from its root block to its leaf blocks. A depth of one indicates the root block and the leaf block are the same.
LEAF_BLOCKS	Number of leaf blocks in the index
DISTINCT_KEYS	Number of distinct indexed values. For indexes enforcing UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table.
AVG_LEAF_BLOCKS_PER_KEY	N/A
AVG_DATA_BLOCKS_PER_KEY	N/A
CLUSTERING_FACTOR	N/A
STATUS	State of the index: VALID
NUM_ROWS	N/A
SAMPLE_SIZE	N/A
LAST_ANALYZED	N/A
DEGREE	N/A
INSTANCES	N/A
PARTITIONED	N/A
TEMPORARY	N/A
GENERATED	N/A
BUFFER_POOL	N/A

ALL_OBJECTS

Objects accessible to the user.

OWNER	Owner of the object
OBJECT_NAME	Name of object
SUBOBJECT_NAME	N/A
OBJECT_ID	Object number of the object
OBJECT_TYPE	Type of object
CREATED	N/A
LAST_DDL_TIME	N/A
TIMESTAMP	N/A
STATUS	State of the object
TEMPORARY	N/A
GENERATED	N/A

ALL_SYNONYMS

All synonyms accessible to the user.

OWNER	Owner of the synonym
SYNONYM_NAME	Name of the synonym
TABLE_OWNER	Owner of the object referenced by the synonym
TABLE_NAME	Name of the object referenced by the synonym
DB_LINK	N/A

ALL_TAB_COLUMNS

Columns of all tables, views, and clusters accessible to the user.

OWNER	Owner of the table or view
TABLE_NAME	Table or view name
COLUMN_NAME	Column name
DATA_TYPE	Datatype of the column
DATA_TYPE_MOD	N/A
DATA_TYPE_OWNER	N/A
DATA_LENGTH	Maximum length of the column in bytes
DATA_PRECISION	N/A
DATA_SCALE	Digits to the right of decimal point in a number
NULLABLE	Asks if the column allow nulls. Value is `n` if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY key.
COLUMN_ID	Sequence number of the column as created
DEFAULT_LENGTH	N/A
DATA_DEFAULT	N/A
NUM_DISTINCT	Number of distinct values in each column of the table
LOW_VALUE	Second lowest and the second highest values for tables with more than three rows. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values.
HIGH_VALUE
DENSITY	N/A
NUM_NULLS	N/A
NUM_BUCKETS	N/A
LAST_ANALYZED	N/A
SAMPLE_SIZE	N/A
CHARACTER_SET_NAME	N/A
CHAR_COL_DECL_LENGTH	N/A

ALL_TAB_COMMENTS

Comments on tables and views accessible to the user.

OWNER	Owner of the object
TABLE_NAME	Name of the object
TABLE_TYPE	Type of object
COMMENTS	Comments on the object

ALL_TABLES

Description of tables accessible to the user.

OWNER	Owner of the table
TABLE_NAME	Name of the table
TABLESPACE_NAME	Name of the tablespace containing the table
CLUSTER_NAME	N/A
IOT_NAME	N/A
PCT_FREE	N/A
PCT_USED	N/A
INI_TRANS	N/A
MAX_TRANS	N/A
INITIAL_EXTENT	N/A
NEXT_EXTENT	N/A
MIN_EXTENTS	N/A
MAX_EXTENTS	N/A
PCT_INCREASE	N/A
FREELISTS	N/A
FREELIST_GROUPS	N/A
LOGGING	N/A
BACKED_UP	N/A
NUM_ROWS	Number of rows in the table
BLOCKS	N/A
EMPTY_BLOCKS	N/A
AVG_SPACE	N/A
CHAIN_CNT	N/A
AVG_ROW_LEN	Average length of a row in the table in bytes
AVG_SPACE_FREELIST_BLOCKS	N/A
NUM_FREELIST_BLOCKS	N/A
DEGREE	N/A
INSTANCES	N/A
CACHE	N/A
TABLE_LOCK	N/A
SAMPLE_SIZE	N/A
LAST_ANALYZED	N/A
PARTITIONED	N/A
IOT_TYPE	N/A
TEMPORARY	N/A
NESTED	N/A
BUFFER_POOL	N/A

ALL_USERS

Information about all users of the database.

USERNAME	Name of the user
USER_ID	N/A
CREATED	N/A

ALL_VIEWS

Text of views accessible to the user.

OWNER	Owner of the view
VIEW_NAME	Name of the view
TEXT_LENGTH	Length of the view text
TEXT	View text. Only the first row of text is returned, even if multiple rows exist.
TYPE_TEXT_LENGTH	N/A
TYPE_TEXT	N/A
OID_TEXT_LENGTH	N/A
OID_TEXT	N/A
VIEW_TYPE_OWNER	N/A
VIEW_TYPE	N/A

COLUMN_PRIVILEGES

Grants on columns for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee.

GRANTEE	Name of the user to whom access is granted
OWNER	User name of the object's owner
TABLE_NAME	Name of the object
COLUMN_NAME	Name of the column
GRANTOR	Name of the user who performed the grant
INSERT_PRIV	Permission to insert into the column
UPDATE_PRIV	Permission to update the column
REFERENCES_PRIV	Permission to reference the column
CREATED	Timestamp for the grant

OTGREGISTER

DB2/400 Special Registers

CURRENT_USER	Primary authorization id
CURRENT_DATE	Current date
CURRENT_TIME	Current time
CURRENT_TIMESTAMP	Current timestamp
CURRENT_TIMEZONE	Current timezone
CURRENT_SERVER	Host name of the current server
"current_user"	Primary authorization id
"current_date"	Current date
"current_time"	Current time
"current_timestamp"	Current timestamp
"current_timezone"	Current timezone
"current_server"	Host name of the current server

TABLE_PRIVILEGES

Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee.

GRANTEE	Name of the user to whom access is granted
OWNER	Owner of the object
TABLE_NAME	Name of the object
GRANTOR	Name of the user who performed the grant
SELECT_PRIV	Permission to select from an object
INSERT_PRIV	Permission to insert into an object
DELETE_PRIV	Permission to delete from an object
UPDATE_PRIV	Permission to update an object
REFERENCES_PRIV	N/A
ALTER_PRIV	Permission to alter an object
INDEX_PRIV	Permission to create or drop an index on an object
CREATED	Timestamp for the grant

USER_CATALOG

Tables, views, synonyms, and sequences owned by the user.

TABLE_NAME	Name of the object
TABLE_TYPE	Type of object

USER_COL_COMMENTS

Comments on columns of user's tables and views.

TABLE_NAME	Object name
COLUMN_NAME	Column name
COMMENTS	Comments on the column

USER_CONS_COLUMNS

Information about columns in constraint definitions owned by the user.

OWNER	Owner of the constraint definition
CONSTRAINT_NAME	Name associated with the constraint definition
TABLE_NAME	Name associated with the table with the constraint definition
COLUMN_NAME	Name associated with the column specified in the constraint definition
POSITION	Original position of the column in the definition

USER_CONSTRAINTS

Constraint definitions on user's tables.

OWNER	Owner of the constraint definition
CONSTRAINT_NAME	Name associated with the constraint definition
CONSTRAINT_TYPE	Type of constraint definition
TABLE_NAME	Name associated with the table with the constraint definition
SEARCH_CONDITION	Text of the search condition for the table check
R_OWNER	Owner of the table used in the referential constraint
R_CONSTRAINT_NAME	Name of the unique constraint definition for the referenced table
DELETE_RULE	Delete rule for the referential constraint
STATUS	Status of the constraint
DEFERRABLE	N/A
DEFERRED	N/A
VALIDATED	N/A
GENERATED	N/A
BAD	N/A
LAST_CHANGE	N/A

USER_INDEXES

Description of the user's own indexes.

INDEX_NAME	Name of the index
INDEX_TYPE	Type of Index
TABLE_OWNER	Owner of the indexed object
TABLE_NAME	Name of the indexed object
TABLE_TYPE	Type of the indexed object
UNIQUENESS	Uniqueness status of the index
TABLESPACE_NAME	Name of the tablespace containing the index
INI_TRANS	N/A
MAX_TRANS	N/A
INITIAL_EXTENT	N/A
NEXT_EXTENT	N/A
MIN_EXTENTS	N/A
MAX_EXTENTS	N/A
PCT_INCREASE	N/A
PCT_THRESHOLD	N/A
INCLUDE_COLUMN	N/A
FREELISTS	N/A
FREELIST_GROUPS	N/A
PCT_FREE	N/A
LOGGING	N/A
BLEVEL	Depth of the index from its root block to its leaf blocks. A depth of one indicates the root block and the leaf block are the same.
LEAF_BLOCKS	Number of leaf blocks in the index
DISTINCT_KEYS	Number of distinct indexed values. For indexes enforcing UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table.
AVG_LEAF_BLOCKS_PER_KEY	N/A
AVG_DATA_BLOCKS_PER_KEY	N/A
CLUSTERING_FACTOR	N/A
STATUS	State of the indexes: VALID
NUM_ROWS	N/A
SAMPLE_SIZE	N/A
LAST_ANALYZED	N/A
DEGREE	N/A
INSTANCES	N/A
PARTITIONED	N/A
TEMPORARY	N/A
GENERATED	N/A
BUFFER_POOL	N/A

USER_OBJECTS

Objects owned by the user.

OBJECT_NAME	Name of object
SUBOBJECT_NAME	N/A
OBJECT_ID	Object number of the object
DATA_OBJECT_ID	N/A
OBJECT_TYPE	Type of object
CREATED	N/A
LAST_DDL_TIME	N/A
TIMESTAMP	N/A
STATUS	State of the object: VALID
TEMPORARY	N/A
GENERATED	N/A

USER_SYNONYMS

The user's private synonyms.

SYNONYM_NAME	Name of the synonym
TABLE_OWNER	Owner of the object referenced by the synonym
TABLE_NAME	Name of the object referenced by the synonym
DB_LINK	N/A

USER_TAB_COLUMNS

Columns of user's tables, views, and clusters.

TABLE_NAME	Table, view, or cluster name
COLUMN_NAME	Column name
DATA_TYPE	Datatype of the column
DATA_TYPE_MOD	N/A
DATA_TYPE_OWNER	N/A
DATA_LENGTH	Maximum length of the column in bytes
DATA_PRECISION	N/A
DATA_SCALE	Digits to the right of decimal point in a number
NULLABLE	Asks if the column allow nulls. Value is `n` if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY key.
COLUMN_ID	Sequence number of the column as created
DEFAULT_LENGTH	N/A
DATA_DEFAULT	N/A
NUM_DISTINCT	Number of distinct values in each column of the table
LOW_VALUE	Second lowest and the second highest values for tables with more than three rows. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values.
HIGH_VALUE
DENSITY	N/A
NUM_NULLS	N/A
NUM_BUCKETS	N/A
LAST_ANALYZED	N/A
SAMPLE_SIZE	N/A
CHARACTER_SET_NAME	N/A
CHAR_COL_DECL_LENGTH	N/A

USER_TAB_COMMENTS

Comments on the tables and views owned by the user.

TABLE_NAME	Name of the object
TABLE_TYPE	Type of object
COMMENTS	Comments on the object

USER_TABLES

Description of the user's own tables.

TABLE_NAME	Name of the table
TABLESPACE_NAME	Name of the tablespace containing the table
CLUSTER_NAME	N/A
IOT_NAME	N/A
PCT_FREE	N/A
PCT_USED	N/A
INI_TRANS	N/A
MAX_TRANS	N/A
INITIAL_EXTENT	N/A
NEXT_EXTENT	N/A
MIN_EXTENTS	N/A
MAX_EXTENTS	N/A
PCT_INCREASE	N/A
FREELISTS	N/A
FREELIST_GROUPS	N/A
LOGGING	N/A
BACKED_UP	N/A
NUM_ROWS	Number of rows in the table
BLOCKS	N/A
EMPTY_BLOCKS	N/A
AVG_SPACE	N/A
CHAIN_CNT	N/A
AVG_ROW_LEN	Average length of a row in the table in bytes
AVG_SPACE_FREELIST_BLOCKS	N/A
NUM_FREELIST_BLOCKS	N/A
DEGREE	N/A
INSTANCES	N/A
CACHE	N/A
TABLE_LOCK	N/A
SAMPLE_SIZE	N/A
LAST_ANALYZED	N/A
PARTITIONED	N/A
IOT_TYPE	N/A
TEMPORARY	N/A
NESTED	N/A
BUFFER_POOL	N/A

USER_USERS

Information about the current user.

USERNAME	Name of the user
USER_ID	N/A
ACCOUNT_STATUS	N/A
LOCK_DATE	N/A
EXPIRY_DATE	N/A
DEFAULT_TABLESPACE	N/A
TEMPORARY_TABLESPACE	N/A
CREATED	N/A
EXTERNAL_NAME	N/A

USER_VIEWS

Text of views owned by the user.

VIEW_NAME	Name of the view
TEXT_LENGTH	Length of the view text
TEXT	First line of view text
TYPE_TEXT_LENGTH	N/A
TYPE_TEXT	N/A
OID_TEXT_LENGTH	N/A
OID_TEXT	N/A
VIEW_TYPE_OWNER	N/A
VIEW_TYPE	N/A

C Data Dictionary Views