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.
Enthusiast

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)