Stored procedure in Netezza

NZPLSQL

There are several blogs for creating anonymous or named blocks of code in all the other databases be it Oracle or MS SQL,but I could see a very few post about Netezza. This could be primarily because Netezza is not being used by many companies and also the usage of named or Anonymous blocks might not come up in Netezza very frequently (since it is primarily used for data mining on large data volumes which can simply be done using a single NZSQL query). Based on my knowledge and work experience with Netezza let me jot down what comes to mind .

Like all the other DB s Netezza also has Anonymous blocks,Stored Procedures,Collection,Functions and so on.Lets examine one by one and also try to differentiate it with other DB's as well.

Anonymous blocks :
     
               There would be declare section and a begin and end section in the Anonymous block and an optional exception session as well.Any block of code that lies in between the begin and end would be executed.The main purpose of Anonymous block could be to execute other named blocks or to execute some blocks for testing purpose which need not be stored as an object in the Database.

DECLARE

       Aid Integer;

       Abc Boolean;

       Pname character varying(100);

BEGIN

          Pname := 'Harvard Thomas';

     

          RAISE NOTICE ' The name of the person is %',Pname;

END;

Upon executing the block the name would be printed with the notice statement .This works perfectly for testing scenarios or for creating another named block and so on.lets examine that at a Later point of time.

Stored Procedure :

stored procedure is one of the most important named block in any Database Query language.The main purpose of using stored procedure is that it gets compiled and stored in the DB as an object and can be reused at a later point of time.If our logic requires us to use loops,branches or any other statements that cannot be executed in one single query ,we might have to do it through a stored procedure.

The syntax of Stored procedure is slightly different in Netezza and lets examine that

CREATE OR REPLACE PROCEDURE my_testing() returns varchar

LANGUAGE NZPLSQL AS

BEGIN_PROC

    DECLARE

          string varchar;

    BEGIN

          string :='This string is wrong';

          RETURN string;

    END;

END_PROC;

I have highlighted the keywords in caps .The create or replace procedure followed by the procedure name (here it is my_testing) and the parenthesis says the procedure does not accept any parameters and then returns varchar means that the procedure will return a character value.The keyword language nzplsql is unique to Netezza which says the programming language is nzplsql and the begin_proc and the end_proc are the opening and the ending blocks in nzplsql and inside this there is an anonymous block with declare begin and end.Here we are declaring a string which is a character and in the body assigning a value 'this string is wrong' to that string.

This is a pretty simple procedure that accepts no parameter and returns the string.

Executing the procedure:

we can execute the procedure my_testing using one of the following commands

CALL  my_testing();

EXEC my_testing();

EXECUTE my_testing();

EXECUTE PROCEDURE my_testing();

select my_testing();

One thing to note here is that the select cannot have any other clause like from or where .

Viewing the procedure :

Privileged users can use the command show procedure for obtaining the code for procedures.It accepts the following arguments.

SHOW PROCEDURE VERBOSE;

SHOW PROCEDURE ALL;

SHOW PROCEDURE PROCEDURE_NAME;

  command to view the contents of the procedure .We can also view the procedure using NZADMIN  tool to view the procedure .

Altering the procedure :

We can use the below command and  do the modification to the procedure body if needed.

ALTER PROCEDURE <procedure_name> 

Dropping the procedure :

We can either hide or drop a procedure .For dropping the procedure, use

DROP PROCEDURE  <procedure name >

Now that we have covered the basics of how to Create,Alter,Drop or View a procedure,we can go a little deep into the additional features available with Netezza for stored procedures.

Security Aspects while creating a procedure :

One of the key concepts of creating a named block is the security concerns it possess.A procedure may access several DB objects inside the body like Tables,views etc.If the table data needs to be prevented from the user then the stored procedure should be designed such a way that the data in the table is not accessible to all,but only to the person who created the table.For this there is a feature in Netezza which is EXECUTE AS CALLER 

Lets see the syntax for that 

CREATE OR REPLACE PROCEDURE my_testing() returns varchar

LANGUAGE NZPLSQL

EXECUTE AS (CALLER | OWNER)

BEGIN_PROC

     DECLARE 

     BEGIN

     END;

END_PROC;

Here the EXECUTE AS CALLER means that the procedure will be executed with the permissions available for the caller and hence the underlying data in the table will be accessible to the user only if he has permissions on the table .

