Hash and encryption functions in DB2

HASH and HASH_algorithm

HASH applies a cryptographic hash algorithm to an input and returns binary bytes. Schema is SYSIBM. Named twins HASH_MD5, HASH_SHA1, HASH_SHA256, and HASH_CRC32 exist so ports and new SQL can say the algorithm in the function name.

1
2
3
4
5
HASH(expression [, algorithm])
HASH_MD5(expression)
HASH_SHA1(expression)
HASH_SHA256(expression)
HASH_CRC32(expression)

1
2
3
4
SELECT HASH(LASTNAME, 2) AS SHA256,
       HASH_SHA256(LASTNAME) AS SHA256_NAMED
  FROM DSN8C10.EMP
 WHERE EMPNO = '000010';

Null input yields a null hash. Hashes are for integrity checks, change detection, and (with care) fingerprints. They are not encryption. MD5 and SHA-1 are broken for collision resistance; do not use them for new security designs. SHA-256 is the least-wrong choice in this built-in set. Real password storage belongs in an application library with a slow salted hasher (bcrypt/argon2), not a single SQL HASH call.

HASH4, HASH8, HASH16

Training outlines and Db2 LUW manuals list HASH4, HASH8, and HASH16 as fast checksum lookup functions (INTEGER / BIGINT / 128-bit), with Adler, CRC32, or Jenkins algorithms. The Db2 for z/OS SQL Reference documents HASH and HASH_algorithm instead. On z/OS, use HASH_CRC32 when you want a CRC-style checksum, and HASH / HASH_SHA256 when you want a cryptographic digest. Do not paste LUW HASH4(..., 0) examples into a z/OS package without verifying the name on your function level.

ENCRYPT_DATAKEY and DECRYPT_DATAKEY_type

These are the modern column-encryption functions (Db2 12 function level 505 and later). The checklist names ENCRYPT_DATA / DECRYPT_DATA refer to this family on z/OS.

1
ENCRYPT_DATAKEY(expression, key-label [, 'AES256D' | 'AES256R'])

• expression — value to encrypt (integer, decimal, character, graphic, and LOB forms as documented). Result is VARBINARY or BLOB.
• key-label — ICSF CKDS key label. Knowing the label is useless without RACF authority to use the key. The label is stored as metadata with the ciphertext, so decrypt functions do not take the label again.
• AES256D — deterministic IV: same cleartext encrypts to the same ciphertext (can support equality lookups; weaker against frequency analysis).
• AES256R — random IV: same cleartext encrypts differently each time (stronger; no simple equality search on ciphertext).

Decrypt with a typed function that matches what you encrypted:

• DECRYPT_DATAKEY_INTEGER
• DECRYPT_DATAKEY_BIGINT
• DECRYPT_DATAKEY_DECIMAL (precision and scale)
• DECRYPT_DATAKEY_VARCHAR / CLOB / VARGRAPHIC / DBCLOB (CCSID as documented)
• DECRYPT_DATAKEY_BIT

Encrypting INTEGER and decrypting with DECRYPT_DATAKEY_VARCHAR fails (SQLCODE −171). There is no CHAR decrypt form — strings come back varying-length. Ciphertext is longer than cleartext; size the VARBINARY/BLOB column accordingly.

ENCRYPT_TDES, ENCRYPT, decrypt, and GETHINT (deprecated)

ENCRYPT_TDES (synonym ENCRYPT) applies Triple DES with a password. IBM marks it deprecated: the algorithms are not considered quantum-safe. Prefer ENCRYPT_DATAKEY.

1
2
3
4
5
ENCRYPT_TDES(string-expression [, password-string [, hint-string]])
DECRYPT_CHAR(encrypted, password)
DECRYPT_BIT(encrypted, password)
DECRYPT_DB(encrypted, password)
GETHINT(encrypted-data)

• Password can be the second argument or the ENCRYPTION PASSWORD special register (SET ENCRYPTION PASSWORD ... WITH HINT ...).
• Encrypted columns are VARCHAR (or similar) because the result is binary-looking data plus metadata. Length grows (block padding plus 24 bytes of metadata, plus up to 32 bytes if a hint is stored).
• GETHINT returns the hint that was stored with that encrypted value — a reminder, not the password.

Never hard-code production passwords in SQL text, host variables logged in traces, or tables. That design is exactly why key labels replaced password arguments.

GENERATE_UNIQUE and GENERATE_UNIQUE_BINARY

These functions return a value unique compared with any other execution of the same function. Empty parentheses are required. They are not deterministic. The result cannot be null.

• GENERATE_UNIQUE() — CHAR(13) FOR BIT DATA.
• GENERATE_UNIQUE_BINARY() — BINARY(16).

The value embeds UTC time and, in a sysplex, the member that processed the function. TIMESTAMP(unique-id) recovers that timestamp. Unlike CURRENT TIMESTAMP, each row of a multiple-row INSERT, INSERT with fullselect, or MERGE insert gets a distinct value.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE EMP_UPDATE
( UNIQUE_ID VARCHAR(13) FOR BIT DATA,
  EMPNO     CHAR(6),
  TEXT      VARCHAR(1000) );
 
INSERT INTO EMP_UPDATE
VALUES (GENERATE_UNIQUE(), '000020', 'Update entry 1...');
 
SELECT TIMESTAMP(UNIQUE_ID), EMPNO, TEXT
  FROM EMP_UPDATE;

A BEFORE INSERT trigger can force the column:

1
2
3
4
5
CREATE TRIGGER EMP_UPDATE_UNIQUE
  NO CASCADE BEFORE INSERT ON EMP_UPDATE
  REFERENCING NEW AS NEW_UPD
  FOR EACH ROW MODE DB2SQL
  SET NEW_UPD.UNIQUE_ID = GENERATE_UNIQUE();

What these functions are not

• Not disk encryption — use DFSMS data set encryption / pervasive encryption for tablespaces and logs.
• Not SSL/TLS for DDF — that is AT-TLS / certificate work.
• Not row permissions or column masks — those are separate security objects.

Explain It Like I'm Five

A hash is a blender: you pour in a secret recipe and get a smoothie colour. You can check later whether a new recipe blends to the same colour, but you cannot un-blend the smoothie back into tomatoes. Encryption is a locked lunchbox: ENCRYPT_DATAKEY puts the sandwich in and tags the box with a label of which school key opens it. Only kids RACF says may use that key can open it. The old ENCRYPT_TDES lunchbox used a password written on a sticky note — IBM says stop doing that. GENERATE_UNIQUE is a ticket machine that never gives two people the same number, even when a whole crowd presses the button at once.

Exercises

1. Write HASH_SHA256 and the equivalent HASH(expr, 2) on LASTNAME.
2. Explain why HASH is a bad way to store user passwords compared with a salted application hasher.
3. Contrast AES256D and AES256R for a National Insurance number column that must sometimes be searched.
4. State what GETHINT returns and why it must not be treated as the TDES password.
5. Write GENERATE_UNIQUE into a VARCHAR(13) FOR BIT DATA column and select TIMESTAMP of that column.