Netezza: SQL and Admin: October 2013

Netezza: procedure to drop an object if exists

CREATE OR REPLACE PROCEDURE DROP_OBJECT_IF_EXISTS(

CHARACTER VARYING(ANY),

CHARACTER VARYING(ANY)

)

RETURNS BOOLEAN

LANGUAGE NZPLSQL

execute as caller

BEGIN_PROC

DECLARE

v_count INTEGER;

v_sql VARCHAR(999 );

BEGIN

* Check if the object exists

IF ($ 2 = 'TABLE') THEN SELECT count (* ) INTO v_count FROM _v_table WHERE UPPER (TABLENAME) = UPPER($1);

ELSIF ($2 = 'VIEW') THEN SELECT count (* ) INTO v_count FROM _v_view WHERE UPPER (VIEWNAME) = UPPER($1);

ELSIF ($2 = 'SEQUENCE') THEN SELECT count (* ) INTO v_count FROM _v_sequence WHERE UPPER (SEQNAME) = UPPER($1);

END IF;

* Return if the object does not exist

IF v_count = 0 THEN

RETURN FALSE ;

END IF;

* If indeed the object exists, then drop it.

IF ($ 2 = 'TABLE') THEN v_sql:= 'DROP TABLE ' || $ 1;

ELSIF ($2 = 'VIEW') THEN v_sql:= 'DROP VIEW ' || $1 ;

ELSIF ($2 = 'SEQUENCE') THEN v_sql:= 'DROP SEQUENCE ' || $1 ;

END IF;

EXECUTE IMMEDIATE v_sql;

* End of procedure

RETURN TRUE;

* Handle exceptions and exit gracefully

EXCEPTION

WHEN OTHERS THEN

RAISE NOTICE 'Dropping object failed. Error msg: %' , SQLERRM ;

RETURN FALSE ;

END;

END_PROC;

Netezza SQL: PRIMARY KEY clause

See related post:

Netezza SQL compiles with ANSI SQL-92 standard implementing Postgrees SQL. The ANSI SQL-92 standard supported by most relational databases on the market.

Netezza supports referential integrity (e.g. definitions for foreign, primary, and unique keys) as part of SQL-92 standard requirement. However, the keys are not enforced. This means that you can define the keys in SQL but they won't make any affect on the data. Nothing would prevent to violate referential integrity like inserting non matching values in child table that suppose to match to parent table.

Let's experiment with PRIMARY KEY constraint:

NOTICE: primary key constraints not enforced <-- Netezza SQL Parser complains here

/* Start time 4/25/2013 5:36:27 PM, end time 4/25/2013 5:36:27 PM. */

/* Duration 0.0624992 sec. */

create table test2delete (

col1 smallint NOT NULL

,col2 date

,col3 varchar(30 )

, CONSTRAINT PK_COL1 PRIMARY KEY (col1) <-- THIS IS CONSTRAINT

);

/* Start time 4/25/2013 5:36:27 PM, end time 4/25/2013 5:36:28 PM. */

/* Duration 0.4999936 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,current_date, 'mystring'); <-- First value "1"

/* Start time 4/25/2013 5:36:28 PM, end time 4/25/2013 5:36:28 PM. */

/* Duration 0.4218696 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,current_date, 'mystring'); <-- Second value "1"
--- THIS IS DUPLICATE !!! ---

Note: observe how long singe records INSERT took: 0.42 sec and 0.49 sec. Netezza is very slow when it comes to a single record INSERT.

Alternative syntax for PRIMARY KEY

/* Start time 4/25/2013 6:09:53 PM, end time 4/25/2013 6:09:53 PM. */

/* Duration 0.0312536 sec. */

create table test2delete (

col1 smallint NOT NULL

,col2 date

,col3 varchar(30 )

);

NOTICE: primary key constraints not enforced <-- Netezza complains here, but proceeds

/* Start time 4/25/2013 6:09:53 PM, end time 4/25/2013 6:09:53 PM. */

/* Duration 0.0312536 sec. */

ALTER TABLE test2delete ADD CONSTRAINT PK_COL1 PRIMARY KEY (col1); <-- THIS IS CONSTRAINT

/* Start time 4/25/2013 6:09:53 PM, end time 4/25/2013 6:11:27 PM. */

/* Duration 93.3857568 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,current_date, 'mystring'); <-- First value "1"

/* Start time 4/25/2013 6:11:27 PM, end time 4/25/2013 6:11:50 PM. */

