Table and Views
CDP includes database objects that enable accessing some features of the NAE Server directly from the DB2 command line. These objects include stored procedures and functions. When CDP for DB2 is downloaded, the objects are placed in the DB2 database. In this article, you will learn about:
Tables
CDP for DB2 includes the following 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 | INTEGER(4) | NOT NULL |
| DB_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_ACCESS_CD | VARCHAR(128) | NOT NULL |
| DGTL_SGNTR_NM | VARCHAR(256) | NOT NULL |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_COLUMN_DEFAULT
This table is used to store information about how null values are passed to insert triggers.
| Column Name | Data Type | Description |
|---|---|---|
| DFLT_CLMN_ID | INTEGER(4) | Primary Key, NOT NULL |
| ENCRYPT_TBL_ID | INTEGER(4) | NOT NULL |
| CLMN_NM | VARCHAR(128) | NOT NULL |
| CLMN_NULL_DFLT_FL | CHARACTER(1) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_ENCRYPTED_TABLE
This table is used to specify which tables contain encrypted columns.
| Column Name | Data Type | Description |
|---|---|---|
| ENCRPT_TBL_ID | INTEGER | Primary Key, NOT NULL |
| DB_NM | VARCHAR(128) | |
| TBL_OWNR_NM | VARCHAR(128) | NOT NULL |
| ORGNL_TBL_NM | VARCHAR(128) | NOT NULL |
| NEW_TBL_NM | VARCHAR(128) | NOT NULL |
| ADD_ID_CLMN_FL | CHARACTER(1) | NOT NULL |
| ID_CLMN_NM | VARCHAR(128) | NOT NULL |
| ID_CLMN_DATA_TYPE_NM | VARCHAR(128) | NOT NULL |
| ID_CLMN_LEN_QTY | INTEGER(4) | NOT NULL |
| ID_CLMN_PRCSN_QTY | INTEGER(4) | |
| ID_CLMN_SCALE_QTY | INTEGER(4) | |
| TMPRY_TBL_NM | VARCHAR(128) | |
| TMPRY_TBL_TBLSPC_NM | VARCHAR(128) | |
| INTRM_VW_NM | VARCHAR(128) | |
| INSRT_TRG_NM | VARCHAR(128) | |
| UPDT_TRG_NM | VARCHAR(128) | |
| PRIMARY_KEY_COL_NMS | VARCHAR(2000) | |
| PRIMARY_KEY_NM | VARCHAR(30) | |
| TMPRY_TBL_CREATE_FL | CHARACTER(1) | NOT NULL |
| VW_CREATE_FL | CHARACTER(1) | NOT NULL |
| SERVER_VERSION | VARCHAR(20) | |
| SERVER_MIGRATION_VERSION | VARCHAR(20) | |
| UDF_VERSION | VARCHAR(20) | |
| SEQ_NM | VARCHAR(30) | |
| OLD_DATA_EXIST_FL | CHARACTER(1) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_ENCRYPTED_COLUMN
This table is used to specify details about each column that is encrypted.
| Column Name | Data Type | Description |
|---|---|---|
| ENCRPT_CLMN_ID | INTEGER(4) | Primary Key, NOT NULL |
| ENCRPT_TBL_ID | INTEGER(4) | NOT NULL |
| ORGNL_CLMN_NM | VARCHAR(128) | NOT NULL |
| NEW_CLMN_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_KEY_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_ALGRTHM_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_MODE_NM | VARCHAR(30) | |
| ENCRPT_PAD_NM | VARCHAR(30) | |
| ENCRPT_IV_TYPE_CD | CHARACTER(1) | |
| ENCRPT_IV_NM | VARCHAR(128) | FOR BIT DATA |
| ENCRPT_IV_CLMN_NM | VARCHAR(128) | |
| ENCRPT_NULL_RPLCMNT_NM | VARCHAR(128) | |
| ERR_RPLCMNT_ID | INTEGER(4) | |
| CLMN_ORGNL_DATA_TYPE_NM | VARCHAR(30) | NOT NULL |
| CLMN_ORGNL_LEN_QTY | INTEGER(4) | NOT NULL |
| CLMN_ORGNL_PRCSN_QTY | INTEGER(4) | |
| CLMN_ORGNL_SCALE_QTY | INTEGER(4) | |
| CLMN_NULL_FL | CHARACTER(1) | NOT NULL |
| CLMN_NEW_DATA_TYPE_NM | VARCHAR(30) | |
| CLMN_NEW_LEN_QTY | INTEGER(4) | |
| CLMN_DATA_MGRT_FL | CHARACTER(1) | NOT NULL |
| ENCRYPT_FL | CHARACTER(1) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) | |
| TWEAKDATA_VAL | VARCHAR(256) | |
| TWEAKALGO_NM | VARCHAR(128) | |
| FPE_FL | CHARACTER(1) | |
| ROTATE_CLMN_NM | VARCHAR(30) | |
| ROTATE_CLMN_CREATE_FL | CHARACTER(1) | |
| ROTATE_KEY_NM | VARCHAR(128) | |
| ROTATE_IV_NM | VARCHAR(128) | FOR BIT DATA |
| REROTATE_FL | 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 | INTEGER(4) | Primary Key, NOT NULL |
| ENCRPT_CLMN_ID | INTEGER(4) | |
| DB_USER_NM | VARCHAR(30) | NOT NULL |
| RQST_TYPE_CD | CHARACTER(1) | NOT NULL |
| ERR_DT | TIMESTAMP(10) | NOT NULL |
| ERR_DESC | VARCHAR(1024) | |
| ERR_MSG_TXT | VARCHAR(1024) | |
| ERR_STACKTRACE_TXT | VARCHAR(2000) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB
This table is used to describe the details of a data migration operation.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_ID | INTEGER(10) | Primary Key, NOT NULL |
| ENCRPT_TBL_ID | INTEGER(10) | NOT NULL |
| JOB_TYPE_CD | VARCHAR(12) | NOT NULL |
| JOB_STATUS_CD | VARCHAR(12) | NOT NULL |
| JOB_START_DT | TIMESTAMP(10) | |
| JOB_END_DT | TIMESTAMP(10) | |
| TOTAL_ROWS_QTY | INTEGER(4) | |
| ROWS_MDFY_QTY | INTEGER(4) | |
| BATCH_SIZE_QTY | INTEGER(4) | |
| JOB_CNCLE_FL | CHARACTER(1) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
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 the 10 batches, and 1 entry for the pre–processing information.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_ID | INTEGER(4) | Primary Key, NOT NULL |
| BATCH_SEQ | INTEGER(4) | NOT NULL |
| JOB_STATUS_CD | VARCHAR(12) | NOT NULL |
| BATCH_START_DT | TIMESTAMP(10) | |
| BATCH_END_DT | TIMESTAMP(10) | |
| BATCH_SIZE_QTY | INTEGER(4) | |
| SQL_ERR_ID | INTEGER(4) | |
| SQL_ERR_STATE_TXT | VARCHAR(200) | |
| ERR_CLASS_NM | VARCHAR(200) | |
| ERR_MSG_TXT | VARCHAR(1024) | |
| ERR_STACKTRACE_TXT | VARCHAR(2000) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB_COLUMN
This table is used to describe which columns are encrypted with which key. Primarily used during key rotation.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_ID | INTEGER(4) | Primary Key, NOT NULL |
| ENCRPT_CLMN_ID | INTEGER(4) | Foreign Key, NOT NULL |
| ENCRPT_KEY_NM | VARCHAR(128) | |
| ENCRPT_IV_NM | VARCHAR(128) | FOR BIT DATA |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB_STATUS
This table is a reference table for valid values of Job Status.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_STATUS_CD | VARCHAR(12) | NOT NULL |
| JOB_STATUS_NM | VARCHAR(128) | |
| JOB_STATUS_DESC | VARCHAR(1024) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_JOB_TYPE
This table is a reference table for valid values of Job Type.
| Column Name | Data Type | Description |
|---|---|---|
| JOB_TYPE_CD | VARCHAR(12) | Primary Key, NOT NULL |
| JOB_TYPE_NM | VARCHAR(128) | |
| JOB_TYPE_DESC | VARCHAR(1024) | |
| CREATE_DT | TIMESTAMP | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_PROPERTY
This table is used to store system properties.
| Column Name | Data Type | Description |
|---|---|---|
| PRPTY_KEY_NM | VARCHAR(100) | Primary Key, NOT NULL |
| PRPTY_VAL_NM | VARCHAR(100) | |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
Views
CDP includes the following views:
ING_AUTH_USER
This view allows a user to select only own data from the ING_AUTHORIZED_USER table. This view contains the following columns.
| Column Name | Data Type | Description |
|---|---|---|
| ATHRZ_USER_ID | INTEGER(4) | NOT NULL |
| DB_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_USER_NM | VARCHAR(128) | NOT NULL |
| ENCRPT_ACCESS_CD | VARCHAR(128) | NOT NULL |
| DGTL_SGNTR_NM | VARCHAR(256) | NOT NULL |
| CREATE_DT | TIMESTAMP(10) | |
| CREATE_USER_NM | VARCHAR(128) | |
| LAST_MDFY_DT | TIMESTAMP(10) | |
| LAST_MDFY_USER_NM | VARCHAR(128) |
ING_GETROLES
This view is used to view roles granted to a user.
| Column Name | Data Type | Description |
|---|---|---|
| ROLENAME | VARCHAR(128) | NOT NULL |
