构造实验环境

--创建2个表
CREATE TABLE STORE (STORE_ID INT NOT NULL PRIMARY KEY, LOCATION CHAR(10))
CREATE TABLE SALES (STORE_ID INT NOT NULL, TOTAL_SALE INT, DY TIMESTAMP DEFAULT CURRENT TIMESTAMP)
--建立外键关联
ALTER TABLE "SALES" ADD CONSTRAINT "FK_SALES_STORE" FOREIGN KEY ("STORE_ID") REFERENCES "STORE" ("STORE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT ENFORCED ENABLE QUERY OPTIMIZATION
--向父表插入数据
INSERT INTO STORE VALUES (1,'BRAMPTON'),(2,'MARKHAM'),(3,'MISSI'),(4,'TORONTO'),(5,'BOLTON')
--向子表load数据，注意这里的最后一行是不符合外键约束的
$ cat sales_week.del
1,10000,"2012-02-01-12.15.00.410005"
1,15000,"2012-02-02-12.15.01.120003"
1,17500,"2012-02-03-12.15.05.920001"
10,20000,"2012-02-04-12.15.03.222211"
$
$ db2 "LOAD FROM sales_week.del OF DEL INSERT INTO SALES NONRECOVERABLE"
SQL3109N  The utility is beginning to load data from file
"/tmp/sales_week.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "12/27/2016
19:09:09.949565".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "4" rows were read from the
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "4".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "12/27/2016
19:09:09.976620".


Number of rows read         = 4
Number of rows skipped      = 0
Number of rows loaded       = 4
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 4

$

-- 在这里不做数据完整性检查
db2 "SET INTEGRITY FOR SALES CHECK IMMEDIATE UNCHECKED"

-- 外键约束已经检查过了
$ db2 "SELECT TABSCHEMA, TABNAME, STATUS, ACCESS_MODE, SUBSTR(CONST_CHECKED,1,1) AS FK_CHECKED FROM SYSCAT.TABLES WHERE TABNAME='SALES'"

TABSCHEMA     TABNAME  STATUS ACCESS_MODE FK_CHECKED
------------- -------- ------ ----------- ----------
DB2INST1      SALES    N      F           Y         

  1 record(s) selected.
 
奇怪的地方到了，下面的查询返回3行
$ db2 "SELECT * FROM STORE S, SALES SL WHERE S.STORE_ID=SL.STORE_ID"

STORE_ID    LOCATION   STORE_ID    TOTAL_SALE  DY                        
----------- ---------- ----------- ----------- --------------------------
          1 BRAMPTON             1       10000 2012-02-01-12.15.00.410005
          1 BRAMPTON             1       15000 2012-02-02-12.15.01.120003
          1 BRAMPTON             1       17500 2012-02-03-12.15.05.920001

  3 record(s) selected.
$
这个查询返回4行数据
$ db2 "SELECT COUNT(*) AS CNT FROM STORE S, SALES SL WHERE S.STORE_ID=SL.STORE_ID"

CNT        
-----------
          4

  1 record(s) selected.

$
这是为什么那？分析执行计划可以找到原因。

首先编写一个简单shell脚本来生成执行计划
$ cat explain.sh
#!/usr/bin/ksh
if [ -f /home/db2inst1/sqllib/db2profile ]; then
    . /home/db2inst1/sqllib/db2profile
fi
sqlname=$1
db2 connect to SAMPLE
db2 set current explain mode explain
db2 -tvf ${sqlname}.sql
db2 set current explain mode no
db2exfmt -d SAMPLE -1 -o ${sqlname}.out
db2 terminate
$

第一个查询的执行计划
Original Statement:
------------------
SELECT
  *
FROM
  STORE S,
  SALES SL
WHERE
  S.STORE_ID=SL.STORE_ID


Optimized Statement:
-------------------
SELECT
  Q1.STORE_ID AS "STORE_ID",
  Q2.LOCATION AS "LOCATION",
  Q1.STORE_ID AS "STORE_ID",
  Q1.TOTAL_SALE AS "TOTAL_SALE",
  Q1.DY AS "DY"
FROM
  DB2INST1.SALES AS Q1,
  DB2INST1.STORE AS Q2
WHERE
  (Q2.STORE_ID = Q1.STORE_ID)

Access Plan:
-----------
        Total Cost:             20.3334
        Query Degree:           1

              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
                4
             ^HSJOIN
             (   2)
             20.3334
                3
         /-----+------\
        4                5
     TBSCAN           TBSCAN
     (   3)           (   4)
     13.5505          6.78227
        2                1
       |                |
        4                5
 TABLE: DB2INST1  TABLE: DB2INST1
      SALES            STORE
       Q1               Q2
       
第二个查询的执行计划
Original Statement:
------------------
SELECT
  COUNT(*) AS CNT
FROM
  STORE S,
  SALES SL
WHERE
  S.STORE_ID=SL.STORE_ID


Optimized Statement:
-------------------
SELECT
  Q3.$C0 AS "CNT"
FROM
  (SELECT
     COUNT(*)
   FROM
     (SELECT
        $RID$
      FROM
        DB2INST1.SALES AS Q1
     ) AS Q2
  ) AS Q3

Access Plan:
-----------
        Total Cost:             13.5511
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
        1
     GRPBY
     (   2)
     13.5508
        2
       |
        4
     TBSCAN
     (   3)
     13.5505
        2
       |
        4
 TABLE: DB2INST1
      SALES
       Q1


由于前面使用了IMMEDIATE UNCHECKED放弃参照完整性检查，并且确实在子表中有一行违反了参照完整性。对于第二个查询来说，因为两个表之间是父表和子表的关系，
所以2表关联求返回行数，只是查看一下子表的行数就可以了，DB2优化器依据此原理，自动忽略了父表，从而这变成了一个简单的单表的查询，返回4行。
对于第一个查询而言，因为要查2表的所有的列，所以，2表还是做了关联查询，把违反参照完整性的那行给过滤掉了。

结论：我们可能有时候使用了IMMEDIATE UNCHECKED作为一个中间的处理步骤，放弃了参照完整性检查，但是最后还是要检查完整性的，不然可能有些查询要出错。