C Data Dictionary Views

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

If a dictionary item is described with "N/A" in this appendix, then that 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:

ALL_CATALOG
ALL_COL_COMMENTS
ALL_CONS_COLUMNS
ALL_CONSTRAINTS
ALL_IND_COLUMNS
ALL_INDEXES
ALL_OBJECTS
ALL_SYNONYMS
ALL_TAB_COLUMNS
ALL_TAB_COMMENTS
ALL_TABLES
ALL_USERS
ALL_VIEWS
COLUMN_PRIVILEGES
DICTIONARY
DUAL
OTGREGISTER
TABLE_PRIVILEGES
USER_CATALOG
USER_COL_COMMENTS
USER_CONS_COLUMNS
USER_CONSTRAINTS
USER_INDEXES
USER_OBJECTS
USER_SYNONYMS
USER_TAB_COLUMNS
USER_TAB_COMMENTS
USER_TABLES
USER_USERS
USER_VIEWS

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

ALL_CATALOG

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

Column Name	Description
`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.

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

ALL_CONS_COLUMNS

Information about columns in constraint definitions for all users. Similar to USER_CONS_COLUMNS.

Column Name	Description
`OWNER`	Schema of the object
`CONSTRAINT_NAME`	Name associated with the constraint definition
`TABLE_NAME`	Object name
`COLUMN_NAME`	Column name
`POSITION`	Original position of the column in the definition

ALL_CONSTRAINTS

Constraint definitions on accessible tables.

Column Name	Description
`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
`RELY`	N/A
`LAST_CHANGE`	N/A
`INDEX_OWNER`	N/A
`INDEX_NAME`	N/A
`INVALID`	N/A
`VIEW_RELATED`	N/A

ALL_IND_COLUMNS

Columns of the indexes on the accessible tables.

Column Name	Description
`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
`CHAR_LENGTH`	N/A
`DESCEND`	ASC for Ascending key, DESC for Descending key

ALL_INDEXES

Description of indexes on tables that are accessible to the user.

Column Name	Description
`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
`COMPRESSION`	N/A
`PREFIX_LENGTH`	N/A
`TABLESPACE_NAME`	Name of the table space 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 blocksA depth of one indicates that 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 valuesFor 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
`SECONDARY`	N/A
`BUFFER_POOL`	N/A
`USER_STATS`	N/A
`DURATION`	N/A
`PCT_DIRECT_ACCESS`	N/A
`ITYP_OWNER`	N/A
`ITYP_NAME`	N/A
`PARAMETERS`	N/A
`GLOBAL_STATS`	N/A
`DOMIDX_STATUS`	N/A
`DOMIDX_OPSTATUS`	N/A
`FUNCIDX_STATUS`	N/A
`JOIN_INDEX`	N/A
`IOT_REDUNDANT_PKEY`	N/A
`DROPPED`	N/A

ALL_OBJECTS

Objects that are accessible to the user.

Column Name	Description
`OWNER`	Owner of the object
`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
`TEMPORARY`	N/A
`GENERATED`	N/A
`SECONDARY`	N/A

ALL_SYNONYMS

All synonyms that are accessible to the user.

Column Name	Description
`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 that are accessible to the user.

Column Name	Description
`OWNER`	Owner of the table or view
`TABLE_NAME`	Table or view name
`COLUMN_NAME`	Column name
`DATA_TYPE`	Data type 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 null valuesValue 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 value 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`	Second highest value 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.
`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
`GLOBAL_STATS`	N/A
`USER_STATS`	N/A
`AVG_COL_LEN`	N/A
`CHAR_LENGTH`	N/A
`CHAR_USED`	N/A
`V80_FMT_IMAGE`	N/A
`DATA_UPGRADED`	N/A
`HISTOGRAM`	N/A

ALL_TAB_COMMENTS

Comments on tables and views that are accessible to the user.

