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:
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 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 |
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 |
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 |
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 |
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 |
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 |
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 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 |
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 |
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 |
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 |
Information about all users of the database.
Column Name | Description |
---|---|
USERNAME |
Name of the user |
USER_ID |
N/A |
CREATED |
N/A |
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 |
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 |
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 |
Information about the all-purpose single-row DUAL table.
Column Name | Description |
---|---|
DUMMY |
A dummy column (has value X) |
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 |
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 |
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 |
Comments on columns of user tables and views.
Column Name | Description |
---|---|
TABLE_NAME |
Object name |
COLUMN_NAME |
Column name |
COMMENTS |
Comments on the column |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |