Using Index

[arrbee]

1). How do we know whether a SELECT query is using INDEX or not?
2). How can we say that a SELECT query is optimized or not? My guess is that before using that query in an embedded SQL we can run EXPLAIN on that. Can anybody explain the details?
3). How to use a TEST region program to run in DB2 PROD region using PROD tables, data?

TIA.
_________________
Arr Bee
-------------
?My joy in learning is partly that it enables me to teach? - Seneca(Roman philosopher, mid-1st century AD)

[Kalicharan]

Hi,

1). How do we know whether a SELECT query is using INDEX or not?

EXPLAIN tells you if an index access or tablespace scan is used.
If indexes are used, EXPLAIN tells you how many indexes and
index columns are used and what I/O methods are used to read the pages.

Before you can use EXPLAIN, you must create a table called PLAN_TABLE to hold the results of EXPLAIN.

ACCESSTYPE keyword of PLAN_TABLE tells the method of accessing the table:
I ==> an index (identified in ACCESSCREATOR and ACCESSNAME)
I1 ==> a one-fetch index scan
N ==> By an index scan when the matching predicate contains the IN keyword
R ==> By a table space scan
M ==> By a multiple index scan (followed by MX, MI, or MU)
MX ==> By an index scan on the index named in ACCESSNAME
MI ==> By an intersection of multiple indexes
MU ==> By a union of multiple indexes
blank Not applicable to the current row

2). How can we say that a SELECT query is optimized or not? My guess is that before using that query in an embedded SQL we can run EXPLAIN on that. Can anybody explain the details?
Based on
a)Predicates
b)Predicate filter factor
c)Indexing
d)Usage of Subquery
e)Access path selection etc
we can determine whether the SELECT query is optimized or not.

EXPLAIN
As we know that DB2 optimizer chooses the access path for the query.
If we want to know what access path DB2 chooses during the plan preparation, then the request should be placed to DB2.
The request can be done in two ways.

Method 1: Use EXPLAIN(YES) parameter in the BIND card.

SELECT * from ownerid.PALN_TABLE order by APPLNAME,COLLID,VERSION,PROGRAME,TIMESTAMP DESC, QUERYNO,QBLOCKNO,PLANNO

Method 2: Use the following command directly in the program or in SPUFI or QMF.

Syntax: EXPLAIN ALL SET QUERYNO=integer FOR SQL-statement.

Before executing the request, there should be a PLAN_TABLE under the user-id, based on model SYSIBM.PLAN_TABLE is a standard table that must be defined with predetermined columns, data types and lengths. During bind process, DB2 optimizer briefs the access path chose by it in the PLAN_TABLE.

If you want to query the access path for single query then use the query below:
SELECT * from PLAN_TABLE where QUERYNO=integer ORDERBY QBLOCKNO, PLANNO.
Now the access path taken by the optimizer is loaded into PLAN_TABLE. Knowing the meaning of PLAN_TABLE columns and values
is very important to understand the access path.
DB2V7 has 49 columns in PLAN_TABLE.
Important Columns in the PLAN_TABLE are
QUERYNO
QBLOCKNO
APPLNAME
PROGRAME
PLANNO
METHOD
ACCESSTYPE
MATCHCOLS
INDEXONLY
PREFETCH
QBLOCK_TYPE
JOIN_TYPE
TIMESTAMP


3). How to use a TEST region program to run in DB2 PROD region using PROD tables, data?
Change the BIND CARD(Package, Plan, Owner, Qualifier) and other datasets accordingly.


Thanks,
Kalicharan

[arrbee]

Thanks Kali. The answers are very helpful.
_________________
Arr Bee
-------------
?My joy in learning is partly that it enables me to teach? - Seneca(Roman philosopher, mid-1st century AD)