db2 - 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
解决方案
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.
推荐阅读
- java - 设置CameraX图像采集文件路径
- vue.js - 如何从其父组件下的组件打开和关闭 v-dialog?使用 Vuex?
- python-3.x - 在循环内仅打印一次输出
- python - 可以在表格中切换角色和状态
- azure - 如何使用 Db Transaction 向 Microsoft EventHub 发送消息?
- docker - 启动 Docker 时使用 {create_group=True} / set_pgid 生成进程挂起
- vuejs2 - V-for 不使用模板中的动态数据
- python - PyQt5 中多个 QWidget 的样式表?
- tensorflow - 缺少 dll tensorflow-gpu
- python - Python statsmodels - 相当于R准二项式GLM?