Creating a Database
To create a database, use the CREATE DATABASE command.
system(admin)=> CREATE DATABASE mydb;
CREATE DATABASE
If you do not have the privileges required to create a database, the system displays the following message:
ERROR: CREATE DATABASE: permission denied.
Database names can have a maximum length of 128 bytes, otherwise the system displays
an error message. Database names must be valid identifier names.
Dropping a Database
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:
system(admin)=> DROP DATABASE mydb;
DROP DATABASE
Renaming a Database
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:
system(admin)=> ALTER DATABASE mydb RENAME TO newdb;
ALTER DATABASE
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.
Changing Database Ownership
If you are logged in as the admin user or the owner of a database, you can change the
owner using the ALTER DATABASE command. The new owner must be a current user of the
system. For example:
system(admin)=> ALTER DATABASE mydb OWNER TO jane;
ALTER DATABASE
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.
Understanding Database Maximums
Handling SQL Identifiers
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.
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.
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.
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
marks and has special considerations.
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.
For example, the following query uses delimited identifiers for both column and table
names:
SELECT "My Field" FROM "My Table" WHERE "My Field" LIKE 'A%'
Note that the string literal 'A%' is enclosed in single quotes.
You can change the system default lettercase behavior at system initialization by using the nzinitsystem -lowercase command. You 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
statement FROM clauses in queries that can include INSERT, DELETE, UPDATE, CREATE
TABLE AS, joins, set operations, aggregations, subselects, view definitions, and so on.
When specifying reference objects, keep in mind the following:
You must specify reference objects that reside on the same Netezza server.
You cannot specify reference objects that are under control of third-party applications.
You cannot specify a cross-reference object in the SELECT portion of a CREATE MATERIALIZED VIEW statement.
Referencing Database Objects
To refer to objects in other databases on the Netezza system, you must use three-level naming, which consists of the following components:
The database — The database or catalog name.
The schema — The schema, which is the name of the database owner. Note that in
Netezza release 3.0, there is only one schema per database.
The object — The name of the object, table, view, or synonym.
Database Object Naming
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.
The database-name.schema.object-name — The three-level or fully qualified form.
The database-name..object-name — A convenient way of specifying a fully qualified
object name. The system supplies the schema name by internally inserting the current
schema name.
The schema.object-name — The two-level form, which you can use only when referring
to the current database.
Cross-Database Usage Examples
In the following examples, the Netezza system has two databases: DEV and PROD. Both
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.
Note: The following examples use the implicit schema represented by “..” between the
database name and the table or view name.
To retrieve all rows from the table EMP in connected-to database DEV, enter:
dev(admin)=>SELECT * FROM DEV..EMP;
To retrieve all rows from the table EMP in the database PROD, enter:
dev(admin)=>SELECT * FROM PROD..EMP;
To truncate the contents of DEV.EMP.
dev(admin)=>TRUNCATE TABLE EMP;
To insert the contents of PROD..EMP into DEV..EMP.
dev(admin)=>INSERT INTO EMP SELECT * FROM PROD..EMP;
To join tables from both PROD and DEV.
dev(admin)=>SELECT COUNT (*) FROM DEV..EMP DE, PROD..EMP PE WHERE
DE.ID = PE.ID;
To retrieve rows from PROD.EMP, enter:
dev(admin)=>SELECT * FROM PROD..EMP WHERE PROD..EMP.DEPTNO IN (SELECT
DE.DEPTNO FROM DEV..EMP DE WHERE DE.ID < 10);
To create a table from PROD.EMP and DEV.EMP, enter:
dev(admin)=>CREATE TABLE KEYEMPS AS SELECT * FROM PROD..EMP INTERSECT
SELECT * FROM DEV..EMP;
Common Error Messages
Note that you cannot use cross-database INSERT, UPDATE, or DELETE statements. If you
attempt to do so, the system displays an error message.
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:
dev(admin)=>INSERT INTO PROD..EMP SELECT * FROM EMP;
Cross Database Access not supported for this type of command.
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:
prod(admin)=>INSERT INTO EMP SELECT * FROM DEV..EMP;
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.
For example, if you attempt to create an object in a different database, the system displays an error message:
dev(admin)=>CREATE PROD..PAYROLL;
Cross Database Access not supported for this type of command.
Qualified Column Names
When a query involves multiple tables, it is sometimes difficult to know which column
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.
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.
For example, emp, admin.emp, dev.admin.emp and dev..emp are all equivalent forms for
the same table:
FROM emp WHERE dev.admin.emp.id = 10;
FROM dev.admin.emp WHERE emp.id = 10;
FROM emp WHERE admin.emp.id = 10;
FROM emp WHERE dev.admin.emp.id = 10;
FROM dev..emp WHERE admin.id = 10;
Table and Column Aliases
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.
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.
For example, the following are correct notations, where E is the alias:
dev(admin)=>FROM emp E WHERE E.id =10
dev(admin)=>FROM admin.emp E WHERE E.id =10
Using Synonyms
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.
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.
You can use the following synonym commands:
CREATE SYNONYM — To create a synonym
DROP SYNONYM — To drop/delete a synonym
ALTER SYNONYM — To rename or change the owner of a synonym
GRANT SYNONYM — To grant permission to create, alter, or drop a synonym to a user
or group
REVOKE SYNONYM — To revoke permission to create, alter, or drop a synonym to a
user or group
No comments:
Post a Comment