Netezza

How to use Netezza Replace Function and Examples

By default, there is no Netezza replace function. Replace function is very much needed in case if you are manipulating strings and there is a need to replace the particular value when displaying reports. Netezza provides replace function in the Netezza SQL extensions toolkit that administrator has to install and grant permission to use. Netezza Replace Function The Netezza replace() function replaces each instance of a pattern in the input with the value in the string replacement. Syntax: The replace() function has the following syntax: REPLACE(varchar input, varchar pattern, varchar…

Continue ReadingHow to use Netezza Replace Function and Examples
Comments Off on How to use Netezza Replace Function and Examples
September 10, 2016
Netezza

Netezza Set Operators:UNION, MINUS and INTERSECT

You can use the Netezza set operators to combine similar data sets from two or more SELECT statements. Here the similar data set literally mean, the data type of the result set should also match, otherwise you have to explicitly type cast data when using Netezza set operators. Read: nzsql command and usage nzload command and usage Types of Netezza Set Operators Netezza supports the three types of set operators: UNION [DISTINCT] and UNION ALL INTERSECT [DISTINCT] EXCEPT [DISTINCT] or MINUS [DISTINCT] Netezza Set Operators Syntax: Below is the general…

Continue ReadingNetezza Set Operators:UNION, MINUS and INTERSECT
Comments Off on Netezza Set Operators:UNION, MINUS and INTERSECT
September 10, 2016
Netezza

Download and Install Netezza SQL Extensions Toolkit

The Netezza SQL Extensions toolkit is an optional package that contains additional SQL functions for IBM Netezza data warehouse appliances. The Netezza SQL Extensions toolkit contains various functions and regular expressions that are useful in day to day computations. You can download this package from the IBM site and install to particular database. Read: Netezza Advanced Date Functions and Examples dasdas Download the Netezza SQL Extensions Toolkit Login to IBM fix central site given below and download sql-ext-version.tar.gz file. Download Extension toolkit here Once the software is downloaded copy that…

Continue ReadingDownload and Install Netezza SQL Extensions Toolkit
Comments Off on Download and Install Netezza SQL Extensions Toolkit
September 9, 2016
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
Greenplum

Greenplum Analyze and Examples

The most important prerequisites for good query performance is to collect the table statistics time to time using Greenplum analyze command. Greenplum analyze collects statistics about the contents of tables in the database, and stores the results in the system catalog table pg_statistic. Greenplum database uses these statistics to determine the best execution plan for the queries. Syntax: ANALYZE [VERBOSE] [ROOTPARTITION [ALL] ] [table [ (column [, ...] ) ]] Where: ROOTPARTITION [ALL]: Collect statistics only on the root partition of partitioned tables. VERBOSE: Enables display of progress messages. Table:…

Continue ReadingGreenplum Analyze and Examples
Comments Off on Greenplum Analyze and Examples
September 5, 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