DECRYPT_DATAKEY_INTEGER, DECRYPT_DATAKEY_BIGINT, DECRYPT_DATAKEY_DECIMAL, DECRYPT_DATAKEY_VARCHAR, DECRYPT_DATAKEY_CLOB, DECRYPT_DATAKEY_VARGRAPHIC, DECRYPT_DATAKEY_DBCLOB, and DECRYPT_DATAKEY_BIT scalar functions

The data key decryption functions return a value that is the result of decrypting the first argument (previously encrypted using the ENCRYPT_DATAKEY function) using the algorithm that was specified when the data was encrypted. The name of the decryption function indicates the desired result data type. The name of the decryption function that is invoked must correspond to the original data type of the encrypted data.

FL 505

Authorization

Start of change The primary authorization ID that is used to invoke the decrypt function must be authorized to use the key label that was used to encrypt the data. RACF and similar external security products can allow access if the authorization to use the key label is permitted to one of the groups that the primary authorization ID is associated with.The key label is saved in the metadata stored with the encrypted value, so the key label does not need to be specified during decryption. When no ACEE is available for the primary authorization ID, Db2 creates one to check for the required authorization when the decrypt function executes. For more information, see When Db2 creates an ACEE. End of change

Syntax

Integer:

Decimal:

String:

Bit:

The schema is SYSIBM.

encrypted-data

An expression that returns a built-in VARBINARY or BLOB value. The value must be returned in the same format that the ENCRYPT_DATAKEY function returns.

ccsid-constant

An integer constant that specifies the CCSID in which the data should be returned by the decryption function. The value must be one of the CCSID values in DECP. If the result of the function is VARCHAR, the CCSID specified must be either an SBCS or MIXED CCSID. If the result of the function is CLOB, the CCSID specified must be a MIXED CCSID if the encoding scheme is Unicode; otherwise the CCSID specified must be an SBCS or MIXED CCSID. If the result of the function VARGRAPHIC or DBCLOB, the CCSID specified must be a DBCS CCSID. See Encoding scheme and CCSID rules for strings for special considerations regarding CCSID 367.

If ccsid is not specified when encrypted-data is a character or graphic string, the default is determined from the encoding scheme and the data type of the result as follows:

For static SQL statements, the ENCODING bind option of the plan or package or the APPLICATION ENCODING SCHEME option of the CREATE or ALTER statement for a compiled SQL scalar function, native SQL procedure, or advanced trigger.
For dynamic SQL statements, the value of the APPLICATION ENCODING special register.

The default CCSID of the result is determined from the result data type:

VARCHAR: the default result CCSID is the mixed data CCSID for the encoding scheme.
CLOB: the default result CCSID is the mixed data CCSID for the encoding scheme.
VARGRAPHIC or DBCLOB: the default result CCSID is the DBCS CCSID for the encoding scheme.

precision

An integer constant with a value greater than or equal to 1 and less than or equal to 31.

scale

An integer constant that is greater than or equal to zero and less than or equal to precision. The value specifies the scale of the result.

The data type of the result of the function is determined by the name of the decryption function that is invoked. The name of the decryption function must indicate the data type of the original value that was encrypted by the ENCRYPT_DATAKEY function.

Table 1. Data key decryption functions and the data type of the result
Function	Data type of the first argument	Original data type of the encrypted data	Data type of the result of the function
DECRYPT_DATAKEY_INTEGER	VARBINARY	INTEGER	INTEGER
DECRYPT_DATAKEY_BIGINT	VARBINARY	BIGINT	BIGINT
DECRYPT_DATAKEY_DECIMAL	VARBINARY	DECIMAL	DECIMAL (`precision`, `scale`)
DECRYPT_DATAKEY_BIT	VARBINARY	CHAR FOR BIT DATA, VARCHAR FOR BIT DATA	VARCHAR FOR BIT DATA
DECRYPT_DATAKEY_VARCHAR	VARBINARY	CHAR, VARCHAR	VARCHAR
DECRYPT_DATAKEY_CLOB	BLOB	CLOB	CLOB
DECRYPT_DATAKEY_VARGRAPHIC	VARBINARY	GRAPHIC, VARGRAPHIC	VARGRAPHIC
DECRYPT_DATAKEY_DBCLOB	BLOB	DBCLOB	DBCLOB

The length attribute of the result is the length attribute of the data type of encrypted-data minus 8 bytes. If a function returns a string data type, the length attribute of the result is the length attribute of the data type of encryted-data minus 16 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 first argument is null, the result can be null; if the first argument is null, the result is the null value.

If the result data type is a string, the encrypted data is decrypted to the original CCSID, and then the intermediate result is cast to the specified CCSID for the result. If the result data type is VARCHAR FOR BIT DATA, the CCSID of the result is 65535. Otherwise, if the result data type is VARCHAR, CLOB, DBCLOB, or VARGRAPHIC, the CCSID of the result is ccsid. If the data is decrypted using a different CCSID than the originally encrypted value, it is possible that expansion might occur when converting the decrypted value to this CCSID. In such situations, the encrypted-data value must first be cast to a varying-length string with a larger number of bytes before performing the decryption.

Example

Decrypt the value in the PROTECTED_SSN VARBINARY column. The encrypted data was originally a character string.

SELECT DECRYPT_DATAKEY_VARCHAR(PROTECTED_SSN)
FROM CUSTOMER
WHERE CID = ?

The CCSID for the result was not specified, so it defaults as described for the ccsid parameter.