View previous topic :: View next topic
|
Author |
Message |
chandraBE
New User
Joined: 02 Feb 2010 Posts: 44 Location: Bangalore
|
|
|
|
Hi,
We have a table where it has data upto 3Millions. In this table every day data deleted and loaded. The deleting and adding happens in defferent streams. Say Morning it will delete and load data of Key 1XXXXXXX in noon it will delete and load the data of Key 2XXXXXXX and like wise repeats.
But in each time data is deleted and loaded tablespace taking extra volumes eventhough the record count is not increased.
I tried to delete and load of only 1XXXXXXX multiple times but this time tablespace did not take extra volumes.
When I tried delete and load of 1XXXXXXX and followed by delete and load of 2XXXXXXX tablespace taking extra volumes. Each time I repeat this delete load its taking more and more spaces.
Can anyone help me to understand why this is happening?. Is there any tablespace definitions?
Regards,
Chandra |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
ChandraBE,
When you say delete, does it mean deleting all the data from the table like without a WHERE clause.
Can you tell us, what type of tablespace it is like segmented or simple or partitioned. You can find this info in SYSTABLESPACE or SYSTABLEPART catalog tables.
Sushanth |
|
Back to top |
|
|
chandraBE
New User
Joined: 02 Feb 2010 Posts: 44 Location: Bangalore
|
|
|
|
Hi Sushanth,
Thanks for the quick response.
I am deleteing data using WHERE Clause to select perticular type of records. The tablespace is segmented.
Thanks & Regards,
Chandra |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Chandra,
You have a segmented tablespace with 3 Million rows.
In the morning = 1. Delete all the rows from table having keys 1XXXXXXX
2. Load keys 1XXXXXXX
In the afternoon = 1. Delete all the rows from table having keys 2XXXXXXX
2. Load keys 2XXXXXXX
Quote: |
When I tried delete and load of 1XXXXXXX and followed by delete and load of 2XXXXXXX tablespace taking extra volumes. Each time I repeat this delete load its taking more and more spaces.
|
Here you have to understand 2 different concepts working together, Like
Segmented Tables = Marks the rows as deleted in the space map page, doesnt delete the actual rows.
LOAD RESUME = Data will be appended to the tablespace and it will not reuse those rows marked deleted.
Here, i think what's happening is, because of deletes there will be holes in your tablespace and due to load resume data will be always appended to the end of the tablespace, so here extension of tablespace is sort of unavoidable.
Please let me know if my understanding is wrong,
I like to know,
How often do you do REORG on this tablespace ?
Are you using WHERE conditions like this '1%' or '2%' for deleting rows.
Any idea on how many rows you are deleting for each key '1XXXXXX' & loading with resume '1XXXXX' each time.
Sushanth |
|
Back to top |
|
|
chandraBE
New User
Joined: 02 Feb 2010 Posts: 44 Location: Bangalore
|
|
|
|
Hi Sushanth,
Thank you very much for the informations. They are pretty useful.
In the WHERE clause I am using a SELECT Query to select the qualified rows. The Query is like
DELETE FROM A.Table1
WHERE A.ACC IN
(SELECT B.ACC FROM B.Table2
WHERE B.SYS = 'ABC')
I understand because of deletes there will be holes in the tablespace. When I tried delete and LOAD RESUME of only 1XXXXXXX multiple times did not took extra volumes. I think in this case the holes were not created since the data deleted was at end of the tablespace and LOAD RESUME will reuse those rows marked deleted.
Please correct me if I am wrong here?.
Daily we are deleting 2.5Million Rows of '1XXXXXX' & loading with resume '1XXXXX' each time. And with '2XXXXXX' its upto 0.25 Million.
This tablespace had defined with smaller Volumes and because of this table was not picked for REORG in our stream. Now we had done REORG for this table and it released all the extra voumes.
Thank you again for your time.
Regards,
Chandra |
|
Back to top |
|
|
chandraBE
New User
Joined: 02 Feb 2010 Posts: 44 Location: Bangalore
|
|
|
|
Hi,
Can anyone explain me what would be the exact reason for the above issue?
Thanks & Regards,
Chandra |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Sushanth wrote: |
Here, i think what's happening is, because of deletes there will be holes in your tablespace and due to load resume data will be always appended to the end of the tablespace, so here extension of tablespace is sort of unavoidable. |
Does this not explain? |
|
Back to top |
|
|
chandraBE
New User
Joined: 02 Feb 2010 Posts: 44 Location: Bangalore
|
|
|
|
Thank you.
But when I tried delete and LOAD RESUME of only 1XXXXXXX multiple times did not took extra volumes. I wanted to know why this case it was not taking extra volumes, if the records were not physically deleted.
Regards,
Chandra |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Hopefully, someone who knows more about this than i do will reply
What comes to mind is that when you delete and insert the "same thing" it reuses the space. When you move across "things" the fragmentation occurs.
If no one provides a definitive answer here, i'm sure IBM support can help. |
|
Back to top |
|
|
chandraBE
New User
Joined: 02 Feb 2010 Posts: 44 Location: Bangalore
|
|
|
|
Thanks Dick Scherrer for your inputs. I believe you are right and that would be the cause.
I will wait for any definitive answer in this Forum.
Regards,
Chandra |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi D.sch & chandraBE,
Quote: |
Here, i think what's happening is, because of deletes there will be holes in your tablespace and due to load resume data will be always appended to the end of the tablespace, so here extension of tablespace is sort of unavoidable. |
I have misunderstood, space will be reused for segmented tablespace and new data will be appended to the end of the dataset if RESUME YES is used.
Sorry for the confusion,
Sushanth |
|
Back to top |
|
|
|