IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

What do I need to do after empty a large table?


 
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Moderator Emeritus


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

PostPosted: Thu Dec 05, 2013 11:25 pm
Reply with quote

Hello,

Why not just TRUNCATE the table?

Use [URL] BBCode for Links
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
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

Moderator Emeritus


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

PostPosted: Fri Dec 06, 2013 6:39 am
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
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
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 All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Inserting records into the empty segm... IMS DB/DC 0
No new posts Need to read duplicate rows from tabl... DB2 3
No new posts CICS Web services Assistant - Handlin... CICS 3
No new posts Hex Table COBOL Programming 2
No new posts Catalog table SYSSTATSFEEDBACK DB2 2

Back to Top