The default option is EXECUTE AS OWNER is the default option while creating the procedure and this means that the procedure will execute with the owners permissions and whoever runs the procedure can view the data in the underlying table since it has the owners permissions.

Hence if we are concerned about the security aspects then we should go for the first option ie execute as caller while designing the procedure.There is another important aspect in security ie what permissions a user should be given.So If you happen to be an Admin then this is one of your major concerns since this issue can caue a huge havoc.Permissions can be granted to a procedure in the following way

GRANT CREATE PROCEDURE  TO abc;

This implies that now abc can create a procedure .

GRANT CREATE PROCEDURE TO group dev;

This means that all the users in the group dev can now create a procedure 

GRANT EXECUTE ON my_testing() to abc;

This means that now abc can create any procedure but execute only my_testing()

we can also revoke the permissions on a procedure using the revoke command 

REVOKE CREATE PROCEDURE from abc;

REVOKE EXECUTE ON my_testing() from ABC;

Auto commit in NZPLSQL procedures:

we have two options available in Netezza  ie auto commit off/on to specify whether we need to execute the statements one by one or by a single block,The default mode is auto commit off in which the entire procedure will run as a single command and if we need commit/rollback on particular statement we can enable the auto commit on statement for that .

Altering the above procedure to include auto commit we can write the code as \

CREATE OR REPLACE PROCEDURE my_testing() returns varchar

LANGUAGE NZPLSQL

EXECUTE AS (CALLER | OWNER)

BEGIN_PROC

     DECLARE AUTOCOMMIT (ON| OFF)

     BEGIN

     END;

END_PROC;

lets look at the difference between auto commit on and off in more detail.In normal plsql code when we execute the procedure it executes as a single block 

eg 

BEGIN
       statement1;
       commit;
       statement2;
      rollback;
      statement3;
END;

Assume the above code is run in auto commit off mode.in this case the statement 1 will execute and then commit the transaction.After that statement 2 will execute and rollback and statement3 will execute and it will not be committed/rollbacked. when I commit the procedure after executing then the third statement will be committed.In case if an error occurred in the first statement then the procedure will not execute.If the error is on the second statement then first statement will be committed and the second statement will not execute and the control will come back to the main.If the error is say on the third statement then the first statement will be committed and the second will be rollbacked and the control will come to the main.Even if we give a commit after calling the procedure the second statement will be rollbacked.

Now let us execute the statement using auto commit on and the block need not require commit or rollback statements here .

BEGIN
      statement 1;
      statement 2;
      rollback;
     statement 3;
END;

Here the statement 1 will be executed and then it will be automatically committed since we have enabled the auto commit on and then the statement 2 will execute and that also will be committed irrespective of the roll back command issued and then statement 3 also will be committed.If any of the statements fail the control will come back to the main with the previous transactions committed. Roll backing the commands after executing will not work here.

So the question is when should we go for Auto commit on/off.It is always safe to use auto commit off since the major transactions can be rechecked before committing them.But in case of any auditing purpose we need to track all the operations on a table we can go for auto commit on.

Loading Datas using Netezza Utilities

Lets look at some of the Utilities available in Netezza 

Nzload :

nzload is a command line utility which can be run in the local or remote client.Nzload commad is used for bulk loading of data into tables .It is like external table but this works faster and multiple files can be parelley loaded into tables.For loading a file first create the table and then use the nzload command to load into table

Syntax for nzload :

nzload -db <database name> -h <host name> -u <username> -pw <password>

-t <tablename> -df <datafilename> -delim <delmiter>  -dateDelim <date delimiter in single quotes> -dateStyle <date format> -bf <bad filename> -lf <log filename>

nzload command returns the following status :

0--> the data is loaded successfully into the target table

1--> the data is loaded into the table but with errors and the errors are within the maxErrors option

2-->nzload failed

Other options available are 

maxErrors-->we can set the limit for errors

Skiprows --> for skipping header records

Fillrecord-->fills the trailing spaces to nulls

ignoreZero-->ignores zero byte record 

Badfile and Logfile :

nzload command generates log and bad files which will be in the format <tablename>.<database name>.bad/log.the files by default will be present in the working directory. we can override the defualt location by specifying the location in outputDir parameter .We can override the log and bad file usinf -lf and -bf parameters.Nzload internally creates an external table ,loads the data into the table and then drop the table after loading .Statistics are automatically generated for these tables upon completion

External tables :

