IBM PureData System for Analytics, Version 7.1

Data Definition Language

Use the IBM® Netezza® SQL Data Definition Language (DDL) to define, modify, and delete databases objects, such as databases, tables, and views.

Netezza SQL uses DDL to manage (create, alter, and drop) all the objects in SQL databases and schemas. The database objects that the DDL manipulates fall into two categories:

Global objects: Objects global to all databases. Database, user, and group objects are examples of global objects.
Local objects: Objects that are in a particular database. Schema, table, and view objects are examples of local objects.

When you create a database object, you must name it. Database object names can be up to 128 bytes in length. For a description of the valid identifier characters and formatting rules, see Handle SQL identifiers. You cannot use a global object name for a user-defined object. You can create local objects with the same name in different databases.

The Netezza SQL system tables are called the system catalog, which is global in scope. The system catalog contains all the metadata for all objects within all databases (global and local). When you enter DDL commands, Netezza SQL changes the system catalog to reflect the request.

When you create a database, Netezza SQL copies the template database master_db. The master_db database is a special, read-only database. You cannot modify, alter, or have user objects created within it.

User and group objects are global in scope; that is, they are not tied to a particular database. There is a predefined group called public. As you create users, they are automatically added to the group public. You cannot remove users from the group public, or drop the group public.

Groups are designed to allow security administrators to associate users by department or functionality. Groups are used to control user privileges. Users can be members of many groups; however, groups cannot be members of other groups.

The following table describes the Netezza SQL DDL, which includes SQL commands and clauses.

Table 1. Data definition language
Component	Description
Database
alter	Sets the default character set and changes the name of the database. See ALTER DATABASE.
create	Creates a database. See CREATE DATABASE.
drop	Drops a database. See DROP DATABASE.
Schema
alter	Sets the schema path, authorization user (owner), and changes the name of the schema. See ALTER DATABASE.
create	Creates a database. See CREATE DATABASE.
drop	Drops a database. See DROP DATABASE.
Group
alter	Changes the limit of a group, drops a user from a group, changes the group owner, or name. See ALTER GROUP.
create	Creates a group. See CREATE GROUP.
drop	Drops a group. See DROP GROUP.
User
alter	Alters a user account. Changes the owner, password, optional expiration time, rowset limits, and name. See ALTER USER.
create	Creates a user. See CREATE USER.
drop	Drops a user. See DROP USER.
Table
alter	Changes the definition of a table. See ALTER TABLE.
create	Creates a table. See CREATE TABLE.
create external	Creates an external table. See CREATE EXTERNAL TABLE.
create table as	Creates a table that is based on query results. See CREATE TABLE AS.
create temp table	Creates a temporary table. See CREATE TABLE.
drop	Drops a table. See DROP TABLE.
View
alter	Changes the owner or name of the view. See ALTER VIEW and ALTER VIEWS ON.
create	Creates a view. See CREATE VIEW.
drop	Drops a view. See DROP VIEW.
Index
create, alter, drop	Not supported.