Defining columns for data encrypted using the ENCRYPT_TDES built-in function

When data is encrypted using the ENCRYPT_TDES built-in function, it is returned as a binary data string. Therefore, encrypted data should be stored in columns that are defined as VARCHAR FOR BIT DATA.

About this task

Columns that hold encrypted data also require additional bytes to hold a header and to reach a multiple of 8 bytes.

Suppose that you have non-encrypted data in a column that is defined as VARCHAR(6). Use the following calculation to determine the column definition for storing the data in encrypted format:

Maximum length of non-encrypted data        6 bytes
Number of bytes to the next multiple of 8   2 bytes
24 bytes for encryption key                24 bytes
                                           --------
Encrypted data column length               32 bytes

Therefore, define the column for encrypted data as VARCHAR(32) FOR BIT DATA.

When encrypting data using the ENCRYPT_TDES built-in functions, you can use a password hint. If you use a password hint, Db2 requires an additional 32 bytes to store the hint. Suppose that you have non-encrypted data in a column that is defined as VARCHAR(10). Use the following calculation to determine the column definition for storing the data in encrypted format with a password hint:

Maximum length of non-encrypted data       10 bytes
Number of bytes to the next multiple of 8   6 bytes
24 bytes for encryption key                24 bytes
32 bytes for password hint                 32 bytes
                                           --------
Encrypted data column length               72 bytes

Therefore, define the column for encrypted data as VARCHAR(72) FOR BIT DATA when using ENCRYPT_TDES to encrypt your data.