0

I'm trying to extract data (using SPUFI) from a DB2 table to a file, with one of the output fields converting a decimal field to the same format as a COBOL comp field. So e.g. today's date (20141007) would be ..ëõ

The SQL HEX function converts 20141007 to 013353CF, and doing a SELECT of x'013353CF' gives me the desired result, but obviously that's a constant, I'm trying to find an equivalent function. Basically an inverse of the HEX function.

I've come across a couple of suggestions using user defined functions. Problem is, we've only recently upgraded to DB2 10 and new function mode isn't enabled yet, which means I don't have access to any control functions in a UDF.

I suspect I'm out of luck, but wondering if anyone has any suggestions. I appreciate this is completely the wrong tool for the job, and would be easier to just write a COBOL program to do it, but various constraints are preventing that. I'm limited to just SQL functions and possibly JCL).

I thought I had a solution using a recursive UDF to get around the lack of control functions, but that's not allowed either.

Mick O'Hea
  • 1,619
  • 2
  • 14
  • 20
  • See if this helps: http://stackoverflow.com/questions/25555479/how-does-the-hextoraw-function-work-what-is-the-algorithm – mustaccio Oct 07 '14 at 13:46
  • Thanks. That gives me the algorithm, which is a help, but without any kind of control loop I can't find a way of implementing it in SQL. I though t I had it using a series of UDFs, one of them recursive, but turns out that's not allowed either. – Mick O'Hea Oct 07 '14 at 14:36
  • `I'm trying to get data out of...` "Out"? What is it going "out" into? That is, what is the target for "out"? A program variable? Another table? – user2338816 Oct 07 '14 at 22:59
  • Sorry, I've edited for (hopefully) clarity – Mick O'Hea Oct 09 '14 at 11:21

0 Answers0