When creating some types of database objects, you should be aware of the CREATE with errors support, as well as the REPLACE option.
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 and inline SQL functions (not compiled functions). This feature is enabled if the auto_reval database configuration parameter is set to IMMEDIATE or DEFERRED.
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.
You can use the ADMIN_REVALIDATE_DB_OBJECTS procedure to revalidate existing objects that have been marked invalid.
create view v2 as select * from v1
If v1 does not exist, the CREATE VIEW statement completes successfully, but v2 remains invalid.
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.
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.