>>-+-DECRYPT_BIT----+--(--encrypted-data--+----------------------------------------+--)->< +-DECRYPT_BINARY-+ '-,--+-password-string-+--+------------+-' +-DECRYPT_CHAR---+ '-DEFAULT---------' '-,--integer-' '-DECRYPT_DB-----'
The DECRYPT_BIT, DECRYPT_BINARY, DECRYPT_CHAR, and DECRYPT_DB functions return a value that is the result of decrypting encrypted data. The password used for decryption is either the password-string value or the ENCRYPTION PASSWORD value assigned by the SET ENCRYPTION PASSWORD statement.
The decryption functions can only decrypt values that are encrypted using the ENCRYPT_RC2 or ENCRYPT_TDES function.
If DECRYPT_CHAR is specified, integer must be a valid SBCS CCSID or mixed data CCSID. It cannot be 65535 (bit data). If the third argument is an SBCS CCSID, then the result is SBCS data. If the third argument is a mixed CCSID, then the result is mixed data. If the third argument is not specified then the CCSID of the result is the default CCSID of the current server.
If DECRYPT_DB is specified, integer must be a valid DBCS CCSID. If the third argument is not specified then the CCSID of the result is the DBCS CCSID associated with the default CCSID of the current server.
The data type of the result is determined by the function specified and the data type of the first argument as shown in the following table. If a cast from the actual type of the encrypted data to the function's result is not supported a warning or error is returned.
Function | Data Type of First Argument | Actual Data Type of Encrypted Data | Result |
---|---|---|---|
DECRYPT_BIT | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Character string | VARCHAR FOR BIT DATA |
DECRYPT_BIT | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Graphic string | Error or Warning ** |
DECRYPT_BIT | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Binary string | Error or Warning ** |
DECRYPT_BIT | BLOB | Any string | Error |
DECRYPT_BINARY | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Any string | VARBINARY |
DECRYPT_BINARY | BLOB | Any string | BLOB |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Character string | VARCHAR |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Unicode graphic string | VARCHAR |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Non-Unicode graphic string | Error or Warning ** |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Binary string | Error or Warning ** |
DECRYPT_CHAR | BLOB | Character string | CLOB |
DECRYPT_CHAR | BLOB | Unicode graphic string | CLOB |
DECRYPT_CHAR | BLOB | Non-Unicode graphic string | Error or Warning ** |
DECRYPT_CHAR | BLOB | Binary string | Error or Warning ** |
DECRYPT_DB | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | UTF-8 character string or graphic string | VARGRAPHIC |
DECRYPT_DB | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Non-UTF-8 character string | Error or Warning ** |
DECRYPT_DB | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Binary string | Error or Warning ** |
DECRYPT_DB | BLOB | UTF-8 character string or graphic string | DBCLOB |
DECRYPT_DB | BLOB | Non-UTF-8 character string | Error or Warning ** |
DECRYPT_DB | BLOB | Binary string | Error or Warning ** |
Note:
** If the decryption function is in the select list of an outer subselect, a data mapping warning is returned. Otherwise an error is returned. For more information on data mapping warnings, see Assignments and comparisons. |
If the encrypted-data included a hint, the hint is not returned by the function. The length attribute of the result is the length attribute of the data type of encrypted-data minus 8 bytes. The actual length of the result is the length of the original string that was encrypted. If the encrypted-data includes bytes beyond the encrypted string, these bytes are not returned by the function.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
If the data is decrypted using a different CCSID than the originally encrypted value, expansion may occur when converting the decrypted value to this CCSID. In such situations, the encrypted-data should be cast to a varying-length string with a larger number of bytes.
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 ENCRYPTION PASSWORD special register 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 iSeries(TM) systems).
Syntax alternatives: For compatibility with previous versions of DB2(R), DECRYPT_BIN can be specified in place of DECRYPT_BIT.
SET ENCRYPTION PASSWORD = :pw INSERT INTO EMP1 (SSN) VALUES ENCRYPT_RC2( '289-46-8832' ) SELECT DECRYPT_CHAR( SSN) FROM EMP1The DECRYPT_CHAR function returns the original value '289-46-8832'.
INSERT INTO EMP1 (SSN) VALUES ENCRYPT_TDES( '289-46-8832', :pw) SELECT DECRYPT_CHAR( SSN, :pw) FROM EMP1The DECRYPT_CHAR function returns the original value '289-46-8832'.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.