Please Note:

User Defined Functions

CDP’s user defined functions (UDFs) enable interfacing with the NAE Server directly by accessing the extended procedures in CDP. Use these UDFs to generate random binary values, or encrypt and decrypt the following data types: char, decimal, date, integer, big integer, small integer, time, varchar. There are also UDFs that enable migrating between these data types and VARCHAR FOR BIT DATA.

Encryption and Decryption UDFs

The encryption and decryption UDFs enable accessing the cryptographic functionality of the NAE Server from the database. There are two types of cryptographic UDFs: command line and name. The command line UDFs take the key name, algorithm, and IV as parameters and apply these values to the input data. The names of these UDFs include the prefix ‘cl’. The encryption and decryption UDFs take the following parameters:

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, 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 or DESEDE 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.

Standard Encryption UDFs

The following table lists the UDFs by data type for the standard encryption:

Data Type	Function	Action
BLOB	—ing_e_blob_cl430 —ing_d_blob_cl430	—encrypt — decrypt
BIG INTEGER	— ing_e_bin_cl430 — ing_d_bin_cl430	— encrypt —decrypt
CHAR	— ing_e_chr_cl430 —ing_d_chr_cl430	— encrypt — decrypt
CLOB	—ing_e_clob_cl430 — ing_d_clob_cl430	— encrypt — decrypt
DATE	— ing_e_dt_cl430 — ing_d_dt_cl430	— encrypt —decrypt
DECIMAL	— ing_e_dec_cl430 — ing_d_dec_cl430	— encrypt — decrypt
DOUBLE	— ing_e_double_cl430 —ing_d_double_cl430	—encrypt —decrypt
INTEGER	— ing_e_int_cl430 — ing_d_int_cl430	— encrypt —decrypt
REAL	— ing_e_rel_cl430 — ing_d_rel_cl430	— encrypt — decrypt
SMALL INTEGER	— ing_e_sin_cl430 — ing_d_sin_cl430	— encrypt — decrypt
TIME	— ing_e_tm_cl430 — ing_d_tm_cl430	— encrypt — decrypt
TIMESTAMP	— ing_e_dtm_cl430 — ing_d_dtm_cl430	— encrypt — decrypt
VARCHAR	— ing_e_vrc_cl430 — ing_d_vrc_cl430	— encrypt — decrypt
VARGRAPHIC	— ing_e_vgr_cl430 — ing_d_vgr_cl430	— encrypt — decrypt

Format Preserving Encryption UDFs

Data Type	Function	Action
BIG INTEGER	— ing_e_bin_fcl430 — ing_d_bin_cl430	— encrypt — decrypt
CHAR	— ing_e_chr_fcl430 — ing_d_chr_cl430	— encrypt — decrypt
INTEGER	— ing_e_int_fcl430 — ing_d_int_cl430	— encrypt — decrypt
SMALL INTEGER	— ing_e_sin_fcl430 — ing_d_sin_cl430	— encrypt — decrypt
VARCHAR	— ing_e_vrc_fcl430 — ing_d_vrc_cl430	— encrypt — decrypt

How to call UDFs

Normally, UDFs are not called directly; they are integrated into the data migration functionality. Example of direct calls to command line UDFs are shown below.

Direct Call to ing_e_chr_cl430 for Standard Encryption

        SELECT cast (ingrian.ing_e_chr_cl430
    ('hello', /* inputData*/
    'yourAESKeyName', /* keyName */
    'AES/CBC/PKCS5Padding', /* encryptType */
    x'24A40F6DFB05F379630AC0533E7F6357', /* iv */
    CAST ('F' AS char(1)))
    AS VARCHAR(100))
    FROM sysibm.sysdummy1
    ---------------------------------
    1
    x'9F71F06D9F1E123B68D910F0EC34E8C5'

Direct Call to ing_d_chr_cl430 for Standard Encryption

        SELECT CAST (ingrian.ing_d_chr_cl430
    (x'9F71F06D9F1E123B68D910F0EC34E8C5',/* inputData*/
    'yourAESKeyName', /* keyName */
    'AES/CBC/PKCS5Padding', /* encryptType */
    x'24A40F6DFB05F379630AC0533E7F6357', /* iv */
    CAST ('F' as char(1)),
    '001422208382:1150254121:840327458', /* DatabaseID*/
    201) /* ErrorReplacementValueID */
    AS VARCHAR (30))
    FROM sysibm.sysdummy1
    ------------------
    1
    hello

Direct Call to ing_e_vrc_fcl430 for FPE Encryption

        SELECT ingrian.ing_e_vrc_fcl430
        ('12335448496797597507496793869831233544849679759750
        74967938', /* inputData*/
        'yourAESKeyName', /* keyName */
        'FPE/AES/CARD10:SHA1:1234', /* encryptType */
        x'08040004030507050605060204000706050600050308030507
        0308020300030101070504050800080308000501020200080205
        0106000305', /* iv */
        CAST ('F' AS char(1)))
        FROM sysibm.sysdummy1
        ---------------------------------
        1
        6254255344720965730736023131648487521849252826734674
        610665

Direct Call to ing_d_vchr_cl430 for FPE Encryption

        SELECT ingrian.ing_d_vchr_cl430
        ('62542553447209657307360231316484875218492528267346
        74610665', /* inputData*/
        'yourAESKeyName', /* keyName */
        'FPE/AES/CARD10:SHA1:1234', /* encryptType */
        x'08040004030507050605060204000706050600050308030507
        0308020300030101070504050800080308000501020200080205
        0106000305', /* iv */
        CAST ('F' AS char(1)),
        '001422208382:1150254121:840327458',201) AS VARCHAR
        (100))
        FROM sysibm.sysdummy1
        ---------------------------------
        1
        6254255344720965730736023131648487521849252826734674
        610665

Data Type Conversion UDFs

Our encryption and decryption UDFs automatically convert the input data to VARCHAR FOR BIT DATA using the conversion UDFs 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

Generate Random (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

Suggest A Change

User Defined Functions

Encryption and Decryption UDFs

Standard Encryption UDFs

Format Preserving Encryption UDFs

How to call UDFs

Direct Call to ing_e_chr_cl430 for Standard Encryption

Direct Call to ing_d_chr_cl430 for Standard Encryption

Direct Call to ing_e_vrc_fcl430 for FPE Encryption

Direct Call to ing_d_vchr_cl430 for FPE Encryption

Data Type Conversion UDFs

(ing_get_property)

Generate Random (ing_gn_rndm)

On this page