Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Overview of this book

With lots of new features, DB2 9.7 delivers one the best relational database systems in the market. DB2 pureXML optimizes Web 2.0 and SOA applications. DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms. This DB2 9.7 Advanced Application Developer Cookbook will provide an in-depth quick reference during any application's design and development. This practical cookbook focuses on advanced application development areas that include performance tips and the most useful DB2 features that help in designing high quality applications. This book dives deep into tips and tricks for optimized application performance. With this book you will learn how to use various DB2 features in database applications in an interactive way.
Table of Contents (15 chapters)
IBM DB2 9.7 Advanced Application Developer Cookbook
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface

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.