Strange error message from DB2 Express C version 9.5.0.0

Harold Pritchett

Strange error message from DB2 Express C version 9.5.0.0
I have two servers running DB2 Express C version 9.5.0.0
on Red Hat Enterprise Linux 5.4.

Each morning a cron job on server 1 does a offline backup
of the database ANIMALS. This backup is then sent to
server 2 where it is restored. The SQL to do this is
as follows:

ATTACH TO db2inst9;

DROP DATABASE ANIMALS;

RESTORE DATABASE ANIMALS
USER db2inst9
using xxxxxxxx
FROM '/db2home/restore1'
REPLACE EXISTING
WITHOUT ROLLING FORWARD
WITHOUT PROMPTING;

CONNECT to animals user db2inst9 using xxxxxxxx;

SELECT count(bhid) from is3.dna;

DETACH;

TERMINATE;


This has been working for months... Today, however, I started
getting this error from the restore step:

RESTORE DATABASE ANIMALS USER db2inst9 using
FROM '/db2home/restore1' REPLACE EXISTING
WITHOUT ROLLING FORWARD WITHOUT PROMPTING

SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "DB Name specified
matches the SPM Name. Please use a different name".) SQLSTATE=58004

I looked up the Reason via google and found this:

http://tinyurl.com/yenzp7q

Since fix packs are not an option with db2 express c and I really don't
want to go to 9.7.0, does anyone have any ideas what to do about this?

I've tried re-booting the machine. It didn't help. The next step I
see is to delete the db2inst9 instance, re-create it, and then reload
all of the databases. If that doesn't work, then the next thing is to
complete remove db2 and re-install it from scratch. Something I really
don't want to do the week before Christmas.

Any other ideas?

Thanks for any help you can provide

Harold
Linux Guru
db2 grasshopper...

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Gregory Palgrave

Re: Strange error message from DB2 Express C version 9.5.0.0
(in response to Harold Pritchett)
Harold,

Not using DB2 Express C here but 9.5 ESE.

Having done far too many restores recently in preparation for a project, try DROP DATABASE ANIMALS and then try the restore again.

I've found that DB2 can get confused occasionally on repeated restores of the same database and/or something in the db directory gets corrupted, making jobs that have worked fine umpteen times go haywire for no obvious reason. DROP and RESTORE has worked for me. YMMV.

Also - are there any other messages in the db2diag.log to give a clue?

Cheers

Greg


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Harold Pritchett
Sent: Wednesday, 16 December 2009 4:47 AM
To: [login to unmask email]
Subject: [DB2-L] Strange error message from DB2 Express C version 9.5.0.0

I have two servers running DB2 Express C version 9.5.0.0
on Red Hat Enterprise Linux 5.4.

Each morning a cron job on server 1 does a offline backup
of the database ANIMALS. This backup is then sent to
server 2 where it is restored. The SQL to do this is
as follows:

ATTACH TO db2inst9;

DROP DATABASE ANIMALS;

RESTORE DATABASE ANIMALS
USER db2inst9
using xxxxxxxx
FROM '/db2home/restore1'
REPLACE EXISTING
WITHOUT ROLLING FORWARD
WITHOUT PROMPTING;

CONNECT to animals user db2inst9 using xxxxxxxx;

SELECT count(bhid) from is3.dna;

DETACH;

TERMINATE;


This has been working for months... Today, however, I started
getting this error from the restore step:

RESTORE DATABASE ANIMALS USER db2inst9 using
FROM '/db2home/restore1' REPLACE EXISTING
WITHOUT ROLLING FORWARD WITHOUT PROMPTING

SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "DB Name specified
matches the SPM Name. Please use a different name".) SQLSTATE=58004

I looked up the Reason via google and found this:

http://tinyurl.com/yenzp7q

Since fix packs are not an option with db2 express c and I really don't
want to go to 9.7.0, does anyone have any ideas what to do about this?

I've tried re-booting the machine. It didn't help. The next step I
see is to delete the db2inst9 instance, re-create it, and then reload
all of the databases. If that doesn't work, then the next thing is to
complete remove db2 and re-install it from scratch. Something I really
don't want to do the week before Christmas.

Any other ideas?

Thanks for any help you can provide

Harold
Linux Guru
db2 grasshopper...

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Harold Pritchett

Re: Strange error message from DB2 Express C version 9.5.0.0
(in response to Gregory Palgrave)
Gregory Palgrave wrote:
> Harold,
>
> Not using DB2 Express C here but 9.5 ESE.
>
> Having done far too many restores recently in preparation for a project, try DROP DATABASE ANIMALS and then try the restore again.
>
> I've found that DB2 can get confused occasionally on repeated restores of the same database and/or something in the db directory gets corrupted, making jobs that have worked fine umpteen times go haywire for no obvious reason. DROP and RESTORE has worked for me. YMMV.
>
> Also - are there any other messages in the db2diag.log to give a clue?

