Netezza Interview Questions - 2

1. What are the partitioning methods available in netezza?
There are two partitioning methods available in netezza:

• Random partitioning: Distributes the data randomly.
• Hash Partitioning: Distributes the data on the specified columns.

2. Up to how many columns you can specify in distribute on clause?
You can specify up to four columns in the distribution clause?

3.If you did not specify any distribute on clause while creating a table, what distribution netezza uses?
Netezza distributes the data on the first column and it uses Hash partitioning

4. Can you update the columns used in distribution clause?
No, the column that is used in distribution clause cannot be used for updates.

5. What data types are most suited for the columns specified in distribution clause?
Integer

6. How do you redistribute a table?
Use Create Table As (CTAS) to redistribute the data in a table. While creating the new table specify the distribute on clause to distribute the data on the new columns.

7. If you did not specify any distribution clause, how the Create Table AS (CTAS) will distribute the rows?
CTAS will get distribution from the original table.

8.How do you check the rows in a table are equally distributed in all SPU's or not?
To check the distribution of rows run the following query
SELECT datasliceid, COUNT(*)FROM <table name> GROUP BY datasliceid

9. What is collocated join?
When you join tables which are distributed on the same key and used these key columns in the join condition, then each SPU in netezza works 100% independent of the other, as the required data is available in itself. This type of joins is called collocated joins.

10. When netezza redistributes a table and when it broadcasts a table?
Whenever it is not possible to do a collocated join, netezza either redistributes the tables or broadcasts the table. When the table is a small one, then netezza broadcasts the table. Otherwise netezza redistributes the table.

11. How do you remove logically deleted records?
Whenever you delete a row in a table, it is not physically deleted. It is logically deleted by flagging the deletexid field in the table. NZRECLAIM utility is used to remove the logically deleted records. 

12. What is nzload?
Nzload utility is used load data from a file into a table. It is used to load bulk data quickly and simultaneously rejects erroneous content.

13. What are the ways to load a data from a table into a file?
Create an external table.
Use nzsql utility with -o option.

14. What are the different ways to load data from a file into a table?
Use nzload to load the data from a file into a table 
Create an external table and then load the original table using the external table.

15. How netezza updates a row in a table?
Netezza logically deletes the original row by flagging the deletexid column with the current transaction id and inserts a new row with the updated values. 

Netezza Commands

# how-to get help
command -h
# how-to get commands' help
commmand -hc
# how-to create an user
nzpassword add -u username -pw password -host hostname

#how-to export sql result set into a file
nzsql -F ";" -d $db_name -c "SELECT * FROM $table WHERE 1=1 AND LOAD_TM > to_date ( '2013-04-16 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') ORDER BY LOAD_TM DESC ;" -o /tmp/data/tmp.txt

nzsql -F " " -d SYSTEM -c "SELECT HW_HWID FROM SYSTEM.._V_SPU ;"

# how-to restore a db without the data
nzrestore -db DbNameToRestore -sourcedb BackUpSetDatabaseName -increment 3 -schema-only -dir /backup2

#drop session on some criteria ( use grep for filtering )
nzsession | grep idle | awk '/idle/ {print "nzsession abort -force -id "$1}' > /tmp/drop_sessions.sh

# show the disk usage
nzds show -detail | perl -ne 'split /\s+/;print $_[9] ." " ; print $_  ' | sort -nr

#backup
#where are stored the log files of the backup scripts
/export/home/nz/BackupScripts/log

#how-to view the latest 4 differential backups
for file in `ls -t1 /export/home/nz/BackupScripts/log/RunBackUpDiff* | head -n 4` ; do cat $file ; done ;

#how-to view the latest 10 backup files starting with the newest on top
ls -alt --time-style=long-iso /export/home/nz/BackupScripts/log/* | head -n 10

#how-to create an repot of the db and table sizes
sh /nz/support/nz_db_size >> /export/home/nz/Logs/nz-dbs-tables-sizes-report.txt

# how-to kill idle sessions
nzsession | grep SLOW_READER_ACCOUNT | awk '/idle/ {print "nzsession abort -force -id "$1}' > drop_sessions.sh

# check the swap space usage
/nz/kit/bin/nzstats -type spu

# show sub command help
/nz/kit/bin/nzstats -hc show

# check the number of active users per database
/nz/kit/bin/nzstats show -type database

/nz/support-TwinFin-5.0-100107-1556/bin/nz_view_references -h

nzhw -detail -type SPU

#how-to find out what where has been backed up
for i in {1..4}; do find "/backup${i}" -maxdepth 3 -mindepth 3 ; done ;

#check the exact errors from the last 1000 backup logs
for file in `ls -lrt /nz/kit.7.0.P4/log/backupsvr/*.log | perl -ne 'split /\s+/;print $_[8]."\n"' | tail -1000` ; do grep -nH Error $file ; done ;


#how-to check from odbc.ini the name of the db by the connection string
export IniFile=/informatica/9.1.0/ODBC6.1/odbc.ini
export IniSection=nz_etl_test
sed -e 's/[[:space:]]*\=[[:space:]]*/=/g' \
      -e 's/;.*$//' \
      -e 's/[[:space:]]*$//' \
      -e 's/^[[:space:]]*//' \
      -e "s/^\(.*\)=\([^\"']*\)$/\1=\"\2\"/" \
      < $IniFile \
      | sed -n -e "/^\[$IniSection\]/,/^\s*\[/{/^[^;].*\=.*/p;}" | grep -i database

#how-to search for "broken" views which need to be recreated
/nz/support-TwinFin-5.0-100107-1556/bin/nz_view_references
/nz/support/bin/nz_view_references

#how-to show the number of active users per db
/nz/kit/bin/nzstats -type database | sort -n -k8


#how-to check current system sfw version and revision
nzrev; nz_stats


#how-to check the current version of the netezza support scripts
nz_help -version

# show the utilization of the components
nzsqa proctbl -all | grep -i util

#how-to show hardware issues
nzhw show -issues

#important paths
#where are the netezza cli binaries
/nz/support/bin/
/nz/kit/bin/
/nz/kit/bin/adm/

#important logs
/var/log/nz/heartbeat_admin.log
/nz/kit.7.0.P4/log/postgres/pg.log

#important conf files
/etc/ha.d/ha.cf

# check the clustering and resource group status on NPS
crm_mon -i5

# report db sizes on
nz_db_size -summary

# Start the nzAdmin windows tool , start , run , type
"C:\Program Files\IBM Netezza Tools\Bin\NzAdmin.exe"

#how-to check for locks on netezza tables
nzsql -F ";" -d $db_name -c "SELECT * FROM _T_PG_LOCKS;" -a | grep -i TERRA

# how-to check which tables do need grooming
perl /nz/kit.7.0.P4/bin/adm/tools/cbts_needing_groom -db TERRA_STG_PROD

# now-to check for haning sessoins
export db_name=SYSTEM;nzsql -F ";" -d $db_name -c " select q.qs_planid, q.qs_sessionid, q.qs_clientid, s.dbname, s.username, q.qs_cliipaddr,
 q.qs_state, q.qs_tsubmit, q.qs_tstart, case when q.qs_tstart = 'epoch' then '0' else abstime 'now' - q.qs_tstart end,
initcap(q.qs_pritxt), case when qs_estcost >= 0 then ltrim(to_char(cast(qs_estcost as float)/1000,'99999999999999999.999'))
else ltrim(to_char(cast((18446744073709551616 + qs_estcost) as float)/1000, '99999999999999999.999')) end,
q.qs_estdisk, q.qs_estmem, q.qs_snippets, q.qs_cursnipt, q.qs_resrows, q.qs_resbytes , q.qs_sql from _v_qrystat q, _v_session s
where 1=1 and q.qs_sessionid = s.id and q.QS_STATE not in ( 2,3);" -a



# find out the heaviest queries today
export db_name=SYSTEM;nzsql -F ";" -d $db_name -c " SELECT * FROM _V_QRYHIST
where 1=1 AND QH_TSUBMIT > CURRENT_DATE ORDER BY QH_ESTCOST ASC" -a

export db_name=SYSTEM;
#how-to list the columns of a table
export sql=" SELECT ' , ' || NAME || '.' || ATTNAME  from _V_RELATION_COLUMN WHERE 1=1
AND NAME LIKE 'DIM_PAYER_ADDRESS'
ORDER BY NAME , ATTNUM
;";