Column Name	Description
`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 that are accessible to the user.

Column Name	Description
`OWNER`	Owner of the table
`TABLE_NAME`	Name of the table
`TABLESPACE_NAME`	Name of the table space 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
`SECONDARY`	N/A
`NESTED`	N/A
`BUFFER_POOL`	N/A
`ROW_MOVEMENT`	N/A
`GLOBAL_STATS`	N/A
`USER_STATS`	N/A
`DURATION`	N/A
`SKIP_CORRUPT`	N/A
`MONITORING`	N/A
`CLUSTER_OWNER`	N/A
`DEPENDENCIES`	N/A
`COMPRESSION`	N/A
`DROPPED`	N/A

ALL_USERS

Information about all users of the database.

Column Name	Description
`USERNAME`	Name of the user
`USER_ID`	N/A
`CREATED`	N/A

ALL_VIEWS

Text of views that are accessible to the user.

Column Name	Description
`OWNER`	Owner of the view
`VIEW_NAME`	Name of the view
`TEXT_LENGTH`	Length of the view text
`TEXT`	View textOnly 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
`SUPERVIEW_NAME`	N/A

COLUMN_PRIVILEGES

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

Column Name	Description
`GRANTEE`	Name of the user to whom access is granted
`OWNER`	User name of the owner of the object
`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`	Time stamp for the grant

DICTIONARY

Information about the tables in the Data Dictionary.

Column Name	Description
`TABLE_NAME`	Name of the table in the dictionary
`COMMENTS`	Comments about this dictionary entry

DUAL

Information about the all-purpose single-row DUAL table.

Column Name	Description
`DUMMY`	A dummy column (has value X)

OTGREGISTER

DB2/400 Special Registers

Column Name	Description
`CURRENT_USER`	Primary authorization id
`CURRENT_DATE`	Current date
`CURRENT_TIME`	Current time
`CURRENT_TIMESTAMP`	Current time stamp
`CURRENT_TIMEZONE`	Current time zone
`CURRENT_SERVER`	Host name of the current server
"current_user"	Primary authorization id
"current_date"	Current date
"current_time"	Current time
"current_timestamp"	Current time stamp
"current_timezone"	Current time zone
"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.

Column Name	Description
`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`	Time stamp for the grant

USER_CATALOG

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

Column Name	Description
`TABLE_NAME`	Name of the object
`TABLE_TYPE`	Type of object

USER_COL_COMMENTS

Comments on columns of user tables and views.

Column Name	Description
`TABLE_NAME`	Object name
`COLUMN_NAME`	Column name
`COMMENTS`	Comments on the column

USER_CONS_COLUMNS

Information about columns in constraint definitions that are owned by the user.

Column Name	Description
`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 tables.

Column Name	Description
`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
`RELY`	N/A
`LAST_CHANGE`	N/A
`INDEX_OWNER`	N/A
`INDEX_NAME`	N/A
`INVALID`	N/A
`VIEW_RELATED`	N/A

USER_INDEXES

Description of the user's own indexes.

Column Name	Description
`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
`COMPRESSION`	N/A
`PREFIX_LENGTH`	N/A
`TABLESPACE_NAME`	Name of the table space 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 blocksA depth of one indicates that 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 valuesFor 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
`SECONDARY`	N/A
`BUFFER_POOL`	N/A
`USER_STATS`	N/A
`DURATION`	N/A
`PCT_DIRECT_ACCESS`	N/A
`ITYP_OWNER`	N/A
`ITYP_NAME`	N/A
`PARAMETERS`	N/A
`GLOBAL_STATS`	N/A
`DOMIDX_STATUS`	N/A
`DOMIDX_OPSTATUS`	N/A
`FUNCIDX_STATUS`	N/A
`JOIN_INDEX`	N/A
`IOT_REDUNDANT_PKEY`	N/A
`DROPPED`	N/A

USER_OBJECTS

Objects that are owned by the user.

Column Name	Description
`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
`SECONDARY`	N/A

USER_SYNONYMS

The user's private synonyms.

Column Name	Description
`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 tables, views, and clusters.

Column Name	Description
`TABLE_NAME`	Table, view, or cluster name
`COLUMN_NAME`	Column name
`DATA_TYPE`	Data type 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 null valuesValue 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 value 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`	Second highest value 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.
`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
`GLOBAL_STATS`	N/A
`USER_STATS`	N/A
`AVG_COL_LEN`	N/A
`CHAR_LENGTH`	N/A
`CHAR_USED`	N/A
`V80_FMT_IMAGE`	N/A
`DATA_UPGRADED`	N/A
`HISTOGRAM`	N/A

USER_TAB_COMMENTS

Comments on the tables and views that are owned by the user.

Column Name	Description
`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.

Column Name	Description
`TABLE_NAME`	Name of the table
`TABLESPACE_NAME`	Name of the table space 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
`SECONDARY`	N/A
`NESTED`	N/A
`BUFFER_POOL`	N/A
`ROW_MOVEMENT`	N/A
`GLOBAL_STATS`	N/A
`USER_STATS`	N/A
`DURATION`	N/A
`SKIP_CORRUPT`	N/A
`MONITORING`	N/A
`CLUSTER_OWNER`	N/A
`DEPENDENCIES`	N/A
`COMPRESSION`	N/A
`DROPPED`	N/A

USER_USERS

Information about the current user.

Column Name	Description
`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
`INITIAL_RSRC_CONSUMER_GROUP`	N/A
`EXTERNAL_NAME`	N/A

USER_VIEWS

Text of views that are owned by the user.

Column Name	Description
`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
`SUPERVIEW_NAME`	N/A