db2Dean  a.k.a. Dean Compher a.k.a “The Dean of DB2” is here to answer your DB2 Questions !

 

Utah Events

None Currently Scheduled

 

Nevada Events

Oct 2017:  World of Watson Conference

 

Search db2Dean

Just for Grins Watch:

DB2 Infomercial by db2Dean  

 

Add your insights to the:

db2Dean and Friends

 

Get the Free Data Studio! 

Get DB2 Express-C Here!

ftware: Microsoft Office

dean@db2Dean.com

 

ttp://twitter-badges.s3.amazonaws.com/twitter-a.png

 

ttp://www.linkedin.com/img/webpromo/btn_profile_bluetxt_80x15.png

 

 

Online Events

Technical Conference Calls

 

Other Events

None Currently Scheduled

db2Dean Discusses Big Data 

   

 

Welcome to db2Dean’s web site.  I’m Dean Compher an IBM IT Specialist who, along with my team, helps customers and prospective customers with DB2 on Linux, UNIX and Windows (LUW) technical questions and issues.  As this page makes painfully clear, I am a DBA and not a web designer, but I would be happy to get your DB2 questions answered or talk to you about the great features of DB2 or IBM Integration Products.  If you are looking at a new database solution or want to compare us to your existing database vendor, please do not hesitate to contact me about getting a presentation or just to ask questions.  My e-mail address is dean@db2Dean.com

 

I am located in Utah and primarily serve DB2/LUW and dashDB customers in the Western US, but I can forward requests to my peers in other technology and geographic areas as well.  My team also covers Big Data and Informix and products.  There are questions that I get on a regular basis, and I will write articles relating to them here.  I hope that you find them useful.  I also welcome suggestions for future content.  Click here for more information about me.

 

 

Favorite Links

 

Previous

Articles

 

Hot Links

 

Manuals

 

Info Center

 

Informix

 

DB2/zOS

 

DB2 i5/OS

 

DB2 Connect

 

Best Practices

 

 

 

 

 

 

 

 

 

 

 

Redirected PIT Tablespace Restore

Kathryn Ericsson

27 February 2017

 

Procedure to Perform a Redirected Restore to a PIT to Restore Only One Tablespace

 

This document addresses how to restore a DB2 LUW database into another database, to a specific point in time (PIT), and restore only one tablespace out of several from a database-level backup. 

DB2 10.5, Linux operating system.  TSM in place to handle log archiving and database backups.

 

DB Name               Desc

DB_PROD              Production database

DB_COPY               The soon-to-be copy of the production database, with only selected tablespaces, restored to a point in time

 

 

DB_COPY will reside in the same DB2 instance as DB_PROD.

They will both share the same DB2 instance owner (db2iown).

 

DB_COPY database objects (tablespaces, logs, etc) will reside in different directories from DB_PROD.

 

DB_COPY objects will be restored to Jan 31, 2017 at 9am local time.

 

The application tablespaces to be recovered are TSPTAB and TSPIDX.

 

Summary of Steps:

1.     Prep Work

2.     Analyze Existing Backups and Logs

3.     Create the New Copy of the Database

4.     Run the Redirected Restore (RR) Command to Create the Script

5.     Edit the RR script

6.     Execute the Edited RR Script

7.     Post-Restore, Manage Logs

8.     Rollforward the Database

9.     Connect to the Database, and Confirm the Data

 

 

1.    Prep Work:

Ensure space is available for the new objects on a different path.  In this case, the path will be in a filesystem starting with ‘filesystem,’ which is completely different from DB_PROD:

Prepare the path for the database data and logs.  Make directory paths similar to this:

/filesystem/db2iown/DB_COPY/data

/filesystem/db2iown/DB_COPY/logs

 

Obtain config information about the existing DB_PROD.

db2 "get database config for DB_PROD" > DB_DB_PROD_dbcfg.txt

 

Obtain database DDL information about the existing DB_PROD.

db2look -d DB_PROD -createdb  -i db2iown  -w  'p  w here' -o  DB_PROD_ddl.txt

 

 

2.    Analyze Existing Backups and Logs: 

Run this command to obtain TSM info.  See below for non-TSM shops:

db2adutl query full db DB_PROD > bkup_list.save

Database had a full backup on Sunday January 29, 2017 at 23:39

And an incremental on Jan 31, 2017 at around 3am local server time with log S0006674:

 

db2adutl output:

Full:

20170129233958  Oldest log: S0006668.LOG  DB Partition Number: 0

 

Incremental:

    Time: 20170131025950  Oldest log: S0006674.LOG  DB Partition Number: 0    Sessions: 2

 