nzsql -F ";" -d $db_name -c "$sql" -a


#how-to export a list of netezza tables into csv files :
export db_name=SET_HERE_YOUR_DB_NAME
export schema_owner=SET_HERE_YOUR_SCHEMA_OWNER


for table in `echo table_name1 table_name2 `; do (nzsql -F ";" -d $db_name -c "SELECT * FROM $db_name.$schema_owner.$table" -A -q -o /tmp/$db_name.$table.csv.tmp ) ; head --lines=-1 /tmp/$db_name.$table.csv.tmp > /tmp/$db_name.$table.csv; rm -fv /tmp/$db_name.$table.csv.tmp ; done ;



nz_responders

#
# Purpose:
# To provide a simple cheat sheet for netezza administration and command line utilities

Netezza Importing Data from File to a Table

 The following method will allow you to import data from a file(any delimited file) into a table

 The sample file is a tab delimited file with two column in it, one as char and other as integer, first row has column name

here is the query to read data from the file and dump into a table called TEMP_TABLE
 insert into TEMP_TABLE
SELECT
 COL1,
 COL2
FROM EXTERNAL 'C:\Sample.txt'
(
COL1 varchar(255),COL2 integer)
USING (
  remotesource 'ODBC' delimiter '\t'
  skiprows 1);

Netezza ISNUMERIC Data Check Logic

Netezza dosen't have any isnumeric check function, so in order to find out whether the column has non-numeric, use the following logic

