Data definition language
Data definition language (DDL) describes the portion of SQL that creates, alters, and deletes database objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, and aliases.
- Creating a schema
A schema provides a logical grouping of SQL objects. To create a schema, use the CREATE SCHEMA statement. - Creating a table
A table can be visualized as a two-dimensional arrangement of data that consists of rows and columns. To create a table, use the CREATE TABLE statement. - Creating a table using LIKE
You can create a table that looks like another table. That is, you can create a table that includes all of the column definitions from an existing table. - Creating a table using AS
You can create a table from the result of a SELECT statement. To create this type of table, use the CREATE TABLE AS statement. - Creating and altering a materialized query table
A materialized query table is a table whose definition is based on the result of a query, and whose data is in the form of precomputed results that are taken from the table or tables on which the materialized query table definition is based. - Declaring a global temporary table
You can create a temporary table for use with your current session. To create a temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement. - Creating a table with remote server data
You can create a table on the local server that references one or more tables on a remote server. - Creating a row change timestamp column
Every time a row is added or changed in a table with a row change timestamp column, the row change timestamp column value is set to the timestamp corresponding to the time of the insert or update operation. - Creating and altering an identity column
Every time a row is added to a table with an identity column, the identity column value for the new row is generated by the system. - Using ROWID
Using ROWID is another way to have the system assign a unique value to a column. ROWID is similar to identity columns. But rather than being an attribute of a numeric column, it is a separate data type. - Creating and using sequences
Sequences are similar to identity columns in that they both generate unique values. However, sequences are objects that are independent of any tables. You can use sequences to generate values quickly and easily. - Defining field procedures
Field procedures are assigned to a table by the FIELDPROC clause of the CREATE TABLE and ALTER TABLE statements. A field procedure is a user-written exit routine that transforms values in a single column. - Creating descriptive labels using the LABEL ON statement
Sometimes a text description is useful for an object (such as a table or an index) or useful as column text or column headings. You can create a more descriptive label for these names by using the LABEL ON statement. - Describing an SQL object using COMMENT ON
After you create an SQL object, such as a table or view, you can provide object information for future reference using the COMMENT ON statement. - Changing a table definition
You can change the definition of a table by adding a new column, changing an existing column definition such as its length or default value, dropping an existing column, adding or removing constraints, changing partitioning for the table, or altering one of many other options. - Creating and using ALIAS names
When you refer to an existing table or view, or to a physical file that consists of multiple members, you can avoid using file overrides by creating an alias. To create an alias, use the CREATE ALIAS statement. - Creating and using views
A view can be used to access data in one or more tables or views. You create a view by using a SELECT statement. - Creating indexes
You can use indexes to sort and select data. In addition, indexes help the system retrieve data faster for better query performance. - Creating and using global variables
You can use global variables to assign specific variable values for a session. - Replacing existing objects
You can replace an existing object using a CREATE statement rather than always needing to drop the object first. - Catalogs in database design
A catalog is automatically created when you create a schema. There is also a system-wide catalog that is always in the QSYS2 library. - Dropping a database object
The DROP statement deletes an object. Depending on the action requested, any objects that are directly or indirectly dependent on that object might also be deleted or might prevent the drop from happening.
Parent topic: SQL programming
Related concepts:
Related tasks: