In the most recent versions of DB2 it is possible to make all kinds of conversions between different datatypes. This is primarily done using the CAST function. But there are certain conversions where CAST must be assisted by other functions. This tip is about how to convert a decimal value stored in an ordinary character string into a DB2 decimal field.

It is relatively rare to find a decimal value (a packed decimal in PLI or COMP-3 in COBOL), but it happens sometimes in so-called RESTART tables. In a RESTART table you store the position reached by a program running with COMMIT intervals. It may also happen for other reasons where it is suitable to store a data structure in a character string containing decimal fields.

In the following example I want to convert a decimal(15) located in a character string starting at position 9. A decimal(15) occupies as you probably know 8 bytes and the conversion looks like this:

SELECT CAST(SUBSTR(HEX(SUBSTR(RESTARTDATA,9,8)),1,15) AS DECIMAL(15))
FROM RESTARTTABLE
WHERE JOBNAME = 'MYJOB'

RESTARTDATA has either a datatype of CHAR or VARCHAR. The inner SUBSTR returns the internal representation of the decimal(15) field. Using the HEX function the internal representation is converted into a character string consisting of the digits in the first 15 characters and a sign in the last character. The sign is typically C for + and D for -. In the above example I do not consider the sign and thus the result is always positive.

In the example I convert the value in a SELECT where I could have left out CAST in order to show the value. Then it would have been displayed with leading zeroes. If I want to use the value in the WHERE clause and make the value indexable (usable for an index lookup) then it is a pretty good idea to use CAST if the column in the index is a decimal column.