Let me tell you the issue here. I have created a new instance and wanted to move 1 of the DB from old-instance to new instance.

It was successful, but after which when I list for tables on the old-instance schema, I gave the result, but when I try to select the table.
it threw an error.

select * from db2inst1.a
SQL0551N "DBI" does not have the required authorization or privilege to
perform operation "SELECT" on object "DB2INST1.A". SQLSTATE=42501

Ideally im logging with new instance owner and why I don have the privilege... how to overcome this?

Check this URL if you're on v9.7:
IBM DB2 9.7 for Linux, UNIX and Windows Information Center

In v9.7, your new instance doesn't get DBADM for newly created (restored from v9.7) databases.

Bello, thanks for the reply,

But if that is the case who will have the admin access, and how will i overcome it ??

pl. suggest

You can create your old-instance id on this server and use that to grant the necessary authority. Also, check the "User response" section in the link.


I'm in a similar situation (restored v9.7 db from another server).

$ db2 get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = NO
Direct BINDADD authority = NO
Direct CONNECT authority = NO
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO
Direct QUIESCE_CONNECT authority = NO
Direct CREATE_EXTERNAL_ROUTINE authority = NO
Direct SYSMON authority = NO

Indirect SYSADM authority = YES
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = YES
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = YES
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO


$ db2 "select count(*) from inst461.stock"
SQL0551N "xxxxxx" does not have the required authorization or privilege to
perform operation "SELECT" on object "INST461.STOCK". SQLSTATE=42501

Just to clarify: if you create a new db (create db command) using your instance id, then you get DBADM; but not if you create it via a restore from another v9.7 instance.

I haven't done this myself so not sure if this would work or not, but you can give it a try.

Instead of creating a user on the target, I set the AUTHENTICATIION to CLIENT on the new instance.
Then catalog the new instance and db on the old instance - connect from there and grant authorization.

A simpler approach, but you do not want to do this is the 'old' instance is in a controlled environment - say production

HTH

bello,

here im going to this activity in the same server
in that case how do i go ahead and grant the permission,

i have
user A owner of instance A,
user B owner of instance B.

But how do i grant user B the Admin privilage

you want me to login to A instance and give the privilage
or login to B instance and connect to DB with A user privilage ...


may be i confused pl. clear me

sathya,

thanks firstly, But can you elaborate step wise , as i don get what you want me to perform.

hey guys it worked this way.

i logged into the db in new instance with old instance owner id , and granted the admin privilage to new instance owner.

as of now it is working....

If you are on FP2+, then you can also use

DB2_RESTORE_GRANT_ADMIN_AUTHORITIES

This will work for RESTORE command or db2inidb command.
If you are using unix cp, and relocate then this is not the option

In HADR environment, do not use this registry to restore the standby. There is a known open APAR.

I set this registry variable, restarted the instance, dropped the db and restored it again. This is after the restore with this registry variable set:


$ db2set -all
[i] DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
[i] DB2_ATS_ENABLE=YES
[i] DB2_PINNED_BP=YES
[i] DB2_FCM_SETTINGS=FCM_MAXIMIZE_SET_SIZE:NO
[i] DB2COMM=tcpip


$ db2 get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = NO
Direct BINDADD authority = NO
Direct CONNECT authority = NO
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO
Direct QUIESCE_CONNECT authority = NO
Direct CREATE_EXTERNAL_ROUTINE authority = NO
Direct SYSMON authority = NO

Indirect SYSADM authority = YES
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = YES
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = YES
Indirect LOAD authority = NO
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = NO


It looks the same as before setting this variable and I can't access the tables created with the source-schema name. The manual seems to indicate that this setting is not supported with a full db restore... not sure why??


I don't have access to the source system so I think my only option is create the source-instance ID and use it to do the grants.

Now i have a new error

when im trying to relocate to a new instance it throws this error
how to overcome


[dbowner@WESSSTDW01 opt]$ db2relocatedb -f /home/db2inst1/rloc.cfg
DBT1025N Neither old nor new database name were found in the database directory.
[dbowner@WESSSTDW01 opt]$

Bella, Thanks for pointing out ..

Yes, as per the doc, it does not appear to support full db restores.

I have used this parameter with ACS restores only ..

Didn't RTFM properly ;-( .


But, pl note, this APAR

https://www-304.ibm.com/support/docv...id=swg1IC71864

is valid for both normal RESTOREs and ACS/split mirror restores.

Cheers
Sathyaram

Post your config file