Db2 – The query result changes in remote and local database

db2

The below table is created in local database and remote databases.

CREATE TABLE EMPLOYEE1 ( EMP_ID INTEGER, EMP_NAME VARCHAR(10), EMP_DEPT VARCHAR(10) );

Insert the below rows in tables created in both the databases.

INSERT INTO EMPLOYEE1 (EMP_ID, EMP_NAME,EMP_DEPT)
VALUES (1,'A','IT'), (2,'B','IT'), (3,'C','SALES'), (4,'D','SALES'), (5,'E','ACCOUNTS'), (6,'F','ACCOUNTS'), (7,'G','HR'), (8,'H','HR');

COMMIT;

If i run the below query in local database of my system then the query result is correct.i.e it is returning all the rows in the table as the query exactly has to do. But the same query if i run in remote database then only 4 rows are returned,which is a wrong result.

SELECT * FROM EMPLOYEE1 WHERE (EMP_DEPT NOT IN ('IT','SALES') OR EMP_DEPT IN ('IT','SALES'));

**Can anyone suggest why the query behavior changes?**I am using DB2 9.7 Express C

Best Answer

It might be that there is an unenforced (ie, informational) constraint on the table. Those may cause the optimizer to ignore the data that violates that constraint. Here is a good blog post about that.

Related Question