DB2 DDL ( Data Definition Language )

DB2 DDL ( Data Definition Language )

Following are the DDL ( Data Definition Language ) statements 

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

Example - Creating a Employee Table. 


 



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

Note : You cannot alter an index, you must drop it and then create a new one with a different definition.

DROP

In DB2, You can drop any db2 objects created using CREATE or DECLARE statement

You can drop any of the following db2 objects

• Buffer pools
• Event monitors
• Functions
• Indexes
• Schemas
• Stored procedures
• Tables
• Table spaces
• Triggers
• Views

The DROP statement will delete the object definition of catalog. Following is an exaple 

DROP TABLE EMPLOYEE