Managing Database Objects

SQL DDL is used to create, modify, or delete objects in a database. The DDL contains four main SQL statements:

• CREATE

• ALTER

• DROP

• DECLARE

The CREATE Statement

 CREATE <database object> 

The CREATE statement is used to define database objects. Database objects are used for various purposes. Some database objects are used to define a condition or relationship (index, trigger); others are a logical representation of the data as it is physically stored on disk (table, table space). The following database objects can be created with the CREATE statement:

• Alias

• Auxiliary table (LOBs)

• Database

• Distinct type (user-defined data type)

• Function (user-defined functions)

• Global temporary table

• Index

• Materialized query table

• Procedure (stored procedures)

• Sequence object

• Storage groups (stogroups)

• Synonym

• Table

• Table space

• Trigger

• View

The creation of any database object using DDL results in an update to the DB2 system catalog tables. Special database authorities or privileges are required to create database objects (refer to Chapter 3).

The ALTER Statement

 ALTER <database object>.... 

The ALTER statement allows you to change some characteristics of database objects. Any object being altered must already exist in the database. The following database objects can be altered:

• Database

• Function

• Index

• Procedure

• Sequence object

• Storage group

• Table

• Table space

Every time you issue a DDL statement, the system catalog tables will be updated. The update will include a creation or modification timestamp and the authorization ID of the creator (modifier).

The DROP Statement

 DROP <database object> 

The DROP statement is used to remove definitions from the system catalog tables and hence the database itself. Because the system catalog tables cannot be directly deleted from, the DROP statement is used to remove data records from these tables. Database objects can be dependent on other database objects, so the act of dropping an object will result in dropping any object that is directly or indirectly dependent on that object. Any plan or package that is dependent on the object deleted from the catalog on the current server will be invalidated. You can drop any object created with the CREATE <database object> and temporary tables created with the DECLARE <table> statements.

The DECLARE Statement

 DECLARE <database object> 

The DECLARE statement is very similar to the CREATE statement except that one of the objects it can create is a temporary table. Temporary tables are used only for the duration of an application or stored procedure, or connection. The table does not cause any logging or contention against the system catalog tables and is very useful for working with intermediate results. The creation of a temporary table will not result in any update to the system catalog tables, so locking, logging, and other forms of contention are avoided with this object. This table must be placed into an existing TEMP database. The TEMP database should have several segmented table spaces created within it. A single declared table cannot span table spaces.

Declared tables can be dropped and altered, but no other database objects, such as views or triggers, can be created to act against them. Temporary tables do allow for the specification of a partitioning key.

Once a table is declared, it can be referenced like any other SQL table. For more information on declared temporary tables, refer to Chapter 12.