How can I translate the FileNet ID ID in DB2/Oracle into friendly GUID?

2.1k views Asked by At
2

There are 2 answers

0
Jeffrey Knight On BEST ANSWER

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

2
Mark Barinstein On

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