DB2 LUW Truncate, Restore and Rollforward

Starting in DB2 LUW version 9, TRUNCATE is supported. This gave ability to quickly get rid of massive amounts of data from tables without worrying about possibility of filling up logs. But, one question that seems to linger around in the minds of many DB2 LUW DBAs is “Is TRUNCATE operation recoverable?” I set out to find this out myself recently. This blog post details such findings.

Main differences between TRUNCATE and DELETE:

Let us first take a look at the main difference between DELETE and TRUNCATE.

Truncate does not generate ton of logs when getting rid of massive amounts of data while delete operation gets logged.
Delete is a DML (Data Manipulation Language) operation while Truncate is a DDL (Data Definition Language) operation.
You could get rid of subset of data using delete operation. Truncate gets rid of entire data in a table. So, one has to be absolutely sure that all the data is not needed before executing a TRUNCATE operation. There is an indirect way of copying the data into a different table, truncating the
TRUNCATE statement must be the first statement in a transaction. A transaction starting with TRUNCATE TABLE may include other statements, but if the transaction is rolled back, the TRUNCATE TABLE operation is not undone. Below is an example to demonstrate this point.

$ db2 "select count(*) from sales_test"
1
-----------
        123
  1 record(s) selected.

$ db2 +c "truncate table sales_test immediate"
DB20000I  The SQL command completed successfully.

$ db2 rollback
DB20000I  The SQL command completed successfully.

$ db2 "select count(*) from sales_test"
1
-----------
          0
  1 record(s) selected.

Recovering from a TRUNCATE operation:

Does this mean if a table is truncated by mistake, it cannot be undone? This is not true. If a proper backup that was taken before TRUNCATE was run is available, data could be recovered using a combination of RESTORE and ROLLFORWARD operations.

Recovering data to a point-in-time just before truncate operation was run.

In this section, we will attempt to recover data that was truncated by mistake.
If you have a DB2 LUW DBA that has worked on any restore/rollforward operation, this section would seem to be quite straight forward.

Setting up archival logging..


db2 "update db cfg for sample using LOGARCHMETH1 DISK:/dev_dbbackups/qaadb2mon01/logs"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 connect to sample
SQL1116N  A connection to or activation of database "SAMPLE" failed because
the database is in BACKUP PENDING state.  SQLSTATE=57019

$ db2 "backup database sample "
Backup successful. The timestamp for this backup image is : 20160302144823

$ db2 connect to sample
   Database Connection Information
 Database server        = DB2/AIX64 10.5.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

Let us create a tablespace and table for our test case.


$ db2 "create tablespace truncate_test "
DB20000I  The SQL command completed successfully.

$ db2 "create table db2inst1.sales_test like db2inst1.sales in truncate_test"
DB20000I  The SQL command completed successfully.

$ db2 "select tbspace from syscat.tables where tabname = 'SALES_TEST'"
TBSPACE
-------------------------------------
TRUNCATE_TEST
  1 record(s) selected.

$ db2 "select count(*) from db2inst1.sales"
1
-----------
         41
  1 record(s) selected.

Let us now write some data into SALES_TEST table.


$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "select count(*) from db2inst1.sales_test"
1
-----------
        205
  1 record(s) selected.

Let us now record timestamp before truncating the table.
In real world, we wouldn’t be able to record the timestamp but need to rely on combination of human intuition, entries in db2diag.log to come up with a timestamp to rollforward to.
For now, below is the timestamp we will recover to.


 $ db2 "select current timestamp from sysibm.sysdummy1"
1
--------------------------
2016-03-03-12.30.35.069825
  1 record(s) selected.

Now, let us truncate the table.


$ db2 "truncate table db2inst1.sales_test immediate"
DB20000I  The SQL command completed successfully.

$ db2 "select count(*) from db2inst1.sales_test"
1
-----------
          0
  1 record(s) selected.

Let us now restore from the online backup we took.


$ db2 "restore database sample from /dev_dbbackups/qaadb2mon01/backups taken at 20160303122749"
SQL2539W  The specified name of the backup image to restore is the same as the
name of the target database.  Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

We shouldn’t be able to connect to the database as it would be in rollforward pending mode.


$ db2 connect to sample
SQL1117N  A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

