Folks,
I am having problem with an application that uses static SQL,
the application basically browses through the table given start and
end key most of the time it is processed from begining to end.
The db2expln tells me that there is no Data or Index prefetch.
I am running DB2 UDB 8 on AIX 5.3, and I am using DMS tablespace on
raw logical volumes on a SAN.
# db2level
DB21085I Instance "db2dev1" uses "32" bits and DB2 code release "SQL08023"
with level identifier "03040106".
Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak
"10".
Product is installed at "/usr/opt/db2_08_01".
This is the ddl for the table:
CREATE TABLE SCHEMA.EADMFX00
(KEY0 CHAR (29) NOT NULL,
KEY1 CHAR (26 ) NOT NULL,
KEY2 CHAR (30 ) NOT NULL,
KEY3 CHAR (50 ) NOT NULL,
REC VARCHAR(1000) NOT NULL)
IN TBSP1
INDEX IN TBSP1
LONG IN TBSP1;
CREATE UNIQUE INDEX EADMFX00_ASC ON SCHEMA.EADMFX00 (KEY0 ASC) ALLOW REVERSE SCANS;
CREATE INDEX EADMFX01_ASC ON SCHEMA.EADMFX00 (KEY1 ASC);
CREATE INDEX EADMFX02_ASC ON SCHEMA.EADMFX00 (KEY2 ASC);
CREATE INDEX EADMFX03_ASC ON SCHEMA.EADMFX00 (KEY3 ASC);
Here is the output of db2expln:
SQL Statement:
DECLARE KEY0_ASCBR CURSOR
FOR
SELECT KEY0, REC
FROM cfgdev1.EADMFX00
WHERE KEY0 >=:H00006 AND KEY0 <=:H00001
ORDER BY KEY0 ASC
FOR
FETCH ONLY
OPTIMIZE
FOR 15 ROWS
Intra-Partition Parallelism Degree = 4
Section Code Page = 819
Estimated Cost = 166.983475
Estimated Cardinality = 828.506165
Process Using 4 Subagents
| Table Constructor
| | 1-Row(s)
| Residual Predicate(s)
| | #Predicates = 1
| Nested Loop Join
| | Access Table Name = SCHEMA.EADMFX00 ID = 4,23
| | | Index Scan: Name = SCHEMA.EADMFX00_ASC ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: KEY0 (Ascending)
| | | #Columns = 2
| | | Parallel Scan
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| Insert Into Asynchronous Local Table Queue ID = q1
Access Local Table Queue ID = q1 #Columns = 2
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Return Data to Application
| #Columns = 2
Here is how I bind the package:
db2 PREP ${SqcCode} BINDFILE USING ${BindFile} ISOLATION CS \
BLOCKING NO DATETIME ISO EXPLAIN ALL EXPLSNAP ALL LANGLEVEL SAA1 \
OPTLEVEL 1 \
OUTPUT ${CCode} SQLERROR NOPACKAGE TARGET C
db2 BIND ${BindFile} BLOCKING NO DATETIME ISO EXPLAIN ALL EXPLSNAP ALL \
GRANT PUBLIC ISOLATION CS SQLERROR NOPACKAGE
Here is the output of reorgchk:
SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: SCHEMA.EADMFX00
SCHEMA EADMFX00 2413580 0 31529 31529 - 1.03e+09 0 99 100 ---
----------------------------------------------------------------------------------------
SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: SCHEMA.EADMFX00
SCHEMA EADMFX00_ASC 2e+06 3119 0 3 29 14 2e+06 100 90 22 0 0 -----
SCHEMA EADMFX01_ASC 2e+06 2060 0 3 26 0 2e+06 63 89 37 0 0 *----
SCHEMA EADMFX02_ASC 2e+06 3202 0 3 30 0 2e+06 100 90 21 0 0 -----
SCHEMA EADMFX03_ASC 2e+06 4658 0 3 50 0 2e+06 97 89 10 0 0 -----
-------------------------------------------------------------------------------------------------
It takes 3 minutes to browse through the whole table. I have other tables that
are bigger then this one and I can browse through them in few seconds.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail:
No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.