MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Defining an index where you know the values of key fields

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Thu May 20, 2021 9:02 am    Post subject: Defining an index where you know the values of key fields Reply with quote

I am wondering if DB2 has the following capability.

I have a table that has approximately 170 million rows. I have a scenario where I want to delete the rows from this table where column_1 has a value of "AA" and column_2 has a resolved_date of null (i.e. has not been resolved).

If there was an index created on these two columns, there would need to be space allocated for 170 million indexes. I was wondering if there is a capability in DB2 where a rule could be defined that when a row has a column with a value of "AA" and a resolved date of null that the row_ID is captured or something to that effect.

If there is no other need for this index other than for this exact condition, it seems like a waste of space to create an index to hold all the other combinations of column-1,column_2 when I am only interested in the condition I mention above.

Is this possible?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu May 20, 2021 9:28 am    Post subject: Reply with quote

jim haire,

You can have partitioned index with a specific range of values. Check this link

https://www.ibm.com/docs/en/db2/11.1?topic=indexes-creating-partitioned
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Thu May 20, 2021 12:24 pm    Post subject: Reply with quote

Thank you Kolusu.
Back to top
View user's profile Send private message
haatvedt
Beginner


Joined: 14 Nov 2003
Posts: 66
Topics: 0
Location: St Cloud, Minnesota USA

PostPosted: Tue May 25, 2021 10:14 pm    Post subject: Reply with quote

If you are only using this new index to delete rows from the table, you might consider the following which would be more efficient.

Set up an on demand REORG job which includes a DISCARD parameter. It's more efficient and you get the reorganization done at the same time.
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group