tag:blogger.com,1999:blog-11735156057347342562024-03-20T08:13:05.970-07:00Netezza Tutorials OnlineTutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-1173515605734734256.post-52118764614996026902016-01-13T14:37:00.001-08:002016-01-13T14:37:03.868-08:00Netezza Introduction<div dir="ltr" style="text-align: left;" trbidi="on">
The IBM® Netezza appliance is a test and development system and packs the performance and simplicity of Netezza’s unique architecture into a compact footprint. The IBM Netezza appliance soffers customers an economical platform to develop and test their Business Intelligence (BI) and advanced analytic applications. It also shares the same characteristics as its enterprise-class counterpart of simplicity, ease of deployment and use and hardware-based acceleration of analytic queries and workloads.<br />
<br />
<u><b>Simplicity</b></u><br />
The IBM Netezza is an easy-to-use appliance that requires minimal tuning and administration, speeding up application development. It is delivered ready-to-go for immediate data loading and query execution and integrates with leading ETL, BI and analytic applications through standard ODBC, JDBC and OLE DB interfaces.<br />
<br />
<b><u>Performance</u></b><br />
The IBM Netezza system’s performance advantage comes from IBM’s unique Asymmetric Massively Parallel Processing™ (AMPP™) architecture, which combines open, blade-based servers with commodity disk storage and patented data filtering using Field Programmable Gate Arrays (FPGAs). As an appliance that shares the same software and hardware architecture with other members of the IBM Netezza data warehouse appliance family, the IBM Netezza is ideal for use as a test and development system for high-performance BI applications.<br />
<br />
<b><u>Value</u></b><br />
As a commodity based appliance, IBM Netezza is a very affordable analytic option, delivering up to 10 TB of user data capacity in a compact physical and environmental footprint. The IBM Netezza appliance requires minimal ongoing administration, both in internal resources as well as implementation costs, for an overall low cost of ownership.There are no hidden costs.<br />
<br />
<b><u>Inside the IBM Netezza </u></b><br />
The IBM Netezza appliance is built using commodity blade servers and storage, turbocharged by FPGAs that filter out extraneous data as it streams off the disk. Each appliance contains a Snippet Blade™ (or S-Blade™), which is responsible for processing SQL queries in parallel across 8 pairs of Intel CPU cores and FPGA cores. Skimmer packs this power in a compact 7 rack-unit chassis, while still offering up to 10 TB of user data capacity.</div>
Tutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com12tag:blogger.com,1999:blog-1173515605734734256.post-84215939585110685822016-01-13T14:36:00.001-08:002016-01-13T14:36:46.966-08:00Netezza Quick Reference<div dir="ltr" style="text-align: left;" trbidi="on">
The nzsql command provides many command line options.<br />
<br />
<span style="font-weight: bold;">Using Command Inputs</span><br />
<br />
Using the nzsql command, you can specify different input options:<br />
<br />
�� To run a single query from the command line, enter:<br />
nzsql -c "select * from test_table"<br />
<br />
�� To read input from the current source, for example a script, enter:<br />
nzsql<br />
<<eof br="">SELECT * FROM test_table;<br />eof<br /><br />�� To read input (standard in), enter:<br />nzsql < foo.sql<br /><br />�� To execute queries from a file (command line argument), enter:<br />nzsql -f foo.sql<br /><br />�� To execute queries from a file (nzsql option), enter:<br />nzsql<br />\i foo.sql<br /><br /><span style="font-weight: bold;">Using Command Outputs</span><br />Using the nzsql command, you can specify different output options:<br /><br />�� To save the resulting count value into a variable for later use, enter:<br />VAR1=‘nzsql -A -t -c "select count(*) from test_table"‘<br /><br />�� To pipe the output to a printer, enter:<br />nzsql | lpr<br /><br />�� To send the output to a file (command line argument), enter:<br />nzsql -o foo.out<br /><br />�� To send the output to a file (nzsql option), enter:<br />nzsql<br />\o foo.out<br />Note: The nzsql command pipes interactive output (to a screen) through the more command so it can be paginated and scrolled for viewing. To change to another command, set the PAGER environment variable. For example, export PAGER=cat.<br /><br /><br /><span style="font-weight: bold;">Using the nzsql Command Line Options</span><br />When you type nzsql -h, the system displays the command line options that you can use. For a list and descriptions of the all the command line options, see Table D-1 on page D-1. The following describes some useful command line options:<br /><br />�� -A — Unaligned table output mode<br />Normally output is well formatted with white space added so that columns align. If you use the -A option, the nzsql command removes extra white space. Use this command<br />when you want to compare results between two systems or when you want to transfer<br />data.<br /><br /><br />�� -c — Run a single query and exit<br />This option lets you run a single query. When you combine it with options -A, and -t<br />(print rows only) you can create useful scripts. For example, to find out the number of records in a table, enter:<br /><br />#!/bin/bash<br />export CNT=‘nzsql -A -t -c "select count (*) from $1"‘<br />echo "The number of records in table $1 is $CNT"<br /><br />�� -E — Display queries that internal commands generate<br />This option lets you see how the SQL is being generated. For example, \l displays the<br />list of databases and when you add -E to the command line, the system shows you the<br />actual SQL used to generate the list.<br />nzsql -E<br />\l<br /><br />********* QUERY *********<br />SELECT Database, Owner FROM _v_database<br />*************************<br />List of databases<br />database | owner<br />--------------+-------<br />database_one | admin<br />database_two | admin<br />system | admin<br />(3 rows)<br />�� -f — Execute queries from a file<br /><br />�� -F — Set the field separator<br />The default delimiter between output columns is a pipe (|) symbol. When used with the<br />-A option (unaligned output), you can specify a different delimiter string, such as a<br />space, tab, comma, colon, and so on. Note that you can use the -R <string>to change<br />the record separator. The default is newline.<br /><br />�� -H — HTML Table Output Mode<br />You can format the nzsql output to use HTML tags.<br /><br />�� -t — Print rows only<br />Normally the nzsql command includes column headings and a summary row for all SQL<br />queries. Use the -t option to eliminate the column headings and summary row. Use this<br />option with the -A option to produce data in a transportable format.<br /><br />�� -x — Expand table output<br />You can use this option to display the query results vertically instead of in the default table/2D grid.<br /><br /><br /><span style="font-weight: bold;">Using Miscellaneous Command Options</span><br /><br />The nzsql command has the following command line options that you use when running<br />queries:<br /><br /><br />�� -- — Two dashes denote the beginning of a single-line comment.<br /><br />�� /* — Forward slash and an asterisk denote the beginning of a multiline comment.<br /><br />�� ‘literal’ — Use single quotes around literals. For example, ‘May 10, 2000’, ‘Netezza’. ‘US’. Use a pair of single quotes when you want to embed a single quote. For example, ‘Mark''s Test'.<br /><br />�� ”label” — Use double quotes around labels. For example, SELECT lst_name AS<br />"Employee Last Name" FROM emp_table;<br /><br />�� Identifiers — The system automatically converts identifiers, such as database, table, and column names, to the default system case, which is Upper on new systems. If you want to use mixed case and/or spaces, you must use double quotes around the identifier. For example, CREATE TABLE "Emp Table" (emp_id integer, emp_name char(20));<br />SELECT emp_id FROM "Emp Table";<br /><br />�� */ — Asterisk and forward slash denote the end of a multiline comment.<br /><br /><br /><br /><span style="font-weight: bold;">Using the nzsql Internal Slash Options</span><br /><br /><br />When you use the nzsql command in interactive mode, there are many options that you can use. These options, known as internal slash options, are called with a backslash (\) . Many of these options are the same as those available on the command line.<br />The following are some useful internal slash options. For a list and description of all the internal slash options, see Table D-2 on page D-2.<br /><br />�� \d — Describe a table or view.<br />Displays the DDL for a specific table.<br /><br />�� \dt and \dv — List tables or views.<br />Lists the tables or views in the current database.<br /><br />�� \dSt and \dSv — List system tables or views.<br />Lists the Netezza internal tables or views if you are the admin user. If you are another user, you must have the appropriate privileges. Note that internal tables begin with _t_ and internal views begin with _v_.<br />Note: Do not modify these tables. Doing so could impact the integrity of your system.<br /><br />�� \du and \dU — List users and users’ groups.<br />Displays a list of all users or a list of users and the groups in which they are members.<br /><br />�� \dg and \dG — List groups and groups of users.<br />Displays a list of all groups or a list of all the groups and their members.<br /><br />�� \dGr — List resource sharing groups.<br />Displays a list of the groups that are assigned to Guaranteed Resource Allocation<br />(GRA).<br /><br />�� \echo <text>— Write text to standard output.<br />Allows you to include descriptive text between SQL statements. This is especially useful when writing scripts, as in the following example:<br /><br />nzsql <<eof br="">\echo Rowcount before the truncate<br />SELECT COUNT(*) FROM customer;<br />\echo Rowcount after the truncate<br />TRUNCATE TABLE customer;<br />SELECT COUNT(*) FROM customer;<br />eof<br /><br />When you run this script, the system displays the messages “Rowcount before (or after) the truncate count” before the two select statements.<br /><br />�� \h [cmd] — Display help on SQL syntax.<br />Use this option to display help for SQL syntax for a specific command. The help displays a description of the command and the command syntax. ”<br /><br />�� \l — List all databases.<br />Use this option to list all the databases and their owners.<br /><br />�� \![cmd] — Issue shell command.<br />Use this option to run a shell command without terminating your nzsql session. You<br />can use this option to issue shell commands between SQL statements, which is especially useful in scripts.<br /><br />nzsql <<eof br="">\! date<br />SELECT COUNT(*) From customer;<br />\! date<br />eof<br /><br />The example produces the following output:<br />Wed Jun 27 11:23:50 EDT 2007<br />count<br />-------<br />12399<br />(1 row)<br />Wed Jun 27 11:23:50 EDT 2007<br /><br />You can use the \set command to store an often-used expression or SQL statement in a<br />variable. This variable is visible for the length of your connected session.<br /><br />a. Set the variable:<br />\set my_sql 'select * from sales_tbl where amt > '<br /><br />b. Use the variable in a query:<br />:my_sql 4000;<br />city | state | region | quarter | amt<br />------------+-------+------------+---------+------<br />New York | NY | Northeast | 3 | 4300<br />New York | NY | Northeast | 4 | 5000<br /><br /><br /><span style="font-weight: bold;">Using the Query Buffer</span><br /><br />Because the nzsql command is line oriented, it is difficult to edit a complex, multiline SQL statement. To make it easier, use the query buffer.<br /><br />�� \e — Edit the current query buffer or file with an external editor.<br />When you exit the editor, the system automatically runs your query. Note that the query buffer stores only the last SQL statement.<br />The default editor is vi. To change to another editor, set the EDITOR environment variable.<br />For example, export EDITOR=emacs.<br /><br />�� \p — Show the contents of the query buffer.<br /><br />�� \r — Reset (clear) the query buffer.<br /><br />�� \w <file>— Write the query buffer to a file.<br /><br /><br /><span style="font-weight: bold;">nzsql Exit Codes</span><br /><br /><br />The nzsql command returns one of the following error codes:<br />�� 0 — Success on the last statement executed (SQL or "\" commands, including "\q").<br />�� 1 — Any type of failure (syntax error, database problems, and so on) on the last statement executed.<br />�� 2 — Any type of connection failure (bad database name, wrong user/password, database down, and so on).<br />�� 3 — The user cancelled a query by pressing Control-C.<br />When executing commands from a file (with the -f command line option) the nzsql command normally executes all statements. To have the nzsql command stop execution and exit if it encounters an error, use ‘-v ON_ERROR_STOP=1’ on the command line. </file></eof></eof></text></string></eof></div>
Tutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com1tag:blogger.com,1999:blog-1173515605734734256.post-46304558299659775692013-09-01T05:40:00.001-07:002013-09-01T05:54:40.388-07:00Netezza Introduction<div dir="ltr" style="text-align: left;" trbidi="on">
The IBM® Netezza appliance is a test and development system and packs the performance and simplicity of Netezza’s unique architecture into a compact footprint. The IBM Netezza appliance soffers customers an economical platform to develop and test their Business Intelligence (BI) and advanced analytic applications. It also shares the same characteristics as its enterprise-class counterpart of simplicity, ease of deployment and use and hardware-based acceleration of analytic queries and workloads.<br />
<br />
<u><b>Simplicity</b></u><br />
The IBM Netezza is an easy-to-use appliance that requires minimal tuning and administration, speeding up application development. It is delivered ready-to-go for immediate data loading and query execution and integrates with leading ETL, BI and analytic applications through standard ODBC, JDBC and OLE DB interfaces.<br />
<br />
<b><u>Performance</u></b><br />
The IBM Netezza system’s performance advantage comes from IBM’s unique Asymmetric Massively Parallel Processing™ (AMPP™) architecture, which combines open, blade-based servers with commodity disk storage and patented data filtering using Field Programmable Gate Arrays (FPGAs). As an appliance that shares the same software and hardware architecture with other members of the IBM Netezza data warehouse appliance family, the IBM Netezza is ideal for use as a test and development system for high-performance BI applications.<br />
<br />
<b><u>Value</u></b><br />
As a commodity based appliance, IBM Netezza is a very affordable analytic option, delivering up to 10 TB of user data capacity in a compact physical and environmental footprint. The IBM Netezza appliance requires minimal ongoing administration, both in internal resources as well as implementation costs, for an overall low cost of ownership.There are no hidden costs.<br />
<br />
<b><u>Inside the IBM Netezza </u></b><br />
The IBM Netezza appliance is built using commodity blade servers and storage, turbocharged by FPGAs that filter out extraneous data as it streams off the disk. Each appliance contains a Snippet Blade™ (or S-Blade™), which is responsible for processing SQL queries in parallel across 8 pairs of Intel CPU cores and FPGA cores. Skimmer packs this power in a compact 7 rack-unit chassis, while still offering up to 10 TB of user data capacity.</div>
Tutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com0tag:blogger.com,1999:blog-1173515605734734256.post-42195541445301675822011-03-06T00:35:00.000-08:002011-03-06T00:56:49.263-08:00Accessing Other DatabasesYou can execute queries that reference tables, views, and synonyms in other databases on the same Netezza server. This means that you can use these references in the SELECT<br />statement FROM clauses in queries that can include INSERT, DELETE, UPDATE, CREATE<br />TABLE AS, joins, set operations, aggregations, subselects, view definitions, and so on.<br />When specifying reference objects, keep in mind the following:<br /><br /> You must specify reference objects that reside on the same Netezza server.<br /> You cannot specify reference objects that are under control of third-party applications.<br /> You cannot specify a cross-reference object in the SELECT portion of a CREATE MATERIALIZED VIEW statement.<br /><br /><br /><span style="font-weight:bold;">Referencing Database Objects</span><br />To refer to objects in other databases on the Netezza system, you must use three-level naming, which consists of the following components:<br /><br /> The database — The database or catalog name.<br /> The schema — The schema, which is the name of the database owner. Note that in<br />Netezza release 3.0, there is only one schema per database.<br /> The object — The name of the object, table, view, or synonym.<br /><br /><br /><span style="font-style:italic;">Database Object Naming</span><br />You specify objects in the FROM clause of SQL statements in the three-level form. You can use the standard form or the shorthand notation.<br /><br /> The database-name.schema.object-name — The three-level or fully qualified form.<br /> The database-name..object-name — A convenient way of specifying a fully qualified<br />object name. The system supplies the schema name by internally inserting the current<br />schema name.<br /> The schema.object-name — The two-level form, which you can use only when referring<br />to the current database.<br /><br /><br /><span style="font-style:italic;">Cross-Database Usage Examples</span><br />In the following examples, the Netezza system has two databases: DEV and PROD. Both<br />databases have tables named EMP. A client program connected to DEV is able to reference tables in PROD in the FROM clause of SQL queries. This is referred to as a cross-database query.<br /><br />Note: The following examples use the implicit schema represented by “..” between the<br />database name and the table or view name.<br /><br />To retrieve all rows from the table EMP in connected-to database DEV, enter:<br />dev(admin)=>SELECT * FROM DEV..EMP;<br /><br />To retrieve all rows from the table EMP in the database PROD, enter:<br />dev(admin)=>SELECT * FROM PROD..EMP;<br /><br />To truncate the contents of DEV.EMP.<br />dev(admin)=>TRUNCATE TABLE EMP;<br /><br />To insert the contents of PROD..EMP into DEV..EMP.<br />dev(admin)=>INSERT INTO EMP SELECT * FROM PROD..EMP;<br /><br />To join tables from both PROD and DEV.<br />dev(admin)=>SELECT COUNT (*) FROM DEV..EMP DE, PROD..EMP PE WHERE<br />DE.ID = PE.ID;<br /><br />To retrieve rows from PROD.EMP, enter:<br />dev(admin)=>SELECT * FROM PROD..EMP WHERE PROD..EMP.DEPTNO IN (SELECT<br />DE.DEPTNO FROM DEV..EMP DE WHERE DE.ID < 10);<br /><br />To create a table from PROD.EMP and DEV.EMP, enter:<br />dev(admin)=>CREATE TABLE KEYEMPS AS SELECT * FROM PROD..EMP INTERSECT<br />SELECT * FROM DEV..EMP;<br /><br /><span style="font-style:italic;">Common Error Messages</span><br />Note that you cannot use cross-database INSERT, UPDATE, or DELETE statements. If you<br />attempt to do so, the system displays an error message.<br /><br />For example, if you attempt to insert data into a table that does not reside in the current database (the database you are logged in to), the system displays an error message:<br /><br />dev(admin)=>INSERT INTO PROD..EMP SELECT * FROM EMP;<br /><br />Cross Database Access not supported for this type of command.<br />For this type of query, consider changing the query to a cross-database SELECT statement (which is supported) while logged in to the target database. For example:<br /><br />prod(admin)=>INSERT INTO EMP SELECT * FROM DEV..EMP;<br /><br />You cannot use CREATE, ALTER, or DROP commands to change objects in a database outside your current database. If you attempt to do so, the system displays an error message.<br /><br />For example, if you attempt to create an object in a different database, the system displays an error message:<br /><br />dev(admin)=>CREATE PROD..PAYROLL;<br /><br />Cross Database Access not supported for this type of command.<br /><br /><br /><span style="font-style:italic;">Qualified Column Names</span><br />When a query involves multiple tables, it is sometimes difficult to know which column<br />belongs to which table especially if the tables have the same column names. To help distinguish among column names, SQL allows you to fully qualify column names by specifying the column as: database.schema.table.column.<br /><br />When referring to column names in cross-database access, the system expects the qualified column name to be in the form of exposed-table-reference.column-name; where the exposed-table-reference is any of the acceptable tables references in the FROM clause.<br /><br />For example, emp, admin.emp, dev.admin.emp and dev..emp are all equivalent forms for<br />the same table:<br /><br />FROM emp WHERE dev.admin.emp.id = 10;<br />FROM dev.admin.emp WHERE emp.id = 10;<br />FROM emp WHERE admin.emp.id = 10;<br />FROM emp WHERE dev.admin.emp.id = 10;<br />FROM dev..emp WHERE admin.id = 10;<br /><br /><br /><span style="font-style:italic;">Table and Column Aliases</span><br />Aliases are like synonyms in that they are alternate names for tables or columns. Aliases differ from synonyms in that they exist only for the duration of the query.<br />Aliases can be single letters or words, but when using aliases in cross-database access, the system expects the column name to be in the form of the alias.column-name.<br /><br />For example, the following are correct notations, where E is the alias:<br /><br />dev(admin)=>FROM emp E WHERE E.id =10<br />dev(admin)=>FROM admin.emp E WHERE E.id =10<br /><br /><span style="font-weight:bold;">Using Synonyms</span><br /><br />You can create SQL synonyms as an alternate way of referencing tables or views that reside in the current or other databases on the Netezza system. Synonyms allow you to create easily typed names for long table or view names. They also allow you to create a level of abstraction for the database objects and thereby enable you to swap the underlying objects without affecting the code that references these objects.<br /><br />Synonyms share the same naming restrictions as tables and views, that is, they must be unique within a database and their names cannot be the same as global objects such as those of databases, users, or groups.<br /><br />You can use the following synonym commands:<br /> CREATE SYNONYM — To create a synonym<br /> DROP SYNONYM — To drop/delete a synonym<br /> ALTER SYNONYM — To rename or change the owner of a synonym<br /> GRANT SYNONYM — To grant permission to create, alter, or drop a synonym to a user<br />or group<br /> REVOKE SYNONYM — To revoke permission to create, alter, or drop a synonym to a<br />user or groupTutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com1tag:blogger.com,1999:blog-1173515605734734256.post-86125224283680318302011-03-06T00:21:00.000-08:002011-03-06T00:35:14.009-08:00Managing DatabasesUsing SQL commands, you can create, drop, rename, or change the owner of databases.<br /><br /><span style="font-weight:bold;">Creating a Database</span><br />To create a database, use the CREATE DATABASE command.<br /><br />system(admin)=> CREATE DATABASE mydb;<br />CREATE DATABASE<br /><br />If you do not have the privileges required to create a database, the system displays the following message:<br /><br />ERROR: CREATE DATABASE: permission denied.<br /><br />Database names can have a maximum length of 128 bytes, otherwise the system displays<br />an error message. Database names must be valid identifier names.<br /><br /><br /><span style="font-weight:bold;">Dropping a Database</span><br />If you are logged in as the admin user or the owner of a database, you can drop the database using the DROP DATABASE command. Dropping the database removes the entries for an existing database and deletes the directory that contains the data. For example:<br /><br />system(admin)=> DROP DATABASE mydb;<br />DROP DATABASE<br /><br /><span style="font-weight:bold;">Renaming a Database</span><br />If you are logged in as the admin user or the owner of a database, you can rename the database using the ALTER DATABASE command. The data remains of the same type and size. For example:<br /><br />system(admin)=> ALTER DATABASE mydb RENAME TO newdb;<br />ALTER DATABASE<br /><br />Note: After you rename a database, recompile any views that are associated with that database. Any materialized views in the database will be converted to regular (non-materialized) views. <br /><br /><br /><span style="font-weight:bold;">Changing Database Ownership</span><br />If you are logged in as the admin user or the owner of a database, you can change the<br />owner using the ALTER DATABASE command. The new owner must be a current user of the<br />system. For example:<br /><br />system(admin)=> ALTER DATABASE mydb OWNER TO jane;<br />ALTER DATABASE<br /><br />Specifying International Character Sets You can use Netezza SQL to specify international characters based on the syntax extensions to SQL:1999, which use Unicode and ISO standards. Using these extensions, you can specify Latin and other international characters sets, including Kanji.<br /><br /><span style="font-weight:bold;">Understanding Database Maximums</span><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdYeqkvztV9spvUgcGlUrkPgv45yOYtQrENV-S9JYFgcaTh2xDLOcpGYGUK2xCwxYJ0ee451S2K_dZXTPP9t4nK6RNkp8KYnwtVLSaGWOKS0eVpaAE0-gu-NGVaz_8jddJs0e2cZkCA_I/s1600/Netezza+Understanding+Database+Maximums.jpg"><img style="cursor:pointer; cursor:hand;width: 400px; height: 133px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdYeqkvztV9spvUgcGlUrkPgv45yOYtQrENV-S9JYFgcaTh2xDLOcpGYGUK2xCwxYJ0ee451S2K_dZXTPP9t4nK6RNkp8KYnwtVLSaGWOKS0eVpaAE0-gu-NGVaz_8jddJs0e2cZkCA_I/s400/Netezza+Understanding+Database+Maximums.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5580881473672088754" /></a><br /><br /><br /><span style="font-weight:bold;">Handling SQL Identifiers</span><br /><br />A SQL identifier is the name of a database object such as a table, column, user, group, user-defined object, and database. Netezza supports the SQL 1999 definition for naming identifiers, and they can be up to 128 bytes in length. There are two types of identifiers — regular and delimited.<br /><br />Note: Account passwords, the names of files, and other values are not identifiers, and thus may support a reduced set of characters, including only 7-bit ASCII characters. The file names for external tables must be in UTF-8.<br /><br />A regular identifier is not case sensitive; that is, if you create a database named SALES, you can refer to it using any case combination of letters. For example, SALES, sales, SaLeS, and SALEs all match the database named SALES. The ANSI SQL standard specifies that systems should convert all regular SQL identifiers to the corresponding upper-case characters, so the Netezza system converts any regular identifier you specify into uppercase characters when it is saved in the database, and also when the regular identifiers are used in query processing.<br /><br />Regular identifiers can contain only letters (in any alphabet, not just the Latin alphabet), syllables (as in the Japanese Hiragana syllabary), ideographs, decimal digits, underscores, and dollar sign ($). Regular identifiers must begin with a letter; they cannot begin with a digit, underscore, or dollar sign. Regular identifiers also cannot be a SQL reserved word (as described in Appendix A, “SQL Reserved Words and Keywords”). The encoding in the Netezza catalog is in UTF-8; the encoding for any display will depend on the client. A delimited identifier is also a name of a database object, but it is enclosed in double-quotation <br />marks and has special considerations. <br /><br />A delimited identifier is case-sensitive, so a database named “Sales” is not the same database as one named SALES, for example. The Netezza system does not convert delimited identifiers to the default system case, nor does it save the enclosing double-quotation marks in the database. Within the double quotation marks, a delimited identifier can include the same letters, syllables, ideographs, decimal digits, and underscores as a regular identifier, but it can also include spaces, special characters such as hyphens and percent signs, and SQL reserved keywords. A delimited identifier can begin with any of these letters, digits, or symbols.<br /><br />For example, the following query uses delimited identifiers for both column and table<br />names:<br /><br />SELECT "My Field" FROM "My Table" WHERE "My Field" LIKE 'A%'<br /><br />Note that the string literal 'A%' is enclosed in single quotes.<br />You can change the system default lettercase behavior at system initialization by using the nzinitsystem -lowercase command.Tutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com0tag:blogger.com,1999:blog-1173515605734734256.post-44216576475665347582011-03-06T00:19:00.000-08:002011-03-06T00:22:51.329-08:00Using nzsql CommandsThe nzsql command provides many command line options.
<br />
<br /><span style="font-weight:bold;">Using Command Inputs</span>
<br />
<br />Using the nzsql command, you can specify different input options:
<br />
<br />�� To run a single query from the command line, enter:
<br />nzsql -c "select * from test_table"
<br />
<br />�� To read input from the current source, for example a script, enter:
<br />nzsql
<br /><<eof
<br />SELECT * FROM test_table;
<br />eof
<br />
<br />�� To read input (standard in), enter:
<br />nzsql < foo.sql
<br />
<br />�� To execute queries from a file (command line argument), enter:
<br />nzsql -f foo.sql
<br />
<br />�� To execute queries from a file (nzsql option), enter:
<br />nzsql
<br />\i foo.sql
<br />
<br /><span style="font-weight:bold;">Using Command Outputs</span>
<br />Using the nzsql command, you can specify different output options:
<br />
<br />�� To save the resulting count value into a variable for later use, enter:
<br />VAR1=‘nzsql -A -t -c "select count(*) from test_table"‘
<br />
<br />�� To pipe the output to a printer, enter:
<br />nzsql | lpr
<br />
<br />�� To send the output to a file (command line argument), enter:
<br />nzsql -o foo.out
<br />
<br />�� To send the output to a file (nzsql option), enter:
<br />nzsql
<br />\o foo.out
<br />Note: The nzsql command pipes interactive output (to a screen) through the more command so it can be paginated and scrolled for viewing. To change to another command, set the PAGER environment variable. For example, export PAGER=cat.
<br />
<br />
<br /><span style="font-weight:bold;">Using the nzsql Command Line Options</span>
<br />When you type nzsql -h, the system displays the command line options that you can use. For a list and descriptions of the all the command line options, see Table D-1 on page D-1. The following describes some useful command line options:
<br />
<br />�� -A — Unaligned table output mode
<br />Normally output is well formatted with white space added so that columns align. If you use the -A option, the nzsql command removes extra white space. Use this command
<br />when you want to compare results between two systems or when you want to transfer
<br />data.
<br />
<br />
<br />�� -c — Run a single query and exit
<br />This option lets you run a single query. When you combine it with options -A, and -t
<br />(print rows only) you can create useful scripts. For example, to find out the number of records in a table, enter:
<br />
<br />#!/bin/bash
<br />export CNT=‘nzsql -A -t -c "select count (*) from $1"‘
<br />echo "The number of records in table $1 is $CNT"
<br />
<br />�� -E — Display queries that internal commands generate
<br />This option lets you see how the SQL is being generated. For example, \l displays the
<br />list of databases and when you add -E to the command line, the system shows you the
<br />actual SQL used to generate the list.
<br />nzsql -E
<br />\l
<br />
<br />********* QUERY *********
<br />SELECT Database, Owner FROM _v_database
<br />*************************
<br />List of databases
<br />database | owner
<br />--------------+-------
<br />database_one | admin
<br />database_two | admin
<br />system | admin
<br />(3 rows)
<br />�� -f — Execute queries from a file
<br />
<br />�� -F — Set the field separator
<br />The default delimiter between output columns is a pipe (|) symbol. When used with the
<br />-A option (unaligned output), you can specify a different delimiter string, such as a
<br />space, tab, comma, colon, and so on. Note that you can use the -R <string> to change
<br />the record separator. The default is newline.
<br />
<br />�� -H — HTML Table Output Mode
<br />You can format the nzsql output to use HTML tags.
<br />
<br />�� -t — Print rows only
<br />Normally the nzsql command includes column headings and a summary row for all SQL
<br />queries. Use the -t option to eliminate the column headings and summary row. Use this
<br />option with the -A option to produce data in a transportable format.
<br />
<br />�� -x — Expand table output
<br />You can use this option to display the query results vertically instead of in the default table/2D grid.
<br />
<br />
<br /><span style="font-weight:bold;">Using Miscellaneous Command Options</span>
<br />
<br />The nzsql command has the following command line options that you use when running
<br />queries:
<br />
<br />
<br />�� -- — Two dashes denote the beginning of a single-line comment.
<br />
<br />�� /* — Forward slash and an asterisk denote the beginning of a multiline comment.
<br />
<br />�� ‘literal’ — Use single quotes around literals. For example, ‘May 10, 2000’, ‘Netezza’. ‘US’. Use a pair of single quotes when you want to embed a single quote. For example, ‘Mark''s Test'.
<br />
<br />�� ”label” — Use double quotes around labels. For example, SELECT lst_name AS
<br />"Employee Last Name" FROM emp_table;
<br />
<br />�� Identifiers — The system automatically converts identifiers, such as database, table, and column names, to the default system case, which is Upper on new systems. If you want to use mixed case and/or spaces, you must use double quotes around the identifier. For example, CREATE TABLE "Emp Table" (emp_id integer, emp_name char(20));
<br />SELECT emp_id FROM "Emp Table";
<br />
<br />�� */ — Asterisk and forward slash denote the end of a multiline comment.
<br />
<br />
<br />
<br /><span style="font-weight:bold;">Using the nzsql Internal Slash Options</span>
<br />
<br />
<br />When you use the nzsql command in interactive mode, there are many options that you can use. These options, known as internal slash options, are called with a backslash (\) . Many of these options are the same as those available on the command line.
<br />The following are some useful internal slash options. For a list and description of all the internal slash options, see Table D-2 on page D-2.
<br />
<br />�� \d — Describe a table or view.
<br />Displays the DDL for a specific table.
<br />
<br />�� \dt and \dv — List tables or views.
<br />Lists the tables or views in the current database.
<br />
<br />�� \dSt and \dSv — List system tables or views.
<br />Lists the Netezza internal tables or views if you are the admin user. If you are another user, you must have the appropriate privileges. Note that internal tables begin with _t_ and internal views begin with _v_.
<br />Note: Do not modify these tables. Doing so could impact the integrity of your system.
<br />
<br />�� \du and \dU — List users and users’ groups.
<br />Displays a list of all users or a list of users and the groups in which they are members.
<br />
<br />�� \dg and \dG — List groups and groups of users.
<br />Displays a list of all groups or a list of all the groups and their members.
<br />
<br />�� \dGr — List resource sharing groups.
<br />Displays a list of the groups that are assigned to Guaranteed Resource Allocation
<br />(GRA).
<br />
<br />�� \echo <text> — Write text to standard output.
<br />Allows you to include descriptive text between SQL statements. This is especially useful when writing scripts, as in the following example:
<br />
<br />nzsql <<eof
<br />\echo Rowcount before the truncate
<br />SELECT COUNT(*) FROM customer;
<br />\echo Rowcount after the truncate
<br />TRUNCATE TABLE customer;
<br />SELECT COUNT(*) FROM customer;
<br />eof
<br />
<br />When you run this script, the system displays the messages “Rowcount before (or after) the truncate count” before the two select statements.
<br />
<br />�� \h [cmd] — Display help on SQL syntax.
<br />Use this option to display help for SQL syntax for a specific command. The help displays a description of the command and the command syntax. ”
<br />
<br />�� \l — List all databases.
<br />Use this option to list all the databases and their owners.
<br />
<br />�� \![cmd] — Issue shell command.
<br />Use this option to run a shell command without terminating your nzsql session. You
<br />can use this option to issue shell commands between SQL statements, which is especially useful in scripts.
<br />
<br />nzsql <<eof
<br />\! date
<br />SELECT COUNT(*) From customer;
<br />\! date
<br />eof
<br />
<br />The example produces the following output:
<br />Wed Jun 27 11:23:50 EDT 2007
<br />count
<br />-------
<br />12399
<br />(1 row)
<br />Wed Jun 27 11:23:50 EDT 2007
<br />
<br />You can use the \set command to store an often-used expression or SQL statement in a
<br />variable. This variable is visible for the length of your connected session.
<br />
<br />a. Set the variable:
<br />\set my_sql 'select * from sales_tbl where amt > '
<br />
<br />b. Use the variable in a query:
<br />:my_sql 4000;
<br />city | state | region | quarter | amt
<br />------------+-------+------------+---------+------
<br />New York | NY | Northeast | 3 | 4300
<br />New York | NY | Northeast | 4 | 5000
<br />
<br />
<br /><span style="font-weight:bold;">Using the Query Buffer</span>
<br />
<br />Because the nzsql command is line oriented, it is difficult to edit a complex, multiline SQL statement. To make it easier, use the query buffer.
<br />
<br />�� \e — Edit the current query buffer or file with an external editor.
<br />When you exit the editor, the system automatically runs your query. Note that the query buffer stores only the last SQL statement.
<br />The default editor is vi. To change to another editor, set the EDITOR environment variable.
<br />For example, export EDITOR=emacs.
<br />
<br />�� \p — Show the contents of the query buffer.
<br />
<br />�� \r — Reset (clear) the query buffer.
<br />
<br />�� \w <file> — Write the query buffer to a file.
<br />
<br />
<br /><span style="font-weight:bold;">nzsql Exit Codes</span>
<br />
<br />
<br />The nzsql command returns one of the following error codes:
<br />�� 0 — Success on the last statement executed (SQL or "\" commands, including "\q").
<br />�� 1 — Any type of failure (syntax error, database problems, and so on) on the last statement executed.
<br />�� 2 — Any type of connection failure (bad database name, wrong user/password, database down, and so on).
<br />�� 3 — The user cancelled a query by pressing Control-C.
<br />When executing commands from a file (with the -f command line option) the nzsql command normally executes all statements. To have the nzsql command stop execution and exit if it encounters an error, use ‘-v ON_ERROR_STOP=1’ on the command line.
<br />
<br />Tutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com0tag:blogger.com,1999:blog-1173515605734734256.post-20725138897830562572011-03-05T23:49:00.000-08:002011-03-06T00:19:15.616-08:00Accessing Netezza SQL Using nzsqlYou can use the nzsql command on the Netezza system or from a UNIX client system that<br />can access the Netezza host. The command uses a client/server model, which includes:<br /><br />�� A server that manages database files, accepts connections to the database from client<br />applications, and performs actions on the database on behalf of the client.<br /><br />�� A client application that can perform a variety of database operations. The client could be one of many tools, and is often created by the user.<br /><br /><span style="font-weight:bold;">Logging On</span><br /><br />When you invoke the nzsql command, you must supply a database account user name,<br />password, and the name of the database to which you are connecting. You can enter this information on the nzsql command line, or you can specify the information in environment variables before you begin your nzsql session. For example, you can enter the following from a command window prompt:<br /><br />nzsql -d sales -u mlee -pw blue<br /><br />Welcome to nzsql, the Netezza SQL interactive terminal.<br /><br />Type: \h for help with SQL commands<br />\? for help on internal slash commands<br />\g or terminate with semicolon to execute query<br />\q to quit<br />SALES(MLEE)=><br /><br />Or, you can set the variables in your command shell using variables such as the following, and then use the nzsql command without any arguments:<br /><br />export NZ_USER=john<br />export NZ_PASSWORD=red<br />export NZ_DATABASE=sales<br />nzsql<br /><br />Welcome to nzsql, the Netezza SQL interactive terminal.<br />Type: \h for help with SQL commands<br />\? for help on internal slash commands<br />\g or terminate with semicolon to execute query<br />\q to quit<br />SALES(JOHN)=><br />Note: Throughout the remainder of this guide, the nzsql command output will be abbreviated to omit the “welcome” text for brevity in the examples.<br /><br />The Netezza administrator creates and manages the database user accounts using SQL<br />commands or the Netezza NzAdmin and Web Admin administration interfaces. For a complete description of how to manage user accounts, see the Netezza System Administrator’s Guide.<br /><br />The Netezza system has a default “admin” database user account who is the superuser of the Netezza databases. The admin user can connect to any database; load data; create, alter and drop any objects; create and manage new database users; and so on. Typically the admin user creates new accounts so that other users can access one or more databases and run queries. The admin user can also create accounts with administrative permissions so that other users can be allowed to perform tasks such as manage databases and user setup, backups, and other administrative tasks.<br /><br /><br /><span style="font-weight:bold;">Session Management</span><br /><br />Each client user who connects to the Netezza system opens a session. Users can view information about their sessions, as well as manage them to do such tasks as alter or drop their sessions. The admin account or any permitted user can also show, drop, and manage sessions (that is, change the priority and/or rollback a transaction) for a session. For a description of the SQL commands to manage sessions (ALTER SESSION, DROP SESSION, and SHOW SESSION)<br /><br /><span style="font-weight:bold;">SSL Support for Clients</span><br /><br /><br />Starting in Release 4.5, the Netezza system supports secure sockets layer (SSL) encryption and authentication for connections to the Netezza system. When you run the nzsql command, you can use the following two options to specify the security options for the connection:<br /><br />�� -securityLevel specifies the security level that you want to use for the session. The argument has four values:<br /><br />�� preferredUnsecured — This is the default value. Specify this option when you<br />would prefer an unsecured connection, but you will accept a secured connection if<br />the Netezza system requires one.<br /><br />�� preferredSecured — Specify this option when you want a secured connection to<br />the Netezza system, but you will accept an unsecured connection if the Netezza<br />system is configured to use only unsecured connections.<br /><br />�� onlyUnsecured — Specify this option when you want an unsecured connection to<br />the Netezza system. If the Netezza system requires a secured connection, the connection will be rejected.<br /><br />�� onlySecured — Specify this option when you want a secured connection to the<br />Netezza system. If the Netezza system accepts only unsecured connections, or if<br />you are attempting to connect to a Netezza system that is running a release prior to<br />4.5, the connection will be rejected.<br /><br />�� -caCertFile specifies the pathname of the root certification authority (CA) file. The CA file must be obtained from the Netezza system administrator and installed on the client system. The CA file authenticates the server (the Netezza host) to the client. The default value is NULL, which indicates that no peer authentication will occur.<br /><br />When you invoke the nzsql command, you can specify these arguments on the command<br />line or you can specify the information in environment variables before you begin your nzsql session. The environment variables follow:<br /><br />�� export NZ_SECURITY_LEVEL=level<br />�� export NZ_CA_CERT_FILE=pathname<br /><br />These SSL security arguments are also used with the nzsql \c switch when a user attempts to connect to a different Netezza database. If you do not specify values for these fields, the Netezza system uses the values specified for the existing connection. <br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHGMPZe2Yv8IiL4WzXz2iUueVehhK0J0_LtOoovXH1dj8nvl-GSRFzoYCScoGx8LksibGIXfLK0v3GVyB3rr8FF3AFIQwG1Vh3W6SUDtTS2fFh8UNLJU5L6I-XIYCfDXT46a6UMex7HII/s1600/Security+Settings+and+Netezza+Host+Configurations.jpg"><img style="cursor:pointer; cursor:hand;width: 400px; height: 185px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHGMPZe2Yv8IiL4WzXz2iUueVehhK0J0_LtOoovXH1dj8nvl-GSRFzoYCScoGx8LksibGIXfLK0v3GVyB3rr8FF3AFIQwG1Vh3W6SUDtTS2fFh8UNLJU5L6I-XIYCfDXT46a6UMex7HII/s400/Security+Settings+and+Netezza+Host+Configurations.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5580872990015122274" /></a><br /><br /><br /><span style="font-weight:bold;">Understanding the nzsql Prompt</span><br /><br />After you invoke the nzsql command, the prompt contains the name of the database and<br />your user name. In the following example, the database is system and the user is admin:<br />SYSTEM(ADMIN)=><br />By default, the Netezza system uses uppercase letters to display SQL output. The system case can be configured to use lowercase instead, which was the default in earlier Netezza releases.<br /><br />To connect to another database without exiting the nzsql command, use the \c option:<br />\c[onnect] [dbname [user] [password]]<br /><br />For example, the follow command connects to the database named sales as the user mlee<br />with the password blue:<br /><br />SYSTEM(ADMIN)=> \c sales mlee blue<br /><br />You are now connected to database sales as user mlee.<br />SALES(MLEE)=><br /><br /><span style="font-weight:bold;">Getting Command Feedback</span><br /><br />When you issue a Netezza SQL command, you either succeed or receive an error. In either case, the system provides feedback that you can use in a script.<br /><br /><br />The system feedback for inserts, updates, and deletes shows you the number of rows acted upon. The feedback for inserts includes an extra zero before the actual number (due to a historical artifact). Samply commands (shown in bold) and the command feedback follows:<br /><br />nzsql<br /><br />CREATE TABLE test1 (col1 INTEGER, col2 INTEGER, col3 CHARACTER(40));<br />CREATE TABLE<br /><br />INSERT INTO test1 VALUES (100, 200, 'This is a test');<br />INSERT 0 1<br /><br />INSERT INTO test1 VALUES (101, 201, 'Another test');<br />INSERT 0 1<br /><br />UPDATE test1 SET col2 = 999 WHERE col1 < 1000;<br />UPDATE 2<br /><br />INSERT INTO test1 SELECT * FROM test1;<br />INSERT 0 2<br /><br />delete from test1 where col1 > 0;<br />DELETE 4<br /><br />TRUNCATE TABLE test1;<br />TRUNCATE TABLE<br /><br />DROP TABLE test1;<br />DROP TABLE<br /><br /><br /><span style="font-weight:bold;">Displaying SQL User Session Variables</span><br /><br />You can display the current user-defined session variables using the \set command with no arguments. For example:<br /><br />SALES(MLEE)=> \set<br />VERSION = 'Netezza SQL Version 1.1'<br />PROMPT1 = '%/%(%n%)%R%# '<br />PROMPT2 = '%/%(%n%)%R%# '<br />PROMPT3 = '>> '<br />HISTSIZE = '500'<br />DBNAME = 'SALES'<br />USER = 'MLEE'<br />HOST = '127.0.0.1'<br />PORT = '5480'<br />ENCODING = 'LATIN9'<br />NZ_ENCODING = 'UTF8'<br />LASTOID = '0'Tutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com0tag:blogger.com,1999:blog-1173515605734734256.post-65389255277767746432011-03-05T23:48:00.000-08:002011-03-05T23:49:50.739-08:00Netezza SQL IntroductionNetezza SQL is the Netezza Structured Query Language (SQL), which runs on the Netezza<br />data warehouse appliance. Throughout this blog, the term SQL refers to Netezza’s<br />SQL implementation. Several standards relate to the definition of Netezza SQL:<br />�� SQL-92 (also called SQL/2) is the operative ANSI/ISO standard for relational databases today. While no vendor supports the complete SQL-92 standard, Netezza SQL conforms to all the commonly supported components of SQL-92.<br /><br />�� SQL/CLI — Callable language interface (CLI), which is an addition to the SQL/92 specification<br />in 1995 to define required functionality for CLIs to support client-server and<br />other network based access models. The CLI was largely driven by ODBC, but JDBC is<br />also a CLI-based interface.<br />�� SQL:1999 (also called SQL-99) — Added regular expression matching, recursive queries, triggers, and other object-oriented features.<br /><br />If you have direct access to the Netezza appliance from a command shell, or if you have UNIX clients with the Netezza CLI tools, you can run SQL commands using the nzsql command line interpreter. You can also run Netezza SQL commands using common SQL tools and applications that support ODBC, JDBC, and OLE DB data connectivity APIs. The Netezza system can support multiple concurrent connections from clients. Within a connection, Netezza supports only one active SQL activity at a time.<br /><br />This blog uses the nzsql command to show query and command examplesTutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com0tag:blogger.com,1999:blog-1173515605734734256.post-77487822497095576872011-03-03T06:45:00.001-08:002011-03-03T06:45:46.211-08:00Netezza Tutorial<div style="width:477px" id="__ss_7135389"><strong style="display:block;margin:12px 0 4px"><a href="http://www.slideshare.net/divjeev/netezza-database-usersguide" title="Netezza database users_guide">Netezza database users_guide</a></strong><object id="__sse7135389" width="477" height="510"><param name="movie" value="http://static.slidesharecdn.com/swf/doc_player.swf?doc=netezzadatabaseusersguide-110303084359-phpapp01&stripped_title=netezza-database-usersguide&userName=divjeev" /><param name="allowFullScreen" value="true"/><param name="allowScriptAccess" value="always"/><embed name="__sse7135389" src="http://static.slidesharecdn.com/swf/doc_player.swf?doc=netezzadatabaseusersguide-110303084359-phpapp01&stripped_title=netezza-database-usersguide&userName=divjeev" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="477" height="510"></embed></object><div style="padding:5px 0 12px">View more <a href="http://www.slideshare.net/">documents</a> from <a href="http://www.slideshare.net/divjeev">Tutorial Blogs</a>.</div></div>Tutorial Blogshttp://www.blogger.com/profile/08779672772085427042noreply@blogger.com0