Part six of my – everlasting – walk through of new Aggregate and Scalar functions.
Following with : HASH & WRAP
Previous “SOUNDEX” Newsletters
- Part 5: 2018-07: SOUNDEX and other „cool“ features – Part five All new for Db2 12
- Part 4: 2015-06: SOUNDEX and other „cool” features – Part four for Db2 10 & DB2 11
- Part 3: 2012-06: SOUNDEX and other „cool“ features – Part three for Db2 10
- Part 2: 2012-05: SOUNDEX and other „cool“ features – Part two for Db2 V9
- Part 1: 2012-04: SOUNDEX and other „cool“ features – Part one for Db2 V8
SCALARS
Now I move on to the new scalar functions, really only two new ones came with Db2 12, HASH and WRAP.
HASH it
Four new HASH Scalar functions are supplied. The first runs every time:
SELECT HEX(HASH_CRC32 ('ROY LIKES BEER A LOT!'))
FROM SYSIBM.SYSDUMMYU;
---------+---------+---------+---------+----------+---------+---------+
4FA13CD
DSNE610I NUMBER OF ROWS DISPLAYED IS 1Then I tried MD5
SELECT HEX(HASH_MD5 ('ROY LIKES BEER A LOT!'))
FROM SYSIBM.SYSDUMMYU;
---------+---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -20223, ERROR: THE ENCRYPT_TDES OR DECRYPT FUNCTION
FAILED. ENCRYPTION FACILITY NOT AVAILABLE 12 0
DSNT418I SQLSTATE = 560BF SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRBIN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 1032 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000408' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Oops! What is that?
– 20223 THE OPERATION FAILED. ENCRYPTION FACILITY NOT AVAILABLE return-code, reason-code
Explanation: The encryption facility is not available, or not able to service the encryption or decryption request.
System action: The statement cannot be processed.
Programmer response: If the encryption facility is not installed, install it before you use the ENCRYPT_TDES, DECRYPT, HASH_CRC32, HASH_MD5, HASH_SHA1, HASH_SHA256 functions or data set encryption. If the encryption facility is installed, verify that it is working correctly. The returncode and reason-code might give further information about why this message was issued.
Digging deeper into the “ICSF and cryptographic coprocessor return and reason codes” docu
| Reason codes for return code C (12) | |
| Reason Code Hex (Decimal) | Description |
| 0 (0) | ICSF is not available. One of the following situations is possible:
|
Yep – We have no crypto here in the labs, so no big surprise! But hey, at least the CRC32 worked!
Let’s WRAP it up
WRAP is useful for people like me that write software that other people buy! If you are using any of these:
CREATE FUNCTION (compiled SQL scalar)
CREATE FUNCTION (inlined SQL scalar)
CREATE PROCEDURE (SQL - native)
CREATE PROCEDURE (SQL table)
CREATE TRIGGER (basic)
CREATE TRIGGER (advanced)
Then you are aware that the code is in clear text and easy to read – Not good for my intellectual property rights! WRAP solves this dilemma by letting me obfuscate the code completely.
Here are two examples with the problems they can pose:
SELECT WRAP ('CREATE FUNCTION SALARY(WAGE DECFLOAT) RETURNS DECFLOAT RETURN WAGE * 40 * 52')
FROM SYSIBM.SYSDUMMY1
;
SELECT WRAP('
CREATE TRIGGER BOXWEL2.TESTTRIGH
AFTER
UPDATE OF COL1 ON BOXWEL2.TESTTRIG
REFERENCING NEW AS NTABLE
FOR EACH ROW
MODE DB2SQL
WHEN (NTABLE.COL1 = ''2'')
UPDATE MVNXTEST.MVNXT80 A
SET UT_STATUS = ''X''
')
FROM SYSIBM.SYSDUMMY1
;The output looks like this:
CREATE FUNCTION SALARY(WAGE DECFLOAT) WRAPPED DSN12100 ablGWmdiWmtGTmdCTmtaTmtmUntqUmJeUmZi2mdKZidaWmdaWmdaWmZG1mIaGicaGi6TRm8Eu4e30 AmlGUcEdToUnWtd2:hIfdEN_bBCpEmtl1lKH:UAzaqaa
One very long line that you must then “reverse” parse to get it back into, e.g. 72 byte chunks for SPUFI use.
The second example is even better, because of the whitespace being not encoded/encoded as well:
CREATE TRIGGER BOXWEL2.TESTTRIGH WRAPPED DSN1210 ablGWmdiWmtGTmdCTmtaTmtmUntqUmJeUmZm0otKXidaWmdaWmda1nJKGicaGicaGQ:TRO87Axb:VP8p_d8E9N887FL:EnRZ:8ltM:4sMnV5iyw3QGPUu0hDP4uwUK4lkDz0xvk3PU6lCMBGOCZar_sbJSQyaHYumRRSkATFXus8DesyjqaYmOLMD2HWdLt6GFDTxkNr4g8ht874tPZXN5ZIpJW4Xx15CI:VZ4f7ENGxV6_jxL4tVN0MFF2:tFR6EcD:g3nZurpPzOd2PYTuslWXKWXEWsWk2Q1KuT1VjPlY_MeNhCnpppEFEoQgp:dudFUcPhFyesEPIrPYTZxKAHlj1sDOKQTp1fYWoQ5nQBXOZGGtBreN2j9oSdSJNgf3roCpVJS8EFxdZ5DuoI_PDP5t7d1DPTL7Vtlp2EMsESTnl6s80KH4O13Wr72s4y56iQwBwB0KuOUYKxUZb:zNdchpguBUNZom2p2yyTpOXXkcHhKGnFnbBaUd2rlMbRmKZdgDiNwi7rEKHTLYulpLOa7kqrHKLymXZKurj64TGpF2IjXUmYkMbgbn4CFnOLk20rArSOIWIzbpajemWcmyHWmWdWfhyr8dJWZ8ghXJ5mF_nKr3ZyNUNw7Co9OqUxuEPuocFy4vUswVtOmonhr23d4VgBQz3Zf8nV2p5AVOEYLsJ7QDnNq7Bb132t3R7Rn603gF0PUi_PdCH6ef8Kmk4a8uWr2hkicHj8apO7hQf5w3Tc2tsvOCvOe5RaopCLKgg5BmnnYYzAl7lLiNGAvob1_Dd7PkoX51LDZ3QlBJUugQm5WsPJKKtwDjM81Wa
Notice that I had to double up the apostrophes due to this being passed as input to WRAP now – so you do have to watch out for that.
Remember too, that this is not encryption, it is just encoding and can be broken – It just makes it a tick harder to read your code, that is all. IBM writes:
Important
The encoding of the statement is meant to obfuscate the content and is not considered a form of strong encryption.
Well that wraps up this newsletter! Sorry….
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect