DB2 - Data definition language statements




DB2 Data definition language statements

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.

What is DB2 Data definition language statements ?

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.