## UDF decimal(38) division

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted

## UDF decimal(38) division

Hi, in our warehouse we use numbered md5 for generating surrogate keys insted of lookuping to seqvence/business key table. This is very fast way to build 3NF warehouse... but

...now we use this function which is not optimased:

REPLACE FUNCTION DWHLIB.HPK( hodnota varchar(2000))

....
to_number(DWHLIB.hash_MD5(DWHLib.UDF_UTF16to8(hodnota)),'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') / 10
....

Because this is so performance crucial function for as I'm now trying to optimize it and write it only in C and probably in unprotected mode. Problem is that

MD5 returns 16bytes value = 32characters in hexa

But DECIMAL(38) takes 16bytes = but 38decimal places, so you can save here only -10^38 to 10^38, but 2^128= 3.4*10^38, so you lose some information. To save md5 into decimal data type we slove this problem dividing md5 value by 10.

My question is: How can I in C divide DECIMAL16 number by 10

Is there some divide function for DECIMAL16 for example?

code below has some rounding error so we can't use it...

DECIMAL16 hn;

hn.int1=((h3 >> (8*0)) & 0xff)*256*256*25.6+
((h3 >> (8*1)) & 0xff)*256*25.6+
((h3 >> (8*2)) & 0xff)*25.6+
((h3 >> (8*3)) & 0xff)*0.1
;

hn.int2=((h2 >> (8*0)) & 0xff)*256*256*25.6+
((h2 >> (8*1)) & 0xff)*256*25.6+
((h2 >> (8*2)) & 0xff)*25.6+
((h2 >> (8*3)) & 0xff)*0.1
;

hn.int3=((h1 >> (8*0)) & 0xff)*256*256*25.6+
((h1 >> (8*1)) & 0xff)*256*25.6+
((h1 >> (8*2)) & 0xff)*25.6+
((h1 >> (8*3)) & 0xff)*0.1
;

hn.int4=((h0 >> (8*0)) & 0xff)*256*256*25.6+
((h0 >> (8*1)) & 0xff)*256*25.6+
((h0 >> (8*2)) & 0xff)*25.6+
((h0 >> (8*3)) & 0xff)*0.1
;

*result=hn;

If you would like, I can share some information about md5 keys architecture....

Thanks

Frantisek

Tags (3)