Db2 supports three different hash functions, and each of them can use different algorithms. Let’s shred some light on them to understand their use cases.
HASH()
The HASH() function is intended for 1-way cryptography. In other words, its purpose is to hash passwords or other secrets, so that they won’t be stored in clear in the database.
Its return value is always VARBINARY, and the length depends on the chosen algorithm.
Supported algorithms are:
| Algorithm | Value | Size in bits | Secure |
| MD5 | 0 | 128 | INSECURE |
| SHA1 | 1 | 160 | INSECURE |
| SHA2_256 | 2 | 256 | Yes |
| SHA2_512 | 3 | 512 | Yes |
As a general rule, the most secure algorithms are the slowest and the ones that generate bigger hashes. The default is MD5.
Example:
UPDATE USERS SET PASS = HASH('secret', 3)Checksum lookup functions
HASH4() and HASH8() are intended for fast lookup of potentially big values, particularly character strings and byte strings.
For example, you can store a PDF document in a column, and its Jenkins checksum in another column. Then you can find the PDF based on its hash:
SELECT * FROM PDFDOC WHERE PDF_HASH = HASH8('...')HASH4()
HASH4() returns an INTEGER value (32 bits hashes). Supported algorithms are:
- 0 – Adler checksum
- 1 – CRC32
The Adler algorithm is the default. It is faster, but is discouraged for values shorter than a few hundreds hashes.
Modern CPUs run CRC32 fast.
Example:
UPDATE EBOOK SET
TEXT = 'abcde',
CRC = HASH4('abcde', 1)
ADLER = HASH4('abcde', 0)HASH8()
HASH8() returns a BIGINT value (64 bits hashes). Currently it only supports the Jenkins algorithm. Note that the return value varies depending wether Db2 runs on a little-endian or big-endian machine (or virtual machine).
Example:
UPDATE EBOOK SET
TEXT = 'abcde',
JENKINS = HASH8('abcde')Other hash functions
db2-hash-routines is a repository of hash-related used-defined functions written by Helmut K. C. Tessarek. They include a PHP-compatible MD5 function and a function to validate passwords.