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
 

 

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: 1278
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 Not able to compress a very large PDS Hervey Martinez JCL & VSAM 7 Fri Nov 04, 2016 5:57 pm
No new posts VSAM define for large file jerryte JCL & VSAM 9 Wed Oct 05, 2016 1:51 am
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 Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


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