Tables and Views
CDP for DB2 includes the following tables and views:
Tables
ING_AUTHORIZED_USER
This table is used to define all users authorized to perform encrypt and decrypt operations.
| Column Name | Data Type | Description |
|---|---|---|
| ATHRZ_USER_ID | NUMBER | NOT NULL, PRIMARY KEY |
| DB_USER_NM | VARCHAR2(30) | NOT NULL |
| ENCRPT_USER_NM | VARCHAR2(128) | NOT NULL |
| ENCRPT_ACCESS_CD | VARCHAR2(128) | NOT NULL |
| DGTL_SGNTR_NM | VARCHAR2(256) | NOT NULL |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) | |
| ENCRYPTED_USER_NAME | VARCHAR(256) | |
| ENCRYPTED_ACCESS_CRED | VARCHAR(256) | |
| ENCRYPTED_DGTL_SGNTR | VARCHAR(512) |
Note
In case of fresh installation, the ENCRPT_USER_NM, ENCRPT_ACCESS_CD, and DGTL_SGNTR_NM columns will always remain empty.
ING_COLUMN_DEFAULT
This table is used to specify details about columns that have default values in their insert triggers.
| Column Name | Data Type | Description |
|---|---|---|
| DFLT_CLMN_ID | NUMBER | NOT NULL, PRIMARY KEY |
| ENCRPT_TBL_ID | NUMBER | NOT NULL, FOREIGN KEY |
| CLMN_NM | VARCHAR2(128) | NOT NULL |
| CLMN_NULL_DFLT_FL | CHAR(1) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
ING_ENCRYPTED_COLUMN
This table is used to specify details about each column that is encrypted.
| Column Name | Data Type | Description |
|---|---|---|
| ENCRPT_CLMN_ID | NUMBER | NOT NULL, PRIMARY KEY |
| ENCRPT_TBL_ID | NUMBER | NOT NULL, FOREIGN KEY |
| ORGNL_CLMN_NM | VARCHAR2(30) | NOT NULL |
| NEW_CLMN_NM | VARCHAR2(30) | NOT NULL |
| ENCRPT_KEY_NM | VARCHAR2(128) | NOT NULL |
| ENCRPT_ALGRTHM_NM | VARCHAR2(128) | NOT NULL |
| ENCRPT_MODE_NM | VARCHAR2(30) | |
| ENCRPT_PAD_NM | VARCHAR2(30) | |
| ENCRPT_IV_TYPE_CD | CHAR(1) | |
| ENCRPT_IV_NM | RAW(128) | |
| ENCRPT_IV_CLMN_NM | VARCHAR2(30) | |
| ENCRPT_NULL_RPLCMNT_NM | VARCHAR2(128) | |
| ERR_RPLCMNT_ID | NUMBER | |
| CLMN_ORGNL_DATA_TYPE_NM | VARCHAR2(30) | NOT NULL |
| CLMN_ORGNL_LEN_QTY | NUMBER | NOT NULL |
| CLMN_ORGNL_PRCSN_QTY | NUMBER | |
| CLMN_ORGNL_SCALE_QTY | NUMBER | |
| CLMN_NULL_FL | CHAR(1) | NOT NULL |
| CLMN_NEW_DATA_TYPE_NM | VARCHAR2(30) | |
| CLMN_NEW_LEN_QTY | NUMBER | |
| CLMN_DATA_MGRT_FL | CHAR(1) | NOT NULL |
| ENCRPT_FL | CHAR(1) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) | |
| DMN_IDX_TBLSPC_NM | VARCHAR2(30) | |
| DMN_REG_IDX_NM | VARCHAR2(30) | |
| DMN_IDX_NM | VARCHAR2(30) | |
| DMN_IDX_CREATE_FL | VARCHAR2(1) | DEFAULT 'N' |
| DMN_IDX_FL | VARCHAR2(1) | DEFAULT 'N' |
| ROTATE_CLMN_NM | VARCHAR2(30) | |
| ROTATE_CLMN_CREATE_FL | CHAR(1) | DEFAULT 'N' |
| ROTATE_KEY_NM | VARCHAR2(128) | |
| ROTATE_IV_NM | RAW(128) | |
| REROTATE_FL | CHAR(1) | |
| TWEAKDATA_VAL | VARCHAR2(256) | |
| TWEAKALGO_NM | VARCHAR2(128) | |
| FPE_FL | CHAR(1) | DEFAULT 'N' |
ING_ENCRYPTED_TABLE
This table is used to specify which tables contain encrypted columns.
| Column Name | Data Type | Description |
|---|---|---|
| ENCRPT_TBL_ID | NUMBER | NOT NULL, PRIMARY KEY |
| DB_NM | VARCHAR2(30) | |
| TBL_OWNR_NM | VARCHAR2(30) | NOT NULL |
| ORGNL_TBL_NM | VARCHAR2(30) | NOT NULL |
| NEW_TBL_NM | VARCHAR2(30) | NOT NULL |
| ADD_ID_CLMN_FL | CHAR(1) | NOT NULL |
| ID_CLMN_NM | VARCHAR2(30) | NOT NULL |
| ID_CLMN_DATA_TYPE_NM | VARCHAR2(30) | NOT NULL |
| ID_CLMN_LEN_QTY | NUMBER | NOT NULL |
| ID_CLMN_PRCSN_QTY | NUMBER | |
| ID_CLMN_SCALE_QTY | NUMBER | |
| TMPRY_TBL_NM | VARCHAR2(30) | |
| TMPRY_TBL_TBLSPC_NM | VARCHAR2(30) | |
| INTRM_VW_NM | VARCHAR2(30) | |
| INSRT_TRG_NM | VARCHAR2(30) | |
| UPDT_TRG_NM | VARCHAR2(30) | |
| PRIMARY_KEY_COL_NMS | VARCHAR2(2000) | |
| PRIMARY_KEY_NM | VARCHAR2(30) | |
| TMPRY_TBL_CREATE_FL | CHAR(1) | NOT NULL |
| VW_CREATE_FL | CHAR(1) | NOT NULL |
| SERVER_VERSION | VARCHAR2(20) | |
| SERVER_MIGRATION_VERSION | VARCHAR2(20) | |
| UDF_VERSION | VARCHAR2(20) | |
| SEQ_NM | VARCHAR2(30) | |
| OLD_DATA_EXIST_FL | CHAR(1) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) | |
| META_CNTR | NUMBER | |
| OP_ONLINE | CHAR(1) |
ING_ERROR_LOG
This table is used to log errors during column-specific encrypt and decrypt operations.
| Column Name | Data Type | Description |
|---|---|---|
| ERR_LOG_ID | NUMBER | NOT NULL, PRIMARY KEY |
| ENCRPT_CLMN_ID | NUMBER | |
| DB_USER_NM | VARCHAR2(30) | NOT NULL |
| RQST_TYPE_CD | CHAR(1) | NOT NULL |
| ERR_DT | DATE | NOT NULL |
| ERR_DESC | VARCHAR2(1024) | |
| ERR_MSG_TXT | VARCHAR2(1024) | |
| ERR_STACKTRACE_TXT | VARCHAR2(2000) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
ING_JOB
This table is used to describe the details of a data migration operation.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_ID | NUMBER | NOT NULL, PRIMARY KEY |
| ENCRPT_TBL_ID | NUMBER | NOT NULL, FOREIGN KEY |
| JOB_TYPE_CD | VARCHAR2(12) | NOT NULL, FOREIGN KEY |
| JOB_STATUS_CD | VARCHAR2(12) | NOT NULL, FOREIGN KEY |
| JOB_START_DT | DATE | |
| JOB_END_DT | DATE | |
| TOTAL_ROWS_QTY | NUMBER | |
| ROWS_MDFY_QTY | NUMBER | |
| BATCH_SIZE_QTY | NUMBER | |
| JOB_CNCL_FL | CHAR(1) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
ING_JOB_BATCH
This table is used to record the status of a migration job. For each job that is run, there is one entry in this table for each batch that is processed, and there is one entry for pre–processing information, such as input and output table validation.
For example, if there are 100,000 rows to process in a batch job, and the batch size is 10,000, then there are 11 entries in this table for the job: 10 entries for 10 batches and 1 entry for pre–processing information.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_ID | NUMBER | NOT NULL, PRIMARY KEY, FOREIGN KEY |
| BATCH_SEQ | NUMBER | NOT NULL, PRIMARY KEY |
| JOB_STATUS_CD | VARCHAR2(12) | NOT NULL, FOREIGN KEY |
| BATCH_START_DT | DATE | |
| BATCH_END_DT | DATE | |
| BATCH_SIZE_QTY | NUMBER | |
| SQL_ERR_ID | NUMBER | |
| SQL_ERR_STATE_TXT | VARCHAR2(200) | |
| ERR_CLASS_NM | VARCHAR2(200) | |
| ERR_MSG_TXT | VARCHAR2(1024) | |
| ERR_STACKTRACE_TXT | VARCHAR2(2000) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
ING_JOB_COLUMN
This table is used to describe column-level encryption details. Primarily used during key rotation.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_ID | NUMBER | NOT NULL, PRIMARY KEY, FOREIGN KEY |
| ENCRPT_CLMN_ID | NUMBER | NOT NULL, PRIMARY KEY, FOREIGN KEY |
| ENCRPT_KEY_NM | VARCHAR2(128) | |
| ENCRPT_IV_NM | RAW(128) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
ING_JOB_STATUS
This table is a reference table for valid values of Job Type.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_STATUS_CD | VARCHAR2(12) | NOT NULL, PRIMARY KEY |
| JOB_STATUS_NM | VARCHAR2(128) | |
| JOB_STATUS_DESC | VARCHAR2(1024) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
ING_JOB_TYPE
This table is a reference table for valid values of Job Type.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_TYPE_CD | VARCHAR2(12) | NOT NULL, PRIMARY KEY |
| JOB_TYPE_NM | VARCHAR2(128) | |
| JOB_TYPE_DESC | VARCHAR2(1024) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
ING_PROPERTY
This table is used to store system properties.
| Column Name | Data Type | Description |
|---|---|---|
| PRPTY_KEY_NM | VARCHAR2(100) | NOT NULL, PRIMARY KEY |
| PRPTY_VAL_NM | VARCHAR2(100) | |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) |
Views
ING_ALL_PRIVS
This view is used to view all privileges granted through roles as well as directly (except for privileges on system objects).
| Column Name | Data Type |
|---|---|
| GRANTEE | VARCHAR2(30) |
| OWNER | VARCHAR2(30) |
| TABLE_NAME | VARCHAR2(30) |
| PRIVILEGE | VARCHAR2(40) |
| GRANTED_ROLE | VARCHAR2(30) |
| ADM | VARCHAR2(1) |
ING_AUTH_USER
This view allows a user to select only own data from the ING_AUTHORIZED_USER table.
| Column Name | Data Type | Description |
|---|---|---|
| ATHRZ_USER_ID | NUMBER | NOT NULL |
| DB_USER_NM | VARCHAR2(30) | NOT NULL |
| ENCRPT_USER_NM | VARCHAR2(128) | NOT NULL |
| ENCRPT_ACCESS_CD | VARCHAR2(128) | NOT NULL |
| DGTL_SGNTR_NM | VARCHAR2(256) | NOT NULL |
| CREATE_DT | DATE | |
| CREATE_USER_NM | VARCHAR2(30) | |
| LAST_MDFY_DT | DATE | |
| LAST_MDFY_USER_NM | VARCHAR2(30) | |
| ENCRYPTED_USER_NAME | VARCHAR(256) | |
| ENCRYPTED_ACCESS_CRED | VARCHAR(256) | |
| ENCRYPTED_DGTL_SGNTR | VARCHAR(512) |
Note
In case of fresh installation, the ENCRPT_USER_NM, ENCRPT_ACCESS_CD, and DGTL_SGNTR_NM columns will always remain empty.
ING_GETROLES
This view is used to view roles granted to a user.
| Column Name | Data Type | Description |
|---|---|---|
| GRANTED_ROLE | VARCHAR2(30) | NOT NULL |
ING_USER_PRIVS
This view is used to view privileges granted to metadatabase user directly or through roles.
| Column Name | Data Type |
|---|---|
| GRANTEE | VARCHAR2(30) |
| OWNER | VARCHAR2(30) |
| TABLE_NAME | VARCHAR2(30) |
| PRIVILEGE | VARCHAR2(40) |
