The database manager creates and maintains a set of system
catalog base tables. This appendix contains a description of each system catalog
base table, including column names and data types. All of the system catalog
base tables are created by the database manager. The system catalog base tables
cannot be explicitly created or dropped. The system catalog base tables are
updated during normal operation in response to SQL data definition statements,
environment routines, and certain utilities. Data in the system catalog base
tables is available through normal SQL query facilities. The system catalog
base tables cannot be modified using normal SQL data manipulation commands.
In order to access the system catalog tables, you need to use a delimited
identifier.
ǥ 151. System catalog base tables
| Description | Catalog base table |
|---|---|
| tables | *** |
| columns | *** |
| referential constraints | *** |
| users | *** |
DB2eSYSTABLES
This system catalog base table
contains one row for each table that is created. All of the catalog tables
have entries in the DB2eSYSTABLES catalog.
ǥ 152. DB2eSYSTABLES system catalog base table
| Column name | Data type | Nullable | Description |
|---|---|---|---|
| TNAME | VARCHAR (19) |
| Table name |
| NUMCOLS | INTEGER (4) |
| Number of columns |
| FLAGS | INTEGER (4) |
| (Internal use only) |
| NUMKEY | INTEGER (4) |
| Number of columns in the primary key |
| CHK | BLOB (512) | Yes | Check constraint (internal use only) |
| IDXINFO | BLOB (700) | Yes | Index (internal use only) |
| NUMREFS | INTEGER (4) | Yes | Primary and foreign key (internal use only) |
| F_ID | INTEGER (4) | Yes | (Internal use only) |
| PD | BLOB (4096) | Yes | (Internal use only) |
DB2eSYSCOLUMNS
This system catalog base table
contains one row for each column that is defined for a table.
ǥ 153. DB2eSYSCOLUMNS system catalog base table
| Column name | Data type | Nullable | Description |
|---|---|---|---|
| CNAME | VARCHAR (19) |
| Column name |
| TNAME | VARCHAR (19) |
| Table name |
| TYPE | INTEGER (4) |
| Data type |
| ATTR | INTEGER (4) |
| (Internal use only) |
| LENGTH | INTEGER (4) |
| Length of the column |
| POS | INTEGER (4) |
| Column number |
| FLAGS | INTEGER (4) |
| (Internal use only) |
| KEYSEQ | INTEGER (4) |
| Ordinal position of the column in the primary key |
| SCALE | INTEGER (4) |
| Scale for decimal column |
| DEF | VARCHAR (128) | Yes | Default value (internal use) |
DB2eSYSRELS
This system catalog base table contains
a row for each referential constraint.
ǥ 154. DB2eSYSRELS system catalog base table
| Column name | Data type | Nullable | Description |
|---|---|---|---|
| RMD_ID | INTEGER (4) |
| Primary and foreign key (internal use only) |
| PKTABLE_NAME | VARCHAR (19) |
| Parent table name |
| PKCOLUMN_NAME | VARCHAR (19) |
| Parent table primary key column |
| FKTABLE_NAME | VARCHAR (19) |
| Child table name |
| FKCOLUMN_NAME | VARCHAR (19) |
| Child table foreign key column name |
| ORDINAL_POSITION | INTEGER (4) |
| Position of the column in the foreign key reference |
DB2eSYSUSERS
The DB2eSYSUSERS table is created automatically when the first encrypted table is created or when the first GRANT statement is executed. This table is tightly bound to the database and encrypted data; it cannot be moved to another DB2 Everyplace database that contains different encrypted data.
This system catalog base table contains one row for each registered user
name that is defined for a database.
ǥ 155. DB2eSYSCOLUMNS system catalog base table
| Column name | Data type | Nullable | Description |
|---|---|---|---|
| USERNAME | VARCHAR (19) |
| Part of primary key and is case sensitive. The name of the user associated with this row. |
| DATABASENAME | VARCHAR (19) |
| For future use. Empty string is stored. Part of primary key. |
| TABLENAME | VARCHAR (19) |
| For future use. Empty string is stored. Part of primary key. |
| ENCMETHOD | VARCHAR (198) |
| For future use. Empty string is stored. Part of primary key. |
| PRIVILEGES | CHAR (19) | Yes | Defines user privileges. Currently, only the value 'E', indicating encryption, is allowed. |
| ENCKEYDATA | BLOB (64) | Yes | Used to regenerate encryption key. |
| ATTIME | TIMESTAMP (26) | Yes | Time when the user was added or the record was most recently modified, whichever is most recent. |
| VALIDATE | BLOB (64) | Yes | Verifies that the record is authentic and the user was added by an authenticated user. |
| GRANTOR | VARCHAR (19) | Yes | The user name that registered the user name in column 1. |
| INTERNALDATA | BLOB (255) | Yes | (Internal future use) |