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 change For better security, use option 2 (SHA256). The 0 (MD5) and 1 (SHA1) options for the algorithm parameter are deprecated because the encryption algorithms used are not considered quantum safe. For more information, see Deprecated algorithm options: 0 (MD5) and 1 (SHA1).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 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, which is deprecated. For more information, see Notes.

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 Hash algorithm Result size Number of returnable values Result data type
2 SHA256 256 bits 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

Start of changeDeprecated algorithm options: 0 (MD5) and 1 (SHA1)End of change
Start of change

The MD5 and SHA1 algorithms are deprecated because they are not considered quantum safe. For better security, use option 2 (SHA256) for the algorithm parameter.

Table 2. Characteristics of the result for each algorithm
algorithm Hash algorithm Result size Number of returnable values Result data type
0 (deprecated) MD5 128 bits 2128 VARBINARY(16)
1 (deprecated) SHA1 160 bits 2160 VARBINARY(20)
End of change
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 13, 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
The HASH function is similar to the other hashing functions, where the hash algorithm is specified as part of the function name, as shown in the following example. For more information, see HASH_algorithm scalar function.
HASH_SHA256 (  expression  )

However, invoking the HASH function for hashing is recommended to increase the portability of applications.

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.

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’