A
Oracle DB2 Data Dictionary Views

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

N/A is used in the following tables to mean that the column is not valid for the gateway.

This appendix contains the following sections:

Supported Views

The following is a list of Oracle data dictionary views which are supported by the gateway for DB2/OS390 and DB2/400 servers. This release of the gateway does not have data dictionary view support for DB2/VM and DB2/UDB DRDA servers.

ALL_CATALOG
ALL_COL_COMMENTS
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
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

Data Dictionary View Tables

The remainder of this chapter contains tables describing data dictionary views. In the following descriptions, all are supported for DB2/OS390 and DB2/400.

ALL_CATALOG

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

ALL_COL_COMMENTS

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

ALL_CONS_COLUMNS

Information about accessible columns in constraint definitions:

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

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 table with constraint definition
SEARCH_CONDITION	Text of search condition for table check
R_OWNER	Owner of table used in referential constraint
R_CONSTRAINT_NAME	Name of unique constraint definition for referenced table
DELETE_RULE	Delete rule for referential constraint
STATUS	Status of 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 las enabled or disabled

ALL_INDEXES

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 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	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 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	N/A
AVG_DATA_BLOCKS_PER	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 this 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 this index is partitioned
TEMPORARY	Whether the index is on a temporary table
GENERATED	Whether the name of the index is system generated
BUFFER_POOL	Whether the index is a secondary object

ALL_IND_COLUMNS

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 column or attribute within the index
COLUMN_LENGTH	Indexed length of the column

ALL_OBJECTS

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	Was the name of this object system generated?

ALL_SYNONYMS

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

ALL_TABLES

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	The average freespace of all blocks on a freelist
NUM_FREELIST_BLOCKS	The number of blocks on the freelist
DEGREE	The number of threads per instance for scanning the table
INSTANCES	The 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?
NESTED	If the table is a nested table
BUFFER_POOL	The default buffer pool for the object

ALL_TAB_COLUMNS

Columns of all tables, views, and clusters 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	Datatype of column
DATA_TYPE_MOD	Datatype modifier of the column
DATA_TYPE_OWNER	Owner of the datatype of the column
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	Does 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	For tables with more than three rows, the second lowest and second highest values. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values.
HIGH_VALUE	N/A
DENSITY	N/A
NUM_NULLS	The number of nulls in the column
NUM_BUCKETS	The number of buckets in histogram for the column
LAST_ANALYZED	The date on which this column was most recently analyzed
SAMPLE_SIZE	The sample size used in analyzing this column
CHARACTER_SET_NAME	The name of the character set
CHAR_COL_DECL_LENGTH	The length of the character set

ALL_TAB_COMMENTS

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 object
COMMENTS	Comments on the object

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 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

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 was granted
OWNER	Username 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

DICTIONARY

List or data dictionary tables:

column name	description
TABLE_NAME	Table name
COMMENTS	Description of table

DUAL

column name	description
DUMMY	A dummy column

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	Timestamp for the grant

USER_CATALOG

Tables, views, synonyms, and sequences owned by the use:

column name	description
TABLE_NAME	Name of the object
TABLE_TYPE	Type of object

USER_COL_COMMENTS

Comments on columns of user's tables and views:

column name	description
TABLE_NAME	Object name
COLUMN_NAME	Column name
COMMENTS	Comments on column

USER_CONSTRAINTS

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 constraint definition
TABLE_NAME	Name associated with table with constraint definition
SEARCH_CONDITION	Text of search condition for table check
R_OWNER	Owner of table used in referential constraint
R_CONSTRAINT_NAME	Name of unique constraint definition for referenced table
DELETE_RULE	Delete rule for referential constraint
STATUS	Status of 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 las enabled or disabled

USER_CONS_COLUMNS

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

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
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 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 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	N/A
AVG_DATA_BLOCKS_PER	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 this 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 this index is partitioned
TEMPORARY	Whether the index is on a temporary table
GENERATED	Whether the name of the index is system generated
BUFFER_POOL	Whether the index is a secondary object

USER_OBJECTS

Objects owned by the user:

column name	description
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: VALID
TEMPORARY	Whether the object is temporary
GENERATED	Was the name of this object system generated?

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_TABLES

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 this segment
FREELIST_GROUPS	Number of freelist groups allocated to this 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	The average freespace of all blocks on a freelist
NUM_FREELIST_BLOCKS	The number of blocks on the freelist
DEGREE	The number of threads per instance for scanning the table
INSTANCES	The 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?
NESTED	If the table is a nested table
BUFFER_POOL	The default buffer pool for the object

USER_TAB_COLUMNS

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

column name	description
TABLE_NAME	Table, view, or cluster name
COLUMN_NAME	Column name
DATA_TYPE	Datatype of column
DATA_TYPE_MOD	Datatype modifier of the column
DATA_TYPE_OWNER	Owner of the datatype of the column
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	Does 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	For tables with more than three rows, the second lowest and second highest values. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values.
HIGH_VALUE	N/A
DENSITY	N/A
NUM_NULLS	The number of nulls in the column
NUM_BUCKETS	The number of buckets in histogram for the column
LAST_ANALYZED	The date on which this column was most recently analyzed
SAMPLE_SIZE	The sample size used in analyzing this column
CHARACTER_SET_NAME	The name of the character set
CHAR_COL_DECL_LENGTH	The length of the character set

USER_TAB_COMMENTS

Comments on the tables and views owned by the user:

column name	description
TABLE_NAME	Name of the object
TABLE_TYPE	Type of object
COMMENTS	Comments on the object

USER_USERS

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 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	User external name

USER_VIEWS

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

A Oracle DB2 Data Dictionary Views