Problem:
The following two queries returned the same result:
db2 "select count(*) from v_user"
db2 "select count(*) from v_user where sampling = 'Y'"
instnb@TPEPADBCM:/db2/dump/instnb > db2 "select count(*) from
v_user"
1
-----------
2136
1 record(s) selected.
instnb@TPEPADBCM:/db2/dump/instnb > db2 "select count(*) from
v_user
where sampling = 'Y'"
1
-----------
2136
1 record(s) selected.
Actually the result for the second query is wrong. There are a
lot of rows whose SAMPLING column is not 'Y'.
The access plan shows both of them have the same remote
statement and the count(*) is pushed down to data source:
RMTQTXT : (Remote statement)
SELECT COUNT(*) FROM (TABLE (SELECT A1."USER_ID" C0
FROM "EPOS_UAM"."U_USER" A1, "EPOS_UAM"."U_USR_ROLE" A2,
"EPOS_UAM"."V_BRANCHES" A3 WHERE ((A2."SYSTEM_ID" = :H0 ) OR
(A2."SYSTEM_ID" = :H1 )) AND (A1."USER_ID" = A2."USER_ID") AND
(A1."BRANCH_CD1" = A3."BRANCH_CD1")) A0 LEFT OUTER JOIN TABLE
(SELECT A5."USER_ID" C0 FROM "EPOS_UAM"."T_UW_GRADE" A5,
"EPOS_UAM"."V_BRANCHES" A6 WHERE (A5."BR_ID" = A6."BRANCH_CD2"))
A4 ON (A0.C0 = A4.C0)) FOR READ ONLY
That's why they get the same result. Here is the definition for
view v_user and its dependency's DDL:
CREATE VIEW EPOS.V_USER (SYSTEM_ID ,USER_ID ,FIRST_NM ,EXT_NO
,ROLE_ID ,DPDSK ,BRANCH_CD1 ,BRANCH_CD2 ,BRANCH_NM ,UW_GRADE
,SAMPLING ,MANAGER ,EMAIL ,STATUS ,NSDC_ACCT ,AP_USER_ID
,UW_QUAL ,TAXID )AS SELECT B.SYSTEM_ID,A.USER_ID,A.FIRST_NM,
A.EXT_NO,B.ROLE_ID,A.DPDSK,
A.BRANCH_CD1,A.BRANCH_CD2,C.BRANCH_NM, V.UWG_GRADE AS
UW_GRADE,(SELECT CASE P_ID WHEN 'APPROVE' THEN 'Y' END FROM
EPOS_UAM.U_OTHER O WHERE O.SYSTEM_ID='EPOS' AND
O.USER_ID=A.USER_ID AND O.P_ID='APPROVE') AS SAMPLING,(SELECT
CASE P_ID WHEN 'CONTROLLER' THEN 'Y' END FROM EPOS_UAM.U_OTHER O
WHERE O.SYSTEM_ID='EWS' AND O.USER_ID=A.USER_ID AND
O.P_ID='CONTROLLER') AS MANAGER,A.EMAIL, A.STATUS, A.R7_CODE ,
A.AP_USER_ID,A.UNDERWRITINGPERSONNELQUALIFICATION,A.TAXID FROM
EPOS_UAM.U_USER A JOIN EPOS_UAM.U_USR_ROLE B ON
((A.USER_ID=B.USER_ID AND B.SYSTEM_ID='EPOS') OR
(A.USER_ID=B.USER_ID AND B.SYSTEM_ID='EWS')) JOIN
EPOS.V_BRANCHES C ON A.BRANCH_CD1=C.BRANCH_CD1 LEFT OUTER JOIN
EPOS.V_UW_GRADE V ON A.USER_ID=V.USER_ID ;
CREATE VIEW EPOS.V_BRANCHES (BRANCH_CD1 ,BRANCH_CD2 ,ZONE
,ZONE_NM ,BRANCH_NM ,TEL ,FAX ,"PATH" ,"TYPE" ,PARENT_ID
,DAG_OFFICE ,D_AGENT ,PSC ,STATUS ,AGENT_NM ,AGENT_EXT
,CREATE_USR , UPDATE_USR , CREATE_TS , UPDATE_TS ) AS SELECT
BRANCH_CD1 , BRANCH_CD2 , ZONE , ZONE_NM , BRANCH_NM , TEL , FAX
, "PATH" , "TYPE" , PARENT_ID , DAG_OFFICE , D_AGENT , PSC ,
STATUS , AGENT_NM , AGENT_EXT , CREATE_USR , UPDATE_USR ,
CREATE_TS , UPDATE_TS FROM EPOS_UAM.V_BRANCHES ;
CREATE VIEW EPOS.V_UW_GRADE (USER_ID , BRANCH_CD1 ,BRANCH_CD2 ,
BRANCH_NM , DEPT_ID , UW_NM , UWG_GRADE ) AS SELECT USER_ID,
B.BRANCH_CD1, BR_ID AS BRANCH_CD2, B.BRANCH_NM, SUBSTR(DEPT_ID,
2, 1 ) AS DEPT_ID, UW_NM, UWG_GRADE FROM EPOS_UAM.T_UW_GRADE A
JOIN EPOS_UAM.V_BRANCHES B ON A.BR_ID = B.BRANCH_CD2 ;
All the underlying objects are nickname, no local table. Here is
the Fed configuration:
CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.a' OPTIONS (DB2_FENCED
'N');
CREATE SERVER "INSTNB_UAM" TYPE DB2/UDB VERSION '9.7' WRAPPER
"DRDA" AUTHORIZATION "" PASSWORD "" OPTIONS (DATE_COMPAT
'N' ,DB2_CONCAT_NULL_NULL
'Y',DB2_VARCHAR_BLANKPADDED_COMPARISON 'Y',DBNAME
'UAM',NO_EMPTY_STRING 'N',NUMBER_COMPAT
'N',SAME_DECFLT_ROUNDING 'Y',VARCHAR2_COMPAT 'N');
Comparing their optimized statements, it looks like that we
ignored the red part when generating access plan for the second
query, which caused the incorrect result.
Query 1:
Optimized Statement:
-------------------
SELECT Q10.$C0
FROM
(SELECT COUNT(*)
FROM
(SELECT Q8.USER_ID
FROM
(SELECT Q4.USER_ID
FROM
(SELECT Q2.USER_ID
FROM EPOS_UAM.U_USR_ROLE AS Q1, EPOS_UAM.U_USER AS
Q2,
EPOS_UAM.V_BRANCHES AS Q3
WHERE (Q2.USER_ID = Q1.USER_ID) AND (Q2.BRANCH_CD1 =
Q3.BRANCH_CD1) AND ((Q1.SYSTEM_ID = G'EPOS')
OR
(Q1.SYSTEM_ID = G'EWS'))) AS Q4 LEFT OUTER
JOIN
(SELECT Q6.USER_ID
FROM EPOS_UAM.V_BRANCHES AS Q5, EPOS_UAM.T_UW_GRADE
AS Q6
WHERE (Q6.BR_ID = Q5.BRANCH_CD2)) AS Q7 ON
(Q4.USER_ID =
Q7.USER_ID)) AS Q8) AS Q9) AS Q10
Query 2:
Optimized Statement:
-------------------
SELECT Q12.$C0
FROM
(SELECT COUNT(*)
FROM
(SELECT Q8.USER_ID
FROM
(SELECT Q4.USER_ID
FROM
(SELECT Q2.USER_ID
FROM EPOS_UAM.U_USR_ROLE AS Q1, EPOS_UAM.U_USER AS
Q2,
EPOS_UAM.V_BRANCHES AS Q3
WHERE (Q2.USER_ID = Q1.USER_ID) AND (Q2.BRANCH_CD1 =
Q3.BRANCH_CD1) AND ((Q1.SYSTEM_ID = G'EPOS')
OR
(Q1.SYSTEM_ID = G'EWS'))) AS Q4 LEFT OUTER
JOIN
(SELECT Q6.USER_ID
FROM EPOS_UAM.V_BRANCHES AS Q5, EPOS_UAM.T_UW_GRADE
AS Q6
WHERE (Q6.BR_ID = Q5.BRANCH_CD2)) AS Q7 ON
(Q4.USER_ID =
Q7.USER_ID)) AS Q8,
(SELECT Q9.USER_ID
FROM EPOS_UAM.U_OTHER AS Q9
WHERE (Q9.P_ID = G'APPROVE') AND (Q9.USER_ID =
Q8.USER_ID) AND
(Q9.SYSTEM_ID = G'EPOS')) AS Q10) AS Q11) AS
Q12 |
