HASH scalar function

The HASH function returns a varying-length value that is the result of applying the specified algorithm to the first argument. The function is intended for cryptographic purposes.

Tip: Start of changeUse of 0 (MD5) and 1 (SHA1) for the algorithm parameter of the HASH function is deprecated because the encryption algorithms used are not considered quantum safe. For more information, see HASH_algorithm scalar function.End of change
Read syntax diagramSkip visual syntax diagramHASH( expression,0,algorithm)

The schema is SYSIBM.

expression
An 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.
algorithm
An integer constant value of 0, 1, or 2 that indicates the hash algorithm to be used when the function name is HASH. If no algorithm is specified, the default value of 0 is used which indicates the MD5 algorithm.

The result is produced by applying the hash algorithm, algorithm, to expression.

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

Table 1. Characteristics of the result for each algorithm
Algorithm Value for algorithm parameter Result size Number of different values that can be returned HASH function result data type
MD5 (deprecated) 0 128 bit 2128 VARBINARY(16)
SHA1 (deprecated) 1 160 bit 2160 VARBINARY(20)
SHA256 2 256 bit 2256 VARBINARY(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 encryption and decryption functions
The following built-in scalar functions for encryption or decryption are deprecated because the encryption algorithms used are not considered quantum safe. They remain supported, but their use is no longer recommended in Db2 13, and alternatives that use quantum-safe algorithms should be used instead. For more information, see Deprecated function in Db2 13.
  • ENCRYPT_TDES or ENCRYPT
  • HASH with algorithm 0 (MD5) or 1 (SHA1)
  • HASH_CRC32
  • HASH_MD5
  • HASH_SHA1
  • DECRYPT_BINARY
  • DECRYPT_BIT
  • DECRYPT_CHAR
  • DECRYPT_DB
Security considerations for SHA1 and MD5 algorithms
Security flaws have been identified in both the SHA1 and MD5 algorithms. You can find acceptable hash algorithms in applicable compliance documentation, such as National Institute of Standards and Technology (NIST) Special Publication 800-131A.
Syntax alternatives
The HASH function is similar to the other hashing functions, where the hash algorithm is specified as part of the function name. For example: 
HASH_SHA256 (  expression  )

Invoking the HASH function for hashing is recommended to increase the portability of applications.

Examples

  • Invoke the HASH function to use the MD5 algorithm to generate a hashed value. 

    SELECT HEX(HASH(’ABCDEFGHIJKLMNOPQRZTUVWXYZ’ , 0 )) 
FROM SYSIBM.SYSDUMMYU;

    The following value is returned:

    X’E433BC7BE26A152E54E2EA0C92778160’

  • Invoke the HASH_SHA1 function to use the SHA1 algorithm to generate a hashed value.

    SELECT HEX(HASH(’ABCDEFGHIJKLMNOPQRZTUVWXYZ’, 1 )) 
FROM SYSIBM.SYSDUMMYU;

    The following value is returned:

    X’8F34563A0FA4BA1A285C8035935D010629385474’

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

    SELECT HEX(HASH(’ABCDEFGHIJKLMNOPQRZTUVWXYZ’ , 2 )) 
FROM SYSIBM.SYSDUMMYU;

    The following value is returned:

    X’403AC046B04F4A749E9810971083997B71F2B6FAF87CECCDE657E93FFCF700F0’