Have any function in the DB2 database that can generate unique id for each String?

[Mamu...@gmail.com]

Hello,
Have any function in the DB2 database that can generate unique id
for each string like oracle, mysql,sybase,sqlserver database.

In mysql:-
select md5(concat_ws("Row name")) from tablename;

Here this function generate unique id for each row of the table.

Regards..
..Mamun

[Mark Yudkin]

To generate a unique string use the appropriately named GENERATE_UNIQUE
function.

<Mamu...@gmail.com> wrote in message
news:1129440764....@o13g2000cwo.googlegroups.com...

[Mamun]

If i use generate_unique() then there is no output. On the other hand
if i use generate_unique("dsf") then error generate.
Will you tell me how can i use it with an example Please?

[Tonkuma]

What is the name of such function in Oracle or MS SQL Server?
Oracle have psuedocolumns like ROWID, ROWNUM. But, these are not
function and take no parameter.

[Bob [IBM]]

Please use the information center on the web as a resource:

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

Entering GENERATE_UNIQUE in the search area you should find the first entry
gives you the description, syntax and examples of its use.

Bob

"Mamun" <Mamu...@gmail.com> wrote in message
news:1129451451.6...@z14g2000cwz.googlegroups.com...

[Madison Pruet]

Is Oracle claiming that md5 will generate a unique id? I hope not since
it's not true.

MD5 (Message Digest 5) is a hash algorthm, much like a CRC, or any other
checksum. It can not guarentee that the number generated is a unique value,
only that it is the hash associated with the object that the checksum is
generated on. But it is entirely possible that the same hash value can be
generated from a different string.

There are many such hashes. While md5 was in use and popular for quite some
time, it has been proven to have some weaknesses and has been somewhat
replaced by the sha-1 algorthm.

If you want, you can get a whole bunch of encryption and hash routines from
www.opensssl.org, including md5, md2, sha1, etc. These routines can then
be implemented in your client code, or in the server as a fenced routine.

My personal thought is that encryption and hashing is best done in the
client code rather than in the server because of the overhead associated
with either encryption or hashing. If I have 1000 client programs attached
to a single server, I'd rather the server spend it's time getting data to me
and then have each of the 1000 client programs responsible for any hash
logic.

BTW - I'm not a java programmer, but I think that java has both md5 and
sha-1 included in the builtin security package.

M.P.

<Mamu...@gmail.com> wrote in message
news:1129440764....@o13g2000cwo.googlegroups.com...

[Mamun]

The name of the hash function in the oracle database is
dbms_utility.get_hash_value(). I am looking like this in the db2
database.
..Mamun

[Mark Yudkin]

Please review the documentation.

I wonder if your question is for an MD5 hash function? Since you asked about
"generate a unique id", MD5 is a hash function and not a unique string
generator, and SQL Server (I don't know MySQL and I haven't used Sybase
since V9) doesn't have an MD5 function, I had assumed not. Otherwise, try
asking your question clearly and without inconsistencies and contradictions,
so that we don't have to guess what you're asking.

"Mamun" <Mamu...@gmail.com> wrote in message
news:1129451451.6...@z14g2000cwz.googlegroups.com...

[Mamun]

Hello Mark Yuddin,
I am sorry that u feel bothering. But i
gave an example and my wanted was have any function in DB2 database
that can generate unique value or id for each string? If it fall u in
heavy contradiction, i am sorry, Any way i could not find my answer. If
u know Please help me.

[Knut Stolze]

Mamun wrote:

> Hello Mark Yuddin,
> I am sorry that u feel bothering. But i
> gave an example and my wanted was have any function in DB2 database
> that can generate unique value or id for each string?

The point is that a hash function does _not_ generate unique numbers/values
for a give string. You might want to read up on the basics of hash
techniques and in particular about collisions.

> If it fall u in
> heavy contradiction, i am sorry, Any way i could not find my answer. If
> u know Please help me.

You might want to explain what exactly you did so that GENERATE_UNIQUE did
not "produce any output". It shows this for me:

$ db2 "values generate_unique()"

1
-----------------------------
x'20051017115326287128000000'

1 record(s) selected.

I also assume that you actually want a function that takes a given text as
input and generates the MD5 check sum (according to your example).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

You are not bothering us, it's just very unclear what you are trying to
achieve.
Anyway copy the files below into sqllib\samples\c,
then, from sqllib\samples\c run: bldrtn hash
Make sure sqllib\function contains hash.dll or hash (depending on your
operating system) afterwards.
Execute the sql file and you're ready to go. If you don't have a
C-Compiler on any machine (you can copy the binary from development to
production) you can do the same in Java or define an SQL Function with
similar logic.
Keep in mind though that there is no such thing as a UNIQUE mapping
between a string of non trivial length and an INTEGER.
The space of strings has 2^(8 * (length)) elements, whie integer only
has 2^32 numbers. So beyond 4 characters it ain't gonna happen.

------------
hash.sql:
-- HASH function
DROP FUNCTION HASH;
CREATE FUNCTION HASH
(HASH VARCHAR(32000) FOR BIT DATA)
RETURNS INTEGER
SPECIFIC HASH EXTERNAL NAME 'hash!hash'
NOT FENCED RETURNS NULL ON NULL INPUT
DETERMINISTIC NO SQL NO EXTERNAL ACTION
LANGUAGE C PARAMETER STYLE SQL ALLOW PARALLEL;

-------------
hash.c:
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqludf.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN hash(SQLUDF_VARCHAR_FBD *str,
SQLUDF_INTEGER *outHash,
SQLUDF_SMALLINT *strNullInd,
SQLUDF_SMALLINT *outHashNullInd,
SQLUDF_TRAIL_ARGS)
{
int i;
if (*strNullInd == -1)
{
*outHashNullInd = -1;
}
else
{
for (*outHash = 0, i = 0;
i < str->length / sizeof(SQLUDF_INTEGER);
*outHash ^= ((SQLUDF_INTEGER *) str->data)[i++])
{ /* empty */ }
i *= 4;
switch (str->length % sizeof(SQLUDF_INTEGER))
{
case 3: *outHash ^= ((SQLUDF_INTEGER) str->data[i++]);
case 2: *outHash ^= ((SQLUDF_INTEGER) str->data[i++]) << 8;
case 1: *outHash ^= ((SQLUDF_INTEGER) str->data[i]) << 16;
default : { /* empty */ }
}
*outHashNullInd = 0;
}
}
--------------
# For Unix and Linux
hash.exp:
hash
----------
# For Windows
hash.def
LIBRARY HASH
DESCRIPTION 'Library for DB2 Hash UDF'
EXPORTS
hash

-----------
db2 => values hash('');

1
-----------
0

1 record(s) selected.

db2 => values hash('a');

1
-----------
6356992

1 record(s) selected.

db2 => values hash('b');

1
-----------
6422528

1 record(s) selected.

db2 => values hash('ab');

1
-----------
6447360

1 record(s) selected.

db2 => values hash('abc');

1
-----------
6513249

1 record(s) selected.

db2 => values hash('abcd');

1
-----------
1684234849

1 record(s) selected.

db2 => values hash('abcde');

1
-----------
1678140001

1 record(s) selected.

db2 => values hash('abcdef');

1
-----------
1678051169

1 record(s) selected.

db2 => values hash('abcdefg');

1
-----------
1677984772

1 record(s) selected.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab