Data Definition Language (DDL)

What is Data Definition Language (DDL)?

Data Definition Language (DDL) is used to create and modify the structure of objects in a database using predefined commands and a specific syntax. These database objects include tables, sequences, locations, aliases, schemas and indexes.

Data Definition Language explained

DDL is a standardized language with commands to define the storage groups (stogroups), different structures and objects in a database. DDL statements create, modify and remove database objects, such as tables, indexes and stogroups. DDL is also used in a generic sense to refer to any language that describes data.

DDL includes Structured Query Language (SQL) statements to create and drop databases, aliases, locations, indexes, tables and sequences. It also includes statements to alter these objects and impose or drop certain constraints on tables, such as the following:

• UNIQUE
• PRIMARY
• FOREIGN KEY
• CHECK

These constraints are used to enforce uniqueness, referential or domain integrity.

When a DDL statement is executed, it takes effect immediately in the database.

DDL is sometimes known as Data Description Language since its statements can also be used to describe, comment on and place labels on database objects.

DDL vs. SQL vs. DML vs. DQL

Since DDL includes SQL statements to define changes in the database schema, it is considered a subset of SQL. SQL uses normal English verbs to modify database objects, and DDL does not appear as a different language in a SQL database.

In Data Manipulation Language (DML), commands are used to modify data in a database. DML statements control access to the database data. In contrast, DDL commands are used to create, delete or alter the structure of objects in a database but not its data. DDL deals with descriptions of the database schema and is useful for creating new tables, indexes, sequences, stogroups, etc. and to define the attributes of these objects, such as data type, field length and alternate table names (aliases).

Data Query Language (DQL) is used to get data within the schema objects of a database and also to query it and impose order upon it. Like DDL, DQL is also a subset of SQL. One of the most common commands in DQL is SELECT. It lets users get data from a database table and perform some operation on it. When the statement is executed, the result is compiled into a temporary table and displayed by the front-end program or application.

Common Data Definition Language commands

General application users -- i.e., users who are not authorized to work directly with a database -- do not use DDL commands. These general users can and should only access the database indirectly via the application.

The most common command types in DDL are CREATE, ALTER and DROP. All three types have a predefined syntax that must be followed for the command to run and changes to take effect.

1. CREATE

Syntax

CREATE TABLE [table name] ([column definitions]) [table parameters];

The semicolon at the end of the command is used to process every command before it.

The CREATE group of DDL commands includes the following:

• CREATE DATABASE defines a logical database under the active location root directory. The database normally consists of a subdirectory of the same name that holds the physical table and index files. Users can use stogroups to implicitly specify different storage directories for individual database objects.
• CREATE TABLE creates a table by defining its columns and each column's data type and field length. The command can also be used to create primary and foreign keys for the table.
• CREATE STOGROUP creates a Db2-style stogroup to define a physical directory area for storing database objects. A stogroup is associated with a specific directory path.
• CREATE TABLESPACE creates a Db2-style tablespace to store tables from the same logical database in multiple directory paths. The tablespace is used with a stogroup.
• CREATE ALIAS defines an alias for an existing table or view. The alias may be described in a different location as the table or view. This command also records the alias definition in the catalog tables at the current location.
• CREATE SYNONYM can also be used to create an alternate name for an existing table or view at the current location.
• CREATE INDEX creates an index on one or more columns of a table for faster data retrieval and to enforce uniqueness constraints on the columns.
• CREATE LOCATION creates a new XDB server location in a user-specified subdirectory.
• CREATE SEQUENCE must be used to create a sequence at the application server.
• CREATE VIEW defines a virtual table that restricts data retrieval and updates to a subset of columns and rows from single- or multibase tables.
• CREATE GLOBAL TEMPORARY can be used to create a temporary table's description at the current server.

2. DROP

DDL also includes several DROP commands to delete objects in a database. DROP commands cannot be undone, so once an object is deleted, it cannot be recovered.

Syntax

DROP object type object name;

The most common DROP commands are the following:

• DROP DATABASE does the exact opposite of the CREATE DATABASE It deletes a database defined at a certain location, along with all the objects logically associated with it. It also deletes the database subdirectory even if it is empty and contains no objects logically associated with the database.
• DROP STOGROUP deletes a stogroup by severing the logical connection between data objects defined using the stogroup and the directory path specified in the deleted stogroup definition. It doesn't delete the objects associated with the stogroup. As with other DROP commands, the DROP STOGROUP command should be used sparingly and with caution.
• DROP TABLE deletes a database table and all associated indexes, views and synonyms built on it.
• DROP TABLESPACE deletes a tablespace defined in the current location and all tables logically associated within it.
• DROP ALIAS can be used to delete an alternate name for a table or view in a location's system catalog.
• DROP SYNONYM can also be used to delete an alternate name for a table or view.
• DROP INDEX deletes an index at the current location but only if the index was not created as the result of a UNIQUE, PRIMARY or FOREIGN KEY To drop/delete such indexes, the existing constraint must first be dropped with the ALTER command.
• DROP LOCATION deletes a user-defined XDB server location and the catalog tables, directory structure and objects associated with it.
• DROP VIEW deletes a view and all other views defined on it from the system catalog of the current location.

3. ALTER

The third group of DDL commands is ALTER. These commands are used to make modifications to database objects, such as indexes, locations and stogroups.

Syntax

ALTER object type object name parameters;

The most common ALTER commands are the following:

• ALTER DATABASE modifies the information parameters of a database under the current XDB server location.
• ALTER STOGROUP modifies the specifications of a stogroup defined at the current XDB server location.
• ALTER TABLE adds, removes or alters columns and their data types. It can also enforce referential and domain integrity by creating or dropping UNIQUE, PRIMARY, FOREIGN KEY, and CHECK constraints in XDB mode. In Db2 mode, the command can also be used to enforce uniqueness constraints.
• ALTER TABLESPACE changes the specifications of a tablespace within the current XDB server location.
• ALTER VIEW uses an existing view definition at the current server to regenerate a view.
• ALTER SEQUENCE changes sequence attributes at the current server.
• ALTER INDEX modifies the configuration of an existing index. The XDB server syntactically supports the command to ensure compatibility with Db2.

4. Other commands

Apart from the CREATE, DROP and ALTER commands, DDL includes other commands:

• COMMENT ON is used to add a single-line, multiline or inline comment about an object in the catalog tables at the current location.
• LABEL ON is used to add or change descriptive text labels describing tables, views, aliases or columns to the catalog tables.
• RENAME is used to modify the name of a database table.
• TRUNCATE is used to quickly remove all records from a table, while preserving its full structure so it can be reused later.

Explore the difference between DDL and DML.