Application Development Enhancements in DB2 9.7 | IBM DB2 9.7 Advanced Application Developer Cookbook

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. 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. 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.

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

IBM DB2 9.7 Advanced Application Developer Cookbook

IBM DB2 9.7 Advanced Application Developer Cookbook

Overview of this book

Using the CREATE OR REPLACE clause while creating objects

Getting ready

How to do it...

Note

How it works…

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access