User Defined Functions and APIs

• 1: DB2 User Defined Functions and APIs
• 1.1: General UDFs
• 1.2: Access Check UDFs
• 1.3: VarChar UDFs
• 1.4: VarChar for Bit Data UDFs
• 1.5: Char UDFs
• 1.6: Char for Bit Data UDFs
• 1.7: Long VarChar UDFs
• 1.8: Long VarChar for Bit Data UDFs
• 1.9: Date UDFs
• 1.10: Timestamp UDFs
• 1.11: Time UDFs
• 1.12: Integer UDFs
• 1.13: SmallInt UDFs
• 1.14: BigInt UDFs
• 1.15: Real UDFs
• 1.16: Double UDFs
• 1.17: Blob UDFs
• 1.18: Clob UDFs
• 2: Oracle User Defined Functions and APIs
• 2.1: General UDFs
• 2.2: Access Check Procedures
• 2.3: Insert Encryption UDFs
• 2.4: Insert No-Encryption, Token, and FPE UDFs
• 2.5: Multiple Insert Encryption Procedures
• 2.6: Select Decryption UDFs
• 2.7: Select No-Encryption, Token, and FPE UDFs
• 2.8: Update Encryption UDFs
• 2.9: Update No-Encryption, Token, and FPE UDFs
• 2.10: Multiple Update Encryption Procedures
• 2.11: Hash UDFs
• 2.12: Blob UDFs
• 2.13: Clob UDFs
• 2.14: Bulk UDFs
• 2.15: Oracle Input Datatype to UDF Mapping
• 3: MSSQL User Defined Functions and APIs
• 3.1: General Functions
• 3.2: Insert Procedures
• 3.3: UPDATE Extended Stored Procedures
• 3.4: Access Check Procedures
• 3.5: Select Functions and Procedures
• 3.6: VARCHAR UDFs
• 3.7: NVARCHAR UDFs
• 3.8: Integer UDFs
• 3.9: BLOB UDFs
• 3.10: CLOB UDFs

1 - DB2 User Defined Functions and APIs

This section provides a detailed list of functions and extended stored procedures for general functions, and protection and unprotection of different data types.

1.1 - General UDFs

This section includes list of general UDFs that can be used to retrieve the DB2 Protector version and the current user.

pty.whoami

The UDF returns the name of the user.

Signature:

pty.whoami()

Parameters:
None

Result:

• This UDF returns the name of user logged on to the database as VARCHAR(256).

Example:

SELECT pty.whoami() FROM SYSIBM.SYSDUMMY1;

pty.getversion

The UDF returns the version number of the DB2 protector.

Signature:

pty.getversion()

Parameters:
None

Result:

• The UDF returns the version number of the DB2 protector as VARCHAR(256).

Example:

SELECT pty.getversion() FROM SYSIBM.SYSDUMMY1;

pty.getcoreversion

The UDF returns the core library version, used with the DB2 protector.

Signature:

pty.getcoreversion()

Parameters:
None

Result:

• The UDF returns the core library version, used with the DB2 protector, as VARCHAR(256).

Example:

SELECT pty.getcoreversion() FROM SYSIBM.SYSDUMMY1;

1.2 - Access Check UDFs

These UDFs are used to check access permissions. The functions returns 1 if the user has access. Otherwise, it returns a 0 (zero).

pty.have_sel_perm

The UDF verifies whether the user has select or unprotect access to the data element.

Signature:

pty.have_sel_perm(communicationid INTEGER, dataelement VARCHAR)

Parameters:

Result:

• The UDF returns either of the following values:
  • 1 - If the user has select or unprotect access.
  • 0 - If the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.have_sel_perm(0, 'NoEncryption') FROM SYSIBM.SYSDUMMY1;

pty.have_upd_perm

The UDF verifies whether the user has update or reprotect access to the data element.

Signature:

pty.have_upd_perm(communicationid INTEGER, dataelement VARCHAR)

Parameters:

Result:

• The UDF returns either of the following values:
  • 1 - If the user has update or reprotect access.
  • 0 - If the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.have_upd_perm(0, 'NoEncryption') FROM SYSIBM.SYSDUMMY1;

pty.have_ins_perm

The UDF verifies whether the user has insert or protect access to the data element.

Signature:

pty.have_ins_perm(communicationid INTEGER, dataelement VARCHAR)

Parameters:

Result:

• The UDF returns either of the following values:
  • 1 - If the user has insert or protect access.
  • 0 - If the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.have_ins_perm(0, 'NoEncryption') FROM SYSIBM.SYSDUMMY1;

1.3 - VarChar UDFs

The UDFs, listed in this section, protect and unprotect VARCHAR data.

pty.ins_enc_varchar

The UDF encrypts the VARCHAR data with an encryption data element.

Signature:

