1

The IBM Technote "How to translate the unique identifier as displayed within FileNet Enterprise Manager so that it matches what is stored in the Oracle and DB2 databases" outlines how DB2 & Oracle store guids in a byte reversed order. How can I convert them into a friendly guid?

ﾤ ﾡ
  • 5,669
  • 6
  • 43
  • 57
Jeffrey Knight
  • 5,888
  • 7
  • 39
  • 49

2 Answers2

3

Presuming that object_id is char(16) for bit data, the following expression returns the same.

'{'||translate(
  'GHEFCDAB-KLIJ-OPMN-QRST-UVWXYZ012345'
, hex(F.object_id)
, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345')||'}' as object_id
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • I was hoping to mark this as the accepted answer, but it's a varchar(16) for bit data, so this solution doesn't work. – Jeffrey Knight Nov 19 '18 at 15:36
  • 1
    It does work on varchar(16) for bit data as well. Try this: `select '{'||translate( 'GHEFCDAB-KLIJ-OPMN-QRST-UVWXYZ012345' , hex(F.object_id) , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345')||'}' as object_id , hex(F.object_id) as original_object_id from table( values cast(x'6640864000001eccb61ceaaa453b3870' as varchar(16) for bit data) , cast(x'66460680000015c39fc48c928e241e3b' as varchar(16) for bit data) ) F (object_id)`. If it doesn't work for you, then what's your db2 platform and version? – Mark Barinstein Nov 19 '18 at 16:34
1

The following code does the trick in DB2, following the recipe from the IBM Technote:

‘{’ concat  upper(VARCHAR_FORMAT_BIT(
 cast(substring(F.Object_id, 4, 1) concat
 substring(F.Object_id, 3, 1) concat
 substring(F.Object_id, 2, 1)  concat
 substring(F.Object_id, 1, 1)  concat
 substring(F.Object_id, 6, 1)  concat
 substring(F.Object_id, 5, 1)  concat
 substring(F.Object_id, 8, 1)  concat
 substring(F.Object_id, 7, 1)  concat
 substring(F.Object_id, 9) as char(16)), ‘xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’)) concat ‘}’ as object_id

The "cast as char(16)" is a gotcha - casting to varchar(16) does not work on DB2 at least.

Here's the before and after:

guids

Jeffrey Knight
  • 5,888
  • 7
  • 39
  • 49