| 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: 12367
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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
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: 12367
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 - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
