Data Definition Language (or DDL) includes those SQL statements that create and drop locations, databases and database objects such as tables and indexes. DDL also includes statements to alter database objects and impose integrity constraints on tables. All DDL statements automatically update the appropriate system catalog tables.
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, variables, masks, permissions, and aliases.
ALTER DATABASE
Changes information parameters for a database under the currently set XDB Server location.
ALTER INDEX
Changes the configuration of an existing index (DB2). This command is supported syntactically by the XDB Server to maintain compatibility with DB2.
ALTER LOCATION
Converts a location to DB2 Version 4 or DB2 Version 5 compatibility.
ALTER SEQUENCE
Changes the attributes of a sequence at the current server.
ALTER STOGROUP
Changes the specifications of an existing storage group (stogroup) defined within the current XDB Server location.
ALTER TABLE
Adds, removes or renames table columns, or changes the data types of columns. In XDB mode, the ALTER TABLE command can create or drop UNIQUE, PRIMARY, FOREIGN KEY and CHECK constraints used to enforce referential and domain integrity, plus uniqueness (the DB2-specific syntax of the ALTER TABLE statement is supported in DB2 mode).
ALTER VIEW
Regenerates a view using an existing view definition at the current server.
CREATE INDEX
Creates an index on one or more columns of a table. Indexes speed data retrieval and can enforce uniqueness constraints.
CREATE VIEW
Defines a virtual table that usually restricts data retrieval and updating to a subset of columns and rows from one or more base tables.
CATALOG MANAGER provides several commands that generate data definition language (DDL) statements. The DDL commands generate SQL to create the source objects themselves. In contrast, the CREATE command (see Using an existing object as a model to create objects), generates SQL to create objects that are like source objects,
Data definition language (DDL) statements let you to perform these tasks, Create, alter, and drop schema objects, Grant and revoke privileges and roles, Analyze information on a table, index, or cluster, Establish auditing options, Add comments to the data dictionary.
The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table. The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
Oracle Database implicitly commits the current transaction before and after every DDL statement. Many DDL statements may cause Oracle Database to recompile or reauthorize schema objects. For information on how Oracle Database recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this, see Oracle Database Concepts. DDL statements are supported by PL/SQL with the use of the DBMS_SQL package.
Following are the DDL statement -
CREATE
DECLARE
ALTER
DROP
CREATE
CREATE statement is used to create following DB2 database objects. The system catalog is updated whenver you create a DB2 database object. Buffer pools, Event monitors, Functions, Indexes, Schemas, Stored procedures, Tables, Table spaces, Triggers, Views.
DECLARE
The DECLARE statement is similar to the CREATE statement, except that it is used to create temporary tables that exist only for the duration of a database connection. Temporary tables are useful when you are working with intermediate results. Declared tables can be referenced like any other table, and they can be altered or dropped like any other table. A table is the only object that can be declared. The system catalog is not updated when you declarea temporary table. You can declare a temporary table by using the DECLARE GLOBAL TEMPORARY TABLE statement.
DECLARE GLOBAL TEMPORARY TABLE session.emp1 LIKE employee ON COMMIT PRESERVE ROWS NOT LOGGED IN tempspace
In this example, the DECLARE GLOBAL TEMPORARY TABLE statement is used to declare a temporary table named emp1, located in an existing user temporary table space named TEMPSPACE. The columns in this table will have the same names and definitions as the columns in the EMPLOYEE table. The rows of the temporary table will be preserved (not deleted) whenever a COMMIT statement is processed. Finally, changes to the temporary table are not logged (this is the only option). session is a schema name.
ALTER
The ALTER statement is used to change some of characterstics of following DB2 objects. Buffer pools, Tables, Table spaces, Views,
Example - In the following example, we can alter the table defined.
ALTER TABLE EMPLOYEE ADD DATE_OF_JOIN DATE
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 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 auditing columns
Every time a row is added or changed in a table that has an auditing column, the value of the audit column is generated by the database manager. These generated values are maintained for both SQL and native changes to the row.
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 indexes
You can use indexes to sort and select data. In addition, indexes help the system retrieve data faster for better query performance.