Working with Netezza Stored Procedures

Netezza stored procedures are used to encapsulate the business logic and same time handle the exceptions. SQL provides the power to get and update the database information on the host server, and the procedure language provides the logic for if-then-else branching and application processing on the data. Read: Netezza RECORD Type Variable, Usage and Examples Netezza Stored Procedure ARRAY Variables and Examples For example, you may want to check the table if its existed in database before dropping it. You achieve this by creating stored procedure. e.g. CALL DROP_IF_EXIST(table_name); if you…

Continue ReadingWorking with Netezza Stored Procedures
Comments Off on Working with Netezza Stored Procedures

Working with Netezza Clustered Base Tables (CBT)

A Netezza clustered base tables (CBT) are user table that has data which is organized using one to four organizing keys columns. You can specify max four columns in organize on clause and those columns should not be a part of distribute on clause. An organizing key is a column of the table that you specify for clustering the table records; organizing table helps Netezza to save records in same or nearby extents. You can organize the records using "ORGANIZE ON" clause. Netezza does create zone maps on organizing columns, which will accelerate the performance of queries on that…

Continue ReadingWorking with Netezza Clustered Base Tables (CBT)
Comments Off on Working with Netezza Clustered Base Tables (CBT)

Groom in Netezza Tables and Databases with Aginity

Before getting into how to groom Netezza table and database, first let’s understand what grooming is? Use the GROOM TABLE command to maintain the user tables by reclaiming disk space for deleted or outdated rows. You can also use GROOM TABLE command to reorganize the tables by their organizing keys columns. End user can execute DML statements such as SELECT, UPDATE, DELETE, and INSERT operations while the data grooming is running. The SELECT operations run in parallel with the grooming operations and any INSERT, UPDATE, and DELETE operations run serially between the…

Continue ReadingGroom in Netezza Tables and Databases with Aginity
Comments Off on Groom in Netezza Tables and Databases with Aginity

Netezza Sequence and how to Create/Use it

A Netezza sequence is named objects in an individual database in Netezza, that can provide the unique value when get next value method. You can use sequence to generate unique numbers that can be used as surrogate key values for primary key values. Netezza Sequence Overview A sequence value is an integer that you can use wherever you would use numeric values. Netezza supports user sequences for the four integer types: byteint, smallint, integer, and bigint. You can even create a sequence with an initial value, an increment, a minimum and a maximum value.…

Continue ReadingNetezza Sequence and how to Create/Use it
Comments Off on Netezza Sequence and how to Create/Use it

Netezza Skew and How to avoid it

You will hear a lot about "Netezza Skew" if you are developing data warehouse on Netezza, Redshift, Teradata, hive or Impala database. The performance of the system is directly linked to uniform distribution of the user data  across all of the data slices in the system. When you create a table and then load the data into the system, the rows of the table should be distributed uniformly among all the data slices. If some data slices have more rows of a table than others this scenarios is called skew.…

Continue ReadingNetezza Skew and How to avoid it
Comments Off on Netezza Skew and How to avoid it

Importance of right Netezza Distribution key

This post is all about how data is distributed (Netezza distribution key) in Netezza server. Feel free to make comments or suggestions to improve it, or pass it on if you like. Let’s first understand how NPS stores the data on disk drives. Each Snippet Processor in the Snippet Processing Unit (SPU) has a dedicated hard drive has its separate CPU, FPGA, separate RAM memory, hard disks  and the data on stored on drive is called a data slice. Read: Changing Netezza Table Distribution Key Cluster Based Tables (CBT) in…

Continue ReadingImportance of right Netezza Distribution key
Comments Off on Importance of right Netezza Distribution key

Working with Netezza Zone Maps and Best Practices

Before going to Netezza zone maps first let’s understand extent.  An extent is smallest unit of storage on the disk. In fact it is smallest disk allocation on storage to store the data. Netezza would maintain the maximum and minimum value of the column that is stored in an extent (each extent is of 3MB in size) What are Netezza Zone maps? Netezza Zone maps is an internal mapping structure to the extent (smallest unit of storage) that takes advantage of internal ordering of the data.  These are automatically generated internal…

Continue ReadingWorking with Netezza Zone Maps and Best Practices
1 Comment

A Guide to load data into Netezza Database

Netezza data load is transferring data into Netezza appliance. There are several ways to transfer the data into appliance. Netezza Data Load using External tables These are tables stored as flat files on the host or client systems and not in the Netezza appliance database. Create table on top of the flat file that needs to be loaded to Netezza tables, then use that external table to load target table in Netezza appliance. As a result data will be copied to the target table. Flat Files An external table allows…

Continue ReadingA Guide to load data into Netezza Database
Comments Off on A Guide to load data into Netezza Database

How Netezza FPGA Works on Netezza SQL Query ?

The Netezza FPGA is a the heart of Netezza’s patented streaming architecture and is made of a result of framework of FPGA Accelerated streaming technology (FAST) engine. In this post we will see how FPGA works in Netezza SQL. In the following subsequent paragraphs we will see how FPGA works when we execute Netezza SQL query on host machine. Read: Netezza FPGA  - FAST Framework in Netezza The performance multiplying effect of the FPGA FAST engine: Control, Parse, Visibility, Project and Restrict is shown with help of small example of processing SQL query…

Continue ReadingHow Netezza FPGA Works on Netezza SQL Query ?
2 Comments

Netezza FPGA – FAST Framework in Netezza

Most of the companies around the globe using Netezza data warehouse appliance to perform their near real-time, complex analytics and data warehouse needs. Netezza FPGA is important part of NPS system. NPS performance is made of a result of framework of Netezza FPGA Accelerated streaming technology (FAST) engine. This engine is just like a turbo-charger to an already powerful engine. This FAST engine what makes the Netezza a powerful server. Read: Working with Netezza Zone Maps nzload command and its Usage nzsql Command and its Usage Netezza has introduced a FAST…

Continue ReadingNetezza FPGA – FAST Framework in Netezza
Comments Off on Netezza FPGA – FAST Framework in Netezza