Db2 Tools for z/OS

Db2 Recovery Expert: How to recover to a point prior to the insertion of a new partition

By Robert Bersano posted Wed September 16, 2020 10:46 AM


What sometimes prevents users from exploiting new Db2 features is how can they recover from an error when using that new feature.  For example, if using the new insert partition feature to insert a partition into the middle of a range-partitioned table, how would you recover to a point prior to inserting the new partition?  The answer is by using a Db2 Tool that has exploited these new features such as Recovery Expert.  Recovery Expert understands these new inserted partitions, even if in the middle of a table-space and will assist you in the that type of recovery.  This type of recovery would be very difficult if not impossible with a utility such as DSN1COPY.

Here is an example of a table-space with 10 partitions

Then we alter the table-space to insert a partition in the middle:

Then we perform a REORG to materialize that change:


Then we can see we now have 11 partitions: 

So now we have determined we need to recover back to a point prior to inserting the new partition.  So, what we need to do is use Recovery Expert to recover the object back to that point.

The first step is to create an application profile with the table-space we need to recover back to the point before the insert partition and then build the recovery plans for that application profile.


The next step is to select the recovery point which is a point prior to the insertion of the new partition.


A recovery plan will be created that will drop the table-space and then create the table-space along with all dependents as well as recover the data.


As you can then see below the table-space has been recreated with the original 10 partitions and is as it looked prior to the insert partition.

Using a Db2 tool such as Recovery Expert can give you the confidence you need to exploit the newest features of Db2 knowing that the tools will also be exploiting these new features as well.