IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Index related queries


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Thu Jun 28, 2018 8:36 pm
Reply with quote

Hi Everyone,

I have confusion regarding the Index processing. After reading lot of material online i am still having the below queries.

1. Is it possible in case of Unique Index that data which is being inserted in the table is not being updated in Ascending or descending sequence ? if Yes, then how can we REORG the data in case of Unique Index because i have read that we can REORG the data only in case of Clustered Index.

2. In case of Clustered Index, What will happen if we dont provide the Index column name in REORG utility ? Will it consider any default index ?

3. Is it possible that DB2 will use the Index in a query when the Index column is not given in WHERE clause of the query. In other way, can DB2 Index will automatically pick up the Index in every sql query written in COBOL-DB2 program ?

4. When Unique Index can provide us the exact record then why do we need Non-Unique Index ? How Non-Unique Index is better than Unique Index ? If possible please provide an example.


I am a beginner in DB2 and used DB2 in my previous projects where i was working as a Mainframe Developer. So never used REORG utility that is why i am having these doubts.

Please let me know if anyone can help me to understand these concepts.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jun 28, 2018 9:46 pm
Reply with quote

1. NO and it depends how ASC DESC used while creating the INDEX. If the CLUSTER option is not specified then it treats that particular INDEX as implicit Cluster INDEX. More of a such questions should be asked to DBAs since that's why they are for.
2. If you are REORGanizing on TABLE SPACE name , it should not matter.
3.No. if none of the INDEX columns is used then it does Table spaces scans, if you are using few columns from the Index then it will use the INDEX but matching columns are less. So it is necessary to maintain DB2 Column cardinality.
4.We need Non-Unique Index when situations comes like say, you want to create a INDEX on FIRST and LAST NAME then it cannot be UNIQUE. Where you read that Non- Unique Index is better over Unique?
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Fri Jun 29, 2018 12:50 pm
Reply with quote

Hi Rohit,

Thanks for your reply. Your reply answered all my queries. Now just want to confirm regarding Point No. 3.

Suppose i have the below queries for EMPLOYEE table where Index is EmpId:

Case 1 : SELECT EmpId, EmpName, Empdept FROM Employee WHERE EmpId = '123ABC';

Case 2 : SELECT EmpId, EmpName, Empdept FROM Employee;

So my understanding from your answer is that Index will be considered in Case 1 because in Case 1 we are using Index (EmpId) in WHERE clause whereas in Case 2 the Index will not be used.

Please confirm.

And i dont get what do you meant by this
Quote:
if you are using few columns from the Index then it will use the INDEX but matching columns are less. So it is necessary to maintain DB2 Column cardinality.

Here when you say 'if you are using few columns' then do you mean in Select or in WHERE clause. Please explain with example if possible.

Really appreciate your patience for taking up my queries.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Jun 29, 2018 4:44 pm
Reply with quote

In your case2 there are no predicates in where so no question about indexing.
Moreover, like I said if you have created a index on first and last name but in your where you only used first name then the index is used but the matching columns will be 1 instead of 2 ( which is not good either ) in your explain.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Fri Jun 29, 2018 5:23 pm
Reply with quote

Got it now. Thanks again Rohit for all the explanation icon_smile.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Jun 29, 2018 5:27 pm
Reply with quote

You’re Welcome!!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Cobol file using index COBOL Programming 2
No new posts DL/I status code AK for GU call using... IMS DB/DC 1
No new posts Related to Unit Testing Testing & Performance 2
No new posts Add column to existing records using ... JCL & VSAM 2
No new posts Strange MNOTE related to BMS using PI... CICS 0
Search our Forums:

Back to Top