Start of change

HASH_algorithm scalar function

The HASH_algorithm functions return a fixed-length value that is the result of applying a hash algorithm to an input argument. The functions are intended for cryptographic purposes.

Start of change
Tip: For better security, use the HASH_SHA256 scalar function. The following functions are deprecated because the encryption algorithms used are not considered quantum safe: HASH_CRC32, HASH_MD5, and HASH_SHA1. For more information, see Deprecated functions: HASH_CRC32, HASH_MD5, and HASH_SHA1.
End of change

Syntax

Read syntax diagramSkip visual syntax diagramHASH_SHA256( expression)

The schema is SYSIBM.

expression
Start of changeAn expression that represents the string value that is to be hashed. The expression must return a built-in character string, graphic string, or binary string.End of change

The result is produced by applying the hash algorithm to expression. The hash algorithm is determined by the name of the function that is invoked.

The result of the function is BINARY, and the length attribute of the result depends on the hash algorithm specified. The characteristics of the result are summarized in the following table.

Table 1. Characteristics of the result for each algorithm
Function name Algorithm Result size Number of returnable values Result data type
HASH_SHA256 SHA2-256 256 bits 2256 BINARY(32)

If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.

Notes

Deprecated functions: HASH_CRC32, HASH_MD5, and HASH_SHA1

The following forms of the HASH_algorithm scalar function are deprecated because they are not considered quantum safe. For better security, use HASH_SHA256 instead.

Read syntax diagramSkip visual syntax diagramHASH_CRC32HASH_MD5HASH_SHA1( expression)
Table 2. Characteristics of the result for each algorithm
Function name Algorithm Result size Number of returnable values Result data type
HASH_CRC32 CRC32 32 bits 232 BINDARY(4)
HASH_MD5 MD5 128 bits 2128 BINARY(16)
HASH_SHA1 SHA1 160 bits 2160 BINARY(20)
Quantum-safe encryption and decryption functions
Certain built-in scalar functions for encryption or decryption are deprecated because the encryption algorithms used are not considered quantum safe. These functions remain supported, but their use is no longer recommended in Db2 12, and alternatives that use quantum-safe algorithms should be used instead.
Quantum-safe functions Deprecated functions
  • ENCRYPT_DATAKEY
  • HASH with algorithm 2 (SHA256)
  • HASH_SHA256
  • DECRYPT_DATAKEY_type
  • ENCRYPT_TDES or ENCRYPT
  • HASH with algorithm 0 (MD5) or 1 (SHA1)
  • HASH_CRC32, HASH_MD5, HASH_SHA1
  • DECRYPT_type (any)
Syntax alternatives:
Start of change

The HASH_algorithm scalar function is similar to the following HASH function, where the hash algorithm is specified as an integer constant value. For more information, see HASH scalar function.

HASH ( expression , integer-constant )
End of change

Examples

  • Invoke the HASH_SHA256 function to use the SHA256 algorithm to generate hashed value.

    SELECT HEX(HASH_SHA256('ABCDEFGHIJKLMNOPQRZTUVWXYZ'))
FROM SYSIBM.SYSDUMMYU;

    The following value is returned:

    X'403AC046B04F4A749E9810971083997B71F2B6FAF87CECCDE657E93FFCF700F0'
End of change