MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Reorg and Runstats

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Wed Sep 26, 2018 1:31 pm    Post subject: Reorg and Runstats Reply with quote

Hi,

I have requirement to refresh a db2 table every day.

1.Delete all records from table.
2.Perform Runstats
3.Insert record into table using a cobol db2 program.

May I use Truncate instead of delete ?
I know truncate will not perform any trigger operation.

This is just a single table, no relationship or no referential integrity and No index defined.

Also do I need to perform Runstats/Reorg after I truncate?

Or is there any other method to delete all records effectively in single step.

Thanks
Magesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Wed Sep 26, 2018 4:00 pm    Post subject: Re: Reorg and Runstats Reply with quote

Magesh_J wrote:
Hi,

I have requirement to refresh a db2 table every day.

1.Delete all records from table.


Magesh_J,

You can do that by LOAD REPLACE the table with an DUMMY input dataset.
Magesh_J wrote:

2.Perform Runstats


What is the point of running RUNSTATS on an empty table?

Magesh_J wrote:

3.Insert record into table using a cobol db2 program.


Ideally I will have the COBOL program write a file (a GDG- You can even this as backup/historical reference of the data) and then use this dataset to LOAD REPLACE the table ( this will automatically take care of requirement # 1)

After the LOAD REPLACE, perform the REORG/RUNSTATS and you should be good.

Magesh_J wrote:

May I use Truncate instead of delete ?
I know truncate will not perform any trigger operation.
Also do I need to perform Runstats/Reorg after I truncate?



Truncate? What does it do? I am not aware of any such mechanism. Since your table does not have any index, what is the point of RUNSTATS/REORG? It won't improve the access path of the queries executed against that table.

Magesh_J wrote:

This is just a single table, no relationship or no referential integrity and No index defined.
Or is there any other method to delete all records effectively in single step.


Yes the simple method is called LOAD REPLACE
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54

PostPosted: Fri Sep 28, 2018 11:04 am    Post subject: Reply with quote

Hi Kolusu,

Thanks for the kind advise,

Load replace is not working, because there is an auto-generated field ROW_NUM.

I think I should specify individual columns to delete the records in the table.

Quote:

Truncate? What does it do? I am not aware of any such mechanism. Since your table does not have any index, what is the point of RUNSTATS/REORG? It won't improve the access path of the queries executed against that table.


Truncate Link

Thanks
Magesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Fri Sep 28, 2018 11:54 am    Post subject: Reply with quote

Magesh_J wrote:
Hi Kolusu,

Thanks for the kind advise,

Load replace is not working, because there is an auto-generated field ROW_NUM.


Your table definition should have the ROW_NUM column as GENERATED BY DEFAULT and DB2 will automatically generate it for you.

Magesh_J wrote:

Truncate Link


Thanks for the documentation about truncate. Learned a new thing.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group