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.
Using Index
Moderators: Kalicharan, Moderator Group
Using Index
Arr Bee
-------------
?My joy in learning is partly that it enables me to teach? - Seneca(Roman philosopher, mid-1st century AD)
-------------
?My joy in learning is partly that it enables me to teach? - Seneca(Roman philosopher, mid-1st century AD)
- Kalicharan
- Moderator
- Posts: 31
- Joined: Wed Feb 08, 2006 1:51 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
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
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page