Chapter 5. Other integrity features 167
In this example, we created EMPL table as follows:
CREATE TABLE EMPL
(EMPLID INTEGER NOT NULL PRIMARY KEY,
DEPTID SMALLINT NOT NULL,
RI IN DSNDB04.EMPL ;
If we created the DEPTID to allow nulls, the join is NOT “lossless” and an MQT will not be
5.5.4 Usage recommendations
These are some of the business scenarios where informational RI constraints may be useful:
Application-enforced RI is in effect.
While you do not want to have DB2 enforce the rules, you can still take advantage of
utilities like REPORT TABLESPACESET and QUIESCE TABLESPACESET which allow
you to identify a set of application-related objects and to create a consistency point among
objects that are linked via application-enforced RI. The DB2 catalog also provides a
means of documenting these rules.
Enforcement may be unnecessary in a data warehouse.
Typically, data in a data warehouse environment has been extracted from other sources
and cleansed. RI may already be guaranteed. In this case, informational RI constraints
can be safely used as a means of documenting the business rules.
To encourage automatic query rewrite.
Automatic query rewrite is a process that examines a submitted query that references
source tables and, if appropriate, rewrites the query so that it executes against a
materialized query table. We discussed this in 5.5.3, “Impact on MQT usage” on page 164.
The term locking refers to the collective set of serialization techniques used by DB2 to ensure
the integrity of data in the database. It is important for the application developer to understand
the impact of locking on concurrent accesses to D2 data. This topic is covered in detail in
Chapter 12, “What you need to know about locking” in the redbook, DB2 UDB for z/OS:
Design Guidelines for High Performance and Availability, SG24-7134. In this chapter, we only
provide some further information related to data sharing.
Important: Keep in mind that, for the join to be “lossless”, the definition of the column must
not allow NULLs. In addition, you must issue (or set the proper option in Visual Explain) as
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = ALL ;
SET CURRENT REFRESH AGE = ANY ;