Criterios para el pilotaje de unidades didácticas de ELE

 As with other popular DBMS systems currently available in the market, SQL in Netezza supports SQL-92 standard. The SQLs can be executed through the nzsql command line utility or through any JDBC, ODBC or OLE DB connectivity.

SQL Statements

 The following are the SQL statements available for the various object types in Netezza.

Database

SQL Statement Description

Create Database To create a new Netezza database Drop Database To drop an existing Netezza database

 Alter Database To rename or change owner of an existing database. Renaming a database will require recompilation of any views in the database.

 Also all materialized views will be converted to normal views and  will need to be replaced.

 The following are some limits at the database level

Limit Type Limit

Name Length Maximum number of characters 128 bytes

Connections Maximum number of connections to the server:2000 default:500

 When connected to one database, objects in another database can be accessed by qualifying the object name with “database name.schema name” for e.g. “hrdb.hradmin.emp”  can be used to access the emp table in hrdb database under hradmin schema. The schema name/object owner name is optional and the object can be accessed by “database nama..object name” e.g “hrdb..emp” since object names are unique in a database. While objects in a second database can be queried or used in query joins, no data updates are allowed across databases.

 Tables

SQL Statement Description

CREATE TABLE To create a new Netezza table

DROP TABLE To drop an existing Netezza table

 ALTER TABLE To rename or change owner or modify (add, drop) columns or modify constraints in an existing table

SELECT col_name(s) FROM tbl To retrieve data from a database table INSERT INTO tbl To insert data into a database table UPDATE tbl SET col_nme = val To update existing data in a table DELETE FROM tbl To delete data from a table

 TRUNCATE TABLE tbl To remove all records from the table. Truncate removes the data and recovers the storage instead of logically deleting the rows as in delete statement

© asquareb llc 39

SQL Statement Description

UNION, UNION ALL To combine data from more than one table or row set UNION can be used in conjunction with SELECT statement

INTERSECT, INTERSECT ALL To retrieve common set of rows in more than one table or row set INTERSECT can be used in conjunction with SELECT statement EXCEPT[DISTINCT],

 To re-materialize all the materialized views which are based on a particular table

 The following are some limits at the table level

Limit Type Limit

Name/Column Name Length Maximum number of characters 128 bytes Max Column Count Maximum number of columns per table: 1600 Distribution Keys Maximum number of distribution columns: 4

Char/Varchar Field length Maximum number of characters in char/varchar type: 64000

Row Size Max row size in a table: 65,535

Synonyms

Synonyms can be used to refer tables and views in the same database with a different name or refer to objects in a remote database without the “database.schema” qualifier. The following are the supported SQL statements for Synonyms

SQL Statement Description

CREATE SYNONYM name

FOR table/view

 To create a synonym and it doesn’t verify whether the table or view for which it is getting created exists or not. If the underlying object is non-existent a runtime error will be thrown when user tries to access the Synonym.

DROP SYNONYM name To drop an existing synonym

 ALTER SYNONYM name To rename or change owner of an existing synonym

 Views

 Views are created to give a different perception of data like restricted table column or join of data from two sets of data and/or to control access to the data in the database. The following are the supported SQL statements for views

SQL Statement Description

CREATE OR REPLACE VIEW To create a new Netezza view

DROP VIEW To drop an existing Netezza view

 ALTER VIEW To rename or change owner of an existing view

© asquareb llc 40

Materialized Views

 The following are the supported SQL statements on Materialized Views

SQL Statement Description

CREATE OR REPLACE

MATERIALIZED VIEW

 To create a new materialized view or replace an existing one  when the base table gets changed that impacts materialized view DROP VIEW To drop an existing materialized view (same as normal views)  ALTER VIEW vw MATERIALIZE

REFRESH|SUSPEND

Use “suspend” option to suspend a view so that activites like base table refresh or groom can run. Use “refresh” option to activate a suspended materialized view. The “refresh” option is also used to re-materialize the view so that the rows are in the correct order.

 A system default can be set to specify the percentage of rows in a materialized view that can be out of order and the def ault value is 20%. Once set the command “ALTER VIEWS ON MATERIALIZE REFRESH” will re-materialize all the views in the database which have the set percentage of rows which are not in the correct order.

Functions

Netezza comes with many built in functions are the following are some of them under various categories.

Category Functions

 Aggregates rollup, cube, grouping sets, count, sum, max, min, avg

Casting to_char, to_date, to_number, to_timestamp, cast(value as type),::

Date Time extract(field from datetime value) where field = epoch, year, month, day,dow,doy Checking null value nvl(), nvl2()

String Functions Trim, position, substring, lower, upper, like, not like, character_length Fuzzy String Search le_dst, dle_dst

 Value Functions current_date, current_time, current_timestamp, current_user, current_db Math Functions acos, asin, atan, atan2, cos, cotx, degrees, pi, radiants, sin, tan, random, ceil, abs

 This is just a sample list and other built in functions along with the details can be obtained from the Netezza database user guide.

© asquareb llc 41