Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Index related queries

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 45
Location: India

PostPosted: Thu Jun 28, 2018 8:36 pm    Post subject: Index related queries
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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Thu Jun 28, 2018 9:46 pm    Post subject:
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: 45
Location: India

PostPosted: Fri Jun 29, 2018 12:50 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Fri Jun 29, 2018 4:44 pm    Post subject:
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: 45
Location: India

PostPosted: Fri Jun 29, 2018 5:23 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Fri Jun 29, 2018 5:27 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Query on secondary index Suja.Sai IMS DB/DC 0 Wed Oct 31, 2018 9:47 pm
No new posts Can records in IMS/DB be related usin... SRICOBSAS IMS DB/DC 2 Fri Oct 19, 2018 3:53 pm
No new posts Incorrect output after build alternat... sandeep kumar302 JCL & VSAM 10 Wed May 02, 2018 2:35 pm
No new posts IDCAMS RENAMING VSAM FILE ALONG WITH ... upendrasri JCL & VSAM 12 Tue Apr 17, 2018 10:42 pm
No new posts KSDS and Index file in cobol deepak14450 COBOL Programming 9 Fri Dec 22, 2017 9:36 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us