Netezza

Netezza Dynamic SQL Queries and Examples

There may be situation where you want to generate Netezza dynamic SQL queries inside your Netezza NZPLSQL procedures. Or, you may have procedures that  generates other procedures or CREATE TABLE or update the records form the table based on some condition. You can also set some session specific variables dynamically inside the stored procedure and execute the queries. For such situations, Netezza NZPLSQL provides the “EXECUTE IMMEDIATE” statement. You can execute the Netezza dynamic SQL queries only in stored procedures. You cannot execute the queries within Netezza block statements. Read:…

Continue ReadingNetezza Dynamic SQL Queries and Examples
Comments Off on Netezza Dynamic SQL Queries and Examples
September 9, 2016
Netezza

Netezza NZPLSQL Control Structures: IF, LOOP, WHILE, FOR and EXIT

Netezza NZPLSQL control structures are a useful and important part of the NZPLSQL language. You can use Netezza NZPLSQL control structures to perform some critical decisions based on data and manipulate SQL data in a flexible and powerful way. You can use the Netezza control structures only in procedures. There are two Netezza NZPLSQL control structures: Conditional control and Iterative control Read: Working with Netezza Stored Procedure nzsession command Netezza RECORD Type Variable, Usage and Examples Netezza Conditional Control You can use the IF statement to perform particular task based…

Continue ReadingNetezza NZPLSQL Control Structures: IF, LOOP, WHILE, FOR and EXIT
Comments Off on Netezza NZPLSQL Control Structures: IF, LOOP, WHILE, FOR and EXIT
September 8, 2016
Netezza

nzsession Command: How to Manage Netezza Sessions

A Netezza session represents a single connection to the Netezza appliance. You can use the nzsession command to view and manage the sessions. Session starts when user performs following things: Run nzsql command and session ends when they exit Run nzload command Use nzadmin tool Connect to Netezza system with JDBC, ODBC or OLEDB drivers. The session ends when command completes You must be the system administrator or should have proper permissions to show and manage the sessions and transactions. Read: Commonly used Netezza Basic Commands nzsql command and its…

Continue Readingnzsession Command: How to Manage Netezza Sessions
Comments Off on nzsession Command: How to Manage Netezza Sessions
September 7, 2016
Netezza

Improve performance with help of Netezza Query Plan

Netezza uses the cost based optimizer to determine the best methods for scan, join order and data movement between the SPUs. Planner uses the Netezza query plan to redistributed or broadcast your data. You can use EXPLAIN VERBOSE <your_quey> command to display the Netezza query plan. The planner always tries to avoid redistributing large tables because of the performance impact. Decisions about redistribution are made by the planner and are based on costs like expected table sizes. Optimizer also uses the Netezza query plan to dynamically rewrite queries to improve…

Continue ReadingImprove performance with help of Netezza Query Plan
Comments Off on Improve performance with help of Netezza Query Plan
September 6, 2016
Netezza

Netezza External Table and Examples

A Netezza external table allows you to access the external file as a database table, you can join the external table with other database table to get required information or perform the complex transformations. External table script can be used to access the files that are stores on the host or on client machine. If the files are stored on the client machine, Netezza uses REMOTESOURCE option to access those files. Netezza External Table Syntax Below are the various syntax types that you can use to create external table in…

Continue ReadingNetezza External Table and Examples
Comments Off on Netezza External Table and Examples
September 6, 2016
Netezza

Commonly used Netezza Utilities

It is very easy to perform the ETL (Extract, Transform and Load) functionalities in Netezza. Netezza supports utilities those can be used to perform the ETL/ELT operations. Below are some of commonly used Netezza utilities: Read: Commonly used Netezza Basic Commands nzload utility nzload is bulk data load utility available in Netezza. This utility supports loading data from flat file to Netezza database tables. Below is the syntax and usage: Usage: nzload -host <host> -db <database> -u <username> -pw <password> -t <target_table_name > -df /user/home/flat_file.txt by default, nzload supports '|'…

Continue ReadingCommonly used Netezza Utilities
2 Comments
September 4, 2016
Netezza

Netezza System Tables and Views

There are lot of Netezza system tables and views views available. You can get the more information about the users, tables, synonyms etc. Read: Netezza data types and length restrictions Netezza Query History details using nz_query_history Table Netezza System Tables and Views Below are the list of some commonly used system tables and views: View Table Description _v_sys_columns Return a list of all columns of table available in database. This is very important system view that can be used to search columns. _v_aggregate _t_aggregate Returns a list of all defined…

Continue ReadingNetezza System Tables and Views
1 Comment
September 4, 2016
Netezza

How to Resolve Netezza SPU Swap Partition Error

You might have seen Netezza SPU swap partition error when you are performing some complex transformation that may have included many large table with many complex join. This error does not mean that we have a space issue on any of our Netezza data slices or disks.We get his error when maximum available temporary work space is full. This temporary work space is used by all the temporary tables, sorting, aggregations, joining, implicit data skew (data distribution at run-time) etc. This issue may also occur if the query have cross…

Continue ReadingHow to Resolve Netezza SPU Swap Partition Error
Comments Off on How to Resolve Netezza SPU Swap Partition Error
September 3, 2016
Netezza

Big Data Appliance Netezza Key Features

Netezza is an embedded and advanced analytics database. Netezza key features includes predict with more accuracy, deliver the predictions faster and respond rapidly to the any changes. IBM Netezza Analytics advanced technology supports data warehousing and in-database analytics into a scalable, high-performance, massively parallel advanced analytic platform that is designed to work with petascale data volumes. Below are the some of the Netezza Key features: Netezza bigdata appliance supports both, Business Intelligence and in-database advanced analytics. Lightning fast performance - scalable (10-100x) performance at petascale. Very Efficient even if being used…

Continue ReadingBig Data Appliance Netezza Key Features
Comments Off on Big Data Appliance Netezza Key Features
September 3, 2016
Netezza

List of Netezza Data Types and Best Practices

Data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL. However, different database offers the different data types for columns. Netezza data types are almost similar to what the traditional RDBMS supports. When you issue Netezza create table command each column in a database tables has to have name and a data type associated with it. The data type is based on the types of data which are stored inside the each column of the table.…

Continue ReadingList of Netezza Data Types and Best Practices
Comments Off on List of Netezza Data Types and Best Practices
September 2, 2016