md5 function in netezza

MD5 or hash function in Netezza

rawtohex(hash(UPPER(NAME),1))

Purpose of MD5 Key:-

This is very helpful while doing updates in Netezza. As Netezza by default delete the existing record and insert the New record(as update).

Ex:-
Target data:-

Source Data:-

The incremental data coming from source has one change 8001 record has an update in column name ACCOUNT_PARENT from 'EL_405515' to '405515'.

The second record has no change 8002. Lets see what MD5 function will do

SELECT * FROM
(SELECT A.* FROM
CASE When TGT_ACC_KEY is not null and  SRC_KEY!=TGT_ACC_KEY THEN 'U' 
When TGT_ACC_KEY  is null then 'I' else 'R' END as KEY1 FROM  (
SELECT
A.*,
rawtohex(hash(UPPER(ACCOUNT_CODE||'|'||ACCOUNT_LEVEL||'|'||ACCOUNT_PARENT||'|'||DEBIT_OR_CREDIT),1)) AS TGT_KEY,
rawtohex(hash(UPPER(STG_ACCOUNT_CODE||'|'||STG_ACCOUNT_LEVEL||'|'||STG_ACCOUNT_PARENT||'|'||STG_DEBIT_OR_CREDIT),1)) AS SRC_KEY,
ACCOUNT_KEY AS TGT_ACC_KEY

 FROM STG_ACCOUNT A
left outer join
ACCOUNT_DIMENSION
on
ACCOUNT_KEY=A.ACCOUNT_ID) A )  WHERE KEY1 in ('U','I')

Delete the 'U' Flag record and insert it, this way the update process is fast in Netezza.This way one can also reject the no change records and save lot of processing time(any ways y we want to process no change records).

MD5 Key in SQL server.

UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('MD5', concate and place all the column used for update here), 1, 0))