home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC96772 Status: Geschlossen

QUERIES WITH/WITHOUT PREDICATES RETURNED SAME RESULT, PUSHDOWNABLE
PREDICATE IS NOT COMPOSED INTO REMOTE STATEMENT

Produkt:
DB2 FOR LUW / DB2FORLUW / A50 - DB2
Problembeschreibung:
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
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 V10.5 users.                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V10.5 Fix Pack 3 or higher.                   * 
****************************************************************
Local-Fix:
verfügbare FixPacks:
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows

Lösung
Fixed in DB2 V10.5 Fix Pack 3.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
09.10.2013
11.02.2015
11.02.2015
Problem behoben ab folgender Versionen (IBM BugInfos)
Problem behoben lt. FixList in der Version
10.5.0.4 FixList