Database objects
CDP for DB2 includes database objects that provide direct access to CDP features from DB2. These objects include stored procedures, extended procedures, and functions.
User Defined Functions (UDFs) allow direct interaction with CDP using the extended procedures. These UDFs can be used to generate random binary values or encrypt/decrypt the following data types: char, varchar, vargraphic, graphic, decimal, integer, small integer, big integer, date, time, and timestamp.
Note
In this topic, the terms FPE Algorithm and Non-FPE Algorithm are used interchangeably.
UDFs are broadly classified into:
Cryptographic UDFs
The Cryptographic UDFs enable you to access the cryptographic functionality of the CipherTrust Manager from the database.
UDFs take the key name, encryptType, and IV as parameters and apply these values to the input data. The names of these UDFs include the prefix cl or fcl.
Following table outlines the input parameters required to use UDFs:
| Parameter Name | Data Type | Description |
|---|---|---|
| inputData | varies by UDF | The value to be encrypted or decrypted. The data type is abbreviated in the UDF name. |
| keyName | varchar(100) | The name of the key to use to encrypt or decrypt the data. This key must exist on the NAE Server, and the requester must have permission to use the key for the operation requested. |
| encryptType | varchar(512) | A string representing the algorithm, the mode in which the key will be used (CBC or ECB) and what kind of padding to use. For example, 'AES/CBC/PKCS5Padding'. |
| ivName | varchar(128) for bit data | The value to use as the Initialization Vector (IV) for data being encrypted or decrypted with a symmetric key (DES-EDE or AES algorithms) in CBC mode. For other algorithms or symmetric keys used in ECB mode, this value is ignored. For operations that use DES-EDE keys, the IV must be eight bytes; for AES keys, the IV must be sixteen bytes. If an IV is specified here, it must be base 16 encoded. Note: — The IV value for standard encryption can be null or empty for command line UDFs. For such cases, the value of the IV associated with the Key is used as default value of the IV. — The value of IV for FPE encryption will be 112 s-integers. |
| databaseID | varchar(100) | Identifies the database. |
| errorReplacementValueId | integer | Denotes the error replacement value set used for the column. If no error replacement value is used, this value is -1. |
| isRowLevelFlag | char(1) | The value determines whether the row level or column IV is used for encryption. The values are: — T: indicates separate IV for each row. — F: indicates one IV for column. |
Based on the type of encryption, non-FPE (standard Algorithm) or FPE, UDFs are divided as follows:
Not all the UDFs can be called directly; they are integrated into the data encryption functionality. However, some UDFs can be called directly. Refer to How to Call Direct DB2 UDFs for Non-FPE Algorithms and How to Call Direct DB2 UDFs for FPE Algorithms for details.
Data Type Conversion UDFs
The encryption and decryption of UDFs automatically convert the input data to VARCHAR FOR BIT DATA using the conversion UDFs as shown below. Normally, these UDFs are not called directly, they are integrated into the data migration functionality.
The following table describes the process of converting various data types before the encryption UDFs are applied.
| Data Type | Conversion Method |
|---|---|
| VARCHAR, CHAR, VARGRAPHIC, and GRAPHIC | Converted directly to VARCHAR FOR BIT DATA. |
| DECIMAL, INTEGER, SMALL INTEGER, BIG INTEGER | Converted to a string and then converted to VARCHAR FOR BIT DATA. |
| DATE, TIME, TIMESTAMP | Converted to a standard 31-byte string format, then converted to VARCHAR FOR BIT DATA. |
The following table lists the conversion UDFs. The conversion UDFs are used by the views and triggers functionality.
| Name | Description |
|---|---|
| ing_int_to_raw430 | Converts an INTEGER value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_int430 | Converts a VARCHAR FOR BIT DATA value to an INTEGER value. |
| ing_sin_to_raw430 | Converts a SMALL INTEGER value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_sin430 | Converts a VARCHAR FOR BIT DATA value to a SMALL INTEGER value. |
| ing_bin_to_raw430 | Converts a BIG INTEGER value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_bin430 | Converts a VARCHAR FOR BIT DATA value to a BIG INTEGER value. |
| ing_dt_to_raw430 | Converts a DATE value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_dt430 | Converts a VARCHAR FOR BIT DATA value to a DATE value. |
| ing_tm_to_raw430 | Converts a TIME value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_tm430 | Converts a VARCHAR FOR BIT DATA value to a TIME value. |
| ing_dtm_to_raw430 | Converts a TIMESTAMP value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_dtm430 | Converts a VARCHAR FOR BIT DATA value to a TIMESTAMP value. |
| ing_dec_to_raw430 | Converts a DECIMAL value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_dec430 | Converts a VARCHAR FOR BIT DATA value to a DECIMAL value. |
| ing_vgr_to_raw_430 | Converts a VARGRAPHIC value to a VARCHAR FOR BIT DATA value. |
| ing_raw_to_vgr_430 | Converts a VARCHAR FOR BIT DATA value to a VARGRAPHIC value. |
(ing_get_property)
This function returns values from the CDP_DB2.properties file.
| Parameter Name | Data Type | In/Out |
|---|---|---|
| Property | varchar | In |
| Function Output | Data Type | In/Out |
|---|---|---|
| Return_Value | varchar | Out |
Example
SELECT ingrian.ing_get_property ('NAE_IP.1')
AS NAE_IP
FROM sysibm.sysdummy1
---------------------------------
NAE_IP
001.001.001.001
Random Number Generator UDF (ing_gn_rndm)
This UDF generates a random binary value of the specified number of bytes. This function is used to generate random IVs.
| Parameter Name | Data Type | In/Out | Description |
|---|---|---|---|
| numbytes | integer | In | The byte length of the binary value. |
| Function Output | Data Type | In/Out | Description |
|---|---|---|---|
| ING_PGN_RNDM | varchar | Out | The ciphertext. |
Note
To execute this UDF, user mapping of the database user is required on the CipherTrust Manager.
Examples
SELECT ingrian.ing_gn_rndm(10)
AS RANDOM_VALUE
FROM sysibm.sysdummy1
------------------------------
RANDOM_VALUE
x'C3EE333348964CCCC54C5
