Managing Databases

Using SQL commands, you can create, drop, rename, or change the owner of databases.

Creating a Database
To create a database, use the CREATE DATABASE command.

system(admin)=> CREATE DATABASE mydb;

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;

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;

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;

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

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.

No comments:

Post a Comment