Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197

    Unanswered: Db2relocate error

    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?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  3. #3
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by BELLO4KA View Post
    You can create your old-instance id on this server and use that to grant the necessary authority.
    I haven't done this myself so not sure if this would work or not, but you can give it a try.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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

  9. #9
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    sathya,

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

  10. #10
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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....

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by sathyaram_s View Post
    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.

  13. #13
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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]$

  14. #14
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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


    Quote Originally Posted by BELLO4KA View Post
    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.
    Last edited by sathyaram_s; 03-22-11 at 04:56.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  15. #15
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Post your config file

    Quote Originally Posted by alwayssathya View Post
    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]$
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •