MVSFORUMS.com

jim haire · Beginner Joined: 30 Dec 2002 Posts: 140 Topics: 40

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?

kolusu · Posted: Thu May 20, 2021 9:28 am Post subject:

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

jim haire · Beginner Joined: 30 Dec 2002 Posts: 140 Topics: 40

Thank you Kolusu.

haatvedt · Posted: Tue May 25, 2021 10:14 pm Post subject:

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)