Netezza

Netezza Primary Key Constraint and Syntax

Netezza nzsql supports SQL-92 standard. Netezza data warehouse appliance supports referential integrity such as Netezza primary key, foreign key, and unique keys as part of SQL-92 standard requirement. You can create Netezza primary key constraint while creating tables in Netezza database but it will not be enforced while loading Netezza tables. Read: Netezza Create Table Command and Examples Netezza Unique Key Constraint and Syntax Netezza Foreign Key Constraint and Syntax In this post we will learn about the Netezza primary key constraints and its syntax. Netezza Primary Key Constraint Syntax…

Continue ReadingNetezza Primary Key Constraint and Syntax
Comments Off on Netezza Primary Key Constraint and Syntax
November 19, 2016
General

Search for String Pattern in Netezza Database: Google Like Search

Have you ever wondered how to search for string pattern in Netezza database? Here is one of the method that I used in our organisation to search for string pattern in Netezza database. This is process will take bit time to search full Netezza database. Here we have used the current_catalog to search for string pattern in current database. You can pass the database name and string pattern as a form parameter if you have any front end. You can build the query and get the desired results. Read: Netezza…

Continue ReadingSearch for String Pattern in Netezza Database: Google Like Search
Comments Off on Search for String Pattern in Netezza Database: Google Like Search
November 5, 2016
General

Netezza Cumulative Sum, Average and Example

You can make use of the Netezza analytical functions to calculate the cumulative sum or running sum. Sum and Average analytical functions are used along with window options to calculate the Netezza cumulative sum or running sum. Read: Identify and Remove Netezza Duplicate Records in Table IBM Netezza Extract Numbers from String Examples Netezza Pivot Rows to Column With Example Netezza Update Join Syntax and Examples Netezza Recursive Query Alternative and Examples IBM Netezza Rollup Group Aggregates using Grouping sets  Netezza Cumulative Sum, Average Syntax: Below are the Syntax for…

Continue ReadingNetezza Cumulative Sum, Average and Example
Comments Off on Netezza Cumulative Sum, Average and Example
November 5, 2016
Netezza

Netezza Backup and Restore: Best Practices

In this post you will learn about Netezza backup and restore data. It provides general information on backup and restore methods available with Netezza system. Netezza Backup and Restore Overview Netezza provides several backup and restore methods as per the different requirements. You can take table or database backup whenever required: You can create full and incremental backups of your various Netezza databases in compressed internal using the nzbackup command and restore them to a Netezza system whenever required using nzrestore command. As a best practice create script that perform…

Continue ReadingNetezza Backup and Restore: Best Practices
Comments Off on Netezza Backup and Restore: Best Practices
October 25, 2016
Netezza

Netezza MERGE command to Manipulate Records from Table

Use the Netezza MERGE command to insert, update, or delete rows in a target table using data from a source such as a table, view, or sub-query and based on rules specified for a matching condition in the merge statements. In the Netezza merge command, you must specify at least one matching_condition statement to identify the rows that you want to update, insert or delete. This feature is not supported in all versions of Netezza. IBM has introduced this feature in Netezza 7.2.1 or higher. MEGRE command is used to…

Continue ReadingNetezza MERGE command to Manipulate Records from Table
Comments Off on Netezza MERGE command to Manipulate Records from Table
October 20, 2016
Netezza

Changing Netezza Table Distribution key and Example

Choosing right distribution key is one of the important factor to improve the performance of Netezza server. If you have created the table with RANDOM distribution or with different column with lots of duplicate records then you should immediately change the distribution key otherwise that will reduces the performance. Changing Netezza table distribution key is process of redistributing the Netezza table using Netezza nzsql. Changing Netezza Table Distribution key and Example You can achieve the redistribution in couple of ways: Redistribute using CTAS Creating new table and loading data at…

Continue ReadingChanging Netezza Table Distribution key and Example
1 Comment
October 15, 2016
Netezza

Netezza Table Locking and Concurrency

You cannot explicitly lock the tables in Netezza. The Netezza SQL, however, uses implicit Netezza table locking when there is a DDL operation on it. For example, drop table command is blocked on the table if a DML commands are running on table and vice versa. Netezza uses the serializable transaction isolation to lock the table and is ACID property compliant. That ensures no dirty reads, no non repeatable reads. Read: How Netezza Updates Records in Table? Netezza Identify and Kill Table Locks nzsql Command and its Usage Netezza Best…

Continue ReadingNetezza Table Locking and Concurrency
Comments Off on Netezza Table Locking and Concurrency
October 12, 2016
General

Identify and Remove Netezza Duplicate Records in Table

Netezza do not have primary or unique key. You can insert the duplicate records in the table. There are no constraints to ensure uniqueness or primary key, but if you have a table and have loaded data twice, then you can de-duplicate in several ways. Below methods explain you how to identify and Remove Netezza Duplicate Records Read: Netezza Pivot Rows to Column with Example Netezza Primary Key Constraint and Syntax 1. Use Intermediate and DISTINCT Keyword You can remove the Netezza duplicate records by creating another table using DISTINCT…

Continue ReadingIdentify and Remove Netezza Duplicate Records in Table
Comments Off on Identify and Remove Netezza Duplicate Records in Table
October 11, 2016
Netezza

How Netezza Update Records in Tables?

Netezza update records operation is costlier. IBM Netezza does not perform updates, but rather does deletes the records and inserts updated values. When you run nzsql command to update record, Netezza marks the record being updated as logically deleted by setting current transaction value to the deletexid field, but does not delete it. This ensures that the database system adheres to the ACID properties of RDBMS SQL standards. How Netezza Update Records in Tables? Each record in Netezza contains two slots, one for createxid another for deletexid. Deletexid allows you…

Continue ReadingHow Netezza Update Records in Tables?
Comments Off on How Netezza Update Records in Tables?
October 5, 2016
Netezza

Netezza Encrypt Password with nzpassword Command Utility

Database user accounts must be authenticated during access requests to the IBM Netezza database. You can secure the password by using Netezza encrypt password facility. Local authentication requires a password for every account which connects to the Netezza server. You must enter the clear text password, when you use Netezza CLI commands. You can set the environment variable NZ_PASSWORD to avoid the type of password every time but this variable also stores the clear text password. Read: Commonly used Netezza Utility Netezza Best Practices to Improve Performance Netezza nzsql Commands and…

Continue ReadingNetezza Encrypt Password with nzpassword Command Utility
Comments Off on Netezza Encrypt Password with nzpassword Command Utility
October 4, 2016