Let us now apply logs to the desired timestamp.
I will do this in 2 steps just to demonstrate that we don’t have to finish applying logs in one shot.


$ db2 "rollforward database sample to 2016-03-03-12.30.35.069825 using local time"
                                 Rollforward Status
 Input database alias                   = sample
 Number of members have returned status = 1
 Member ID                              = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000020.LOG
 Log files processed                    = S0000017.LOG - S0000019.LOG
 Last committed transaction             = 2016-03-03-12.28.20.000000 Local
DB20000I  The ROLLFORWARD command completed successfully.

$ db2 "rollforward database sample stop"
                                 Rollforward Status
 Input database alias                   = sample
 Number of members have returned status = 1
 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000017.LOG - S0000020.LOG
 Last committed transaction             = 2016-03-03-12.28.20.000000 Local
DB20000I  The ROLLFORWARD command completed successfully.

$ db2 connect to sample
   Database Connection Information
 Database server        = DB2/AIX64 10.5.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
$ db2 "select count(*) from db2inst1.sales_test"
1
-----------
        205
  1 record(s) selected.

205 was the record count before the table was truncated. This proved that we can recover to a point-in-time just before the truncate.

Recovering to a Point-in-Time after TRUNCATE operation:

From the above activity, we were able to demonstrate that we could restore data to a point-in-time before truncate. But, what if data was written to a table after it was truncated. Is that data recoverable? Let us look into it.


$ db2 "select count(*) from db2inst1.sales_test"
1
-----------
        205
  1 record(s) selected.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.

$ db2 "select count(*) from db2inst1.sales_test"
1
-----------
        492
  1 record(s) selected.

Let us now establish a timestamp to recover to by querying catalog tables.


$ db2 "select current timestamp from sysibm.sysdummy1"
1
--------------------------
2016-03-03-12.55.01.500936
  1 record(s) selected.

We will write more data to this table but we will not recover this data. Our goal is to be able to recover 492 records at the end of this exercise.


$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into db2inst1.sales_test (select * from db2inst1.sales)"
DB20000I  The SQL command completed successfully.
$ db2 "select count(*) from db2inst1.sales_test"
1
-----------
        574
  1 record(s) selected.

Let us now restore the database using the online backup image that was taken.


$ db2 "restore database sample taken at 20160303122749"
SQL2539W  The specified name of the backup image to restore is the same as the
name of the target database.  Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

$ db2 "rollforward database sample to 2016-03-03-12.55.01.500936 using local time"
                                 Rollforward Status
 Input database alias                   = sample
 Number of members have returned status = 1
 Member ID                              = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000021.LOG
 Log files processed                    = S0000017.LOG - S0000020.LOG
 Last committed transaction             = 2016-03-03-12.53.30.000000 Local
DB20000I  The ROLLFORWARD command completed successfully.
$ db2 "rollforward database sample stop"
                                 Rollforward Status

 Input database alias                   = sample
 Number of members have returned status = 1
 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000017.LOG - S0000021.LOG
 Last committed transaction             = 2016-03-03-12.53.30.000000 Local
DB20000I  The ROLLFORWARD command completed successfully.

$ db2 connect to sample
   Database Connection Information
 Database server        = DB2/AIX64 10.5.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
$ db2 "select count(*) from db2inst1.sales_test"
1
-----------
        492
  1 record(s) selected.

In this blog post, we have done the following:
1) Discussed main differences between TRUNCATE and DELETE operations.
2) Recovered data to a point-in-time before TRUNCATE operation was run.
3) Recovered data to a point-in-time after TRUNCATE operation was run.

db2talk

DB2 Linux, Unix and Windows Administration and Development

DB2 LUW Truncate, Restore and Rollforward

Main differences between TRUNCATE and DELETE:

Recovering from a TRUNCATE operation:

Recovering data to a point-in-time just before truncate operation was run.

Recovering to a Point-in-Time after TRUNCATE operation:

Leave a comment Cancel reply

Main differences between TRUNCATE and DELETE:

Recovering from a TRUNCATE operation:

Recovering data to a point-in-time just before truncate operation was run.

Recovering to a Point-in-Time after TRUNCATE operation:

Share this:

Related

Leave a comment Cancel reply