We are using DB2 v9.7.9 and recently started facing a problem with DB purge activity since we switched to HADR.
Actually we are altering target tables using NOT LOG INITIALLY in prod and creating backup tables to ensure zero loss in case if something wrong happens. Moreover, we are using 'LOAD' option for a cursor with NONRECOVERABLE.
Can you please suggest what to do next. Simple delete is taking a lot of time due to huge data available in table moreover leading to higher transaction logs.
Not Logged Initially is incompatible with HADR.
Consider an alternative method for purging content
For example, using range-partitioned tables (rollout old ranges does not use delete) if your DB2 licence allows it.
Permanently running scavenger threads that have a low commitcount for plain delete can spread the purge overhead over a much longer time without saturating the HADR link. Still the same amount of logging however spread over a much longer time period. Might not suit some applications.
If occasional bulk purges are the design, within some maintenance window, where a high percentage of total rows of a table are to be removed, consider copying the smaller percentage (rows-to-preserve) to another table, using a load-replace to put them back - and additionally ensure that the load has copy yes suitably configured, and the HADR standbys configured to replay the load . Might be suitable for some scenarios when more than 50% of rows are being purged in a maintenance window.
But Mark I think Load....copy yes is incompatible too with HADR.
Actually we have to delete rows based on some age criterion (or I can say retention policy)... So earlier we were using not logged initially clause while creating duplicate tables of actual target tables to escape unwanted logging. Later loading it into cursor using NONRECOVERABLE option
But since HADR is implemented it is opposing the two clauses. Now here moving on need a reliable and HADR compatible purging mechanism
LOAD COPY YES is compatible with BLOCKNONLOGGED.
But you need to be careful with such a load - if standby is not able to find a corresponding load copy file, the tablespace where this table resides will be placed in the "restore pending" state and you will have to restore whole database on standby to repair this tablespace.
Can you describe your retention policy in more detail and why can't you use table partitioning for this?