GUIDs in DB2

[Rich Carreiro]

Hello.

I'm trying to find out if there is any way to generate GUIDs
in DB2. I've read the FAQ and have gone a bunch of googling
both against this newsgroup and the web in general.

I have found out about GENERATE_UNIQUE, but from my reading
of the documentation, that does not generate a true GUID.

Is there any way to do it in "pure" DB2? If not, is there
any way to make DB2 do an OS-level call (like to the Win2K/etc.
function that generates GUIDs, or something similar on
other OSes) to get one?

Thanks.

--
Rich Carreiro rlc...@animato.arlington.ma.us
"Home is where you hang your hat." "Character is what you are in the dark."

[lsu...@mb.sympatico.ca]

Use a sequence object...

On Mon, 11 Nov 2002 23:07:04 UTC, Rich Carreiro
<rlc...@animato.arlington.ma.us> wrote:

> Hello.
>
> I'm trying to find out if there is any way to generate GUIDs
> in DB2. I've read the FAQ and have gone a bunch of googling
> both against this newsgroup and the web in general.
>
> I have found out about GENERATE_UNIQUE, but from my reading
> of the documentation, that does not generate a true GUID.
>
> Is there any way to do it in "pure" DB2? If not, is there
> any way to make DB2 do an OS-level call (like to the Win2K/etc.
> function that generates GUIDs, or something similar on
> other OSes) to get one?
>
> Thanks.
>

--
Lorne Sunley

[Mark Yudkin]

You can write a DB2 external scalar UDF that will call CoCreateGuid in C (or
any language that can interoperate with C, such as IBM PL/I) or Java. Take a
look at the Programming manuals for details, and the samples directory for
sample UDFs.

When defining the UDF to DB2, don't forget to specify that it is NOT
DETERMINISTIC, or your GUID will not be globally unique.

- Mark Yudkin

"Rich Carreiro" <rlc...@animato.arlington.ma.us> wrote in message
news:m3bs4vk...@animato.animato.arlington.ma.us...

[Tokunaga T.]

Rich Carreiro <rlc...@animato.arlington.ma.us> wrote in message news:<m3bs4vk...@animato.animato.arlington.ma.us>...

> Hello.
>
> I'm trying to find out if there is any way to generate GUIDs
> in DB2. I've read the FAQ and have gone a bunch of googling
> both against this newsgroup and the web in general.
>
> I have found out about GENERATE_UNIQUE, but from my reading
> of the documentation, that does not generate a true GUID.
>

Why do you think so?
GENERATE_UNIQUE() really generate unique values.
The result includes timestamp value.
But if a generated value is not unique(for example timer was adjusted
to past time), it modifies generated value to be unique.

[Serge Rielau]

GUI generates a globaly unique identifier. To do that it takes some
machine unique information into account (such as e.g. the IP address of
the machine).
generate_unique() generates a number that is unique within DB2 database.
It is not suitable for e.g. replication.
The same is true for sequences. Teh designer of the DB would need to
allocate distinct ranges throughout the propagation radius of these numbers.

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/

[Alexander Kuznetsov]

> GUI generates a globaly unique identifier. To do that it takes some
> machine unique information into account (such as e.g. the IP address of
> the machine).
> generate_unique() generates a number that is unique within DB2 database.
> It is not suitable for e.g. replication.
> The same is true for sequences. Teh designer of the DB would need to
> allocate distinct ranges throughout the propagation radius of these numbers.
>

One of the approaches for distinct ranges allocation is deshcribed in
detail in 2nd chapter of this:

http://www7b.boulder.ibm.com/dmdd/library/techarticle/0209kuznetsov/0209kuznetsov.html

[Lennart Jonsson]

On Mon, 11 Nov 2002 18:07:04 +0000, Rich Carreiro wrote:

> Hello.
>
> I'm trying to find out if there is any way to generate GUIDs
> in DB2. I've read the FAQ and have gone a bunch of googling
> both against this newsgroup and the web in general.
>
> I have found out about GENERATE_UNIQUE, but from my reading
> of the documentation, that does not generate a true GUID.
>
> Is there any way to do it in "pure" DB2? If not, is there
> any way to make DB2 do an OS-level call (like to the Win2K/etc.
> function that generates GUIDs, or something similar on
> other OSes) to get one?
>
> Thanks.

Just some thoughts:

rtrim(char(current server)) || ':' || char(current timestamp)
rtrim(char(current server)) || ':' || generate_unique()

if current server isnt unique enough, you could perhaps store the servers
ip in a table, and select from there. For example:

select ipnumber || ':' || generate_unique() from system_settings