472,328 Members | 1,322 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

To generate Unique id for every SELECT statement

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.

Nov 12 '05 #1
1 10901
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
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: David Berry | last post by:
Hi All. I'm trying to write an ASP page that shows me the UNIQUE account number for a customer (so I can pass it to another page) based on a search...
10
by: Mamuninfo | last post by:
Hello, Have any function in the DB2 database that can generate unique id for each string like oracle, mysql,sybase,sqlserver database. In...
5
by: Terri | last post by:
I have a form with a multi-select combo. I dynamically build a SELECT statement, open a report, and set the recordsource to my dynamic SELECT...
6
by: Sebastien | last post by:
Hi, I am building a products database, linking sales and production. Each part has a unique sales Stock Code and Production Number. The sales...
29
by: Lauren Wilson | last post by:
Does anyone know how the following info is extracted from the user's computer by a Front Page form? HTTP User Agent: Mozilla/5.0 (Windows; U;...
8
by: mortb | last post by:
Hi, How do I write a GenerateHashcode function that will generate guaranteed unique hashcodes for my classes? cheers, mortb
3
by: isis | last post by:
Hi all, I have been googling ard for days and I am still unable to find a solution to generate ids uniquely hence I would like to appeal for...
13
by: gamernaveen | last post by:
I am coding a script , where basically the user has to enter his name , choose file , file comments if required and upload. The file comments , name...
1
by: situ | last post by:
Hi, I'm using DB2 V9 for windows I'm inserting records into DGTT from select statement; the problem is how to insert unique value for every...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
1
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.