Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
What do I need to do after empty a large table?

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

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Dec 05, 2013 7:47 pm    Post subject: What do I need to do after empty a large table?
Reply with quote

I have two tables, each one contains data asof one accounting date, and these two tables are used by online transactions by turn.

If tabA is used tody, then there will be a batch job this evening which will make tabA empty, for the preparation of the next use.
Now I designed the empty job like below:
1. perform LOAD utility which loads a dummy dataset into the table, and STATISTICS parameter is specified in the LOAD statement;
(I think since the table contains tens of millions of rows before it become empty, RTS must have changed a lot, so it's necessary to gather the statistics for better performance. )
2. perform REPAIR utility which reset the COPY-PENDING status of tabA after a dummy dataset is loaded into it, as after the LOAD utility, the table is set to COPY-PENDING status. Because before the job, I have job to backup the data of the table, there should be no risk if I reset the status of the table using REPAIR utility. by the way, there is no online transaction accessing the table during batch window.
3. find out all the packages that are using the table;
4. issue REBIND command against all the packages of the previous step.

I just want to confirm if I'm doing the right thing.
Please kindly suggest. Thanks.
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: Thu Dec 05, 2013 11:25 pm    Post subject:
Reply with quote

Hello,

Why not just TRUNCATE the table?

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqk10/5.129?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Dec 06, 2013 6:37 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Why not just TRUNCATE the table?


I suppose TRUNCATE will not gather the latest statistics for subsequent REBIND.
Please correct me if I'm wrong.
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: Fri Dec 06, 2013 6:39 am    Post subject:
Reply with quote

Hello,

I suppose so . . .

Not sure why you want the old stats when there will be all new data?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Dec 06, 2013 10:12 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Not sure why you want the old stats when there will be all new data?

I don't want the old stats.
I update the new stats of the newly loaded data...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Dec 06, 2013 9:07 pm    Post subject:
Reply with quote

the newly loaded data = dummy so actually your runstats will reset all statistics to 0.
and (re-)binds of the programs are based on empty tables so accesspath wil be optimized for a very small table.

* if by the evening the table contains "tens of millions" it is very unlikely that these are the "best" accesspaths.

* Rebinding every day with 0-statistics is a bit overkill.

* fyi : RTS and RUNSTATS statistics are 2 different things.
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us