View previous topic :: View next topic |
Author |
Message |
Magesh_J Intermediate
Joined: 21 Jun 2014 Posts: 259 Topics: 54
|
Posted: Wed Sep 26, 2018 1:31 pm Post subject: Reorg and Runstats |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
|
Posted: Wed Sep 26, 2018 4:00 pm Post subject: Re: Reorg and Runstats |
|
|
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
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
Magesh_J Intermediate
Joined: 21 Jun 2014 Posts: 259 Topics: 54
|
Posted: Fri Sep 28, 2018 11:04 am Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
|
Posted: Fri Sep 28, 2018 11:54 am Post subject: |
|
|
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.
Thanks for the documentation about truncate. Learned a new thing. _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
|
|