nvl(instr(translate(Column_Name,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'),0)

if the column has non numeric then it will return 1 else it will be 0, I haven't included the special characters in the logic, if you have '%,$,£..' then include them in the translate part and it will work



One of our visitor posted the following method

istrue(length(translate(Column_Name,'0123456789',''))=0) as isnumeric

If the resulting length is zero then it is numeric, or the string was empty. If the latter is a possibility then you could add another check

istrue(length(translate(Column_Name,'0123456789',''))=0 and length(Column_Name)>0) as isnumeric

Netezza Optimizer Parameters

Netezza has 3 internal planner

• Fact Relationship Planner (factrel_planner)

• Snowflake Planner

• Star Planner

In the course of a query plan, the optimizer may use multiple planners in a particular plan, based on least cost

 Fact Relationship Planner

The fact relationship or factrel planner should be enabled for most star or snowflake schemas. It is enabled in

/nz/data/postgresql.conf by adding the following line:

enable_factrel_planner = true

By default, the factrel planner will classify any table over 100,000,000 rows as a FACT table. The optimizer will

try and avoid broadcasting or moving FACT tables as it is almost always much less expensive to broadcast a

smaller dimension table. Depending on the size of the tables involved, the default setting may be inappropriate.

The default threshold can be adjusted by setting an override in postgresql.conf. In the example below, we set it

to 50 million rows, which would cause the optimizer to avoid broadcasting tables of more than 50 million rows.

factrel_size_threshold = 50000000

Short Topics in Netezza Administration Common Optimizer Parameters

 Snowflake Planner

The snowflake planner is enabled in /nz/data/postgresql.conf by adding the following line:

enable_snowflake_planner = true

For the snowflake planner to classify a table as a FACT table, by default it must be at least 1 million rows and at

least 10 times larger than the next smallest table. In most Netezza environments, the default value of 1 million is

too low, and should be adjusted higher, e.g.:

snowflake_min_fact_size = 40000000

The snowflake_min_ratio parameter controls the ratio required for a table to be classified as a FACT table.

Star Planner

The star planner is enabled in /nz/data/postgresql.conf by adding the following line:

enable_star_planner = true

In most cases, no further adjustments are necessary. In some cases, adjusting the parameter

num_star_planner_rels from its default value of 8 can be beneficial. Adjusting this number up slightly will

cause the optimizer to search more deeply for a lower cost plan which can help with some queries that are

running out of system resources. Netezza does not recommend setting this value above 10; in most cases the

default value of 8 is sufficient. Should a higher value be desired, it can be set as follows:

num_star_planner_rels = 10

Netezza Job Interview Questions

Explain FPGA and how is it useful for query performance.

FPGA: Field Programmable Gate Array (FPGA) is located on each SPU. Netezza is different from other architectures. Netezza can do a “hardware upgrade” through software by using FPGA. Hardware is reconfigured during install.

While reading data from disk, FPGA on each SPU also helps in ‘filtering’ unnecessary data before getting loaded into memory on each SPU. This way, FPGA does not overwhelm with all the data from disk.

What is a zone map.

Zone map in Netezza is similar (concept wise) to partitions in Oracle. Netezza maintains map for data so that it does relies on zone map to pull only the range it is interested in. For example, if we need to pull out data from Jan 2009 till June 2009 from a table that is distributed on date column, zone map helps us to achieve this. Zone map is maintained by Netezza automagically, no user intervention needed. Zone mapping is done at a block (extent) level. Netezza has zone maps for all columns (not just distributed column) and includes information such as minimum, maximum, total number of records.

How do you deal with historical data, with respect to zone maps.

Sort data first, based on historical data (for example, date) and load this in using nzload.

What are different ways to load

1. nzload
2. External tables
3. Create table AS (aka, CTAS).
4. Inserts (Eeeewee!!)

Does everything gets cached in Netezza (or any other data appliance).

Typically only schema and other database objects are cached in appliances. Data is not cached, in general. In most cases, data is not saved any where (in any cache or on host computer) and is streamed directly from SPU to client software.

What is the best data appliance.

Obviously, it all depends. This is my (limited) view:

1. From features respect, Green Plum.
2. Popularity with a bit of hype, Netezza.
3. Matured and well respected, Teradata.
4. With existing database integration, Dataupia.

Largest implementations:

* Teradata: 72 nodes (two quad-core CPUs, 32GB RAM,104 / 300GB disks per node) and manages 2.4PB.
* Greenplum: Fox Interactive Media using a 40-node, Sun X4500 with two dual-core CPUs, 48 / 500GB disks, and 16 GB RAM (1PB total disk space)
Source: Vertica’s Michael Stonebraker!

How is load achieved in Netezza and why is that quick / fast.

Loads by pass a few steps that typically a query would go through (a query goes through plan generation, optimization and transaction management). Loads are done in terms of “sets” and this set is based on underlying table structure (thus loads for two different tables are different as their sets are based on table structures). Data is processed to check format and distribution of records calculated very quickly (in one step), fills into ‘set’ structure and writes to storage structure. Storage also performs space availability and other admin tasks, all these operations go pretty quick (think of them as UNIX named pipes that streams data and SPU stores these records).

When are we likely to receive incorrect (aggregate) results.

Very rarely a driver may return aggregated results that are still getting processed back to client. In this case, client may assume that calculation is complete, instead of updating with latest or final results. Obviously, driver has to wait for Netezza to complete operation on host computer, before delivering results.

Explain how data gets stored in Netezza and how does SPU failover take place.

Data is stored based on a selected field(s) which are used for distibution.

==Data (A)==> Hash Function (B) ==> Logical SPU identifier list (C) ==> Physical SPU list (D) ==> Storage (E)

When data arrives, it is hased based on field(s) and a hash function (B) is used for this purpose. For example, for a hypothetical 32 node system system, logical SPU identifier list has 32 unique entries. If there are 1000 hashed data items from (B), there are 1000 entries in (C), all having only 32 SPU entries (a number of data items go to the same SPU, thus multiple (B) entries map to same (C)). For instance, (C) has values [3,19,30,7,20,25,11,3,22,19....]. This way, 1000 data entries are mapped. (D) has physical IP address of both primary and failover SPU. If there is a failover, this is the only place where Netezza need to update its entries. Same goes for a system that has a new SPU added. It is a little complicated, in principle, this is the concept.

what are 4 environment variables that are required. What are different states on Netezza.
Environment variables: NZ_HOST, NZ_DATABASE, NZ_USER and NZ_PASSWORD
* Online: Normal or usual state.
* Stopped: Netezza will shutdown after completing current queries, no new queries allowed.
* Offline: Waits for completion of current queries, new or queries in queue receive error.
* Paused: Same as above, but no error displayed. Typically caused during Netezza bootup or startup.
* Down: Just plain down, could be due to Netezza server problem or user initiated.

Does Netezza support concurrent update of the same recordIn case of conflict in which the same record is set for modification, Netezza rolls back recent transaction that is attempted on the same record, in fact same table. This is generally acceptable in DW environments. Netezza does support serialization transactions and does not permit dirty reads.

How Netezza updates records. Give an idea of how transactions are maintained and how read consistency is maintaned.
Netezza does not update records in place, it marks records with delete flag. In fact, each record contains two slots, one for create xid another for delete xid. Delete xid allows us to mark a record with current transaction for deletion, up to 31 transactions are allowed in Netezza for all tables. As noted earlier, only one update at a time allowed on the same table though. Here update refers to transactions that are not committed yet. Coming back to delete xid, this is how Netezza maintains transaction roll back and recovery. Once a record is modified, it’s delete xid is given transaction id; this is changed from previous value of 0, all records when loaded will contain 0 for delete xid. Note that FPGA uses its intelligence to scan data before delivering them to host or applications.
Sample data:
[ROW id][Create xid][Delete xid]
[R1][T1][0]                           // First time a record is loaded, record R1
                                                  // After some time, updating the same record
[R1][T1][T33]                      // Record R1 is updated; note T33
[R33][T33][0]                     // New update record R33; similar to a new record this has zero for Delete Xid

If the record is deleted, simply deletion xid will contain that transaction id.
* Based on the above, how do you know a record is the latest. It has zero in delete xid flag.
* Extending same logic, how do we know a record is deleted. It has non zero value in delete xid flag.
* How do you roll back to transaction. Follow similar to above listing, we can roll back a transaction of our interest.
Note that transaction id is located in create xid flag and that is our point of interest in this case. From what I know, row id and create id is never modified by Netezza.

What happens to records that are loaded during nzload process, but were not committed.They are logically deleted and administrator can run nzreclaim, we may also truncate table.

Can a group become a member of another group in Netezza user administration. Can we use same group name for databases.
In Netezza, public group is created automatically and every one is a memeber of this group by default. We can create as many groups and any user can be member of any group(s). Group can not be a member of another group. Group names, user names and database names are unique. That is, we can not have a database called sales and a group also called sales.

How can we give a global permission to user joe so that he can create table in any database. 
Login into system database and give that permission to user by saying “grant create table to joe;”

What permission will you give to connect to a database.List.  Grant list, select on table to public (if logged into sales database, this allows all users to query tables in sales database).

Do we need to drop all tables and objects in that database, before dropping a database.
No, drop database will take care of it.

What constraints on a table are enforced.Not null and default. Netezza does not apply PK and FK.

Why NOT NULL specification is better in Netezza.
Specifying not null results in better performance as NULL values are tracked at rowheader level. Having NULL values results in storing references to NULL values in header. If all columns are NOT NULL, then there is no record header.

Create Table AS (CTAS), does it distribute data randomly or based on table on which it received data.Response: Newly created table from CTAS gets distribution from the original table.

Why do you prefer truncate instead of drop table command.Just empties data from table, keeping table structure and permission intact.

When no distribution clause is used while creating a table, what distribution is used by Netezza.First column (same as in Teradata).

Can we update all columns in a Netezza table.No, the column that is used in distribution clause cannot be used for updates. Remember, up to four columns can be used for distribution of data on SPU. From practical sense, updating distribution columns result in redistribution of data; the single most performance hit when large table is involved. This restriction makes sense.

What is dataslice and SPU.For me, they are the same! Of course, this answer is not accurate reply in your interview(s).

What data type works best for zone maps.
Zone maps work best for integer data types.

What feature in Netezza you do not like.

Of course, a large list, especially when compared to Oracle. PK and FK enforcement is a big drawback though this is typically enforced at ETL or ELT process [ELT: Extract, Transform and Load. Note that 'Transform' and 'Load' can happen within Netezza].

Netezza Date Functions Use

SELECT date(current_Date) from dual;
–10/28/10 (if current date is Oct 28, 2010)
SELECT (current_timestamp) from dual;
/*
SELECT DATE_PART(‘MONTH’,current_date)from dual;
SELECT current_time, DATE_PART(‘SECOND’,current_time)from dual;
SELECT current_timestamp, DATE_PART(‘SECOND’,current_timestamp) from dual;*/
select add_months(current_date,-1) from dual;
— DATE_TRUNC FUNCTION:
SELECT add_months (date_trunc(‘month’, current_timestamp),-1) from dual;
select add_months (date_trunc(‘month’, current_date),-1) from dual;
–ADD_MONTHS FUNCTION:
select add_months(current_date,-1) – date_part(‘day’, current_date) from dual;
–8/31/10
select add_months(current_date,1) – date_part(‘day’, current_date) from dual;
–10/31/10
–DATE_PART FUNCTION:
select date_part(‘day’, current_date) from dual;
–28
select date_part(‘month’, current_date) from dual;
–10
— date_part will NOT work here:
—SELECT add_months (date_part(‘month’, current_date),-1) from dual;
select add_months(current_date,0) from dual;
–10/28/10
select add_months(current_date,-1) from dual;
select add_months(current_date,-28) from dual;
select add_months(current_date,-1) – date_part(‘month’, current_date) from dual;
–9/18/10
select add_months(current_date,0) – date_part(‘day’, current_date) from dual;
–9/30/10
SELECT date(current_Date) from dual;
–DOESN’T WORK
SELECT date(current_Date,-1) from dual;
—
SELECT (current_Date)-1 from dual;
select add_months(current_date,-1) – date_part(‘day’, current_date) from dual;
select add_months(current_date,1) – date_part(‘day’, current_date)
from dual;
select date_part(‘day’, current_date) from dual;
select date_part(‘month’, current_date) from dual;
select add_months(current_date,0) from dual;
select add_months(current_date,-1) from dual;
select add_months(current_date,-1) – date_part(‘month’, current_date) from dual;
select add_months(current_date,0) – date_part(‘day’, current_date) from dual;
select extract (day from current_date)-7;

How to use Netezza External Tables

You can use Netezza's external table to view data from an external file and use it like a database table. When you create an external table, the actual data still sits in that physical external file, but you can query it from Netezza like you can query a normal database table.

You can also use external tables to export data out of a Netezza table into a file.

From Netezza's Data Loading Manual  - An external table allows Netezza to treat an external file as a database table. An external table has a definition (a table schema), but the actual data exists outside of the Netezza appliance database. External tables can be used to access files which are stored on the Netezza host server or, in the case of a remote external table, Netezza can treat a file on a client system as an external table (see REMOTESOURCE option).

Below are 2 examples to show you how to do both data export and "import".

I am using Aginity Workbench for Netezza on my windows machine (which comes with a netezza odbc driver), and I have a text file stored in my local drive C:\temp\testfile.txt which has the following column header and 3 rows:


employeeid,employeename,salary
1,'John Lee',100000
2,'Marty Short', 120000
3,'Jane Mars', 150000


CREATE EXTERNAL TABLE ext_tbl_employees(
employee_id integer, 
employee_name character varying(100), 
salary decimal (10,2))
USING (
dataobject('c:\temp\testfile.txt') 
remotesource 'odbc'
delimiter ','
skiprows 1);

Then in your Aginity workbench object browser, expand the folder External Tables, and you will your new external table listed there.

You can then query the table just like a normal database table:


select *
from ext_tbl_employees


You can also create a transient external table, in which it only exists for the duration of your query, which means the external table definition does not persist in the system catalog.


--transient external table
SELECT 
employee_id,
employee_name,
salary
FROM EXTERNAL 'c:\temp\testfile.txt' 
(
employee_id integer, 
employee_name character varying(100), 
salary decimal (10,2))
USING (
remotesource 'odbc'
delimiter ','
skiprows 1);

Transient external table is also a very useful way to export data from a netezza database out to a text file. You can export not just an entire table, but the output of any sql statement. The beauty of it is you don't have to specify the schema definition of the data, which can save you a lot of typing:

create table mytesttable (
studentid integer,
studentname character varying(100)
);

insert into mytesttable 
select 1, 'Jane Doe'
union
select 2, 'John Smith';

CREATE EXTERNAL TABLE 'c:\Temp\ExportDataTest.csv' USING (remotesource 'ODBC' DELIM ',') AS
SELECT *
from mytesttable;

drop table mytesttable;


Note: if the file already exists, any existing data will be deleted by Netezza before it inserts data.

If there's comma in your data, and you want to export to csv, use escapeChar option:

CREATE EXTERNAL TABLE 'c:\Temp\OfferAttributesNightlyFeed.csv' USING (remotesource 'ODBC' DELIM ',' ESCAPECHAR '\') AS
SELECT *
from COUPONATTRIBUTESTEST;

Netezza Performance Check Querys

----Active Moniter---
select current_timestamp, count(*) from _v_qrystat

---Swapspace Monitor--
select current_timestamp, (sum(USED_KB) / (1024))::DECIMAL(9,1)  as used from _vt_swapspace
--NPS Memory Allocation--
select current_timestamp, (select sum(v1.bytes)/(1024*1024) from _vt_memory_usage v1 where hwid = 0)::INTEGER as host_total, avg(a.tot)::INTEGER, max(a.tot)::INTEGER from  (select  sum(bytes)/(1024*1024) as tot from _vt_memory_usage where hwid <>0 group by hwid)  a
---Throughput Per Minute---
select current_timestamp, case when ( max(qh_tsubmit) - min(qh_tsubmit) = 0) then 0 else (count(*) * (60.0 / ( max(qh_tsubmit) - min(qh_tsubmit))::float ))::integer end as qpm from _v_qryhist where qh_tsubmit > current_timestamp - interval '1 minutes'
--Throughput Per Hour--
select current_timestamp, case when ( max(qh_tsubmit) - min(qh_tsubmit) = 0) then 0 else (count(*) * (3600.0 / ( max(qh_tsubmit) - min(qh_tsubmit))::float ))::integer end as qph from _v_qryhist where qh_tsubmit > current_timestamp - interval '60 minutes'
--Query Performance----
select current_timestamp, avg(qh_tend - qh_tsubmit) as foo, avg(qh_tstart - qh_tsubmit) as foo2 from  _v_qryhist where qh_tend::timestamp > current_timestamp - interval '1 minutes'

---Que Moniter--
select current_timestamp, sn.sn_short, sn.sn_long, gra.gra_short, gra.gra_long from (select sum(plans_waiting_short) as sn_short, sum(plans_waiting_long) as sn_long from _v_sched_sn where entry_ts = (select max(entry_ts) from _v_sched_sn)) sn CROSS JOIN (select sum(plans_waiting_short) as gra_short, sum(plans_waiting_long) as gra_long from _v_sched_gra where entry_ts = (select max(entry_ts) from _v_sched_gra)) gra

--Cpu use--
select current_timestamp, avg(HOST_CPU) * 100.0 as host_cpu_percent, avg(SPU_CPU) * 100.0 as spu_cpu_percent, max(MAX_SPU_CPU) * 100.0 as max_spu_cpu_percent from _vt_system_util where entry_ts / 1000000 > ((select max(entry_ts) from _vt_system_util) / 1000000 - 180)

--Disk Utilization---
select current_timestamp, avg(HOST_DISK) * 100.0 as host_disk_percent, avg(SPU_DISK) * 100.0 as spu_disk_percent, max(MAX_SPU_DISK) * 100.0 as max_spu_disk_percent from _vt_system_util where entry_ts / 1000000 > ((select max(entry_ts) from _vt_system_util) / 1000000 - 180)
--Memory Utilization---
select current_timestamp, avg(HOST_MEMORY) * 100.0 as host_memory_percent, avg(SPU_MEMORY) * 100.0 as spu_memory_percent, max(MAX_SPU_MEMORY) * 100.0 as max_spu_memory_percent from _vt_system_util where entry_ts / 1000000 > ((select max(entry_ts) from _vt_system_util) / 1000000 - 180)


Check row count by distribution key

SELECT datasliceid,
       COUNT(*) Num_rows
FROM   IMPRINT_LOOKUP
GROUP BY datasliceid;



delete duplicate records
delete from TABLE_A where rowid not in (
SELECT min(rowid)
  FROM TABLE_A
  group by
COL1,COL2,COL3..
)