For shops without TSM, this command can obtain information about backups:

db2 list history backup all for DB_PROD

( Op = ‘B’ackup, Obj = ‘D’atabase, Type ‘N’ (Online) (vs ‘O’=Incremental online), Status ‘A’ctive (vs ‘E’xpired):)

 

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID

 -- --- ------------------ ---- --- ------------ ------------ --------------

  B  D  20170129233958002   N    A  S0006668.LOG S0006669.LOG

 ----------------------------------------------------------------------------

…………

 

Start Time: 20170129233958

   End Time: 20170130002714

     Status: A

 ----------------------------------------------------------------------------

 

 

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID

 -- --- ------------------ ---- --- ------------ ------------ --------------

  B  D  20170131025950002   O    A  S0006674.LOG S0006674.LOG

 ----------------------------------------------------------------------------

…………

 

Start Time: 20170131025950

   End Time: 20170131030458

     Status: A

 ----------------------------------------------------------------------------

 

SHOWTIME!

 

3.    Create the New Copy of the Database:

CREATE DATABASE DB_COPY ON '/filesystem/db2iown/DB_COPY/data'  DBPATH ON  '/home/db2iown/'

 

4.    Run the Redirected Restore Command to Create the Script:

This example will create a script called, “RESTORE_REDIRECT.clp”

Specify the time of the full backup after the “taken at” parm, and the “into” parm is the new database:

db2 "restore db DB_PROD rebuild with tablespace (SYSCATSPACE, TSPTAB, TSPIDX) use tsm taken at 20170129233958  into DB_COPY redirect generate script RESTORE_REDIRECT.clp without prompting"

 

5.    Edit the RR Script (see below):

Two hyphens in column 1 specify a comment.

The only parts you want to change are (enhanced below):

User & Using:

Uncomment and type in the owner and password

Make sure Syscatspace is not commented out

Uncomment the tablespaces you want to restore

Keep the other tablespaces commented out that you do not want to restore

Uncomment the ‘ON’ parm and make sure it is correct

Make sure the ‘INTO’ parm is pointing to your new db (DB_COPY)

Uncomment the ‘LOGTARGET’ parm and make sure it is correct

Keep everything else the same

 

-- *****************************************************************************

-- ** automatically created redirect restore script

-- *****************************************************************************

UPDATE COMMAND OPTIONS USING S ON Z ON DB_PROD_NODE0000.out V ON;

SET CLIENT ATTACH_MEMBER  0;

SET CLIENT CONNECT_MEMBER 0;

-- *****************************************************************************

-- ** automatically created redirect restore script

-- *****************************************************************************

RESTORE DATABASE DB_PROD

 USER  db2iown

 USING ' p w here'

REBUILD WITH TABLESPACE (

  "SYSCATSPACE"

--, "TEMPSPACE1"

--, "OTHERTS1"

--, "OTHERTS2"

, "TSPTAB"

, "TSPIDX"

--, "SYSTOOLSTMPSPACE"

--, "REORGTS1"

--, "USERTEMPSPACE32K"

)

USE TSM

OPEN 1 SESSIONS

-- OPTIONS '<options-string>'

TAKEN AT 20170129233958

 ON '/filesystem/db2iown/DB_COPY/data'

-- DBPATH ON '<target-directory>'

INTO DB_COPY

 LOGTARGET '/filesystem/db2iown/DB_COPY/logs'

-- NEWLOGPATH '/db2iown/DB_PROD/logs/NODE0000/LOGSTREAM0000/'

-- WITH <num-buff> BUFFERS

-- BUFFER <buffer-size>

-- REPLACE HISTORY FILE

-- REPLACE EXISTING

REDIRECT

-- PARALLELISM <n>

-- COMPRLIB '<lib-name>'

-- COMPROPTS '<options-string>'

-- WITHOUT ROLLING FORWARD

WITHOUT PROMPTING

;

-- *****************************************************************************

-- ** storage group definition

-- **   Default storage group ID                 = 0

-- **   Number of storage groups                 = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** Storage group name                         = IBMSTOGROUP

-- **   Storage group ID                         = 0

-- **   Data tag                                 = None

-- *****************************************************************************

-- SET STOGROUP PATHS FOR IBMSTOGROUP

-- ON '/filesystem/db2iown/DB_PROD/data'

-- ;

-- *****************************************************************************

-- ** table space definition

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = SYSCATSPACE

-- **   Tablespace ID                            = 0

-- **   Tablespace Type                          = Database managed space                     

-- **   Tablespace Content Type                  = All permanent data. Regular table space.   

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 4

