DB2 – DDL (Data Definition Language)

SQL is the language we can interact with RDBMS. When referring to DB2 SQL, the following will give brief idea on DDL.

  • CREATE
  • ALTER
  • DROP
  • Truncate

Why CREATE…

The create statement creates a Table, which is also called database object.

The examples is…

CREATE MYTABLE
{
Invoice_id  number notnull,
vendor_id  number not null
};

To change a database object, then, you need to go for ALTER. What is ALTER. The example is as follows.

Example to Add new column:

ALTER TABLE MYTABLE
ADD balance_due number(9,2);

 

DB2 DDL concepts
Image Courtesy|Stocksnap.io

The other word is DROP.

Why you need DROP…To delete a column or index from the existing table…you can use in your SQL Query.

Delete a column from MYTABLE:

ALTER TABLE MYTABLE
DROP COLUMN vendor_id;

The TRUNCATE..

Why TRUNCATE you need, you already have DROP..

The reason is you can delete all the data from the Table, without deleting table columns.

Tip- When you issued TRUNCATE the indexes will not be affected. This is a good interview question.

Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.

Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

The example  for Truncate table.

TRUNCATE TABLE MYTABLE;

The example for DROP table.

DROP TABLE MYTABLE;
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.