Data can be encrypted and decrypted at the column level using the ENCRYPT and DECRYPT SQL scalar functions. A password is needed to encrypt and decrypt the data. The password can either be supplied as part of the ENCRYPT or DECRYPT function, or it can be set within a DB2 special register via the SET ENCRYPTION PASSWORD statement (recommended). A password hint can also be stored with the encrypted data to help “remember” the password. The password hint can be retrieved by the GETHINT function.
Once the data has been stored after being encrypted via the ENCRYPT function, the same password must be used to correctly decrypt the data when retrieving at a later time. If a different password is used for decryption, the data returned will not match the original string. No error or warning message will be returned.
Example of using the ENCRYPT and DECRYPT functions:
Example 1 (no password hint):
SET ENCRYPTION PASSWORD = ‘TARZAN’;
INSERT INTO EMP (SSN) VALUES ENCRYPT (‘289-46-8832’);
SELECT DECRYPT_CHAR(SSN) FROM EMP;
Example 2 (with password hint):
INSERT INTO EMP (SSN) VALUES ENCRYPT (‘289-46-8832’, ‘TARZAN’, ‘? AND JANE’);
SELECT DECRYPT_CHAR(SSN, ‘TARZAN’) FROM EMP;
NOTE: In the LUW environment, data encryption and decryption is already included in the base product. However, on the z/OS environmenrt, the Encryption Facility is a separately licensed product that requires specific hardware, and is not currently available in the Phoenix environment.