-- **   Using automatic storage                  = Yes    

-- **   Storage group ID                         = 0

-- **   Source storage group ID                  = -1

-- **   Data tag                                 = None

-- **   Auto-resize enabled                      = Yes    

-- **   Total number of pages                    = 73728

-- **   Number of usable pages                   = 73724

-- **   High water mark (pages)                  = 67224

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = TEMPSPACE1

-- **   Tablespace ID                            = 1

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = System Temporary data                      

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 128

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = SYSTOOLSTMPSPACE

-- **   Tablespace ID                            = 21

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = User Temporary data                        

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 4

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = REORGTS1

-- **   Tablespace ID                            = 22

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = System Temporary data                      

-- **   Tablespace Page size (bytes)             = 4096

-- **   Tablespace Extent size (pages)           = 128

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** Tablespace name                            = USERTEMPSPACE32K

-- **   Tablespace ID                            = 35

-- **   Tablespace Type                          = System managed space                       

-- **   Tablespace Content Type                  = User Temporary data                        

-- **   Tablespace Page size (bytes)             = 32768

-- **   Tablespace Extent size (pages)           = 128

-- **   Using automatic storage                  = Yes    

-- **   Total number of pages                    = 1

-- *****************************************************************************

-- *****************************************************************************

-- ** start redirected restore

-- *****************************************************************************

RESTORE DATABASE DB_PROD CONTINUE;

-- *****************************************************************************

-- ** end of file

-- *****************************************************************************

 

 

6.     Execute the Edited RR Script:

db2 -tvf RESTORE_REDIRECT.clp -z RESTORE_REDIRECTout.TXT

Below is the output for this case.  The warnings are fine:

 

UPDATE COMMAND OPTIONS USING S ON Z ON FNLNK_DB_NODE0000.out V ON

DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

 

SET CLIENT ATTACH_MEMBER  0

DB20000I  The SET CLIENT command completed successfully.

 

SET CLIENT CONNECT_MEMBER 0

DB20000I  The SET CLIENT command completed successfully.

 

RESTORE DATABASE DB_PROD USER  db2iown USING           REBUILD WITH TABLESPACE ( "SYSCATSPACE" , "TSPTAB" , "TSPIDX" ) USE TSM OPEN 1 SESSIONS TAKEN AT 20170129233958 ON '/filesystem' DBPATH ON '/home/db2iown/' INTO DB_COPY LOGTARGET '/filesystem/db2iown/DB_COPY/logs' REPLACE EXISTING REDIRECT WITHOUT PROMPTING

 

SQL1277W  A redirected restore operation is being performed. During a table

space restore, only table spaces being restored can have their paths

reconfigured. During a database restore, storage group storage paths and DMS

table space containers can be reconfigured.

DB20000I  The RESTORE DATABASE command completed successfully.

 

 

RESTORE DATABASE DB_PROD CONTINUE

DB20000I  The RESTORE DATABASE command completed successfully.

 

 

While this was running, prior to the “DB20000I  The RESTORE DATABASE command completed successfully” message, we were monitoring it from another session:

db2 list utilities

 

ID                               = 36700

Type                             = RESTORE

Database Name                    = DB_COPY

Member Number                    = 0

Description                      = automatic db rebuild SYSCATSPACE...

Start Time                       = 01/31/2017 13:20:01.947519

State                            = Executing

Invocation Type                  = User

 

 

7.    Post-Restore, Manage Logs:

 

Restore is done now.  It is important to note that, without spending space and time to explain, by no means should the database be connected to at this point.

 

The log path needs to be set up properly.

Even though we specified it in the RR script:

(remember LOGTARGET '/filesystem/db2iown/DB_COPY/logs'?)

 

The logpath somehow reverted to mirror the DB_PROD:

db2 "get database config for DB_COPY"

Changed path to log files                  (NEWLOGPATH) =

 Path to log files        = /filesystem/DB_PROD/logs/NODE0000/LOGSTREAM0000/

 

So now we have to update the DB config to point to the proper logpath.

 

db2 update  db cfg for DB_COPY using NEWLOGPATH

            /filesystem/db2iown/DB_COPY/logs/

 

Another query to the DB config shows the new DB has the proper log file setup:

db2 "get database config for DB_COPY"

Changed path to log files                  (NEWLOGPATH) =

/filesystem/db2iown/DB_COPY/logs/NODE0000/LOGSTREAM0000/

 Path to log files                                       =

/filesystem/DB_PROD/logs/NODE0000/LOGSTREAM0000/

 

 

When the restore was done, the db was in this state.  Note the “Next Log File to be Read,” and the “Last Committed Transaction:”

 