External tables can also be used for loading data into the table 

statistics are automatically generated for an external table.we can drop or insert records into an external table.However we cannot delete or truncate an external table.Also we cant use it in union operation or for multiple table joins.When we create an external table ,it is a logical concept and the data actually resides in the file.When we select or insert records into external table data gets added in the file associated with the table.

Syntax for external table :

CREATE EXTERNAL TABLE <table_name>
[sameas table] | ([column_name [,...]])
USING (DATAOBJECT (<host_file_name>) DELIMITER <field_delimiter>) [options]

The options are same as that of unload command._v_external and _v_extobject can be used to get the  

 meta data about the external tables.

NZSQL:

Execution Plan in Netezza

Cost Based Optimizer :

Netezza also generates an execution plan like other databases.Netezza has a cost based optimizer which creates a plan based on the query and then exeutes the query with the least cost one.

Optimizer generates the plan based on number of statistics some of which are described below

1.Number of rows in a table
2.Minimum and Maximum value of each column involved in the query
3.Number of extends in each table and the total number of extends in each data slice with the largest skew

Plan generation :

When a query is generated it automatically generates the plan and store it under the directory nz/data/plan directory in the host and the c code will be under the directory nz/data/cache .Cache directory contains the code for the  recent query to cmpare them with the new query to prevent re compilation of the same query

We can also view the explain plan for the query to check whether everything is inline using the below commands

explain verbose <sql query>;
explain plantext <sql query>;
explain plangraph <sql query>;

There are several points to check in the explain plan :

• Estimated cost which shows the cost of the query .If the cost is very high we might have to revisit the code to ensure that it is correct.
• Estimated rows which shows the number of rows.If the estimated rows is less than expected it means we might have to generate the statistics for the objects
• Percentage estimation of the cost.
• Restrictions - which shows the where clause used in the query
• Projections -  The columns used in the query
• SPU Distribute on column - this means that the table is redistributed based on the column .We need to check whether proper column is used for re distribution.
• SPU Broadcast - This means that the copy of the entire table is made available in all the snippets.This is more suitable for a large fact table joined with small dim tables.
• Optimizers performs hash,merge and nested loop joins of which hash joints are more efficient .We can check in the plan for the type of joins used .If a floating point integer is joined with an integer,then hash join may not be implemented where as it is expected.

Main Cause of performance issues in NZSQL:

1.Table skew- In correct distribution of the key resulting in more data being stored in one disc when compared to the others.
2.Process skew- even if the table is distributed evenly,fetchign data from only one slice may result in process skew
3.Zone maps not being assigned properly .Only for Integer columns zone map will be defined.so distributing on varchar or varchar2 will not create zone maps
4.Proper keys not beign used resulting in re distribution or broadcast  of big fact tables

Netezza Basics

Important things to note in the Netezza Architecture :

 Host :

          

The host is a high performance Linux server setup in the active passive  mode.This host is responsible for compiling the SQL queries into executable blocks called snippets,for creating an optimized query plan,distributes th snippets to nodes for execution.  

Snippet Blades or S blades :

Sblades or snippet blades are independent server containing multi core CPU s,multiple engine FPGA and its own memory all designed to work concurrently to deliver peak performance .Each snippet is connected to a set of 8 disks.

Field Programmable Gate Array:

FPGA is the key component in the Netezza architecture.It has the following engines embedded in it .

Compress engine - This uncompress the data in wire speed transforming  each block on disk into 4 to 8 blocks in memory.

Project and Restrict engine- This further increases the performance by filtering out the rows and columns mentioned in the select and the where clause.

Visibility engine -Filters out the rows that should not be seen by the Query ie the data that is not committed.

Disk enclosures :

The disks are high density ,high performance ones.Each table data is uniformly distributed across various disks.A high speed network connects the disk with the s blades so that the data gets streamed at maximum rate possible.

Optimizer:

The host compiles the query and generates an execution plan.The optimizer intelligence is a key factor in performance of the query .The optimizer makes use of all the nodes in order to get an up to date statistics of all database objects referenced in a query.Another example of optimizer efficiency is in calculating the join order.if for eg a small table is joined against all the fact tables ,the optimizer can showcase the small table to all the s blades while keeping the large fact table distributed across the snippets.This approach minimizes the data movement while taking advantage of the parallel processing.The optimizer minimizes the I/O and data movement ,the two factor slowing performance in the warehouse system.The other functions of optimizer includes

