- IBM DB2 9.7 Advanced Application Developer Cookbook
- Sanjay Kumar Mohankumar Saraswatipura
- 573字
- 2021-08-20 15:42:19
Using the CREATE WITH ERROR support
The AUTO_REVAL database configuration parameter controls the revalidation and invalidation semantics in DB2 9.7. This configuration parameter can be altered online without taking the instance or the database down. By default, this is set to DEFERRED and can take any of the following values:
IMMEDIATEDISABLEDDEFERREDDEFERRED_FORCE
Now that we know all of the REVALIDATION options available in DB2 9.7, let's understand more about the CREATE WITH ERROR support. Certain database objects can now be created, even if the reference object does not exist. For example, one can create a view on a table which never existed. This eventually errors out during the compilation of the database object body, but still creates the object in the database keeping the object as INVAILD until we get the base reference object.
How to do it...
First, we will look at the ways in which we can change the AUTO_REVAL configuration parameter.
UPDATE DB CFG FOR <DBNAME> USING AUTO_REVAL [IMMEDIATE|DISABLED|DEFERRED|DEFERRED_FORCE]
CREATE WITH ERROR is supported only when we set AUTO_REVAL to DEFERRED_FORCE and the INVALID objects can be viewed from the SYSCAT.INVALIDOBJECTS system catalog table.
- Update the database configuration parameter
AUTO_REVALtoDEFERRED_FORCE.UPDATE DB CFG FOR SAMPLE USING AUTO_REVAL DEFERRED_FORCE - Try to create a view
v_FMSALE, referring to theFMSALEbase table. Since we do not have the base table currently present in the database, DB2 9.7 still creates the view, marking it as invalid until we create the base reference object. This wasn't possible in the earlier versions of DB2.CREATE VIEW c_FMSALE AS SELECT * FROM FMSALE - How do you verify if the object is invalid? The following SQL query on the system catalog table,
SYSCAT.INVALIDOBJECTS, shows why the database object is in an invalid state:SELECT OBJECTNAME, SQLCODE, SQLSTATE FROM SYSCAT.INVALIDOBJECTS - Once you create the base reference object and access the invalid object, DB2 revalidates and marks it as valid.
- The following screenshot illustrates the sample output for the preceding statements:
How it works...
When we create an object without a base reference object, DB2 still creates the object with a name resolution error such as the table does not exist (SQLCODE: SQL0204N SQLSTATE: 42704).
- DB2 creates an object even if the reference column does not exist with the error codes
(SQLCODE: SQL0206N SQLSTATE: 42703). - If the referenced function is not present, we get
SQLCODE: SQL0440N SQLSTATE: 42884. - When
AUTO_REVALis set toIMMEDIATE, all of the dependent objects will be revalidated as soon as they get invalidated. This is applicable toALTER TABLE, ALTER COLUMN, andOR REPLACESSQL statements. - When
AUTO_REVALis set toDEFERRED, all of the dependent objects will be revalidated only after they are accessed the very next time; until then, they are seen asINVALIDobjects in the database. - When
AUTO_REVALis set toDEFERRED_FORCE, it is the same asDEFERREDplus theCREATE WITH ERORRfeature is enabled.
There's more...
Let's have a quick look at the difference between AUTO_REVAL settings and behavior.
Case 1: AUTO_REVAL=DEFERRED
- When the table
T1, on which the viewV1depends, is dropped, the drop would be successful, butV1would be marked as invalid. - After creating
T1, V1would still be marked as invalid until explicitly used.
Case 2: AUTO_REVAL=DEFERRED_FORCE
- One can create an object without having the base reference object present in the database; this only happens when we set
AUTO_REVALtoDEFERRED_FORCE. - Object revalidation happens when an object is being accessed.