pty.ins_enc_varchar(communicationid INTEGER, dataelement VARCHAR, data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_varchar(0, 'AES128', 'Protegrity', 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_varchar

The UDF re-encrypts the VARCHAR data that is updated with an encryption data element.

Signature:

pty.upd_enc_varchar(communicationid INTEGER, dataelement VARCHAR, data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_varchar(0, 'AES128', 'Protegrity', 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_varchar

The UDF decrypts the encrypted VARCHAR data with a data element.

Signature:

pty.sel_dec_varchar(communicationid INTEGER, dataelement VARCHAR, data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted value as VARCHAR(32672).
• The UDF returns the unprotected value as NULL when the user has no unprotect access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_varchar(0, 'AES128', pty.ins_enc_varchar(0, 'AES128', 'Protegrity', 0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_varchar

The UDF protects the VARCHAR value using the Tokenization and No Encyrption data elements.

Signature:

pty.ins_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(32672).

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.ins_varchar(0, 'TE_A_S13_L1R2_Y', 'Protegrity', 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_varchar

The UDF re-protects the VARCHAR data using Tokenization and NoEncryption data elements.

Signature:

pty.upd_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(32672).

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_varchar(0, 'TE_A_S13_L1R2_Y', 'Protegrity', 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_varchar

The UDF unprotects the protected VARCHAR value using Tokenization and NoEncryption data elements.

Signature:

pty.sel_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(32672).
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.sel_varchar(0, 'TE_A_S13_L1R2_Y',pty.ins_varchar(0, 'TE_A_S13_L1R2_Y', 'Protegrity', 0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_hash_varchar

The UDF uses the hash function to protect the VARCHAR data with a data element for hashing to return a protected value.

Signature:

pty.ins_hash_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the hash value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_hash_varchar(0, 'HMAC_SHA256', 'Protegrity', 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_hash_varchar

The UDF uses the hash function to protect the VARCHAR data with a data element for hashing to return a protected value.

Signature:

pty.upd_hash_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the hashed value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_hash_varchar(0, 'HMAC_SHA256', 'Protegrity', 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_unicode_varchar

The UDF protects the VARCHAR data with the following data elements:

• Tokenization types such as Unicode Gen2
• Format Preserving Encryption (FPE) with the Plaintext Encoding type as UTF-8
• No Encryption

Signature:

pty.ins_unicode_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(32672).

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for FPE:

SELECT pty.ins_unicode_varchar(0,'FPE_FF1_AES256_UTF8_APIP_AN_L0R0_ASTNE_ML2','ProtegrityIndia',0) FROM SYSIBM.SYSDUMMY1;

Example for Unicode Gen2:

Note: The Unicode Gen2 data elements support the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer. For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

SELECT pty.ins_unicode_varchar(0,'TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0) FROM SYSIBM.SYSDUMMY1;

SELECT pty.ins_unicode_varchar(0,'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0) FROM SYSIBM.SYSDUMMY1;

pty.upd_unicode_varchar

The UDF reprotects the VARCHAR data with the following data elements:

• Tokenization types such as Unicode Gen2
• No Encryption

Signature:

pty.upd_unicode_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(32672).

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Unicode Gen2:

SELECT pty.upd_unicode_varchar(0,'TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0) FROM SYSIBM.SYSDUMMY1;

SELECT
pty.upd_unicode_varchar(0,'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0) FROM SYSIBM.SYSDUMMY1;

pty.sel_unicode_varchar

The UDF unprotects the protected VARCHAR data with the following data elements:

• Tokenization types such as Unicode Gen2
• No Encryption

Signature:

pty.sel_unicode_varchar(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(32672).
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and user does not have unprotect access to the data.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for FPE:

SELECT pty.sel_unicode_varchar(0, 'FPE_FF1_AES256_APIP_A_L0R0_ASTNI_ML2', 'Protegrity', 0) FROM SYSIBM.SYSDUMMY1;

Example for Unicode Gen2:

Note: The Unicode Gen2 data elements support the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer. For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

SELECT pty.sel_unicode_varchar(0, 'TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES', N'xyzÀÁÂÃÄÅÆÇÈÉÊ', 0) FROM SYSIBM.SYSDUMMY1;

SELECT pty.sel_unicode_varchar(0, 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', N'ϠϡϢϣϥϦ', 0) FROM SYSIBM.SYSDUMMY1;

1.4 - VarChar for Bit Data UDFs

The UDFs, listed in this section, protect and unprotect VARCHAR FOR BIT DATA.

pty.ins_enc_varcharfbd

The UDF encrypts the VARCHAR FOR BIT DATA value with an encryption data element.

Signature:

pty.ins_enc_varcharfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_varcharfbd(0, 'AES128', CAST( 'Protegrity' AS VARCHAR(10) FOR BIT DATA ), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_varcharfbd

The UDF re-encrypts the VARCHAR FOR BIT DATA value that is updated with an encryption data element.

Signature:

pty.upd_enc_varcharfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_varcharfbd(0, 'AES128', CAST( 'Protegrity' AS VARCHAR(10) FOR BIT DATA ), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_varcharfbd

The UDF decrypts the VARCHAR FOR BIT DATA value with a data element.

Signature:

pty.sel_dec_varcharfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_varcharfbd(0, 'AES128', pty.ins_enc_varcharfbd(0, 'AES128', CAST('Protegrity' AS VARCHAR(10) FOR BIT DATA), 0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_varcharfbd

The UDF protects the VARCHAR FOR BIT DATA value with Tokenization and NoEncryption data elements.

Signature:

pty.ins_varcharfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_varcharfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS VARCHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.upd_varcharfbd

The UDF re-protects the VARCHAR FOR BIT DATA value that is updated with a Tokenization and NoEncryption data element.

Signature:

pty.upd_varcharfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data value as VARCHAR(32672) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_varcharfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS VARCHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.sel_varcharfbd

The UDF unprotects the protected VARCHAR FOR BIT DATA value with the following data elements:

• Tokenization
• Format Preserving Encryption(FPE) with the Plaintext Encoding type as ASCII
• NoEncryption

Signature:

pty.sel_varcharfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data value as VARCHAR(32672) FOR BIT DATA.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_varcharfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS VARCHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

1.5 - Char UDFs

The UDFs, listed in this section, protect and unprotect CHAR data.

pty.ins_enc_char

The UDF protects the CHAR data with an encryption data element.

Signature:

pty.ins_enc_char(comm_id INTEGER, dataelement VARCHAR, input_data CHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted value as VARCHAR(290) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_char(0,'AES128','Protegrity',0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_char

The UDF re-protects the CHAR data that is updated with an encryption data element.

Signature:

pty.upd_enc_char(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted value as VARCHAR(290) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_char(0,'AES128','Protegrity',0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_char

The UDF unprotects the protected CHAR data with a data element.

Signature:

pty.sel_dec_char(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(254).

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_char(0,'AES128',pty.ins_enc_char(0,'AES128','Protegrity',0),0) FROM SYSIBM.SYSDUMMY1;

pty.ins_char

The UDF protects the CHAR data with Tokenization and NoEncryption data elements.

Caution: The pty.ins_char UDF cannot be used for a FPE data element.

Signature:

pty.ins_char(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(305).

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_char(0,'TE_A_S13_L1R2_Y','Protegrity',0) FROM SYSIBM.SYSDUMMY1;

pty.upd_char

The UDF re-protects the CHAR data with Tokenization and NoEncryption data elements.

Caution: The pty.upd_char UDF cannot be used for a FPE data element.

Signature:

pty.upd_char(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the protected or tokenized value as VARCHAR(305).

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_char(0,'TE_A_S13_L1R2_Y','Protegrity',0) FROM SYSIBM.SYSDUMMY1;

pty.sel_char

The UDF unprotects the protected CHAR data with Tokenization and NoEncryption data elements.

Caution: The pty.sel_char UDF cannot be used for a FPE data element.

Signature:

pty.sel_char(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(254).
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_char(0,'TE_A_S13_L1R2_Y','Protegrity',0) FROM SYSIBM.SYSDUMMY1;

1.6 - Char for Bit Data UDFs

The UDFs, listed in this section, protect and unprotect CHAR FOR BIT DATA.

pty.ins_enc_charfbd

The UDF encrypts the CHAR FOR BIT DATA value with an encryption data element.

Signature:

pty.ins_enc_charfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(290) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_charfbd(0,'AES128',CAST('Protegrity' AS CHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_charfbd

The UDF re-encrypts the CHAR FOR BIT DATA value that is updated with an encryption data element.

Signature:

pty.upd_enc_charfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(290) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_charfbd(0,'AES128',CAST('Protegrity' AS CHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_charfbd

The UDF decrypts the CHAR FOR BIT DATA value with a data element.

Signature:

pty.sel_dec_charfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as VARCHAR(254) FOR BIT DATA.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_charfbd (0, 'AES128', pty.ins_enc_charfbd(0,'AES128',CAST('Protegrity' AS CHAR(10) FOR BIT DATA),0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_charfbd

The UDF protects the CHAR FOR BIT DATA value with Tokenization and NoEncryption data elements.

Signature:

pty.ins_charfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(254) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_charfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS CHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.upd_charfbd

The UDF re-protects the CHAR FOR BIT DATA value with Tokenization and NoEncryption data elements.

Signature:

pty.upd_charfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(254) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_charfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS CHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.sel_charfbd

The UDF unprotects the protected CHAR FOR BIT DATA value with Tokenization and NoEncryption data elements.

Signature:

pty.sel_charfbd(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as VARCHAR(254) FOR BIT DATA.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_charfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS CHAR(10) FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

1.7 - Long VarChar UDFs

The UDFs, listed in this section, protect and unprotect LONG VARCHAR data.

pty.ins_enc_lvarchar

The UDF encrypts the LONG VARCHAR data with an encryption data element.

Signature:

pty.ins_enc_lvarchar(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as LONG VARCHAR FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_lvarchar(0,'AES128', CAST('Protegrity' AS LONG VARCHAR),0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_lvarchar

The UDF re-encrypts the LONG VARCHAR data that is updated with an encryption data element.

Signature:

pty.upd_enc_lvarchar(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as LONG VARCHAR FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_lvarchar(0,'AES128', CAST('Protegrity' AS LONG VARCHAR),0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_lvarchar

The UDF decrypts the LONG VARCHAR data with a data element.

Signature:

pty.sel_dec_lvarchar(comm_id INTEGER, dataelement VARCHAR(64), input_data LONG VARCHAR FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as LONG VARCHAR.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_lvarchar(0,'AES128', pty.ins_enc_lvarchar(0,'AES128', CAST('Protegrity' AS LONG VARCHAR),0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_lvarchar

The UDF protects the LONG VARCHAR data with Tokenization and NoEncryption data elements.

Signature:

pty.ins_lvarchar(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the protected (tokenized) data as LONG VARCHAR.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_lvarchar(0,'TE_A_S13_L1R2_Y', CAST('Protegrity' AS LONG VARCHAR),0) FROM SYSIBM.SYSDUMMY1;

pty.upd_lvarchar

The UDF re-protects the LONG VARCHAR data with Tokenization and NoEncryption data elements.

Signature:

pty.upd_lvarchar(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the protected (tokenized) data as LONG VARCHAR.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_lvarchar(0,'TE_A_S13_L1R2_Y', CAST('Protegrity' AS LONG VARCHAR),0) FROM SYSIBM.SYSDUMMY1;

pty.sel_lvarchar

The UDF unprotects the protected LONG VARCHAR data with Tokenization and NoEncryption data elements.

Signature:

pty.sel_lvarchar(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as LONG VARCHAR.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_lvarchar(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS LONG VARCHAR),0) FROM SYSIBM.SYSDUMMY1;

1.8 - Long VarChar for Bit Data UDFs

The UDFs, listed in this section, protect and unprotect LONG VARCHAR FOR BIT DATA.

pty.ins_enc_lvcfbd

The UDF encrypts the LONG VARCHAR FOR BIT DATA value with an encryption data element.

Signature:

pty.ins_enc_lvcfbd(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as LONG VARCHAR FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_lvcfbd(0,'AES128',CAST('Protegrity' AS LONG VARCHAR FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_lvcfbd

The UDF re-encrypts the LONG VARCHAR FOR BIT DATA value that is updated with an encryption data element.

Signature:

pty.upd_enc_lvcfbd(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as LONG VARCHAR FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_lvcfbd(0,'AES128',CAST('Protegrity' AS LONG VARCHAR FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_lvcfbd

The UDF decrypts the LONG VARCHAR FOR BIT DATA value with a data element.

Signature:

pty.sel_dec_lvcfbd(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as LONG VARCHAR FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_lvcfbd(0,'AES128', pty.ins_enc_lvcfbd(0,'AES128',CAST('Protegrity' AS LONG VARCHAR FOR BIT DATA),0),0) FROM SYSIBM.SYSDUMMY1;

pty.ins_lvcfbd

The UDF protects the LONG VARCHAR FOR BIT DATA value with Tokenization and NoEncryption data elements.

Signature:

pty.ins_lvcfbd(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the protected/tokenized data as LONG VARCHAR FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_lvcfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS LONG VARCHAR FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.upd_lvcfbd

The UDF re-protects the LONG VARCHAR FOR BIT DATA value with Tokenization and NoEncryption data elements.

Signature:

pty.upd_lvcfbd(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as LONG VARCHAR FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_lvcfbd(0,'TE_A_S13_L1R2_Y',CAST('Protegrity' AS LONG VARCHAR FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

pty.sel_lvcfbd

The UDF unprotects the protected LONG VARCHAR FOR BIT DATA value with Tokenization and NoEncryption data elements.

Signature:

pty.sel_lvcfbd(comm_id INTEGER, dataelement VARCHAR, input_data LONG VARCHAR FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as LONG VARCHAR FOR BIT DATA.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_lvcfbd(0,'TE_A_S13_L1R2_Y', CAST('Protegrity' AS LONG VARCHAR FOR BIT DATA),0) FROM SYSIBM.SYSDUMMY1;

1.9 - Date UDFs

The UDFs, listed in this section, protect and unprotect DATE data.

pty.ins_enc_date

The UDF encrypts a DATE value with an encryption data element.

Signature:

pty.ins_enc_date(comm_id INTEGER, dataelement VARCHAR, input_data DATE, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_date(0,'AES128',current_date,0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_date

The UDF re-encrypts the DATE value that is updated with an encryption data element.

Signature:

pty.upd_enc_date(comm_id INTEGER, dataelement VARCHAR, input_data DATE, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_date(0,'AES128',current_date,0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_date

The UDF decrypts the DATE value with a data element.

Signature:

pty.sel_dec_date(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR(34) FOR BIT DATA, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as DATE.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_date(0,'AES128',pty.ins_enc_date(0,'AES128',current_date,0),0) FROM SYSIBM.SYSDUMMY1;

pty.ins_date

The UDF protects the DATE value with Tokenization and NoEncryption data elements.

Signature:

pty.ins_date(comm_id INTEGER, dataelement VARCHAR, input_data DATE, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as DATE.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_date(0,'TE_Datetime_TY_DY_N',current_date,0) FROM SYSIBM.SYSDUMMY1;

pty.upd_date

The UDF re-protects the DATE value with Tokenization and NoEncryption data elements.

Signature:

pty.upd_date(comm_id INTEGER, dataelement VARCHAR, input_data DATE, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as DATE.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_date(0,'TE_Date_YMD_S13',current_date,0) FROM SYSIBM.SYSDUMMY1;

pty.sel_date

The UDF unprotects the protected DATE value with Tokenization and NoEncryption data elements.

Signature:

pty.sel_date(comm_id INTEGER, dataelement VARCHAR, input_data DATE, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as DATE.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_date(0,'TE_Datetime_TY_DY_N',pty.ins_date(0,'TE_Datetime_TY_DY_N',current_date,0),0) FROM SYSIBM.SYSDUMMY1;

1.10 - Timestamp UDFs

The UDFs, listed in this section, protect and unprotect TIMESTAMP data.

pty.ins_enc_timestamp

The UDF encrypts a TIMESTAMP value with an encryption data element.

Signature:

pty.ins_enc_timestamp(comm_id INTEGER, dataelement VARCHAR, input_data TIMESTAMP, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(50) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_timestamp(0, 'AES128', current_timestamp, 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_timestamp

The UDF re-encrypts a TIMESTAMP value with an encryption data element.

Signature:

pty.upd_enc_timestamp(comm_id INTEGER, dataelement VARCHAR, input_data TIMESTAMP, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(50) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_timestamp(0, 'AES128', current_timestamp, 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_timestamp

The UDF decrypts the TIMESTAMP value with a data element.

Signature:

pty.sel_dec_timestamp(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as TIMESTAMP.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_timestamp(0, 'AES128', pty.ins_enc_timestamp(0,'AES128',current_timestamp,0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_timestamp

The UDF protects the TIMESTAMP value with Tokenization and NoEncryption data elements.

Signature:

pty.ins_timestamp(comm_id INTEGER, dataelement VARCHAR, input_data TIMESTAMP, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as TIMESTAMP.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_timestamp(0,'TE_Datetime_TY_DY_N',current_timestamp,0) FROM SYSIBM.SYSDUMMY1;

pty.upd_timestamp

The UDF re-protects the TIMESTAMP value with the No Encryption data element.

Signature:

pty.upd_timestamp(comm_id INTEGER, dataelement VARCHAR, input_data TIMESTAMP, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as TIMESTAMP.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_timestamp(0, 'NoEncryption', current_timestamp, 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_timestamp

The UDF unprotects the protected TIMESTAMP value with Tokenization and NoEncryption data elements.

Signature:

pty.sel_timestamp(comm_id INTEGER, dataelement VARCHAR, input_data TIMESTAMP, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as TIMESTAMP.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_timestamp(0,'TE_Datetime_TY_DY_N',pty.ins_timestamp(0,'TE_Datetime_TY_DY_N',current_timestamp,0),0) FROM SYSIBM.SYSDUMMY1;

1.11 - Time UDFs

The UDFs, listed in this section, protect and unprotect TIME data.

pty.ins_enc_time

The UDF encrypts TIME value with an encryption data element.

Signature:

pty.ins_enc_time(comm_id INTEGER, dataelement VARCHAR, input_data TIME, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_time(0,'AES128',current_time,0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_time

The UDF re-encrypts the TIME value that is updated with an encryption data element.

Signature:

pty.upd_enc_time(comm_id INTEGER, dataelement VARCHAR, input_data TIME, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_time(0,'AES128',current_time,0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_time

The UDF decrypts the TIME value with a data element.

Signature:

pty.sel_dec_time(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as TIME.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_time(0, 'AES128', pty.ins_enc_time(0,'AES128',current_time,0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_time

The UDF protects the TIME value with a NoEncryption data element.

Signature:

pty.ins_time(comm_id INTEGER, dataelement VARCHAR, input_data TIME, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as TIME.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_time(0,'NoEncryption','09:42:00',0) FROM SYSIBM.SYSDUMMY1;

pty.upd_time

The UDF re-protects the TIME value with a NoEncryption data element.

Signature:

pty.upd_time(comm_id INTEGER, dataelement VARCHAR, input_data TIME, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as TIME.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_time(0,'NoEncryption',current_time,0) FROM SYSIBM.SYSDUMMY1;

pty.sel_time

The UDF unprotects the protected TIME value with a NoEncryption data element.

Signature:

pty.sel_time(comm_id INTEGER, dataelement VARCHAR, input_data TIME, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as TIME.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_time(0,'NoEncryption',current_time,0) FROM SYSIBM.SYSDUMMY1;

1.12 - Integer UDFs

The UDFs, listed in this section, protect and unprotect INTEGER data.

pty.ins_enc_integer

The UDF protects the INTEGER data with a data element for encryption.

Signature:

pty.ins_enc_integer(comm_id INTEGER, dataelement VARCHAR, input_data INTEGER, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_integer(0, 'AES128', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_integer

The UDF re-encrypts the INTEGER data that is updated with an encryption data element.

Signature:

pty.upd_enc_integer(comm_id INTEGER, dataelement VARCHAR, input_data INTEGER, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_integer(0, 'AES128', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_integer

The UDF decrypts the INTEGER data with a data element.

Signature:

pty.sel_dec_integer(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as INTEGER.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_integer(0, 'AES128', pty.ins_enc_integer(0, 'AES128', CAST(123456 AS INTEGER), 0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_integer

The UDF protects the INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.ins_integer(comm_id INTEGER, dataelement VARCHAR, input_data INTEGER, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as INTEGER.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for NoEncryption:

SELECT pty.ins_integer(0, 'NoEncryption', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

Example for Tokenization:

SELECT pty.ins_integer(0, 'TE_INT_4', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_integer

The UDF re-protects the INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.upd_integer(comm_id INTEGER, dataelement VARCHAR, input_data INTEGER, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as INTEGER.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for NoEncryption:

SELECT pty.upd_integer(0, 'NoEncryption', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

Example for Tokenization:

SELECT pty.upd_integer(0, 'TE_INT_4', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_integer

The UDF unprotects the protected INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.sel_integer(comm_id INTEGER, dataelement VARCHAR, input_data INTEGER, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as INTEGER.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for NoEncryption:

SELECT pty.sel_integer(0, 'NoEncryption', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

Example for Tokenization:

SELECT pty.sel_integer(0, 'TE_INT_4', CAST(123456 AS INTEGER), 0) FROM SYSIBM.SYSDUMMY1;

1.13 - SmallInt UDFs

The UDFs, listed in this section, protect and unprotect SMALLINT data.

pty.ins_enc_smallint

The UDF encrypts the SMALL INTEGER data with a data element for encryption.

Signature:

pty.ins_enc_smallint(comm_id INTEGER, dataelement VARCHAR, input_data SMALLINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_smallint(0, 'AES128', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_smallint

The UDF re-encrypts the SMALL INTEGER data that is updated with a data element for encryption.

Signature:

pty.upd_enc_smallint(comm_id INTEGER, dataelement VARCHAR, input_data SMALLINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_smallint(0, 'AES128', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_smallint

The UDF decrypts the SMALL INTEGER data with a data element.

Signature:

pty.sel_dec_smallint(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as SMALLINT.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_smallint(0,'AES128',pty.ins_enc_smallint(0,'AES128',CAST(1234 AS SMALLINT),0),0) FROM SYSIBM.SYSDUMMY1;

pty.ins_smallint

The UDF protects the SMALL INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.ins_smallint(comm_id INTEGER, dataelement VARCHAR, input_data SMALLINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as SMALLINT.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.ins_smallint(0, 'TE_INT_2', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

Example for NoEncryption:

SELECT pty.ins_smallint(0, 'NoEncryption', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_smallint

The UDF re-protects the SMALL INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.upd_smallint(comm_id INTEGER, dataelement VARCHAR, input_data SMALLINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as SMALLINT.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.upd_smallint(0, 'TE_INT_SLT13_2', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

Example for NoEncryption:

SELECT pty.upd_smallint(0, 'NoEncryption', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_smallint

The UDF unprotects the protected SMALL INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.sel_smallint(comm_id INTEGER, dataelement VARCHAR, input_data SMALLINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as SMALLINT.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.sel_smallint(0, 'TE_INT_2', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

Example for NoEncryption:

SELECT pty.sel_smallint(0, 'NoEncryption', CAST(1234 AS SMALLINT), 0) FROM SYSIBM.SYSDUMMY1;

1.14 - BigInt UDFs

The UDFs, listed in this section, protect and unprotect BIGINT data.

pty.ins_enc_bigint

The UDF encrypts the BIG INTEGER data with a data element for encryption.

Signature:

pty.ins_enc_bigint(comm_id INTEGER, dataelement VARCHAR, input_data BIGINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_bigint(0, 'AES128', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_bigint

The UDF re-encrypts the BIG INTEGER data that is updated with a data element for encryption.

Signature:

pty.upd_enc_bigint(comm_id INTEGER, dataelement VARCHAR, input_data BIGINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_bigint(0, 'AES128', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_bigint

The UDF decrypts the BIG INTEGER data with a data element.

Signature:

pty.sel_dec_bigint(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as BIGINT.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_bigint(0, 'AES128', pty.ins_enc_bigint(0, 'AES128', CAST(12345678 AS BIGINT), 0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_bigint

The UDF protects the BIG INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.ins_bigint(comm_id INTEGER, dataelement VARCHAR, input_data BIGINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as BIGINT.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.ins_bigint(0, 'TE_INT_8_BIGINT', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

Example for NoEncryption:

SELECT pty.ins_bigint(0, 'NoEncryption', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_bigint

The UDF re-protects the BIG INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.upd_bigint(comm_id INTEGER, dataelement VARCHAR, input_data BIGINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as BIGINT.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.upd_bigint(0, 'TE_INT_8', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

Example for NoEncryption:

SELECT pty.upd_bigint(0, 'NoEncryption', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_bigint

The UDF unprotects the protected BIG INTEGER data with Tokenization and NoEncryption data elements.

Signature:

pty.sel_bigint(comm_id INTEGER, dataelement VARCHAR, input_data BIGINT, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as BIGINT.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example for Tokenization:

SELECT pty.sel_bigint(0, 'TE_INT_8', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

Example for NoEncryption:

SELECT pty.sel_bigint(0, 'NoEncryption', CAST(12345678 AS BIGINT), 0) FROM SYSIBM.SYSDUMMY1;

1.15 - Real UDFs

The UDFs, listed in this section, protect and unprotect REAL data.

pty.ins_enc_real

The UDF encrypts REAL data with a data element for encryption.

Signature:

pty.ins_enc_real(comm_id INTEGER, dataelement VARCHAR, input_data REAL, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_real(0, 'AES128', CAST(123456 AS REAL), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_real

The UDF re-encrypts the REAL data that is updated with a data element for encryption.

Signature:

pty.upd_enc_real(comm_id INTEGER, dataelement VARCHAR, input_data REAL, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_real(0, 'AES128', CAST(123456 AS REAL), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_real

The UDF decrypts the REAL data with a data element.

Signature:

pty.sel_dec_real(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as REAL.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_real(0, 'AES128', pty.ins_enc_real(0,'AES128',CAST(123456 AS REAL),0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_real

The UDF protects the REAL data with a NoEncryption data element.

Signature:

pty.ins_real(comm_id INTEGER, dataelement VARCHAR, input_data REAL, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as REAL.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_real(0, 'NoEncryption', CAST(123456 AS REAL), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_real

The UDF re-protects the REAL data with a NoEncryption data element.

Signature:

pty.upd_real(comm_id INTEGER, dataelement VARCHAR, input_data REAL, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as REAL.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_real(0, 'NoEncryption', CAST(123456 AS REAL), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_real

The UDF unprotects the protected REAL data with a NoEncryption data element.

Signature:

pty.sel_real(comm_id INTEGER, dataelement VARCHAR, input_data REAL, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as REAL.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_real(0, 'NoEncryption', CAST(123456 AS REAL), 0) FROM SYSIBM.SYSDUMMY1;

1.16 - Double UDFs

The UDFs, listed in this section, protect and unprotect DOUBLE data.

pty.ins_enc_double

The UDF encrypts the DOUBLE data with a data element for encryption.

Signature:

pty.ins_enc_double(comm_id INTEGER, dataelement VARCHAR, input_data DOUBLE, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_double(0, 'AES128', CAST(123456 AS DOUBLE), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_double

The UDF re-encrypts the DOUBLE data that is updated with a data element for encryption.

Signature:

pty.upd_enc_double(comm_id INTEGER, dataelement VARCHAR, input_data DOUBLE, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as VARCHAR(34) FOR BIT DATA.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_double(0, 'AES128', CAST(123456 AS DOUBLE), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_double

The UDF decrypts the DOUBLE data with a data element.

Signature:

pty.sel_dec_double(comm_id INTEGER, dataelement VARCHAR, input_data VARCHAR, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as DOUBLE.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_double(0, 'AES128', pty.ins_enc_double(0,'AES128',CAST(123456 AS DOUBLE),0), 0) FROM SYSIBM.SYSDUMMY1;

pty.ins_double

The UDF protects the DOUBLE data with a NoEncryption data element.

Signature:

pty.ins_double(comm_id INTEGER, dataelement VARCHAR, input_data DOUBLE, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as DOUBLE.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_double(0, 'NoEncryption', CAST(123456 AS DOUBLE), 0) FROM SYSIBM.SYSDUMMY1;

pty.upd_double

The UDF re-protects the DOUBLE data with a NoEncryption data element.

Signature:

pty.upd_double(comm_id INTEGER, dataelement VARCHAR, input_data DOUBLE, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as DOUBLE.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_double(0, 'NoEncryption', CAST(123456 AS DOUBLE), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_double

The UDF unprotects the protected DOUBLE data with a NoEncryption data element.

Signature:

pty.sel_double(comm_id INTEGER, dataelement VARCHAR, input_data DOUBLE, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as DOUBLE.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_double(0, 'NoEncryption', CAST(123456 AS DOUBLE), 0) FROM SYSIBM.SYSDUMMY1;

1.17 - Blob UDFs

The UDFs, listed in this section, protect and unprotect BLOB data.

** Note:** The BLOB UDFs support a maximum input size of 2,147,483,576 bytes, which is approximately 2GB. The the protected output can be up to 2,147,483,610 bytes. Large operations are memory intensive. For example, 2 GB operation can require several gigabytes of memory per member. Ensure the instance is sized adequately. For example, the INSTANCE_MEMORY should be 16 GB or higher. Otherwise, the UDFs may fail with the SQL0930N error.

pty.ins_enc_blob

The UDF encrypts the BLOB data with a data element for encryption.

Signature:

pty.ins_enc_blob(comm_id INTEGER, dataelement VARCHAR, input_data BLOB, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as BLOB 2147483576 bytes.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_enc_blob(0,'AES128', CAST('Protegrity' AS BLOB(1M)), 0)  FROM SYSIBM.SYSDUMMY1;

pty.upd_enc_blob

The UDF re-encrypts the BLOB data that is updated with a data element for encryption.

Signature:

pty.upd_enc_blob(comm_id INTEGER, dataelement VARCHAR, input_data BLOB, scid INTEGER)

Parameters:

Result:

• The UDF returns the encrypted data as BLOB (102434) bytes.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_enc_blob(0, 'AES128', BLOB('blob_db2.jpg'), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_dec_blob

The UDF decrypts the BLOB data with a data element.

Signature:

pty.sel_dec_blob(comm_id INTEGER, dataelement VARCHAR, input_data BLOB, scid INTEGER)

Parameters:

Result:

• The UDF returns the decrypted data as BLOB 100K.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_dec_blob(0,'AES128',pty.ins_enc_blob(0,'AES128', CAST('Protegrity' AS BLOB(1M)), 0),0)  FROM SYSIBM.SYSDUMMY1;

pty.ins_blob

The UDF protects the BLOB data with a NoEncryption data element.

Signature:

pty.ins_blob(comm_id INTEGER, dataelement VARCHAR, input_data BLOB, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as BLOB 2147483576 Bytes.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.ins_blob(0,'NoEncryption', CAST('Protegrity' AS BLOB(1M)), 0)  FROM SYSIBM.SYSDUMMY1;

pty.upd_blob

The UDF re-protects the BLOB data with a NoEncryption data element.

Signature:

pty.upd_blob(comm_id INTEGER, dataelement VARCHAR, input_data BLOB, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as BLOB 100K.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.upd_blob(0, 'NoEncryption', BLOB('blob_db2.jpg'), 0) FROM SYSIBM.SYSDUMMY1;

pty.sel_blob

The UDF unprotects the protected BLOB data with a NoEncryption data element.

Signature:

pty.sel_blob(comm_id INTEGER, dataelement VARCHAR, input_data BLOB, scid INTEGER)

Parameters:

Result:

• The UDF returns the data as BLOB 2147483576 Bytes.
• The UDF returns the unprotected value as NULL, when the user has no unprotect access to the data in the policy.
• The UDF returns the protected value, if this option is configured in the policy and the user does not have unprotect access to the data.
• The UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty.sel_blob(0,'NoEncryption',pty.ins_blob(0,'NoEncryption', CAST('Protegrity' AS BLOB(1M)), 0),0)  FROM SYSIBM.SYSDUMMY1;

1.18 - Clob UDFs

The UDFs, listed in this section, protect and unprotect CLOB data.

Note: The CLOB UDFs support a maximum input size of 2,147,483,576 bytes, which is approximately 2GB. The protected output can be up to 2,147,483,610 bytes. Use the BLOB UDFs for binary data like images, audio, and video. This is because the CLOB codepage conversion can corrupt binary content. Large operations are memory intensive. For example, 2 GB operation can require several gigabytes of memory per member. Ensure the instance is sized adequately. For example, the INSTANCE_MEMORY should be 16 GB or higher. Otherwise, the UDFs may fail with the SQL0930N error.

pty.ins_enc_clob

The UDF encrypts the CLOB data with a data element for encryption.

Signature:

pty.ins_enc_clob(comm_id INTEGER, dataelement VARCHAR, input_data CLOB, scid INTEGER)

Parameters: