ENCRYPT_RC2

data-string

An expression that returns the string value to be encrypted. The string expression must be a built-in string data type.

The length attribute for the data type of data-string must be less than m - MOD(m,8) - n - 1, where m is the maximum length of the result data type and n is the amount of overhead necessary to encrypt the value.

• If a hint-string is not specified, n is 8 bytes.
• If a hint-string is specified, n is 40 bytes.

password-string

An expression that returns a character string value with at least 6 bytes and no more than 127 bytes. The expression must not be a CLOB. The value represents the password used to encrypt the data-string. If the value of the password argument is null or not provided, the data will be encrypted using the ENCRYPTION PASSWORD value, which must have been set using the SET ENCRYPTION PASSWORD statement.

hint-string

An expression that returns a character string value with up to 32 bytes that will help data owners remember passwords (For example, 'Ocean' is a hint to remember 'Pacific'). The expression must not be a CLOB. If a hint value is specified, the hint is embedded into the result and can be retrieved using the GETHINT function. If the password-string is specified and this argument is the null value or not provided, no hint will be embedded in the result. If the password-string is not specified, the hint may be specified using the SET ENCRYPTION PASSWORD statement.

The data type of the result is determined by the first argument as shown in the following table:

The length attribute of the result depends on the arguments that are specified:

• When a password-string is specified but a hint-string is not specified, the length attribute of data-string plus 16 plus the number of bytes to the next 8 byte boundary.¹
• Otherwise, the length attribute of data-string plus 48 plus the number of bytes to the next 8 byte boundary.¹

If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Note that the encrypted result is longer than the data-string value. Therefore, when assigning encrypted values, ensure that the target is declared with sufficient size to contain the entire encrypted value.

Notes

Password protection: To prevent inadvertent access to the encryption password, do not specify password-string as a string constant in the source for a program, procedure, or function. Instead, use the SET ENCRYPTION PASSWORD statement or a host variable.

When connected to a remote relational database, the specified password itself is sent "in the clear". That is, the password itself is not encrypted. To protect the password in these cases, consider using a communications encryption mechanism such as IPSEC (or SSL if connecting between IBM® i products).

Encryption algorithm: The internal encryption algorithm used is RC2 block cipher with padding, the 128 bit secret key is derived from the password using a MD5 message digest.

Encryption passwords and data: It is the user's responsibility to perform password management. Once the data is encrypted only the password used to encrypt it can be used to decrypt it. Be careful when using CHAR variables to set password values as they may be padded with blanks. The encrypted result may contain a null terminator and other non-printable characters.

Table column definition: When defining columns and distinct types to contain encrypted data:

• The column must be defined with a data type of CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, VARBINARY, or BLOB.
• The length attribute of the column must include an additional n bytes, where n is the overhead necessary to encrypt the data as described above.

Any assignment or cast to a column without one of these data types or with a length shorter than the suggested data length may result in an assignment error or, if the assignment is successful, a failure and lost data when the data is subsequently decrypted. Blanks are valid encrypted data values that may be truncated when stored in a column that is too short.

Some sample column length calculations:

Maximum length of non-encrypted data           6 bytes
Number of bytes to the next 8 byte boundary    2 bytes
Overhead                                       8 bytes (or 16 bytes)
                                              --------
Encrypted data column length                  16 bytes (or 32 bytes)


Maximum length of non-encrypted data          32 bytes
Number of bytes to the next 8 byte boundary    8 bytes
Overhead                                       8 bytes (or 16 bytes)
                                              --------
Encrypted data column length                  48 bytes (or 56 bytes)

Administration of encrypted data: Encrypted data can only be decrypted on servers that support the decryption functions that correspond to the ENCRYPT_RC2 function. Hence, replication of columns with encrypted data should only be done to servers that support the decryption functions.

Syntax alternatives: For compatibility with previous versions of DB2®, ENCRYPT can be specified in place of ENCRYPT_RC2.