/* Duration 23.1589176 sec. */

/* Records Affected: 1. */

insert into test2delete values (1,current_date, 'mystring'); <-- Second value "1"
--- THIS IS DUPLICATE !!! ---

<-- DUPLICATES IN THE TABLE !!!

Note: observe how long singe records INSERT took: 93 sec and 23 sec. Netezza is very slow when it comes to a single record INSERT.

To conclude, Netezza NPS 7.2 documentation emphasizes:

Note: The system permits and maintains primary key, default, foreign key, unique, and references. Because IBM® Netezza® does not support constraint checking and referential integrity, you must ensure your own constraint checking and referential integrity.

Related documentation: Column and table constraints

Testing Netezza SQL: rejecting mismatched datatype values

This illustrates how Netezza responds when rejecting mismatch datatypes.

Load string value larger than column size:

------------------------------------------------------------------

/* Duration 0.234375 sec. */

create table testVarcharInsert (col1 varchar(10));

/* Duration 0.484375 sec. */

/* Records Affected: 1. */

insert into testVarcharInsert values ( '1234567890');

/* ERROR [HY000] ERROR: value '1234567890X' too large for column */ <-- ERROR

insert into testVarcharInsert values ('1234567890X');

------------------------------------------------------------------

Load integer value larger than range defined:

Netezza Limitations:

INTEGER: 32-bit values in range –2,147,483,648 to 2,147,483,647

/* Duration 0.234375 sec. */

create table testVarcharInsert (col1 integer);

/* Duration 1.25 sec. */

/* Records Affected: 1. */

insert into testVarcharInsert values (12345);

/* ERROR [HY000] ERROR: pg_atoi: error in "abcd": can't parse "abcd" */ <-- ERROR

insert into testVarcharInsert values ('abcd');

/* ERROR [HY000] ERROR: int8 conversion to int4 is out of range */ <-- ERROR

insert into testVarcharInsert values (999888777666555444);

------------------------------------------------------------------

Pushing the limits of field length and row size in Netezza table.

Creating tables with fields larger than Netezza limits:

Netezza Limitations:

Maximum number of characters in a char/varchar field: 64,000

Maximum row size: 65,535 bytes

------------------------------------------------------------------

/* ERROR [HY000] ERROR: length for type 'varchar' cannot exceed 64000 */ <-- ERROR: over the max length

create table testVarcharLength_FAILS (col1 varchar(64001));

/* ERROR [HY000] ERROR: Table 'TESTVARCHARLENGTH_FAILS' record size 73999 exceeds internal limit of 65535 bytes */ <-- ERROR: over the max row size limit

create table testVarcharLength_FAILS (col1 varchar(64000),col2 varchar(9999 ));

/* ERROR [HY000] ERROR: 65536 : Record size limit exceeded */ <-- ERROR: over the max row size limit

create table testVarcharLength_FAILS (col1 varchar(64000),col2 varchar(1517 ));

/* Duration 0.28125 sec. */ <-- SUCCESS

create table testVarcharLength_SUCCESS_1 (col1 varchar(64000),col2 varchar(1516 ));

/* Duration 0.21875 sec. */ <-- SUCCESS

create table testVarcharLength_SUCCESS_2 (col1 varchar(64000));

------------------------------------------------------------------

Netezza Maximums

Parameter	Description
Columns	Maximum per table: 1600. Maximum per distribution: 4
Names	Maximum length of database and column names: 128 bytes
Characters	Maximum number of characters in a char/varchar field: 64,000
Connections	Maximum connections to the server: 2000. Default: 500
Row size	Maximum row size: 65,535 bytes

Calculating Netezza Row Size

Create XID	Delete XID	Row ID	Null Vector	Record Length	Column_1 …Column_n
8 bytes	8 bytes	8 bytes	N/8	4 bytes	Number of bytes

Type	Value	Values in Range	Disk Usage
byteint (alias int1)	8-bit	–128 to 127	1 byte
smallint (alias int2)	16-bit	–32,768 to 32,767	2 bytes
integer (alias int and int4)	32-bit	–2,147,483,648 to 2,147,483,647	4 bytes
bigint (alias int8)	64-bit	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	8 bytes

Wednesday, October 2, 2013

Netezza: procedure to drop an object if exists

Netezza SQL: PRIMARY KEY clause

Testing Netezza SQL: rejecting mismatched datatype values

Netezza Maximums