DB2 LUW Import from /dev/null, Restore and Rollforward

In an earlier blog post, we looked at recovering data after ‘TRUNCATE’ operation.
In this blog post, we will look at recovering data after using ‘import’ command to quickly get rid of data.

Import command could be used to empty a table. The trick is to use an empty file and then use ‘replace’ option in import command.

Backup

Let us first take a database backup to have an image to recover from.

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

Creating table>

Now, let us create a table.

$ db2 connect to sample
   Database Connection Information
 Database server        = DB2/AIX64 10.5.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
 
$ db2 "create table import_sales like db2inst1.sales in test"
DB20000I  The SQL command completed successfully.

Writing data into table

Let us now write some data into this newly created table.

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

Emptying table using import command

Let us now empty this table by ‘import’ing from an empty file. Do we have to create an empty file for this purpose?
Well, we don’t have to as UNIX readily supplies one for us. It is /dev/null.

$ ls -ltr /dev/null
crw-rw-rw-    1 root     system        2,  2 Mar  7 13:11 /dev/null

As a side note, I wasn’t sure what does ‘c’ (in crw-rw-rw) stand for in the above permission listing until I read this post:
http://askubuntu.com/questions/397493/what-does-the-first-character-of-unix-mode-string-indicate

Anyways, now back to our task. Let us now import this empty file ‘replacing’ into our target table. Here is how to do it.

 $ db2 "import from /dev/null of del replace into db2inst1.import_sales"
SQL3109N  The utility is beginning to load data from file "/dev/null".
SQL3110N  The utility has completed processing.  "0" rows were read from the
input file.
SQL3221W  ...Begin COMMIT WORK. Input Record Count = "0".
SQL3222W  ...COMMIT of any database changes was successful.
SQL3149N  "0" rows were processed from the input file.  "0" rows were
successfully inserted into the table.  "0" rows were rejected.

Number of rows read         = 0
Number of rows skipped      = 0
Number of rows inserted     = 0
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 0

Let us now get the record count after the fact.

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

Table activity after import from /dev/null

Now that we have emptied our table, let us dump some data into it, record a timestamp to recover to and then work on our restore operation.
Here we go.

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

Let us now record a timestamp to rollforward to.
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.

$ db2 "select current timestamp from sysibm.sysdummy1 with ur"
1
--------------------------
2016-03-07-12.53.20.888990
  1 record(s) selected.

Rougue DML Operation

Let us now mess up our data by running a rougue DML, an update in this case.

$ db2 "select count(*) from db2inst1.import_sales where SALES_PERSON = 'LEE'"
1
-----------
        190
  1 record(s) selected.

$ db2 "update db2inst1.import_sales set SALES_PERSON = 'BRUCE LEE' where SALES_PERSON = 'LEE'"
DB20000I  The SQL command completed successfully.

$ db2 "select count(*) from db2inst1.import_sales where SALES_PERSON = 'LEE'"
1
-----------
          0
  1 record(s) selected.

$ db2 "select count(*) from db2inst1.import_sales where SALES_PERSON = 'BRUCE LEE'"
1
-----------
        190
  1 record(s) selected.

Ah! Now, that we realized that somebody (I know we did it! but ssshhh..) messed up data, as DBAs we will help them recover from it.
We will first restore from a backup that was taken as the first step in this blog post and then rollforward to the timestamp we recorded just before rouge DML was run.
If we do this right, we shouldn’t have BRUCE LEEs in our data. We should only see LEEs.

RESTORING DATABASE

 $ db2 "restore database sample taken at 20160307124324"
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

ROLLFORWARD DATABASE aka APPLYING LOGS

Let us now rollforward to our timestamp that we recorded before DML was run.
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-07-12.53.20.888990 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               = S0000051.LOG
 Log files processed                    = S0000049.LOG - S0000050.LOG
 Last committed transaction             = 2016-03-07-12.51.00.000000 Local

We shouldn’t be able to connect to the database as it would be in rollforward pending mode. This is because we haven’t told DB2 that we don’t have any intention of rolling forward more log files.

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

Now, let us tell DB2 that it is OK to bring the database to ‘normal’ mode by appending ‘stop’ to the ‘rollforward’ command.

 $ 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                    = S0000049.LOG - S0000051.LOG
 Last committed transaction             = 2016-03-07-12.51.00.000000 Local
DB20000I  The ROLLFORWARD command completed successfully.

Data Checks after recovery

Let us now check to see if we any more ‘BRUCE LEEs’ in our data.
If restore and rollforward worked the way we expected it to, we shouldn’t see any more ‘BRUCE LEEs’

 $ 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.import_sales where SALES_PERSON = 'BRUCE LEE'"
1
-----------
          0
  1 record(s) selected.

$ db2 "select count(*) from db2inst1.import_sales where SALES_PERSON = 'LEE'"
1
-----------
        190
1 record(s) selected.

Yey! No more BRUCE LEEs. That is good. We only want LEEs.

In this blog post, we have done the following:
1) Discussed ‘import’ command’s use to quickly empty a table.
2) Recovered data to a point-in-time after emptying a table using import from /dev/null.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s