Informix function to generate GUID/UUID
The poster asked whether Informix (9.x/10.x/11.x) has a built-in function to generate a globally unique identifier, equivalent to DB2's GENERATE_UNIQUE(), SQL Server/Sybase NEWID() or Oracle SYS_GUID(). Replies pointed to CREATE SEQUENCE, with the suggestion of keeping one sequence in a single central database (optionally replicated) for global uniqueness; one poster also noted DB2's function is really just partition number plus timestamp. The poster objected that sequences don't give MAC/timestamp-based global IDs, and the only lead found was a developerWorks article with an OS-dependent workaround. No portable Informix solution or clear resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Looking for a Informix supported function or a way by which i can generate GUID..? DB2 has GENERATE_UNIQUE(), MSSQL/SYBASE has NEWID() and ORACLE has SYS_GUID() database functions which generates Global Unique ID, but I didn't find any such function in Informix 9.x/10.x and 11.x which does this..? Can you please help me on this..? Thanks!
Read up on sequences in the Guide to SQL Syntax and Guide to SQL Reference manuals. On Thu, May 29, 2008 at 7:47 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote: > Looking for a Informix supported function or a way by which i can generate > GUID..? DB2 has GENERATE_UNIQUE(), MSSQL/SYBASE has NEWID() and ORACLE has > SYS_GUID() database functions which generates Global Unique ID, but I > didn't > find any such function in Informix 9.x/10.x and 11.x which does this..? > > Can you please help me on this..? > > Thanks! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitely or implicitely. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Thanks Art for your response. I know Informix SEQUENCEs, which generates unique identifier across a database. My requirement is to genearte Identifier which is Unique across the globe, this is what DB2 GENERATE_UNIQUE function claims to generate .. Thanks again.
I think Create sequence may meet your needs.. CREATE SEQUENCE--+----------+--sequence----------------------> '-owner--.-' >--+--------------------------------------+-------------------->< | .-----------------------------. | | V .-BY-. | (1) | '---+-INCREMENT--+----+--step-+-+------' | .-WITH-. | +-START--+------+--origin-+ | .-NOMAXVALUE----. | +-+-MAXVALUE--max-+-------+ | .-NOMINVALUE----. | +-+-MINVALUE--min-+-------+ | .-NOCYCLE-. | +-+-CYCLE---+-------------+ +-+-CACHE--size-+---------+ | '-NOCACHE-----' | | .-ORDER---. | '-+-NOORDER-+-------------'
So, put the sequence into an isolated database in one location and always access that one sequence globally. Replication can even maintain local copies of the sequence I'm sure On Thu, May 29, 2008 at 8:05 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote: > Thanks Art for your response. > I know Informix SEQUENCEs, which generates unique identifier across a > database. My requirement is to genearte Identifier which is Unique across > the > globe, this is what DB2 GENERATE_UNIQUE function claims to generate .. > > Thanks again. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitely or implicitely. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Ah my understanding of the db2 function was it was unique on a particular DB2 data base at a table level. - being a combination of the DB2 partition number and a timestamp... T"The system clock is used to generate the internal Universal Time, Coordinated (UTC) timestamp along with the database partition number on which the function executes. Adjustments that move the actual system clock backward could result in duplicate value. The function is defined as not-deterministic. Furthermore the manual says "The result of this function can be used to provide unique values in a table" DB2 has a similar sequence number as well... that can be unique to the database...
Hi Art, Actually GUID what DB2/Oracle/MSSQL/SYBASE generates are unique across the globe (i.e. uniqueness of a sequence will reamin across database platforms/OS/). This is generated based on OS identfier/Ethernet address/Timestamp/Database Identfier etc. ) which we can't do in SEQUENCE. I found this link which talks about GUID, looks like a workround for IDS 9.x and which is not OS independent : http://www.ibm.com/developerworks/db2/library/techarticle/dm-0401roy/index.html Thanks,
Thanks Robert, I'm not sure how to implement the same thing in Informix, I found this link, but didn't help much because implemntation is OS dependent. I want a common functionality which work across all OS: http://www.ibm.com/developerworks/db2/library/techarticle/dm-0401roy/index.html Thanks,