db2 rollforward database DB_COPY query status

 

                                 Rollforward Status

 

 Input database alias                   = DB_COPY

 Number of members have returned status = 1

 

 Member ID                              = 0

 Rollforward status                     = DB  pending

 Next log file to be read               = S0006668.LOG

 Log files processed                    =  -

 Last committed transaction             = 2017-01-30-08.26.42.000000 UTC

 

 

Now the logs need to be copied to the new database path so that the transactions can be rolled forward.

 

Based on the config of DB_PROD, the logs are in:

 

Changed path to log files                  (NEWLOGPATH) =

 Path to log files                                       = /db2iown/DB_PROD/logs/NODE0000/LOGSTREAM0000/

 

The new DB_COPY is expecting logs in:

/filesystem/db2iown/DB_COPY/logs/NODE0000/LOGSTREAM0000/

 

Log files have to be manually copied from the DB_PROD log path to the DB_COPY path.

We know we need at least S0006668.LOG through S0006674.LOG files, because the “next log file to be read” on the query status shows 6668, and the db2adutl output for the incremental at 3am shows 6674.

 

Therefore, we are going to copy log files from S0006668.LOG through S0006674.LOG.

Based on the timestamps in the DB_PROD log directory, we are also going to copy the next few logs up to and including the point in time we want to restore to (S0006675 and S0006676.  Maybe even S0006677.  Active logs are hard to pinpoint.  Apparently, db2logsForRfwd only shows DB2TSCHG.HIS info for archived logs, not active). 

 

If the logs have been archived, then they need to be retrieved from archives, and copied into the DB_COPY path.

 

8.    Rollforward the Database:

 

Note the value in the “db2 rollforward database DB_COPY query status” command of “Last Committed Transaction:”

Last committed transaction             = 2017-01-30-08.26.42.000000 UTC

 

It is showing UTC time, but our server is on PST time.  So DB2 has restored this database to 1/30/2017 8:26am UTC time, which is the same as 1/29/2017 23:00 PST, when the full backup was taken.

 

To determine the difference from UTC to local server time, issue this SQL:

 

Local server time:

select current timestamp from sysibm.sysdummy1

2017-02-07-09.25.58.727803

 

UTC time:

select current timestamp - current timezone from sysibm.sysdummy1

2017-02-07-17.26.12.939567

 

To determine the difference from UTC to local server time, issue this SQL:

select hour((current timestamp - current timezone) - (current timestamp)) from sysibm.sysdummy1

          8

 

Based on the above, UTC is eight hours ahead of current local server time (PST).

 

The rollforward command uses UTC time by default.  We want to roll forward to Jan 31, 2017 9am local time. 

Issue the rollforward command with the “USING LOCAL TIME” specification to avoid confusion:

db2 rollforward database DB_COPY to 2017-01-31-09.00.00.000000 USING LOCAL TIME

 

While the Rollforward is running, you can check on its progress from another session:

db2 list utilities

 

ID                               = 37180

Type                             = ROLLFORWARD RECOVERY

Database Name                    = DB_COPY

Member Number                    = 0

Description                      = Database Rollforward Recovery

Start Time                       = 01/31/2017 14:29:00.724454

State                            = Executing

Invocation Type                  = User

 

 

After the Rollforward has completed, check the progress of the Rollforward.   You can see the “Last Committed Transaction” that was applied was at 16:59 UTC, which is around 9am local time.  This means we have rolled forward through the logs to the desired point in time:

db2 rollforward database DB_COPY query status

 

 

                                 Rollforward Status

 

 Input database alias                   = DB_COPY

 Number of members have returned status = 1

 

 Member ID                              = 0

 Rollforward status                     = DB  working

 Next log file to be read               = S0006677.LOG

 Log files processed                    = S0006668.LOG - S0006676.LOG

 Last committed transaction             = 2017-01-31-16.59.08.000000 UTC

 

 

Complete the Rollforward process.  The warning message is OK:

db2 rollforward database DB_COPY stop

SQL1271W  Database "DB_COPY" is recovered but one or more table spaces are

offline on members or nodes "0".

 

9.     Connect to the Database, and Confirm the Data.

Congratulations!  You have restored selected tablespaces to a copy of a database to a point in time.

Happy Recovery!

 

Top | Search

 

 

Db2Dean’s

Other  Interests

 

Real Estate Investing

 

Quail at my Bird Feeder

 

Mary Lou’s Stained Glass

 

2B Hawaii Condo Rental

 

1B Hawaii Condo Rental

 

Ski Condo Rental

 

My Googlegängers

 

Fortune Cookie