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)

Netezza Functions

SQL server equivalent functions in Netezza

** As Netezza is case sensitive be careful with String comparison (best practice to use upper/lower case when comparing string columns both the sides) this applicable for joining group by as well.

The exception is when one wants to find a specific string (ex: - I want to search only ‘NeteZZA’ in my column).

String Functions
Date Functionss 

Sno	SQL Server Functions	Netezza Functions	Purpose of the function
	SUBSTRING(CASE_NO, PATINDEX('%[^0 ]%', CASE_NO+ ' '), LEN(CASE_NO))	ltrim(CASENUMBER,'0')	Remove left hand side zero’s from case number
		RTRIM (CASENUMBER,'0') EX:- RTRIM (0011124500,'0') Result:- 00111245	Remove right hand side zero’s from case number
	LEFT()	substr() EX:-  substr(‘VAMSHI’,0,3) Result ‘VA’	When we want only LEFT had side last 3 digits or charades
	RIGHT()	substr('VAMSHI',LENGTH('VAMSHI')-1) RESULT:- HI Formula for last 2 alphabets do Length -1 For 3 its length -2 For 4 its Length -3	Use when we need Right hand side digits or charades
	CONVERT(VARCHAR(10), DATE_FLD, 101)	TO_CHAR(RECIEVEDATE,'DD/MM/YYYY')	To convert date to the format we want Ex:- 'DD/MM/YYYY' or ‘DD/MON/YYYY etc.,
	Like Function	Like	The LIKE  function syntax and functionality is same to SQL server
	Case Function	Case	The CASE function syntax and functionality is same to SQL server
DATE FUNCTION (For numeric result)
	Year(RCV_DAY_DAY_DT)	extract(year from RCV_DAY_DAY_DT)	This give us the year EX:- 2014
	MOTH(RCV_DAY_DAY_DT)	extract(MOTH from RCV_DAY_DAY_DT)	This give us the year EX:- 1(JAN)
	DAY(RecieveDate)	extract(DAY from current_date)	This give us day of the year EX:- 14(day of the year)
		extract(WEEK from current_date)	Week of the year EX:- its 38 week of this year 2014
DATE FUNCTION (For String result)
		to_char(DATE_D,'DAY')	EX:- ‘WEDNESDAY’
		to_char(DATE_D,'Month')	EX:- September
Adding months days and to get QUARTER out of DATE
		SELECT EXTRACT(QUARTER FROM now());	To get the quarter from DATE
		months_between(d1, d2)	To get the months between 2 dates
		age(t,t)	To get the difference between to dates
		last_day(now())	To get the last day of the month
		add_months(now(),-1))	To Add or subtract a month to the date
		date_part('Month', to_date('2014201', 'YYYYDDD'));	Extract month from julian date in netezza

First 10 records or Top 100 records
	TOP 1	LIMIT 1	It limits the no of records we want to see
		LIMIT 2 OFFSET 2 Ex:- SELECT * FROM TABLE ORDER BY CASENUMBER LIMIT 2 OFFSET 2	Offset will skip the first 2 records and limit will give 3 and 4 records **Order by is key for these functions

How to get part of a string separated any symbol's character

Ex:- Comma, Tab, Star etc.,

Function:-

select get_value_varchar(array_split('IBM_NETEZZA_DB_FUNCTIONS','_') , 4);

 Explanation:-

Actual String                  'IBM_NETEZZA_DB_FUNCTIONS'

Position of the string           1      2    3   4  

Result:- 

           'FUNCTIONS'

Functions available in Netezza(in your database)

Select * from _v_Function

the above query will list all the functions available in database.

Netezza is case sensitive:-

Is there a way to make Netezza case insensitive?

Let's see how it will effect and how to handle it in Netezza.

Example 1:- 

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'Netezza Db' AS NAME1)
SELECT NAME
FROM   test
WHERE  NAME = 'netezza' 

Result:-  No records found 

The above behavior is not at all weird, Some one worked on SQL server may feel it as weird (as it is case insensitive)

Example 2:-

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'Netezza Db' AS NAME1)
SELECT NAME
FROM   test
WHERE  NAME LIKE '%ntezza%'  

Result:- No records found

Example 3:-

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'NeTezZa' AS NAME1
         UNION
         SELECT 'NETEZZA' AS NAME1
         UNION
         SELECT 'netezza' AS NAME1)

SELECT NAME ,COUNT(*) AS COUNT
FROM   test
Group by NAME

Result:-  Name           COUNT    
         "Netezza"          1
         "NeTezZa"          1
         "NETEZZA"          1
         "Netezza"          1
 Case sensitive has to handle in below cases.
1) While joining two string columns
2) Group by and Order by (by string columns)
3) WHERE or LIKE conditions.
4) CASE statements or DECODE statements.


The best way to handle is to handle it in the first place ETL.

To handle the same in Netezza data base we have the below function.

Let's see the difference using the function "UPPER".

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'NeTezZa' AS NAME1
         UNION
         SELECT 'NETEZZA' AS NAME1
         UNION
         SELECT 'netezza' AS NAME1)

SELECT UPPER(NAME) ,COUNT(*) AS COUNT
FROM   test
Group by UPPER(NAME)

Result:-  Name           COUNT    
         "NETEZZA"          4

String Functions in Netezza

Netezza Array Functions and their advantages.

How to replace a character in the string in Netezza.

Ex:- SELECT array_combine(Array_split('1NETEZZA','1'),'IBM_');

The above query replace 1 with 'IBM_'

Result:- IBM_NETEZZA

The above function can be used to replace anything (can be used for multiple replace)

Ex:- SELECT array_combine(Array_split('129|259|659|2558|6589','|'),'&');

Result:- 129&259&659&2558&6589

How to split a string or Stringtokenizer in Netezza

SELECT Get_value_varchar(Array_split('IBM-NETEZZA-DATABASE', '-'), 3); 

Result:- DATABASE 

In the above example we want to get database which is the 3rd character to '-'

IBM-NETEZZA-DATABASE

 1    2       3

In the same example if we want to get NETEZZA place 2 in the place of 3. 

Netezza string concatenation

Select 'NETEZZA'||'IBM' 

Result:- NETEZZAIBM

Points to keep in mind while doing string concatenation.

1) check if the columns are null-able 

if yes then one must use coalesce function to handle null values. Lets see if not what will happen.

SELECT 'NETEZZA'||NULL

result:- null 

SELECT 'NETEZZA'||COALESCE(null,'')

Result:- NETEZZA

netezza string replace

Select REPLACE('pageup','up',down');

Result:- pagedown

How to replace a specific character any where in the string any number of times.please refer to the below link

http://netezzamigration.blogspot.com/2014/09/sql-server-to-netezza-migration-part-2.html

netezza substring function

substr('VAMSHI',LENGTH('VAMSHI')-1)

RESULT:- HI

Formula for last 2 alphabets do Length -1

For 3 its length -2

For 4 its Length -3

Use when we need Right hand side digits or charade