We have scenarios where we insert multiple rows (more than a hundred thousand). Since these tables are related to reporting and are like work tables, we don't want it to be logged. I suppose we can use 'NOT LOGGED INITIALLY' but I read the problem with this is that during the COMMIT, it moves all the dirty pages to disk and hence the COMMIT may take longer. Also, I read that probably using a load from cursor doesn't do logging. Is that correct?
Can you please guide the best way by which logging can be avoided?
Post your DB2-server version+fixpack and operating-system detail for the DB2-server when asking for help.
Check if the LOAD command (not the import) can be used for these work tables (if you are loading from files or pipes)
You can also use LOAD if you are loading from a cursor (capabilities depend on your DB2 version+platform).
The load is unlogged (but you need to be certain about the recoverability aspects)
Avoid 'not logged initially' (because on any failure you have to drop and recreate the table)
If they are true work tables you can consider making them DGTT or CGTT and making them unlogged (again, dependin on your version+platform).
Okay, Thanks for the information. Below are the required details. They are work tables but we would want to keep the data in the table after the process is run so that if there is any issue in the output report, we can check the data in the table for trouble-shooting. In that case, I suppose DGTT or CGTT may not work. And we don't bother about recovery of these tables because these are related to reports that can be run anytime. So, please suggest accordingly.
Nope, there is no specific reason. I was not sure if logging doesn't happen with LOAD and if there is any downside of using the LOAD command. That is the reason wanted to check the best option. But as you mentioned 'NOT LOGGED INITIALLY' may not be good option and that LOAD doesn't do logging, so would go with LOAD from Cursor option.
Thanks for prompt responses. I believe the above options to delete would delete the whole data of teh table. What if we need to delete only selected records. Say in a table of 5 Million records, need to delete only a million records need to be deleted (based on value of a column). How can I do that without logging?
You can use table partitioning or MDC.
To delete a subset of rows you can detach the corresponding partition if you can choose the partitioning key appropriately.
Deletes from MDC tables can produce significantly less logging than usually.
I hit a road block while using load cursor with transaction log full. Its 60mil rows (2 CLOB ) and I had 50 logs with 4 mb each. Load cursor does logging however minimally. I tried with Insert / replace. nonrecoverable/copy no.