redirected restore using offline backup, and related access problem on target instance

I have two instances-db2inst1 and db2inst2

In db2inst1, I created a db SAMPLE, and took it’s offline backup in a shared director /backups

Now I login in to other instance on another server, and issue below restore command,

db2inst2@db2v10:~> db2 restore db sample from /backups/ on /home/db2inst2/ without rolling forward

SQL2539W  Warning!  Restoring to an existing database that is the same as the

backup image database.  The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

Now I will try to access a table TESTTB,

db2inst2@db2v10:~> db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0

 SQL authorization ID   = DB2INST2

 Local database alias   = SAMPLE

db2inst2@db2v10:~> db2 "select count(*) from DB2INST1.TESTTB"

SQL0551N  "DB2INST2" does not have the required authorization or privilege to

perform operation "SELECT" on object "DB2INST1.TESTTB".  SQLSTATE=42501

If we grant db2inst2 select privilege at db2inst1, it will not get reflected on other server where db2inst2 is located. If we try to issue grant on the server where db2inst2 is located, we will get error,

db2inst2@db2v10:~> db2 "grant select on DB2INST1.TESTTB to user db2inst2"

DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL0551N  "DB2INST2" does not have the required authorization or privilege to

perform operation "GRANT" on object "DB2INST1.TESTTB".  SQLSTATE=42501

to get the grant from user db2inst1 on it’s objects, user db2inst1 should be created at server where db2inst2 is located. After that grants can be issued.

Another alternative is to run db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON, and then recyle db2 server. Followed by restore.  Observe below steps,

db2inst2@db2v10:~> db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON

db2inst2@db2v10:~> db2 force application all

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

db2inst2@db2v10:~> db2 terminate

DB20000I  The TERMINATE command completed successfully.

db2inst2@db2v10:~> db2stop

SQL1064N  DB2STOP processing was successful.

db2inst2@db2v10:~> db2start

SQL1063N  DB2START processing was successful.

db2inst2@db2v10:~> db2 restore db sample from /backups/ on /home/db2inst2/ without rolling forward

SQL2539W  Warning!  Restoring to an existing database that is the same as the

backup image database.  The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

db2inst2@db2v10:~> db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0

 SQL authorization ID   = DB2INST2

 Local database alias   = SAMPLE

db2inst2@db2v10:~> db2 "select count(*) from DB2INST1.TESTTB"

1

-----------

     336336

  1 record(s) selected.