To generate Unique id for every SELECT statement

[hikums]

I am posting this here, just in case anyone may need this.

Step 1:
CREATE SEQUENCE ID_SEQ
START WITH 1050000
INCREMENT BY 1
MAXVALUE 9999999
NO CYCLE
NO CACHE
ORDER

Step 2:
create function uniq()
returns integer
begin atomic
declare i int;
set i = ( next value for db2inst1.id_seq );
return i;
end

Step 3:

select uniq() from sysibm.sysdummy1
--will generate unique id's using the sequence and function described
above.

[Philip Nelson]

hi****@gmail.com wrote:

I am posting this here, just in case anyone may need this.

Step 1:
CREATE SEQUENCE ID_SEQ
START WITH 1050000
INCREMENT BY 1
MAXVALUE 9999999
NO CYCLE
NO CACHE
ORDER

Step 2:
create function uniq()
returns integer
begin atomic
declare i int;
set i = ( next value for db2inst1.id_seq );
return i;
end

Step 3:

select uniq() from sysibm.sysdummy1
--will generate unique id's using the sequence and function described
above.

I've been working on a similar problem : how to produce a GUID (globally
unique ID) within DB2 for use where sometimes data is being produced on
Windows machines with GUIDs and sometimes is being produced elsewhere and
we have to generate the GUIDs at insert time.

I discovered a Java class, actually part of the Java garbage collection
functionality, which produces a "VMID" which is guaranteed unique across
any virtual machine. So I wrapped this in code to produce a genGUID()
UDF. Here's the UDF definition -

CREATE FUNCTION myfuncs.genGUID()
RETURNS CHAR(50)
EXTERNAL NAME 'genGUID!genGUID'
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT VARIANT
FENCED
CALLED ON NULL INPUT
NO SQL
NO EXTERNAL ACTION@

To define this, put the above into genGUID.db2 and run -

db2 -td@ -vf genGUID.db2

The Java code for the genGUID class is -

import java.lang.*; Â* Â* Â* Â* Â*// for String class
import java.rmi.dgc.VMID; Â* Â*// for VMID generator class

public class genGUID
{
Â* // scalar UDF
Â* public static String genGUID()
Â* throws Exception
Â* {
Â* Â* String outGUID = new java.rmi.dgc.VMID().toString();

Â* Â* return outGUID;
Â* }
}

To compile this code you put this into genGUID.java and run -

javac genGUID.java

This produces a file genGUID.class, which you then copy into
~db2inst1/function. Â* If you change the code and replace the class it
doesn't take effect until you refresh the DB2 class library -

db2 "call sqlj.refresh_classes()"

Then running the UDF gives -

bash-2.05b$ db2 "values myfuncs.genguid()"

1
--------------------------------------------------
8149868854a78ac2:3384e52c:10747918e3c:-7ff0

Â* 1 record(s) selected.

The actual value produced is 43 characters, but I'm defining the return as
50 to give a bit leeway in case they change it later.

Knocking that lot together was a pleasant evening's diversion on Linux last
night. Thankfully the same code works on the AIX box at work this morning
too.(AIX box has Java 1.3.1, whereas on Linux it was Java 1.5.0).

HTH

Phil Nelson
teamdbaNOATSPAMscotdb.com