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? 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 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
