Im supporting DB2 v7.1 on OS/390.

SELECT from VIEWs based on UNION ALL of tables is not using Indexes available on the base tables, but instead going for a tablespace scan. EXPLAIN as well as TMON confirm the tablespace scan.

CREATE INDEX T1IX1 ON TABLE1(COLA)
;
CREATE INDEX T2IX1 ON TABLE2(COLA)
;
CREATE VIEW VIEW1
AS
(SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2
)
;
SELECT COLA FROM VIEW1
WHERE COLA = ?
;

TABLE1 and TABLE2 have INDEXes on COLA and runstats are current. These tables have around 10 million records each and the query goes for a tablespace scan and does not use the Indexes on COLA. Any joins on this VIEW based on COLA also go for tablespace scan causing a big performance issue.

I did some research and found that its a known problem with v7.1 and IBM has provided fix in the form of (1) PTF UQ62215 for APAR PQ55393 and (2) PTF UQ54302 for APAR PQ47178. Here are the links:

http://www-1.ibm.com/support/docview...id=swg1PQ55393
http://www-1.ibm.com/support/docview...id=swg1PQ47178

The real problem, however, is that our System folks have confirmed that we are already at a higher PTF level with FMID HDB7710 which is inclusive of PTFs UQ62215 & UQ54302.

Can anyone please help to figure out what could be missing here. The PTF level of running DB2 was found out by the system folks using SMP/E, though, when I do DIAGNOSE DISPLAY MEPL, I dont see these PTFs in there.

Are there any other PTFs that needs to go on top of this? Or is there some global parameter/ setting that would control this?

Any thoughts would be highly appreciated.

Thanks,

Hi,

How selective is the value you're using for the variable? If it spans more than (roughly) 20% of the table, DB2 will probably not bother to use the index and shoot for a TS scan instead.

Best regards,

Willy

Hi, Thanks for your response.

This COLA is part of primary key and has a very high cardinality. When the SELECT is executed only againt TABLE1 or TABLE2, it does use INDEXes and responds in sub-select.

Regards,

Have you raised this issue with IBM folks ?

Sathyaram

Could we have a look at the table and index definitions? An extract of the stats from the catalog would also be helpful.

Thx!

Willy