Hi all,

DB2 LUW 9.5 FP5.

Looking the following statement:

1)

WITH T_MACHINE (MACHINE_ID) AS

(SELECT * FROM (VALUES ('00144FFA113A849F6BA4432F'),
('00144FA0EF6084A0EF604329'), ('00144FAABFC284AABFC24339') ) AS X)

SELECT

SS.MACHINE_ID,
TOTAL_STORAGE,
TOTAL_USED,
UNPARTITIONED_FREE,
FILESYSTEM_FREE,
TOTAL_FREE_SPACE,
IS_EXTERNAL

FROM

VWS.V_EXTINT_STORAGE_SUMMARY SS INNER JOIN T_MACHINE T ON
( SS.MACHINE_ID = T.MACHINE_ID )

I get several tablescans, bad access plan and bad response time.. in
others words, the pushdown does not works.



########

When I try:

2)

SELECT

SS.MACHINE_ID,
TOTAL_STORAGE,
TOTAL_USED,
UNPARTITIONED_FREE,
FILESYSTEM_FREE,
TOTAL_FREE_SPACE,
IS_EXTERNAL

FROM

VWS.V_EXTINT_STORAGE_SUMMARY SS WHERE SS.MACHINE_ID IN
( '00144FFA113A849F6BA4432F', '00144FA0EF6084A0EF604329',
'00144FAABFC284AABFC24339' )

Good acces plan, IXScans, fast response time, the pushdown works fine!

I would like to use the first way (1), because there is situations
where the user can select a big amount of machine_ids.

Any ideas?

Thanks in advance.

Bruno.

On Mar 23, 3:52*pm, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote:

VWS.V_EXTINT_STORAGE_SUMMARY a View ?
Can you please paste the Structures/Text of the tables/views involved
and also..the db2exfmt outputs for both Good and Bad

Cheers..
Shashi Mannepalli

How about this?
WITH T_MACHINE (MACHINE_ID) AS (
VALUES
('00144FFA113A849F6BA4432F')
, ('00144FA0EF6084A0EF604329')
, ('00144FAABFC284AABFC24339')
)
SELECT MACHINE_ID
, TOTAL_STORAGE
, TOTAL_USED
, UNPARTITIONED_FREE
, FILESYSTEM_FREE
, TOTAL_FREE_SPACE
, IS_EXTERNAL
FROM VWS.V_EXTINT_STORAGE_SUMMARY SS
WHERE MACHINE_ID
IN (SELECT MACHINE_ID
FROM T_MACHINE T
)
;

On Mar 23, 6:29*pm, whatever <audheya2... (AT) gmail (DOT) com> wrote:
View text:

create view VWS.V_EXTINT_STORAGE_SUMMARY as

select

MACHINE_ID,IS_EXTERNAL,TOTAL_STORAGE,TOTAL_USED,UN PARTITIONED_FREE,FILESYSTEM_FREE,TOTAL_FREE_SPACE
from
VWS.V_EXTINT_STORAGE_WIN
where
SUBSTR(MACHINE_ID,21,2)='11'

union
select

MACHINE_ID,IS_EXTERNAL,TOTAL_STORAGE,TOTAL_USED,UN PARTITIONED_FREE,FILESYSTEM_FREE,TOTAL_FREE_SPACE
from
VWS.V_EXTINT_STORAGE_AIX
where
SUBSTR(MACHINE_ID,21,2)='33'

union
select
S.MACHINE_ID, 'U' as IS_EXTERNAL,
TOTAL_STORAGE,TOTAL_USED,UNPARTITIONED_FREE,FILESY STEM_FREE,TOTAL_FREE_SPACE
from
CLT.STORAGE_SUMMARY S
inner join CLT.TBL_COLLECT_FSSTAT_MAX F on (S.MACHINE_ID =
F.MACHINE_ID and F.MAX_COLLECT_TIME = S.COLLECT_TIME )
where
SUBSTR(F.MACHINE_ID,21,2) in ('63','43','81','51','75');




The output of db2exfmt is too long, so I put the files in my
production server.
Link to download:

Good:
http://tinyurl.com/6cnuabw

Bad:
http://tinyurl.com/6apn87d

Thanks.

Bruno

1) Aren't VWS.V_EXTINT_STORAGE_WIN and VWS.V_EXTINT_STORAGE_AIX views?

2) UNION ALL would be better to avoid unnecessary sort to eliminate
duplicated rows.
Each selected MACHINE_ID between UNIONed subselects must be diffrent.
Because, SUBSTR(MACHINE_ID,21,2) are not overlapped with each UNIONed
subselects.

On Mar 24, 10:15*am, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
I am assuming there are some UNION ALL and DISTINCT in the View
Definitions..of VWS.V_EXTINT_STORAGE_WIN and VWS.V_EXTINT_STORAGE_AIX
Seems like QRW (Query ReWrite) is not Pushing Into these Subqueries.
Seems like IBM PMR is the way to go

Cheers...
Shashi Mannepalli