Creating and maintaining database objects

When creating some types of database objects, you should be aware of the CREATE with errors support, as well as the REPLACE option.

CREATE with errors support for certain database objects

Some types of objects can be created even if errors occur during their compilation; for example, creating a view when the table to which it refers does not exist.

Such objects remain invalid until they are accessed. CREATE with errors support currently extends to views, variables, triggers, and inline SQL functions (not compiled functions). This feature is enabled if the auto_reval database configuration parameter is set to DEFERRED_FORCE.

The errors that are tolerated during object creation are limited to the following types:
  • Any name resolution error, such as: a referenced table does not exist (SQLSTATE 42704, SQL0204N), a referenced column does not exist (SQLSTATE 42703, SQL0206N), or a referenced function cannot be found (SQLSTATE 42884, SQL0440N)
  • Any nested revalidation failure. An object being created can reference objects that are not valid , and revalidation will be invoked for those invalid objects. If revalidation of any referenced invalid object fails, the CREATE statement succeeds, and the created object will remain invalid until it is next accessed.
  • Any authorization error (SQLSTATE 42501, SQL0551N)

An object can be created successfully even if there are multiple errors in its body. The warning message that is returned contains the name of the first undefined, invalid, or unauthorized object that was encountered during compilation. The SYSCAT.INVALIDOBJECTS catalog view contains information about invalid objects.

To reduce the impact of object revalidation on future access, once you have finished with the changes that cause object invalidation, it is strongly recommended that you run the SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS procedure to revalidate invalid objects affected and the db2rbind command to revalidate invalid packages.

Examples

create view v2 as select * from v1

If v1 does not exist, the CREATE VIEW statement completes successfully, but v2 remains invalid.

REPLACE option on several CREATE statements

The OR REPLACE clause on the CREATE statement for several objects, including aliases, functions, modules, nicknames, procedures (including federated procedures), sequences, triggers, variables, and views allows the object to be replaced if it already exists; otherwise, it is created. This significantly reduces the effort required to change a database schema.

Privileges that were previously granted on an object are preserved when that object is replaced. In other respects, CREATE OR REPLACE is semantically similar to DROP followed by CREATE. In the case of functions, procedures, and triggers, support applies to both inline objects and compiled objects.

In the case of functions and procedures, support applies to both SQL and external functions and procedures. If a module is replaced, all the objects within the module are dropped; the new version of the module contains no objects.

Objects that depend (either directly or indirectly) on an object that is being replaced are invalidated. Revalidation of all dependent objects following a replace operation is always done immediately after the invalidation, even if the auto_reval database configuration parameter is set to DISABLED.

Examples

Replace v1, a view that has dependent objects.
create table t1 (c1 int, c2 int);
create table t2 (c1 int, c2 int);

create view v1 as select * from t1;
create view v2 as select * from v1;

create function foo1()
 language sql
 returns int
 return select c1 from v2;

create or replace v1 as select * from t2;

select * from v2;

values foo1();

The replaced version of v1 references t2 instead of t1. Both v2 and foo1 are invalidated by the CREATE OR REPLACE statement. Under revalidation deferred semantics, select * from v2 successfully revalidates v2, but not foo1, which is revalidated by values foo1(). Under revalidation immediate semantics, both v2 and foo1 are successfully revalidated by the CREATE OR REPLACE statement.