Strange result in DB2. Divergences queries

Question

A strange thing, that I don't know the cause, is happenning when trying to collect results from a db2 database.

The query is the following:

SELECT
    COUNT(*)
FROM
    MYSCHEMA.TABLE1 T1
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            MYSCHEMA.TABLE2 T2
        WHERE
            T2.PRIMARY_KEY_PART_1 = T1.PRIMARY_KEY_PART_2
            AND T2.PRIMARY_KEY_PART_2 = T1.PRIMARY_KEY_PART_2
    )

It is a very simple one. The strange thing is, this same query, if I change COUNT(*) to * I will get 8 results and using COUNT(*) I will get only 2. The process was repeated some more times and the strange result is still continuing.

At this example, TABLE2 is a parent table of the TABLE1 where the primary key of the TABLE1 is PRIMARY_KEY_PART_1 and PRIMARY_KEY_PART_2, and the primary key of the TABLE2 is PRIMARY_KEY_PART_1, PRIMARY_KEY_PART_2 and PRIMARY_KEY_PART_3. There's no foreign key between them (because they were legacy ones) and they have a huge amount of data.

The DB2 query SELECT VERSIONNUMBER FROM SYSIBM.SYSVERSIONS returns:

7020400
8020400
9010600

And the client used is SquirrelSQL 3.6 (without the rows limit marked).

So, what is the explanation to this strange result?

Have you tried doing an Explain on your query? Are you pointing to the same database? using the same tool? Please show 2 queries and the 2 results. — Pat B, Aug 05 '15 at 13:52
When I tried *EXPLAIN PLAN SELECTION SET QUERYNO = 13 SET QUERYTAG = 'TEST13' FOR [MY_QUERY_HERE]* I receive a error: *The required Explain table "USER.EXPLAIN_INSTANCE" does not exist.. SQLCODE=-219, SQLSTATE=42704, DRIVER=4.14.111 SQL Code: -219, SQL State: 42704*. — GarouDan, Aug 05 '15 at 14:00
look here to use explain : http://stackoverflow.com/questions/1098814/how-do-i-use-db2-explain . you can easily do it from the db2cmd prompt : db2expln -database sample -t -g -f "sql query" — Pat B, Aug 05 '15 at 14:05
This looks like an old and unsupported version of DB2. In one of the old releases once was a defect producing that inconsistency. — data_henrik, Aug 07 '15 at 13:54

score 0 · Answer 1 · answered Dec 27 '19 at 09:18

Without the details (including, at least, the exact Db2 version and DDL for both tables and their indexes) it can be just anything, and even with that details only IBM support will be really able to say, what is the actual reason.

Generally this looks like damaged data (e.g. differences in index vs table data).

Worth to open the support case with IBM.

Strange result in DB2. Divergences queries

1 Answers1