How Can Inclusion/Exclusion of Columns Affect Number Rows Returned?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

esmith2112 - 23 Jun 2006 22:14 GMT

I have a situation that I can't explain. Boiled down to its essence, I
have a query of the form

SELECT A.COL1, A.COL2, B.COL1
FROM A
LEFT JOIN B ON A.KEY = B.KEY

This query produces 5383 rows of output. Because B.COL1 was defined as
VARCHAR(2000), it was making my ouput file too hard to browse so I
eliminated it from the select clause.

SELECT A.COL1, A.COL2
FROM A
LEFT JOIN B ON A.KEY = B.KEY

Now I get only 4459 rows returned. I am confused. I thought the joins
(and any subsequent filtering) would determine the number of rows.

What am I missing here?

Platform is AIX 4.3 + DB2 7.2 FP10.

Thanks,
Evan

Reply to this Message

Serge Rielau - 24 Jun 2006 02:23 GMT

> I have a situation that I can't explain. Boiled down to its essence, I
> have a query of the form
[quoted text clipped - 17 lines]
>
> Platform is AIX 4.3 + DB2 7.2 FP10.

DB2 V7? Ehem, and and outdated fixpack at that...
Anyway, the only explanation I have which does not involve a bug could
be that you have an RI relationship between the two keys and it has been
corrupted because SET INTEGRITY as run using the UNCHECKED option.
If you have such an RI, perform SET INTEGRITY to verify your data is the
way DB2 thinks it is.
I'm basing my assumption on the fact that DB2 has dropped the join
assuming it is key preserving. And that.. was wrong. There WERE
duplicate B.KEYs

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

esmith2112 - 26 Jun 2006 14:39 GMT

> > Platform is AIX 4.3 + DB2 7.2 FP10.
> DB2 V7? Ehem, and and outdated fixpack at that...
[quoted text clipped - 14 lines]
> DB2 Solutions Development
> IBM Toronto Lab

V7, yes. The road to upgrade has been difficult with ties to legacy
applications that have proven to be upgrade-resistent. With that
said...

The relationship between A to B is one-to-many. There is a FK
relationship in place.

I tried experiementing with SET INTEGRITY. I also dropped all
constraints at one point. Nothing altered the crazy results. I used the
explain facility to generate the plans. The only difference between the
two is that the query that fetches the extra column shows the FETCH in
addition to an IXSCAN. The other has just the IXSCAN.

Being out of support for so long has made us largely self-reliant on
problem solving but this one has our heads spinning. I will continue to
test. Is it possible that higher level FixPak will solve the problem?

Thanks,
Evan

Reply to this Message

Serge Rielau - 26 Jun 2006 16:18 GMT

>>> Platform is AIX 4.3 + DB2 7.2 FP10.
>> DB2 V7? Ehem, and and outdated fixpack at that...
[quoted text clipped - 31 lines]
> problem solving but this one has our heads spinning. I will continue to
> test. Is it possible that higher level FixPak will solve the problem?

Yes, but if it does it should be in the APAR list. If things went as
they should, it should have been flagged as HIPER High Pervasive.
So peruse that list.

Take a look at the explains for the two queries to validate whether the
join partner was dropped.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message