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

LOAD RESUME taking more volumes for tablespace


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

New User


Joined: 02 Feb 2010
Posts: 44
Location: Bangalore

PostPosted: Tue Jul 20, 2010 10:14 am
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Jul 21, 2010 5:30 pm
Reply with quote

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
View user's profile Send private message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 44
Location: Bangalore

PostPosted: Thu Jul 22, 2010 9:08 am
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Jul 22, 2010 10:39 pm
Reply with quote

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
View user's profile Send private message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 44
Location: Bangalore

PostPosted: Fri Jul 23, 2010 8:31 am
Reply with quote

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
View user's profile Send private message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 44
Location: Bangalore

PostPosted: Mon Jul 26, 2010 10:16 am
Reply with quote

Hi,

Can anyone explain me what would be the exact reason for the above issue?

Thanks & Regards,
Chandra
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 26, 2010 10:33 am
Reply with quote

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
View user's profile Send private message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 44
Location: Bangalore

PostPosted: Mon Jul 26, 2010 12:29 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Jul 26, 2010 7:49 pm
Reply with quote

Hello,

Hopefully, someone who knows more about this than i do will reply icon_neutral.gif

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
View user's profile Send private message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 44
Location: Bangalore

PostPosted: Wed Jul 28, 2010 3:32 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Jul 28, 2010 4:21 pm
Reply with quote

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
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 Load new table with Old unload - DB2 DB2 6
No new posts How to load to DB2 with column level ... DB2 6
No new posts REASON 00D70014 in load utility DB2 6
No new posts force tablespace using LISTDEF input DB2 1
No new posts DB2 Load - Sort Or order BY DB2 1
Search our Forums:

Back to Top