CODEWITHSUNDEEP
db2
Souf
Souf

Reputation: 389

DB2 encrypt field in select statement

So I need to do a select statement, while encrypting a field in SHA2_512. I'm not sure which DB2 version it is, but I tried both queries and neither work.

When trying this query:

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

I get the following error:

Error: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FUNCTION;HASH_SHA256, DRIVER=3.71.22 SQLState: 42884 ErrorCode: -440 Error: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.71.22 SQLState: 26501 ErrorCode: -514

While with this query

SELECT hash('Charlie at IBM', 3)
FROM SYSIBM.SYSDUMMYU;

Error: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FUNCTION;HASH, DRIVER=3.71.22 SQLState: 42884 ErrorCode: -440 Error: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.71.22 SQLState: 26501 ErrorCode: -514

(while without the hash functions the query runs without any issues). Anyone know how I can fix this issue?

I'm not sure which DB2 version I'm using but by using these queries I get these results:

  SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1

DSN11015

select  GETVARIABLE('SYSIBM.NEWFUN')  AS COMPATIBILITY from sysibm.sysdummy1

COMPATIBILITY: V11

https://www.ibm.com/docs/en/db2/11.1?topic=functions-hash

https://www.ibm.com/docs/en/db2-for-zos/12?topic=sf-hash-crc32-hash-md5-hash-sha1-hash-sha256

Upvotes: 0

Views: 889

Answers (1)

mao
mao

Reputation: 12267

You appear to be using Db2-v11 for Z/OS "DSN11015" in V11 compatibility mode.

This version lacks the scalar function HASH_SHA256 (and others) which is present in Db2-v12 for z/OS. That is the reason you get sqlcode -440 (no such function HASH...) with your Db2-for-z/os version.

At Version-11 , IBM documents a function ENCRYPT_TDES which may help you.

Background information on using this function.

You can write your own functions if you have the skills.

Upvotes: 2

Related Questions