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

How to upload Netezza NZSQL and NZLOAD output to a file.

Can I export the SQL query results from Netezza? 

Lets see how to do this

nzsql -u USER001 -pw pass1 -db PGBDB201  -host myhost.com -c "SELECT * FROM COMPLE.PROD_DIM" > /infa001/Src/PROD.txt

the out put of the query "SELECT * FROM COMPLE.PROD_DIM" will be loaded to the file PROD.txt

Is the above query fails with any error, then that error will be captured in the file PROD.txt

For Auditing or for error capturing the above method is very use full (when you don't have access to bad file or log file).

In the above example instead of using Query we can pass a script file which will have a set of query's.

EX:-  open unix env

$ touch my_script

$ select * from product_dim; >> my_script

$ Select * from customer_dim; >> my_script

$ more my_script

select * from product_dim;

Select * from customer_dim;

and call my_script in NZSQL as follows 

nzsql -u USER001 -pw pass1 -db PGBDB201  -host myhost.com -f $QUER_PATH/my_script  > /infa001/Src/PROD.txt

String aggregation

Netezza, Oracle and SQL server string aggregation examples.

String aggregation in Netezza

Input data 

101 Kumar

101 Swami

102 John

102 D

103 David

103 R

104 jean morgan

required Out put 

101 Kumar Swami

102 John D

103 David R

104 Jean Morgan

Below Query can handle up to 10 repetitions per id (for example 101 ten repetitions) 

SELECT id,

 (RTRIM((MAX(CASE WHEN RNO=1 THEN STRING_NAME ELSE '' END) ||','||

MAX(CASE WHEN RNO=2 THEN  coalesce(STRING_NAME,'') else '' END)||','||

MAX(CASE WHEN RNO=3 THEN  coalesce(STRING_NAME,'')else '' END)||','||

MAX(CASE WHEN RNO=4 THEN  coalesce(STRING_NAME,'')else '' END)||','||

MAX(CASE WHEN RNO=5 THEN  coalesce(STRING_NAME,'')else '' END)||','||

MAX(CASE WHEN RNO=6 THEN  coalesce(STRING_NAME,'')else '' END)||','||

MAX(CASE WHEN RNO=7 THEN  coalesce(STRING_NAME,'')else '' END)||','||

MAX(CASE WHEN RNO=8 THEN  coalesce(STRING_NAME,'')else '' END)||','||

MAX(CASE WHEN RNO=9 THEN  coalesce(STRING_NAME,'')else '' END)||','||

MAX(CASE WHEN RNO=10 THEN  coalesce(STRING_NAME,'')else '' END)),',')) AS REG_CD

FROM

(SELECT ID,STRING_NAME,

ROW_NUMBER) OVER

(PARTITION BY ID ORDER BY STRING_NAME) AS RNO

FROM YOUR_TABLE) A GROUP BY A.ID

The above requirement can also be achieved using UDF Group Concat in Netezza.

In Oracle:- 

SELECT ID, 
       Listagg(STRING_NAME, ',') 
         within GROUP (ORDER BY ID) AS employees 
FROM   TABLE
GROUP  BY ID; 

Using Oracle Unlisted function:- 

SELECT ID, 
       WM_CONCAT(STRING_NAME)  
FROM   TABLE
GROUP  BY ID;

In SQL Server :-

SELECT  ID,
                ( Stuff((SELECT ',' + STRING_NAME
                         FROM   TABLE E2 
                         WHERE  E1.ID= E2.ID
                         ORDER  BY STRING_NAME
                         FOR xml path(''), type, 
                  root).value('root[1]', 'nvarchar(max)'), 1, 1, '') ) AS 
                STRING_NAME
FROM   TABLE E1 
ORDER  BY ID

Netezza Utilities

The 2 frequently used Netezza Utilities are as follows

NZSQL and NZLOAD

The nzload command returns three status codes. They are

• 0: Nzload command is success and the data is loaded into the target table.
• 1: Nzload command is success. However errors are occurred in loading. But the number of errors does not exceeded the -maxErrors option
• 2: Nzload command failed

Let's see how to create a generic script and pass the sql script from file to the generic script.

How to call this script in Informatica command task and pass the SQL script file name as parameter.

Lets first create a generic script for the following operations. 

1)Select 

2)Insert 

3)Delete

4)Update

To make the script generic we need to have a profile file which hold the parameter file values need for the script.

Lets see how the above values will be used in the script

Lets see how to call this script from informatica command task

Lets see the PROD_DIM_SQL Script, that we are passing as parameter in the above example.

In netezza :WH_DBNAME act as parameter
: is the syntax for defining a parameter (similar to "$" is the syntax for shell script or Teadata bteq Script)