This appendix includes the gateway data dictionary views accessible to all users of an Oracle database server. Most views can be accessed by any user with SELECT
privileges for DB2 catalog tables.
This appendix include the following data dictionary views:
If a dictionary item is described with N/A in the following lists, then it means that the item is not available for the Oracle Transparent Gateway for DB2.
All tables:
OWNER
is the owner of the object.
TABLE_NAME
is the name of the object.
TABLE_TYPE
is the type of object.
Comments on columns of all tables:
OWNER
is the owner of the object.
TABLE_NAME
is the object name.
COLUMN_NAME
is the column name.
COMMENTS
are the comments on the column.
Information about accessible columns in constraint definitions:
OWNER
is the owner of the constraint definition.
CONSTRAINT_NAME
is the name associated with the constraint definition.
TABLE_NAME
is the name associated with the table containing the constraint definition.
COLUMN_NAME
is the name associated with the column specified in the constraint definition.
POSITION
is the original position of the column in the definition.
Constraint definitions on accessible tables:
OWNER
is the owner of the constraint definition.
CONSTRAINT_NAME
is the name associated with the constraint definition.
CONSTRAINT_TYPE
is the type of constraint definition.
TABLE_NAME
is the name associated with the table containing the constraint definition.
SEARCH_CONDITION
is the text of the search condition for the table check.
R_OWNER
is the owner of the table used in the referential constraint.
R_CONSTRAINT_NAME
is the name of the unique constraint definition for the referenced table.
DELETE_RULE
is the delete rule for the referential constraint.
STATUS
is the status of the constraint.
Columns of the indexes on the accessible tables:
INDEX_OWNER
is the owner of the index.
INDEX_NAME
is the name of the index.
TABLE_OWNER
is the table or cluster owner.
TABLE_NAME
is the table or cluster name.
COLUMN_NAME
is the column name.
COLUMN_POSITION
is the position of column within index.
COLUMN_LENGTH
is the indexed length of column.
Description of indexes on all tables:
OWNER
is the owner of the index.
INDEX_NAME
is the name of the index.
TABLE_OWNER
is the owner of the indexed object.
TABLE_NAME
is the name of the indexed object.
TABLE_TYPE
is the type of the indexed object.
UNIQUENESS
is the uniqueness status of the index.
TABLESPACE_NAME
is the 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_FREE
N/A
BLEVEL
is the depth of the index from its root block to its leaf blocks. A depth of one indicates that the root block and the leaf block are the same.
LEAF_BLOCKS
is the number of leaf blocks in the index.
DISTINCT_KEYS
is the 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_PE
N/A
AVG_DATA_BLOCKS_PE
N/A
CLUSTERING_FACTOR
N/A
STATUS
is the valid state of the index: VALID
.
All indexes and tables:
OWNER
is the owner of the object.
OBJECT_NAME
is the name of object.
OBJECT_ID
is the object number of the object.
OBJECT_TYPE
is the type of object.
CREATED
N/A
LAST_DDL_TIME
N/A
TIMESTAMP
N/A
STATUS
is the state of the object.
All synonyms accessible to the user:
OWNER
is the owner of the synonym.
SYNONYM_NAME
is the name of the synonym.
TABLE_OWNER
is the owner of the object referenced by the synonym.
TABLE_NAME
is the name of the object referenced by the synonym.
DB_LINK
N/A
Columns of all tables:
OWNER
is the owner of the table or view.
TABLE_NAME
is the table or view name.
COLUMN_NAME
is the column name.
DATA_TYPE
is the data type of the column.
DATA_LENGTH
is the maximum length of the column in bytes.
DATA_PRECISION
N/A
DATA_SCALE
are the digits to the right of the decimal point in a number.
NULLABLE
asks if the column allows nulls. The 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
is the sequence number of the column that is created.
DEFAULT_LENGTH
N/A
DATA_DEFAULT
N/A
NUM_DISTINCT
is the number of distinct values in each column of the table.
LOW_VALUE
and HIGH_VALUE
are the second lowest and second highest values for tables. These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values.
DENSITY
N/A
Comments on all tables:
OWNER
is the owner of the object.
TABLE_NAME
is the name of the object.
TABLE_TYPE
is the type of object.
COMMENTS
are comments on the object.
Description of all tables:
OWNER
is the owner of the table.
TABLE_NAME
is the name of the table.
TABLESPACE_NAME
is the name of the tablespace containing the table.
CLUSTER_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
BACKED_UP
N/A
NUM_ROWS
is the number of rows in the table.
BLOCKS
N/A
EMPTY_BLOCKS
N/A
AVG_SPACE
N/A
CHAIN_CNT
N/A
AVG_ROW_LEN
is the average length of a row in the table in bytes.
Information about current users of the database:
USERNAME
is the name of the user.
USER_ID
N/A
CREATED
N/A
Text of views accessible to the user:
OWNER
is the owner of the view.
VIEW_NAME
is the name of the view.
TEXT_LENGTH
is the length of the view text.
TEXT is the view text. Only the first row of text is returned, even if multiple rows exist.
Grants on columns for which the user is the grantor, grantee or owner, or PUBLIC is the grantee:
GRANTEE
is the name of the user to whom access was granted.
OWNER
is the user name of the object's owner.
TABLE_NAME
is the name of the object.
COLUMN_NAME
is the name of the column.
GRANTOR
is the name of the user who performed the grant.
INSERT_PRIV
is the permission to insert into the column.
UPDATE_PRIV
is the permission to update the column.
REFERENCES_PRIV
is the permission to reference the column.
CREATED
is the time stamp for the grant.
CURRENT_SQLID
is the DB2 SQL authorization id.
CURRENT_USER
is the DB2 primary authorization id.
CURRENT_DATE
is the DB2 current date.
CURRENT_TIME
is the DB2 current time.
CURRENT_TIMESTAMP
is the DB2 current time stamp.
CURRENT_TIMEZONE
is the DB2 current time zone.
CURRENT_SERVER
is the DB2 location name of current server.
CURRENT_PACKAGESET
is the DB2 collection id of the package used for running SQL statements.
CURRENT_LC_CTYPE
is the DB2 locale for functions.
CURRENT_OPTIM_HINT
is the DB2 CURRENT OPTIMIZATION HINT special register - optimization hint.
CURRENT_PATH
is the DB2 search path for unqualified functions and procedures.
CURRENT_PRECISION
is the DB2 CURRENT PRECISION special register - numeric precision.
CURRENT_RULES
is the DB2 CURRENT RULES special register - SQL rules in effect.
Grants on objects for which the user is the grantor, grantee or owner, or PUBLIC is the grantee:
GRANTEE
is the name of the user to whom access is granted.
OWNER
is the owner of the object.
TABLE_NAME
is the name of the object.
GRANTOR
is the name of the user who performed the grant.
SELECT_PRIV
is the permission to select from an object.
INSERT_PRIV
is the permission to insert into an object.
DELETE_PRIV
is the permission to delete from an object.
UPDATE_PRIV
is the permission to update an object.
REFERENCES_PRIV
N/A
ALTER_PRIV
is the permission to alter an object.
INDEX_PRIV
is the permission to create or drop an index on an object.
CREATED
is the time stamp for the grant.
Tables owned by the user:
TABLE_NAME
is the name of the object.
TABLE_TYPE
is the type of object.
Comments on columns of user's tables:
TABLE_NAME
is the object name.
COLUMN_NAME
is the column name.
COMMENTS
are the comments on the column.
Information about columns in constraint definitions owned by the user:
OWNER
is the owner of the constraint definition.
CONSTRAINT_NAME
is the name associated with the constraint definition.
TABLE_NAME
is the name associated with the table with the constraint definition.
COLUMN_NAME
is the name associated with the column specified in the constraint definition.
POSITION
is the original position of the column in the definition.
Constraint definitions on user's tables.
OWNER
is the owner of the constraint definition.
CONSTRAINT_NAME
is the name associated with the constraint definition.
CONSTRAINT_TYPE
is the type of constraint definition.
TABLE_NAME
is the name associated with the table with the constraint definition.
SEARCH_CONDITION
is the text of the search condition for the table check.
R_OWNER
is the owner of the table used in the referential constraint.
R_CONSTRAINT_NAME
is the name of the unique constraint definition for the referenced table.
DELETE_RULE
is the delete rule for the referential constraint.
STATUS
is the status of the constraint.
Description of the user's own indexes:
INDEX_NAME
is the name of the index.
TABLE_OWNER
is the owner of the indexed object.
TABLE_NAME
is the name of the indexed object.
TABLE_TYPE
is the type of the indexed object.
UNIQUENESS
is the uniqueness status of the index.
TABLESPACE_NAME
is the name of the tablespace containing the index.
INIT_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_FREE
N/A
BLEVEL
is the depth of the index from its root block to its leaf blocks. A depth of one indicates that the root block and the leaf block are the same.
LEAF_BLOCKS
is the number of leaf blocks in the index.
DISTINCT_KEYS
is the 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 tables.
AVG_LEAF_BLOCKS_PE
N/A
AVG_DATA_BLOCKS_PE
N/A
CLUSTERING_FACTOR
N/A
STATUS
is the state if the indexes: VALID
.
Objects owned by the user:
OWNER
is the owner of the object.
OBJECT_NAME
is the name of the object.
OBJECT_ID
is the object number of the object.
OBJECT_TYPE
is the type of object.
CREATED
N/A
LAST_DDL_TIME
N/A
TIMESTAMP
N/A
STATUS
is the state of the object: VALID.
Private synonyms owned by the user:
SYNONYM_NAME
is the name of the synonym.
TABLE_OWNER
is the owner of the object referenced by the synonym.
TABLE_NAME
is the name of the object referenced by the synonym.
DB_LINK
N/A
Columns of user's tables:
TABLE_NAME
is the table, view, or cluster name.
COLUMN_NAME
is the column name.
DATA_TYPE
is the data type of the column.
DATA_LENGTH
is the maximum length of the column in bytes.
DATA_PRECISION
N/A
DATA_SCALE
are the digits to the right of the decimal point in a number.
NULLABLE
asks if the column allow nulls. The 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
is the sequence number of the column that is created.
DEFAULT_LENGTH
N/A
DATA_DEFAULT
N/A
NUM_DISTINCT
is the number of distinct values in each column of the table.
LOW_VALUE
and HIGH_VALUE
are the second lowest and the second highest values respectively, 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
Comments on the tables owned by the user:
TABLE_NAME
is the name of the object.
TABLE_TYPE
is the type of object.
COMMENTS
are the comments on the object.
Description of the user's own tables:
TABLE_NAME
is the name of the table.
TABLESPACE_NAME
is the name of the tablespace containing the table.
CLUSTER_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
BACKED_UP
N/A
NUM_ROWS
is the number of rows in the table.
BLOCKS
N/A
EMPTY_BLOCKS
N/A
AVG_SPACE
N/A
CHAIN_CNT
N/A
AVG_ROW_LEN
is the average length of a row in the table in bytes.
Information about the current user:
USERNAME
is the name of the user.
USER_ID
N/A
DEFAULT_TABLESPACE
N/A
TEMP_TABLESPACE
N/A
CREATED
N/A