Using Index

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
User avatar
arrbee
Active Member
Posts: 144
Joined: Fri Feb 24, 2006 11:33 am

Using Index

Post by arrbee » Sun Jun 11, 2006 11:37 am

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)

User avatar
Kalicharan
Moderator
Posts: 31
Joined: Wed Feb 08, 2006 1:51 pm

Post by Kalicharan » Thu Jul 06, 2006 12:07 pm

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

User avatar
arrbee
Active Member
Posts: 144
Joined: Fri Feb 24, 2006 11:33 am

Post by arrbee » Mon Jul 10, 2006 10:52 am

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)

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others