Starting in Db2 13 for z/OS at application compatibility level V13R1M505, you can now convert binary hexadecimal values to certain numeric or character data types by invoking the new built-in INTERPRET scalar function.
This capability is particularly useful for DBAs, who often need to convert a binary HEX data value to a specific data type. One typical situation is RID values in messages. In messages like DSNU709 and DSNU711, Db2 returns seven-byte RID (record identifier) values in HEX format, to identify exact rows where errors occurred. To effectively address these errors, DBAs often find it necessary to retrieve the exact data by using the RID scalar function for comparison with the RID value.
However, the RID function returns values in BIGINT format, and this disparity in formats between HEX and BIGINT can pose a challenge when troubleshooting issues. Consequently, DBAs have expressed a growing need for Db2 to provide a capability to convert HEX to numeric data types such as BIGINT, INT, or SMALLINT.
The new INTERPRET scalar function is general-purpose built-in function. It currently supports conversion of binary HEX data values to SMALLINT, INTEGER, BIGINT, CHAR, or VARCHAR. These capabilities provide a more streamlined and effective process for comparing RID values that are presented in different formats, ultimately simplifying troubleshooting and error resolution.
The following table shows some example invocations of the INTERPRET function and the result values that it returns.
|
INTERPRET function invocation
|
Result value
|
|
INTERPRET(BX'00000011' AS INTEGER)
|
17
|
|
INTERPRET(BX'0000000000B0370D' AS BIGINT)
|
11548429
|
|
INTERPRET(BX'616263' AS CHAR(3) CCSID 37)
|
/ÃÄ
|
|
INTERPRET(BX'616263' AS CHAR(3) CCSID 1208)
|
abc
|
|
INTERPRET (BX'0005C1C2C3C4C5' AS VARCHAR(5))
|
ABCDE
|
|
INTERPRET(BX'0003C1C2C3C4C5' AS VARCHAR(5))
|
ABC
|
|
INTERPRET(BX'0007C1C2C3C4C5' AS VARCHAR(7))
|
Error
|
The new INTERPRET scalar function has the following syntax. For the full descriptions, see INTERPRET scalar function in the Db2 product documentation.
The functional code for the INTERPRET scalar function is delivered by the PTF for APAR PH59595, and it becomes available for use when you activate function level 505 and run the queries at application compatibility level V13R1M505 or higher.
What other data conversions would you find useful? Customers and vendors have already submitted Aha ideas to expand the capabilities of the INTERPET function to support for more data types. Let us know by voting for existing Aha ideas, like this one for packed-decimal data, or submit your own Aha idea!
Fung Lee is a Db2 for z/OS RDS developer, and Paul McWilliams is the content lead for Db2 for z/OS documentation.
#Db2forz/OS