Checking data consistency with SQL queries

If you suspect that a table contains inconsistent data, you can submit an SQL query to search for a specific type of error.

About this task

Consider the view that is created by the following statement as an example of submitting an SQL query to search for an error:

CREATE VIEW V1 AS
  SELECT * FROM T
    WHERE C1 BETWEEN 10 AND 20
    AND  (C2 LIKE 'A%' OR C2 LIKE 'B%')
  WITH CHECK OPTION;

The view allows an insert or update to table T1 only if the value in column C1 is in the range 10–20 and if the value in C2 begins with A or B. To check that the control has not been bypassed, issue the following statement:

SELECT * FROM T1
  WHERE  NOT (C1 BETWEEN 10 AND 20)
  AND  (C2 LIKE 'A%' OR C2 LIKE 'B%');

If the control has not been bypassed, Db2 returns no rows and thereby confirms that the contents of the view are valid. You can also use SQL statements to get information from the Db2 catalog about referential constraints that exist.