Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Unanswered: Inaccessible DB2 after restore from V8

    Hi all,

    I have to restore old DB2 data. Unfortunately I'm not a DB2 administrator and I'm facing a strange problem.

    Scenario:
    I have DB2 version 9.7 and six backups (= six individual files). Two of them were taken from DB2 version 9.5, the others from DB2 version 8.? (I don't know the exact version). Operating system for both version is Windows Server 2003. I used a simple RESTORE command like this:

    RESTORE DATABASE <db name from the backup> FROM <full path to backup files> INTO <new db name>

    Two databases (V 9.5) have been restored succesfully and they work perfectly. But there is something wrong with the rest of them (V 8.?). The restore command ends up succesfully with no errors but database is inaccessible:

    db2 => connect to rc_ov user <user> using <pwd>
    SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "Scan interface error".) SQLSTATE=58004

    Having used the Control Center I found two problems:
    1. No user has been granted privileges - right click the db name => Privileges, windows is empty.
    2. (I guess) no storage - right click the db name -> Storage administration => Display storage, shows java.lang.NullPointerException

    I have been searching the Internet for over a week but no success with my insane attempts. I wonder if someone could help.

    Many thanks in advance.
    Last edited by edaben; 09-16-10 at 05:21.

  2. #2
    Join Date
    Sep 2010
    Location
    poland
    Posts
    7
    try to use restore data wizard from control center

  3. #3
    Join Date
    Sep 2010
    Posts
    6
    Hi iksior,

    thanks for your reply. I have already tried many things, among others the Restore Wizard with options it provides. Result has always been the same.

    Regards

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do you know anything about the V8.x backups? Are the online or offline backups?

    Andy

  5. #5
    Join Date
    Sep 2010
    Posts
    6
    Hi Andy,

    thanks for your reply. No, I know nothing. BTW, is there any way how to get information from the backup file itself ? E.g. type, tablespaces, ... ?

    Regards,
    Eda

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by edaben View Post
    Hi Andy,

    thanks for your reply. No, I know nothing. BTW, is there any way how to get information from the backup file itself ? E.g. type, tablespaces, ... ?

    Regards,
    Eda
    Look here: IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    Andy

  7. #7
    Join Date
    Sep 2010
    Posts
    6
    Thanks Andy,

    that's extremely useful utility providing a lot of information. I compared information from the working backup against information from the not-working one - I found nothing special. Names, paths seem to be OK. What information could be useful for you?

    Regards

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I suspect that the V8 backups are online backups. If they are, you cannot restore them into V9. The format of the log files changed between the two versions and therefore you can only restore an offline backup from V8 into V9.

    Andy

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by edaben View Post
    db2 => connect to rc_ov user <user> using <pwd>
    SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "Scan interface error".) SQLSTATE=58004
    connect should not return sql0901n. db2diag.log will have more information about this failure. I'd suggest opening a PMR.

  10. #10
    Join Date
    Sep 2010
    Posts
    6
    Hi Bella,

    thank you for your reply. I went through the file db2diag.log. There are messages like this:

    The database detected in the backup image requires that this restore be done WITHOUT ROLLING FORWARD. No rollforward will be required following this restore. (I have tried - no success)

    An empty database history has been detected. The database history will be overwritten by the backup image. (I think it's OK)

    There are also the following messages for all table spaces:
    MESSAGE : ADM10502W Health indicator "Table Space Operational State" ("ts.ts_op_status") is in state "Backup in progress" on "table space" "DB2.RC_KA".

    It generated a number of huge dump files as well. The database status is "Accessible". Will it help?

    P.S. Sorry for my stupid question - what is it PMR ?

    Regards,
    Eda

  11. #11
    Join Date
    Sep 2010
    Posts
    6

    Thumbs up

    Hi folks,

    Got it! I'm the happiest bunny all around the world! I have installed DB2 Express V9.7 Fix Pack 3. Starting from Fix Pack 2 a new registry variable has been introduced. It's name is

    DB2_RESTORE_GRANT_ADMIN_AUTHORITIES.

    If DB2_RESTORE_GRANT_ADMIN_AUTHORITIES is set to ON, and you are restoring to a new database, then SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user that issues the restore operation.
    Nevertheless I thank you all very very much for your help because you have directed me to usefull information.

    Regards,
    Eda
    Last edited by edaben; 09-17-10 at 08:52.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by edaben View Post
    what is it PMR ?
    See n_i 's reply on 09-09-10 here:
    http://www.dbforums.com/db2/1660148-...-v9-7-fp2.html

  13. #13
    Join Date
    Jan 2012
    Location
    London, UK
    Posts
    1
    Quote Originally Posted by edaben View Post
    Hi all,

    I have to restore old DB2 data. Unfortunately I'm not a DB2 administrator and I'm facing a strange problem.

    Scenario:
    I have DB2 version 9.7 and six backups (= six individual files). Two of them were taken from DB2 version 9.5, the others from DB2 version 8.? (I don't know the exact version). Operating system for both version is Windows Server 2003. I used a simple RESTORE command like this:

    RESTORE DATABASE <db name from the backup> FROM <full path to backup files> INTO <new db name>

    Two databases (V 9.5) have been restored succesfully and they work perfectly. But there is something wrong with the rest of them (V 8.?). The restore command ends up succesfully with no errors but database is inaccessible:

    db2 => connect to rc_ov user <user> using <pwd>
    SQL0901N The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed. (Reason "Scan interface error".) SQLSTATE=58004

    Having used the Control Center I found two problems:
    1. No user has been granted privileges - right click the db name => Privileges, windows is empty.
    2. (I guess) no storage - right click the db name -> Storage administration => Display storage, shows java.lang.NullPointerException
    [/B]
    I have been searching the Internet for over a week but no success with my insane attempts. I wonder if someone could help.

    Many thanks in advance.

    I had the same issue when taking a restore from a 9.7.5 image and restoring it into a new db2 instance on another server, which was initially at 9.7.0.

    The restore completed successfully but when I tried to connect to the db I got the above error.

    However after upgrading the new instance to FP5 and then connecting I was able to connect with no probs!

    Best to check the fixpack levels first!

    Eug

  14. #14
    Join Date
    Jun 2011
    Posts
    1

    db restore using db v9.5

    hi experts!
    can u help me how to upgrade to v9.7? is it free?
    i am also having trouble restoring my database in a clone server, it's running ok but restoring the database to the other server is working i had same sql0901n error.Thanks guys.

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you have IBM support for V9.5 then you can upgrade to 9.7 without cost.

    Can you be more specific on the error you are getting?

    Andy

Posting Permissions

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