Determining the correct join order

Rewriting expressions

Removing redundancy in the SQL operations

Compiler :

The compiler converts the query plan into executable segments known as snippets which are executed in parallel.The intelligence of the compiler is that it has a feature called object cache which is a large cache of previously compiled snippet code with parameter variation.This will eliminate compilation for many snippets.

When an SQL query is executed the following events take place.

• The optimizer generates an execution plan and the compiler creates scheduled task called snippets.
• The data is moved from the disks to the corresponding S blades in a compressed manner through a high speed network.
• The snippet processor reads the table data into memory utilizing a technique. called as zone map which will reduce disk scans by storing the  minimum and maximum value and hence it avoids fetching the data out of range.The details of zone map unlike indexes are created and updated automatically.
• The compressed data is cached in the memory using a smart algorithm which will make the most accessible data to be available instantly rather than fetching from the disk.
• The data then moves to FPGA field programmable gate array responsible for uncompressing data ,extracting the data and then applies the filter condition and pass it to the CPU 
• The CPU performs other operations like Joins and the results of each snippet is sent to the host which does the final calculation and pass down the results to the end user .

Here,Most of the processing happens in the Hard disk and less in CPU.  

Disk has data ,mirror data and free space .So even if one of the disk fails it obtains data from the mirror of the other disk .  

When a table is created pieces of table will be distributed across all the discs and hence the data is fetched at a faster rate and Parallel processing is achieved in that way . 

DMLs in Netezza :

In Netezza the DML such as insert,update and delete are autocommit and it gets commited once you execute the query.However once you delete or update a row the data is not completely lost.

Rollback of a delete :

If we have accidentally deleted a record and need to rollback we can set an option such as set show_deleted_records=true

Now if we select deletexid from the table where deletexid !=0 we will get the data for that row with a transaction id and then we can re insert that record into the table

insert into t select * from t where deletexid=123;

Rollback of an Update :

If you update a table,a record is deleted and a new record will be inserted .so there will be a deletexid and insertxid populated for the record.to recover or rollback the delete all we need is to delete the record inserted with that transaction id and insert the record with the update statements deletexid

In both the case if a groom table is issued then the table will be updated  with the latest stats and a rollback may not be possible.

Also A truncate table will remove the records permanently and hence cannot be roll backed.

Tuning of NZSQL:

1.Always distribute the tables with a proper key.Integer values which are having high cardinality is a good choice for distribution.

2.When joining two tables always use the common column as distribution key in both the tables.for eg dept id is primary key in dept table and foreign key in employees tables use dept id column distribution key in both the tables.also the datatype of both the tables key should be same.

3.even after using proper distribution key some of the joins might be long

running.check whether any distinct value has more records eg -1 or Unknown.if those records are high then the result might take longer time.try to create random numbers for these in a seperate table and associate them with the main table.

4.Always try to broadcast the small table to improve the performance.you can set enable_factrel_planner = true and then set factrel_size_threshold high like 15000000, which says anything under 15 million rows is more of a dimension so broadcast or redistribute it, and tables over 15M rows are big facts so try to leave in place and don’t redistribute.

5.Always run the groom table and groom table version.Also generate the statistics of the objects created.

Netezza Architecture

Unlike other Databases the Query Processing happens in the hardware and not much of the proceesing happening in the CPU thus leading to more optimized use of CPU.

Parellel Processing :

When we create a table with 1000 rows in Netezza with 10 snippets,the data will be spread across
all the 10 snippets,having 100 rows each.

This enables parellel processing thus improving the efficiency of the Server .

Netezza Appliance :

The disks ,Blades also called as Nodes and two redundant modes active and passive.

Node :

Each node has its own CPU,Memory and a specified unit called FPGA(Filed programmable Gate Array) which does lot of things to increase the processing speed of Netezza.

Each node is connected only to a specific set of disks .

Query Execution:

The FPGA retreives the compressed data from the disk thereby reducing the IO and uncompress the data and it projects only the columns required in the query and also restrict the visibility(where clause).and sends the output to CPU.
After all this only the other operations like aggregation or joins are done in CPU.

Advantages:

1.It is quite useful in the olap or Data warehousing environment .
2.Parellel Processing increases the performance of the Netezza Many folds.
3.Optimal Use of CPU

Disadvantages :

1.No rollback of transactions
2.No constraints can be forced