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
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.
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.
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.
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.
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.
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.
create, alter, drop Not supported.

Feedback | Copyright IBM Corporation 2014 | Last updated: 2014-02-28