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.

try to use restore data wizard from control center

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

Do you know anything about the V8.x backups? Are the online or offline backups?

Andy

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

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

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

connect should not return sql0901n. db2diag.log will have more information about this failure. I'd suggest opening a PMR.

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

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.

Nevertheless I thank you all very very much for your help because you have directed me to usefull information.

Regards,
Eda

See n_i 's reply on 09-09-10 here:
SQL4401C error on v9.7 fp2

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

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.

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