Our db2 guy, Jeff recognized the problem. Here's his response to me about this.

It should be fixed, here is what I did:

db2 list database directory
db2 drop database ANIMALS
db2 list database directory /db2home/db2inst9 (ANIMALS still listed)
db2 uncatalog database animals
db2 restore... as normal

SPM is the DB2 sync point manager for 2 phase commit and someway or another,
the system database directory (list database directory /db2home/db2inst9)
and the local database directory (list database directory) were different
for the animals definition. Basically; one still recognized the animals
name/alias and the other didn't - so nothing would work; delete, drop,
create, restore; nothing. The "uncatalog database animals" command is what
finally clears it up.

Thanks for your input.

Harold

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Leon Katsnelson

Re: Strange error message from DB2 Express C version 9.5.0.0
(in response to Harold Pritchett)

Harold, I am glad you fixed the problem. In your earlier posting you
indicated that you did not want to upgrade to 9.7. I know many people don't
want to go to a new release until there was a FixPack. With DB2 Express-C
we do not have FixPacks but we do update the product periodically whenever
we feel such an update is warranted and would be beneficial to the
community and would increase adoption of the new release. So, we shipped
DB2 Express-C v9.7.1 (i.e. FixPack 1 level) on the same date as FixPack 1
was made available. There is a ton of really good function in 9.7 and I
encourage you and everyone else to try it, especially now that DB2
Express-C is at the FP1 level.

We also made DB2 Express-C available on the Cloud the same day. You can go
to RightScale.com open up a developer account and try one of several DB2
Express-C templates we have there. You will be paying only for the Amazon
cloud time and prices start at $0.085/compute hour and you pay only for the
time you have the server up.

Leon Katsnelson
Program Director, IBM Data Servers


FreeDB2






From: Harold Pritchett <[login to unmask email]>
To: [login to unmask email]
Date: 12/15/2009 11:16 PM.
Subject: Re: [DB2-L] Strange error message from DB2 Express C version
9.5.0.0
Sent by: IDUG DB2-L <[login to unmask email]>



Gregory Palgrave wrote:
> Harold,
>
> Not using DB2 Express C here but 9.5 ESE.
>
> Having done far too many restores recently in preparation for a project,
try DROP DATABASE ANIMALS and then try the restore again.
>
> I've found that DB2 can get confused occasionally on repeated restores of
the same database and/or something in the db directory gets corrupted,
making jobs that have worked fine umpteen times go haywire for no obvious
reason. DROP and RESTORE has worked for me. YMMV.
>
> Also - are there any other messages in the db2diag.log to give a clue?

Our db2 guy, Jeff recognized the problem. Here's his response to me about
this.

It should be fixed, here is what I did:

db2 list database directory
db2 drop database ANIMALS
db2 list database directory /db2home/db2inst9 (ANIMALS still listed)
db2 uncatalog database animals
db2 restore... as normal

SPM is the DB2 sync point manager for 2 phase commit and someway or
another,
the system database directory (list database directory /db2home/db2inst9)
and the local database directory (list database directory) were different
for the animals definition. Basically; one still recognized the animals
name/alias and the other didn't - so nothing would work; delete, drop,
create, restore; nothing. The "uncatalog database animals" command is what
finally clears it up.

Thanks for your input.

Harold

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Harold Pritchett

Re: Strange error message from DB2 Express C version 9.5.0.0
(in response to Leon Katsnelson)
Leon Katsnelson wrote:
> Harold, I am glad you fixed the problem. In your earlier posting you
> indicated that you did not want to upgrade to 9.7. I know many people
> don't want to go to a new release until there was a FixPack. With DB2
> Express-C we do not have FixPacks but we do update the product
> periodically whenever we feel such an update is warranted and would be
> beneficial to the community and would increase adoption of the new
> release. So, we shipped DB2 Express-C v9.7.1 (i.e. FixPack 1 level) on
> the same date as FixPack 1 was made available. There is a ton of really
> good function in 9.7 and I encourage you and everyone else to try it,
> especially now that DB2 Express-C is at the FP1 level.

Leon,

Thanks for your note. My concern was not upgrading to 9.7, but having
to do it on short notice right before the holidays. I'm planning to
put 9.7.1 up right after the new year begins in a test environment and with
a few weeks testing, hopefully we can make it our production version.

Thanks again

Harold

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L