I encountered error SQL0902C when migrating database from DB2 version 9.1 to DB2 9.7. I am using DB2 9.7.0.441 Enterprise edition on Windows 2008 R2 Enterprise 64-bit.

db2 restore db ic2thu from 'f:\backups'
SQL0902C A system error (reason code = "SYSDATAPARTITIONS Migration DMS rc=8404007e: Open") occurred. Subsequent SQL statements cannot be processed.
SQLSTATE=58005

I'd suggest to open a pmr to address this issue. APAR IC62537 looks a bit similar and support should be able to investigate further.

Thanks. Have already opened PMR with IBM. Will take a look at IC62537

What was the fix in the end? I am currently experiencing the same issue.


...Waiting on a phone call back from IBM.

IBM said this was due to APAR IZ52987 - MIGRATION MAY FAIL DUE TO CATALOG TABLE INDEXES MARKED AS BAD. However, I had the same error after applied the fix. Still waiting for them to follow up. I have since downgraded to v9.5.

You can ask them for the fix. It may work for you.

Out of curiousity, what fix did they send you???

The IBM guy just sent me a stored procedure to run to get the indexes "fixed."

-I did a redirected restore of the DB in another instance on the same server.
-Ran his stored procedure
-took a new backup image
-tried the restore again....
-epic fail....

Sooo frustrated...

I received SQL1224N when I tried to upgrade a small 9.1 db to 9.7. This was after a successful in-place (Work with Existing) upgrade of 9.1 executables and single instance upgrade. The TOOLSDB, also in the instance that auto-upgraded to 9.7, was successfully upgraded via the DATABASE UPGRADE command, but the small app database failed. Further analysis, primarily in
DB2DIAG.log, indicated possible catalog index issues, with SYSDATAPARTITIONs mentioned right before Severe errors. APAR IC62537 looked like the culprit. No problem, right, as this was quickly fixed in 9.1 under APAR IZ52987 (but wait...why is it still poppin up in 9.7 if it was known and fixed??). So I called DB2 Support.



2-3 very long days later, after 4 (5?) different Advanced Support dudes from all over the globe (and apparently beyond), after a "Special Fix" (persistent error from hell: "the system administrator has set policies that prevent this installation". Wait, I'm the admin and aint no such policy!) and a "HotFix" from Germany that did nothing at all, apparently, as DB2DIAG had same errors, and an epic, useless tromp through Windows registry, User/Group settings, Group Policies, etc etc....after all that...still the same dumb errors. Not even a different error message, ferchissakes.

Back to v9.1 for me, as project schedule has been trashed and mgmt is expecting something for all the free time I put in . I'd stick with 8.2.7 if I could; or switch to Oracle. This experience has burned me up, mostly because I was really looking forward to finally getting some advanced features in DB2 LUW (SQL PL built-in functions like UTL_FILE, DBMS_SQL etc , compression enhanced, less ALTOBJ usage, oh yea!).

What the heck happened? Was it some dumb thing I missed? I read every darn note, instruction, procedure to the letter, and I've been doing this stuff a loong time. Is this a case of V9.7 just being pushed out without proper testing? Sure seems like it. Poor/misleading DB2 Support? I dunno. All I know is I got a simple upgrade that failed with an apparently simple error and did not get resolved in 3 loooong days. DBA God, speak to me!

Unfortunatly this was a HUGE headache for me as well. The error that I received is exactly like the one you got (bad index with sysdatapartitions). IBM sent me a fix to rebuild the index and try migrating again... epic fail, didn't work.

Finally, a special build was sent to me and it actually worked! What happens is during migration the indexes are marked as invaild and rebuilt after migration. The bug here is that it marked the catalogue table indexes invalid but then it needed them for the migration. Hence, the migration failed. The special build that was sent to me is going to be in FP1.

There is a work around without the special build. What we were forced to do is do is as follows:
1) Get a full db2look on the DB, backup db cfg, Bufferpool info, and db creation info, TB space info, and all the tables.
2) Run "db2move export" on the entire DB to export all the data.
3) Drop the db
4) Untag the containers (only needed if you are using raw devices)
5) Recreate the db using db creation info in the 9.7 instance.
6) Create the tablespaces
7) Create the tables
8) Run "db2move load" to load the data back into the tables
9) Set the cfg back to how we want it.

Unfortunatly we also hit a few other APARs a few weeks after migrating and decided 9.7 GA was scary and needed a lot of FP1 which was not going to be released until the end of november. We then decided to downgrade to 9.5. It is important to point out this whole process had us starting at 9.1. We now had a huge problem because we had been on 9.7 for 3 weeks and you can't restore a 9.7 to 9.5. Essentially we had to do the above steps where we did a db2move export and recreated the dbs in a 9.5 instance. What a pain this whole process has turned out to be..... On the positive note i sure have learned alot.

Thanks for the info and for the work-around suggestion. It's good to know that the problems I encountered aren't limited to my databases only.

I actually did manually build a 9.7 DEV database from db2look/db2move (lots of DDL script hacking and re-sequencing needed) that development group is successfully using; however that technique doesn't address 9.7 inherent vulnerabilities, plus it isn't the recommended/approved upgrade procedure so I can't confidently switch to using it (for QA, STG, then PROD databases).

I was interested to hear that you eventually ran into other APARs. We need to decide whether to push ahead with 9.7 or revert back to 9.1 (too late in the game to get/approve 9.5) and your experiences make me lean towards reverting back. Which pains me, cause as a DBA with lots of SQL PL procs and db changes, I really wanted those 9.7 enhancements.

Bummer all around.

Thanks !

All the issues that I ran into are going to be released in FP1 which last i heard was going to be released in 11/22 ish. If you can wait another week then maybe that is an option. I also really wanted some of the 9.7 options. The biggest one we wanted was the ability to read from a HADR DB. We backed off 9.7 once we found out that this functionality is not going to be out until a later fixpack.

fail..

Well finally got 9.7 in on a cloned db. IBM sent a db2cleancat tool (doesn't clean..just displays but that's good enough at this point) that showed a single orphan row in SYSCAT.ROUTINEAUTHS in the failing DEV db. The entry has no SPECIFICNAME (null) so I couldn't revoke it, but in checking all other dbs I find none had the orphan, just DEV.

So I cloned the QA db as 8.2, upgraded to 9.1 without problems, then ran the new db2ckupgrade.exe IBM also sent (this is from upcoming FP1). That ran successfully, so I was able to upgrade to 9.7 without errors. The original db2ckupgrade, when run against 8.2 dbs, errors out AND flips the DBM CFG parm "Is Database Consistent" to 'NO'.

This Saturday I'll repeat with the actual QA db, per royal mgmt mandate.
I guess I'll play it safe and upgrade to 9.1 then 9.7 , but I'm sure tempted to try 8.7 to 9.7 just like the freakin doc says should be able to.

It better work again or I'm joining the Foreign Legion.

Cheers.

Installed FP1 for 9.7...much better. Test of direct upgrade from 8.2.7 to 9.7.1
works, so that's good.

Installed HADR and tried out the read capability on the standby: works so far!
A big improvement there.

Reading this thread just made me more confused to

- How to recover the database file of DB2 9.1 in DB2 9.7 Fix Pack 2 . I tried to migrate , but it gave me Migration error : SQL 1224 N

Ny1 got suggestion 2 me 2 how to sucessfully recovr data from backup file .

I m using : Window XP

Ny help is appreciated ....

I'd suggest 2 call IBM 2 open a PMR