Serge Rielau wrote:
73blazer wrote:
Mark A wrote:
"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...
I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.
Ken
I will assume you did the reorg first, then the runstats. Reorg and
runstats syntax is different between V7 and V8, so make sure you
check the syntax and make sure you reorg the indexes also (they
automatically get reorged in version 7).
Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I
didn't know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.
Can you post the two plans?
Cheers
Serge
I was watching in nmon, and it seems on my 8.2 instance that 1 CPU is
being pegged out for the duration of the query. Where on the 7.2
machine, that doesn't seem to happen, but it's hard to tell there
because the query comes back very quickly
here are the db2exfmt outputs for the queries:
DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2005-04-14-19.10.54.089552
EXPLAIN_REQUESTER: PRDTST
Database Context:
----------------
Parallelism: None
CPU Speed: 6.691544e-07
Comm Speed: 0
Buffer Pool size: 200000
Sort Heap size: 647
Database Heap size: 3422
Lock List size: 1467
Maximum Lock List: 6
Average Applications: 50
Locks Available: 9946
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 28
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select S_PART_NUMBER,S_TYPE
from physical.part_list
where S_PART_NUMBER like '%KEN%'
Optimized Statement:
-------------------
SELECT Q1.S_PART_NUMBER AS "S_PART_NUMBER", Q1.S_TYPE AS "S_TYPE"
FROM PHYSICAL.PART_LIST AS Q1
WHERE (Q1.S_PART_NUMBER LIKE '%KEN%')
Access Plan:
-----------
Total Cost: 78834.3
Query Degree: 1
NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT)
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 08.02.1
SOURCE_NAME: SQLC2E06
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2005-04-15-09.14.16.538703
EXPLAIN_REQUESTER: DB2864
Database Context:
----------------
Parallelism: None
CPU Speed: 6.723442e-07
Comm Speed: 100
Buffer Pool size: 200000
Sort Heap size: 647
Database Heap size: 3422
Lock List size: 1467
Maximum Lock List: 6
Average Applications: 1
Locks Available: 5633
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select S_PART_NUMBER,S_TYPE
from physical.part_list
where S_PART_NUMBER like '%KEN%'
Optimized Statement:
-------------------
SELECT Q1.S_PART_NUMBER AS "S_PART_NUMBER", Q1.S_TYPE AS "S_TYPE"
FROM PHYSICAL.PART_LIST AS Q1
WHERE (Q1.S_PART_NUMBER LIKE '%KEN%')
Access Plan:
-----------
Total Cost: 28265.9
Query Degree: 1
NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT)