This appendix covers the Oracle Transparent Gateway for DRDA data dictionary views accessible to all users of Oracle Database. Most of the views can be accessed by any user with SELECT
privileges for DB2 catalog tables.
N/A is used in the tables to denote that the column is not valid for the gateway.
This appendix contains the following sections:
The following is a list of Oracle data dictionary views that are supported by the gateway for DB2/OS390, DB2/400, and DB2/UDB DRDA Servers. The 10.2 release of the gateway does not have data dictionary view support for DB2/VM servers.
ALL_CATALOG
ALL_COL_COMMENTS
ALL_CONS_COLUMNS
ALL_CONSTRAINTS
ALL_INDEXES
ALL_IND_COLUMNS
ALL_OBJECTS
ALL_SYNONYMS
ALL_TAB_COMMENTS
ALL_TABLES
ALL_TAB_COLUMNS
ALL_USERS
ALL_VIEWS
COL_PRIVILEGES
DICTIONARY
DUAL
TABLE_PRIVILEGES
USER_CATALOG
USER_COL_COMMENTS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_INDEXES
USER_OBJECTS
USER_SYNONYMS
USER_TABLES
USER_TAB_COLUMNS
USER_TAB_COMMENTS
USER_USERS
USER_VIEWS
This section contains tables that describes describing data dictionary views. In the following descriptions, all are supported for DB2/OS390 and DB2/400.
All tables, views, synonyms, and sequence accessible to the user:
column name | description |
---|---|
OWNER |
Owner 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 |
Owner of the object |
TABLE_NAME |
Object name |
COLUMN_NAME |
Column name |
COMMENTS |
Comments on column |
Information about accessible columns in constraint definitions:
column name | description |
---|---|
OWNER |
Owner of the constraint definition |
CONSTRAINT_NAME |
Name of the constraint definition |
TABLE_NAME |
Name of the table with a constraint definition |
COLUMN_NAME |
Name of the column specified in the constraint definition |
POSITION |
Original position of column in definition |
Constraint definitions on accessible tables:
column name | description |
---|---|
OWNER |
Owner of the constraint definition |
CONSTRAINT_NAME |
Name of the constraint definition |
CONSTRAINT_TYPE |
Type of the constraint definition |
TABLE_NAME |
Name of the table with constraint definition |
SEARCH_CONDITION |
Text of the search condition for table check |
R_OWNER |
Owner of the table used in referential constraint |
R_CONSTRAINT_NAME |
Name of the unique constraint definition for referenced table |
DELETE_RULE |
Delete rule for a referential constraint |
STATUS |
Status of a constraint |
DEFERRABLE |
Whether the constraint is deferrable |
DEFERRED |
Whether the constraint was initially deferred |
VALIDATED |
Whether all data obeys the constraint |
GENERATED |
Whether the name of the constraint is user or system generated |
BAD |
Constraint specifies a century in an ambiguous manner |
RELY |
Whether an enabled constraint is enforced or unenforced |
LAST_CHANGE |
When the constraint was last enabled |
INDEX_OWNER |
N/A |
INDEX_NAME |
N/A |
Description of indexes on tables 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 the indexed object |
UNIQUENESS |
Uniqueness status of the index |
COMPRESSION |
N/A |
PREFIX_LENGTH |
0 |
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 |
Threshold percentage of block space allowed per index entry |
INCLUDE_COLUMN |
Column ID of the last column to be included in an index-organized table |
FREELISTS |
Number of process freelists allocated to this segment |
FREELIST_GROUPS |
Number of freelist groups allocated to this segment |
PCT_FREE |
N/A |
LOGGING |
Logging information |
BLEVEL |
Depth of the index from its root block to its leaf blocks. A depth of 1 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 values. For indexes that enforce 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 |
Number of rows in the index |
SAMPLE_SIZE |
Size of the sample used to analyze the index |
LAST_ANALYZED |
Date on which an index was most recently analyzed |
DEGREE |
Number of threads per instance for scanning the index |
INSTANCES |
Number of instances across which the index is to be scanned |
PARTITIONED |
Whether the index is partitioned |
TEMPORARY |
Whether the index is on a temporary table |
GENERATED |
Whether the name of the index is system generated |
SECONDARY |
N/A |
BUFFER_POOL |
Whether the index is a secondary object |
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_ELIM |
N/A |
ALL_IND_COLUMNS
describes the columns of indexes on all tables that are accessible to the current user.
column names | description |
---|---|
INDEX_OWNER |
Owner of the index |
INDEX_NAME |
Name of the index |
TABLE_OWNER |
Owner of the table or cluster |
TABLE_NAME |
Name of the table or cluster |
COLUMN_NAME |
Column name or attribute of object type column |
COLUMN_POSITION |
Position of a column or attribute within the index |
COLUMN_LENGTH |
Indexed length of the column |
CHAR_LENGTH |
Maximum codepoint length of the column |
DESCEND |
Whether the column is sorted in descending order (Y/N) |
Objects accessible to the user:
column name | description |
---|---|
OWNER |
Owner of the object |
OBJECT_NAME |
Name of object |
SUBOBJECT_NAME |
Name of the subobject |
OBJECT_ID |
Object number of the object |
DATA_OBJECT_ID |
Dictionary object number of the segment that contains the object |
OBJECT_TYPE |
Type of object |
CREATED |
N/A |
LAST_DDL_TIME |
N/A |
TIMESTAMP |
N/A |
STATUS |
State of the object |
TEMPORARY |
Whether the object is temporary |
GENERATED |
Whether the name of this object system is generated |
SECONDARY |
N/A |
All synonyms 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 |
Description of tables accessible to the user:
column name | description |
---|---|
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 |
Name of the index organized table |
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 |
Number of process freelists allocated to this segment |
FREELIST_GROUPS |
Number of freelist groups allocated to this segment |
LOGGING |
Logging attribute |
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 |
Average freespace of all blocks on a freelist |
NUM_FREELIST_BLOCKS |
Number of blocks on the freelist |
DEGREE |
Number of threads per instance for scanning the table |
INSTANCES |
Number of instances across which the table is to be scanned |
CACHE |
Whether the cluster is to be cached in the buffer cache |
TABLE_LOCK |
Whether the table locking is enabled or disabled |
SAMPLE_SIZE |
Sample size used in analyzing this table |
LAST_ANALYZED |
Date on which this table was most recently analyzed |
PARTITIONED |
Whether this table is partitioned |
IOT_TYPE |
Whether the table is an index-organized table |
TEMPORARY |
Can the current session only see data that it placed in this object itself? |
SECONDARY |
N/A |
NESTED |
Whether the table is a nested table |
BUFFER_POOL |
Default buffer pool for the object |
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 |
Columns of all tables, views, and clusters accessible to the user:
Comments on tables and views accessible to the user:
column name | description |
---|---|
OWNER |
Owner of the object |
TABLE_NAME |
Name of the object |
TABLE_TYPE |
Type of the object |
COMMENTS |
Comments on the object |
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 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 text. Only the first row of text is returned, even if multiple rows exist. |
TYPE_TEXT_LENGTH |
Length of the type clause of the typed view |
TYPE_TEXT |
Type clause of the typed view |
OID_TEXT_LENGTH |
Length of the WITH OID clause of the typed view |
OID_TEXT |
WITH OID clause of the typed view |
VIEW_TYPE_OWNER |
Owner of the type of the view, if the view is a typed view |
VIEW_TYPE |
Type of the view, if the view is a typed view |
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 was granted |
OWNER |
Username 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 |
Timestamp for the grant |
List or data dictionary tables:
column name | description |
---|---|
TABLE_NAME |
Table name |
COMMENTS |
Description of the table |
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 data from an object |
INSERT_PRIV |
Permission to insert data into an object |
DELETE_PRIV |
Permission to delete data 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 |
Tables, views, synonyms, and sequences owned by the use:
column name | description |
---|---|
TABLE_NAME |
Name of the object |
TABLE_TYPE |
Type of the object |
Comments on columns of user's tables and views:
column name | description |
---|---|
TABLE_NAME |
Name of the object |
COLUMN_NAME |
Name of the coloumn |
COMMENTS |
Comments on the column |
Constraint definitions on user's tables:
column name | description |
---|---|
OWNER |
Owner of the constraint definition |
CONSTRAINT_NAME |
Name associated with the constraint definition |
CONSTRAINT_TYPE |
Type of the constraint definition |
TABLE_NAME |
Name associated with the table with constraint definition |
SEARCH_CONDITION |
Text of the search condition for table check |
R_OWNER |
Owner of table used in referential constraint |
R_CONSTRAINT_NAME |
Name of the unique constraint definition for referenced table |
DELETE_RULE |
Delete rule for referential constraint |
STATUS |
Status of a constraint |
DEFERRABLE |
Whether the constraint is deferrable |
DEFERRED |
Whether the constraint was initially deferred |
VALIDATED |
Whether all data obeys the constraint |
GENERATED |
Whether the name of the constraint is user or system generated |
BAD |
Constraint specifies a century in an ambiguous manner |
LAST_CHANGE |
When the constraint was last enabled |
INDEX_OWNER |
N/A |
INDEX_NAME |
N/A |
Information about columns in constraint definitions 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 table with constraint definition |
COLUMN_NAME |
Name associated with column specified in the constraint definition |
POSITION |
Original position of column in definition |
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 |
0 |
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 |
Threshold percentage of block space allowed per index entry |
INCLUDE_COLUMN |
Column ID of the last column to be included in index-organized table |
FREELISTS |
Number of process freelists allocated to a segment |
FREELIST_GROUPS |
Number of freelist groups allocated to a segment |
PCT_FREE |
N/A |
LOGGING |
Logging information |
BLEVEL |
Depth of the index from its root block to its leaf blocks. A depth of 1 indicates that the root and leaf block are the same. |
LEAF_BLOCKS |
Number of leaf blocks in the index |
DISTINCT_KEYS |
Number of distinct indexed values. For indexes that enforce 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 |
Number of rows in the index |
SAMPLE_SIZE |
Size of the sample used to analyze the index |
LAST_ANALYZED |
Date on which the index was most recently analyzed |
DEGREE |
Number of threads per instance for scanning the index |
INSTANCES |
Number of instances across which the index is to be scanned |
PARTITIONED |
Whether the index is partitioned |
TEMPORARY |
Whether the index is on a temporary table |
GENERATED |
Whether the name of the index is system generated |
SECONDARY |
N/A |
BUFFER_POOL |
Whether the index is a secondary object |
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_ELIM |
N/A |
Objects owned by the user:
column name | description |
---|---|
OBJECT_NAME |
Name of the object |
SUBOBJECT_NAME |
Name of the subobject |
OBJECT_ID |
Object number of the object |
DATA_OBJECT_ID |
Dictionary object number of the segment that contains the object |
OBJECT_TYPE |
Type of object |
CREATED |
N/A |
LAST_DDL_TIME |
N/A |
TIMESTAMP |
N/A |
STATUS |
State of the object: VALID |
TEMPORARY |
Whether the object is temporary |
GENERATED |
Was the name of this object system generated? |
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 |
Description of the user's own tables:
column name | description |
---|---|
TABLE_NAME |
Name of the table |
TABLESPACE_NAME |
Name of the tablespace containing the table |
CLUSTER_NAME |
N/A |
IOT_NAME |
Name of the index organized table |
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 |
Number of process freelists allocated to a segment |
FREELIST_GROUPS |
Number of freelist groups allocated to a segment |
LOGGING |
Logging information |
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 |
Average freespace of all blocks on a freelist |
NUM_FREELIST_BLOCKS |
Number of blocks on the freelist |
DEGREE |
Number of threads per instance for scanning the table |
INSTANCES |
Number of instances across which the table is to be scanned |
CACHE |
Whether the cluster is to be cached in the buffer cache |
TABLE_LOCK |
Whether table locking is enabled or disabled |
SAMPLE_SIZE |
Sample size used in analyzing this table |
LAST_ANALYZED |
Date on which this table was most recently analyzed |
PARTITIONED |
Indicates whether this table is partitioned |
IOT_TYPE |
If this is an index organized table |
TEMPORARY |
Can the current session only see data that it placed in this object itself? |
SECONDARY |
N/A |
NESTED |
If the table is a nested table |
BUFFER_POOL |
The default buffer pool for the object |
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 |
Columns of user's tables, views, and clusters:
Comments on the tables and views owned by the user:
column name | description |
---|---|
TABLE_NAME |
Name of the object |
TABLE_TYPE |
Type of the object |
COMMENTS |
Comments on the object |
Information about the current user:
column name | description |
---|---|
USERNAME |
Name of the user |
USER_ID |
N/A |
ACCOUNT_STATUS |
Indicates if the account is locked, expired or unlocked |
LOCK_DATE |
Date on which the account was locked |
EXPIRE_DATE |
Date of expiration of the account |
DEFAULT_TABLESPACE |
N/A |
TEMPORARY_TABLESPACE |
N/A |
CREATED |
N/A |
EXTERNAL_NAME |
Name of the external user |
Text of views owned by the user:
column name | description |
---|---|
VIEW_NAME |
Name of the view |
TEXT_LENGTH |
Length of the view text |
TEXT |
First line of the view text |
TYPE_TEXT_LENGTH |
Length of the type clause of the typed view |
TYPE_TEXT |
Type clause of the typed view |
OID_TEXT_LENGTH |
Length of the WITH OID clause of the typed view |
OID_TEXT |
WITH OID clause of the typed view |
VIEW_TYPE_OWNER | Owner of the type of the view, if the view is a typed view |
VIEW_TYPE | Type of the view, if the view is a typed view |
SUPERVIEW_NAME | N/A |