Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

LOAD RESUME taking more volumes for tablespace

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

New User


Joined: 02 Feb 2010
Posts: 40
Location: Bangalore

PostPosted: Tue Jul 20, 2010 10:14 am    Post subject: LOAD RESUME taking more volumes for tablespace
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: 1013
Location: India

PostPosted: Wed Jul 21, 2010 5:30 pm    Post subject:
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: 40
Location: Bangalore

PostPosted: Thu Jul 22, 2010 9:08 am    Post subject:
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: 1013
Location: India

PostPosted: Thu Jul 22, 2010 10:39 pm    Post subject:
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: 40
Location: Bangalore

PostPosted: Fri Jul 23, 2010 8:31 am    Post subject:
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: 40
Location: Bangalore

PostPosted: Mon Jul 26, 2010 10:16 am    Post subject:
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

Site Director


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

PostPosted: Mon Jul 26, 2010 10:33 am    Post subject:
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: 40
Location: Bangalore

PostPosted: Mon Jul 26, 2010 12:29 pm    Post subject:
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

Site Director


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

PostPosted: Mon Jul 26, 2010 7:49 pm    Post subject:
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: 40
Location: Bangalore

PostPosted: Wed Jul 28, 2010 3:32 pm    Post subject:
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: 1013
Location: India

PostPosted: Wed Jul 28, 2010 4:21 pm    Post subject:
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    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 Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Copybook structure in Load module Vai123 COBOL Programming 7 Fri Sep 16, 2016 8:29 pm
This topic is locked: you cannot edit posts or make replies. ERROR WHILE SUBMITTING LOAD JOB ANURADHA NEELAKANTAN JCL & VSAM 12 Thu Aug 25, 2016 11:50 am
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm
No new posts Reading selected volumes of a multi-v... RickBig JCL & VSAM 6 Wed Jul 13, 2016 7:26 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us