Using the CREATE OR REPLACE clause while creating objects

In DB2 9.7, we can create new database objects, such as aliases, procedures, functions, sequences, triggers, views, nicknames, and variables, with a CREATE OR REPLACE clause. These clauses would replace an object if it's already present; otherwise, they create a new object.

The privileges are preserved while replacing an object. In the case of modules, all of the objects within the module are dropped and the replaced version contains no objects.

The main benefit of using this feature is that DB2 doesn't have to wait for a lock on the database object being replaced. Without this feature, we cannot drop an object that is being used. Now DB2 is very intelligent and capable of making a judgment and recreating the object, even if it's been locked.

Getting ready

For the existing database objects, we need the CONTROL privilege, as the objects will be dropped and recreated.

How to do it...

When we use CREATE OR REPLACE, it replaces the earlier object, if it already exists; otherwise, it creates the object. This feature helps application developers not to worry about existing objects, but the production support team should be very cautious while using this.

1. 1. The following set of SQL statements demonstrates the usage of the CREATE OR REPLACE statement.

   CREATE TABLE REPLACE1 (c1 INT, c2 INT)
   CREATE TABLE REPLACE2 (c1 INT, c2 INT)
   CREATE VIEW v1 AS SELECT * FROM REPLACE1
   CREATE VIEW v2 as SELECT * FROM v1
   CREATE FUNCTION fun1()
   LANGUAGE SQL
   RETURNS INT
   RETURN SELECT c1 FROM v2
   CREATE OR REPLACE VIEW v1 AS SELECT * FROM REPLACE2
   
   

   Note

   Downloading the example code

   You can download the example code fles for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the fles e-mailed directly to you.

2. 2. As we replaced the VIEW v1 with a different base table, VIEW v2 and the function fun1 would get invalidated. The following screenshot shows the sample output for the preceding statements:

How it works…

The CREATE OR REPLACE command will create the object specified, if it doesn't exist, or drop and recreate the object, if it's already present. During this process of recreation, it invalidates any dependent objects. Based on the AUTO_REVAL parameter settings, DB2 will automatically revalidate the dependent objects after recreating the objects with the new definition.