-904 error on alter index

Mike Lawrence

-904 error on alter index

January 3, 2001 12:43 PM

Happy New Year DB2ers!

We are preparing to go to DB2 v6 on OS390 from V5.1. I am in
process of cleaning up all type 1 indexes and have run into a
snag.
I have 4 indexes that will not convert...
SELECT *
FROM SYSIBM.SYSINDEXES
WHERE INDEXTYPE <> '2';
---------+---------+---------+---------+--
NAME CREATOR TBNAME
---------+---------+---------+---------+--
DSNDXX01 SYSIBM SYSINDEXES
DSNKDX02 SYSIBM SYSPACKDEP
DSNPPH01 SYSIBM SYSPLAN
DSNGGX01 SYSIBM SYSPLANDEP
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

I have used the same as above on hundreds of other indexes with no
problems (I am recovering the index after the alter)

00C900C6
(
Explanation: DB2 detected an uncommitted write on an index being
altered
to change the type during the same commit scope. This is not
allowed. +
Commit or rollback all changes to an index before alter index
convert type
is used to change the index type.
(
System Action: The alter index convert type of the index is not
allowed.
+
System Programmer Response: The requested operation is not
performed and ¢
SQLCODE -904 is issued. For more information, refer to the SQL code
in +
"Section 2. SQL Return Codes". Commit or rollback changes to the
index and
retry the Alter Index convert type.

I did a display database on these tablespaces and they are all in
RW status with no claims/locks or anybody using them....

I have looked up all the codes and searched the archives with no
results I do not understand what to do about this... I have bounced
DB2 and still get same results... I am stuck. I am pretty green in
DB2 so if the answer is obvious please be kind in telling me I am
stupid.

Thanks in advance
Mike Lawrence

Michael S. Lawrence
Systems Programmer
[login to unmask email]

NOTE: This e-mail message may contain information that may be
privileged,
confidential, and exempt from disclosure. It is intended for use
only by
the person to whom it is addressed. If you have received this
message in
error, please do not forward or use this information in any way,
delete it
immediately, and contact the sender as soon as possible by
telephone at the
telephone number below or by the reply option. Thank you.

Smike Toppins

If memory serves me correctly, you need to run a CATMAINT utility
to convert
the catalog/directory indexes to be TYPE 2 indexes.

SMike Toppins
Great-West Life
[login to unmask email]
(303) 737-5094

> ----------
> From: Mike Lawrence[SMTP:[login to unmask email]
> Reply To: DB2 Data Base Discussion List
> Sent: Wednesday, January 03, 2001 11:43 AM
> To: [login to unmask email]
> Subject: -904 error on alter index
>
> Happy New Year DB2ers!
>
> We are preparing to go to DB2 v6 on OS390 from V5.1. I am in
process of
> cleaning up all type 1 indexes and have run into a snag.
> I have 4 indexes that will not convert...
> SELECT *
> FROM SYSIBM.SYSINDEXES
> WHERE INDEXTYPE <> '2';
> ---------+---------+---------+---------+--
> NAME CREATOR TBNAME
> ---------+---------+---------+---------+--
> DSNDXX01 SYSIBM SYSINDEXES
> DSNKDX02 SYSIBM SYSPACKDEP
> DSNPPH01 SYSIBM SYSPLAN
> DSNGGX01 SYSIBM SYSPLANDEP
> DSNE610I NUMBER OF ROWS DISPLAYED IS 4
>
> But when I execute I get the following error:
>
> ALTER INDEX SYSIBM.DSNDXX01
>
> CONVERT TO TYPE 2
>
> SQL ERROR DURING EXECUTE IMMEDIATE
>
> DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED
BY AN
> UNAVAILABLE RESOURCE. REASON 00C900C6, TYPE OF RESOURCE
00000C01, AND
> RESOURCE NAME SYSIBM.DSNDXX01
> DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
>
> DSNT415I SQLERRP = DSNXIAIX SQL PROCEDURE DETECTING ERROR
>
> DSNT416I SQLERRD = 60 0 0 -1 0 0 SQL DIAGNOSTIC
INFORMATION
>
> DSNT416I SQLERRD = X'0000003C' X'00000000' X'00000000'
X'FFFFFFFF'
> X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
>
>
> I have used the same as above on hundreds of other indexes
with no
> problems (I am recovering the index after the alter)
>
> 00C900C6
>
> (
>
> Explanation: DB2 detected an uncommitted write on an index
being altered
>
> to change the type during the same commit scope. This is not
allowed. +
>
> Commit or rollback all changes to an index before alter index
convert type
>
> is used to change the index type.
>
> (
>
> System Action: The alter index convert type of the index is
not allowed.
>
> +
>
> System Programmer Response: The requested operation is not
performed and
> ¢
> SQLCODE -904 is issued. For more information, refer to the SQL
code in +
>
> "Section 2. SQL Return Codes". Commit or rollback changes to
the index and
>
> retry the Alter Index convert type.
>
>
>
>
> I did a display database on these tablespaces and they are all
in RW
> status with no claims/locks or anybody using them....
>
> I have looked up all the codes and searched the archives with
no results I
> do not understand what to do about this... I have bounced DB2
and still
> get same results... I am stuck. I am pretty green in DB2 so if
the answer
> is obvious please be kind in telling me I am stupid.
>
> Thanks in advance
> Mike Lawrence
>
>
>
> Michael S. Lawrence
> Systems Programmer
> [login to unmask email]
>
>
>
> NOTE: This e-mail message may contain information that may be
privileged,
> confidential, and exempt from disclosure. It is intended for
use only by
> the person to whom it is addressed. If you have received this
message in
> error, please do not forward or use this information in any
way, delete it
> immediately, and contact the sender as soon as possible by
telephone at
> the
> telephone number below or by the reply option. Thank you.
>
>
>
> http://www.ryci.com/db2-l. The
owners of the list can be reached at
> [login to unmask email]
>

Linda Billings

Hi, Mike,
My suspicion is that you are the one who is using the indexes
when
you issue the alter and locking yourself out. CATMAINT should take
care of
that for you when you run it as part of the catalog conversion.

We are preparing to go to DB2 v6 on OS390 from V5.1. I am in
process of
cleaning up all type 1 indexes and have run into a snag.
I have 4 indexes that will not convert...
SELECT *
FROM SYSIBM.SYSINDEXES
WHERE INDEXTYPE <> '2';
---------+---------+---------+---------+--
NAME CREATOR TBNAME
---------+---------+---------+---------+--
DSNDXX01 SYSIBM SYSINDEXES
DSNKDX02 SYSIBM SYSPACKDEP
DSNPPH01 SYSIBM SYSPLAN
DSNGGX01 SYSIBM SYSPLANDEP
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

I have used the same as above on hundreds of other indexes with no
problems
(I am recovering the index after the alter)

00C900C6

(

Explanation: DB2 detected an uncommitted write on an index being
altered

to change the type during the same commit scope. This is not
allowed. +

Commit or rollback all changes to an index before alter index
convert type

is used to change the index type.

(

System Action: The alter index convert type of the index is not
allowed.

+

System Programmer Response: The requested operation is not
performed and ¢

SQLCODE -904 is issued. For more information, refer to the SQL code
in +

"Section 2. SQL Return Codes". Commit or rollback changes to the
index and

retry the Alter Index convert type.

I did a display database on these tablespaces and they are all in
RW status
with no claims/locks or anybody using them....

I have looked up all the codes and searched the archives with no
results I
do not understand what to do about this... I have bounced DB2 and
still get
same results... I am stuck. I am pretty green in DB2 so if the
answer is
obvious please be kind in telling me I am stupid.

Thanks in advance
Mike Lawrence

Michael S. Lawrence
Systems Programmer
[login to unmask email]

NOTE: This e-mail message may contain information that may be
privileged,
confidential, and exempt from disclosure. It is intended for use
only by
the person to whom it is addressed. If you have received this
message in
error, please do not forward or use this information in any way,
delete it
immediately, and contact the sender as soon as possible by
telephone at the
telephone number below or by the reply option. Thank you.

>>> [login to unmask email] 12:55:17 PM Wednesday,
January 03, 2001 >>>
Hi, Mike,
My suspicion is that you are the one who is using the indexes
when
you issue the alter and locking yourself out. CATMAINT should take
care of
that for you when you run it as part of the catalog conversion.

We are preparing to go to DB2 v6 on OS390 from V5.1. I am in
process of
cleaning up all type 1 indexes and have run into a snag.
I have 4 indexes that will not convert...
SELECT *
FROM SYSIBM.SYSINDEXES
WHERE INDEXTYPE <> '2';
---------+---------+---------+---------+--
NAME CREATOR TBNAME
---------+---------+---------+---------+--
DSNDXX01 SYSIBM SYSINDEXES
DSNKDX02 SYSIBM SYSPACKDEP
DSNPPH01 SYSIBM SYSPLAN
DSNGGX01 SYSIBM SYSPLANDEP
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

I have used the same as above on hundreds of other indexes with no
problems
(I am recovering the index after the alter)

00C900C6

(

Explanation: DB2 detected an uncommitted write on an index being
altered

to change the type during the same commit scope. This is not
allowed. +

Commit or rollback all changes to an index before alter index
convert type

is used to change the index type.

(

System Action: The alter index convert type of the index is not
allowed.

+

System Programmer Response: The requested operation is not
performed and ¢

SQLCODE -904 is issued. For more information, refer to the SQL code
in +

"Section 2. SQL Return Codes". Commit or rollback changes to the
index and

retry the Alter Index convert type.

I did a display database on these tablespaces and they are all in
RW status
with no claims/locks or anybody using them....

I have looked up all the codes and searched the archives with no
results I
do not understand what to do about this... I have bounced DB2 and
still get
same results... I am stuck. I am pretty green in DB2 so if the
answer is
obvious please be kind in telling me I am stupid.

Thanks in advance
Mike Lawrence

Michael S. Lawrence
Systems Programmer
[login to unmask email]

NOTE: This e-mail message may contain information that may be
privileged,
confidential, and exempt from disclosure. It is intended for use
only by
the person to whom it is addressed. If you have received this
message in
error, please do not forward or use this information in any way,
delete it
immediately, and contact the sender as soon as possible by
telephone at the
telephone number below or by the reply option. Thank you.

=======================
NOTE: This e-mail message may contain information that may be
privileged,
confidential, and exempt from disclosure. It is intended for use
only by
the person to whom it is addressed. If you have received this
message in
error, please do not forward or use this information in any way,
delete it
immediately, and contact the sender as soon as possible by
telephone at the
telephone number below or by the reply option. Thank you.

Tim Lowe

Linda,
According to my DB2 V5 Installation Guide, you should be able to
use the
catmaint job to convert the indexes to type 2, or to use ALTER
INDEX
....CONVERT TO TYPE 2. (topic 2.7.26)
Is the manual wrong?

Hi, Mike,
My suspicion is that you are the one who is using the indexes
when
you issue the alter and locking yourself out. CATMAINT should take
care of
that for you when you run it as part of the catalog conversion.

We are preparing to go to DB2 v6 on OS390 from V5.1. I am in
process of
cleaning up all type 1 indexes and have run into a snag.
I have 4 indexes that will not convert...
SELECT *
FROM SYSIBM.SYSINDEXES
WHERE INDEXTYPE <> '2';
---------+---------+---------+---------+--
NAME CREATOR TBNAME
---------+---------+---------+---------+--
DSNDXX01 SYSIBM SYSINDEXES
DSNKDX02 SYSIBM SYSPACKDEP
DSNPPH01 SYSIBM SYSPLAN
DSNGGX01 SYSIBM SYSPLANDEP
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

I have used the same as above on hundreds of other indexes with no
problems
(I am recovering the index after the alter)

00C900C6

(

Explanation: DB2 detected an uncommitted write on an index being
altered

to change the type during the same commit scope. This is not
allowed. +

Commit or rollback all changes to an index before alter index
convert type

is used to change the index type.

(

System Action: The alter index convert type of the index is not
allowed.

+

System Programmer Response: The requested operation is not
performed and
¢

SQLCODE -904 is issued. For more information, refer to the SQL code
in +

"Section 2. SQL Return Codes". Commit or rollback changes to the
index and

retry the Alter Index convert type.

I did a display database on these tablespaces and they are all in
RW status
with no claims/locks or anybody using them....

I have looked up all the codes and searched the archives with no
results I
do not understand what to do about this... I have bounced DB2 and
still get
same results... I am stuck. I am pretty green in DB2 so if the
answer is
obvious please be kind in telling me I am stupid.

Thanks in advance
Mike Lawrence

Michael S. Lawrence
Systems Programmer
[login to unmask email]

NOTE: This e-mail message may contain information that may be
privileged,
confidential, and exempt from disclosure. It is intended for use
only by
the person to whom it is addressed. If you have received this
message in
error, please do not forward or use this information in any way,
delete it
immediately, and contact the sender as soon as possible by
telephone at the
telephone number below or by the reply option. Thank you.

Linda Billings

You are correct, Tim. The V5 Installation Guide does say that. I
don't
have an answer for that except that it doesn't make sense to me
that you can
alter an index when the process may be using the very same index
that you
are trying to alter. If Mike is using SPUFI and the dynamic bind
that SPUFI
performs when it executes the SQL decides to use those indexes it
will take
a lock on those indexes. It is possible that those tables needed to
be
updated because of the alter. Plans and packages may have become
invalid
because of the alter so information in those tables would have to
be changed
as well. If anyone has any other ideas, please contribute.

Linda,
According to my DB2 V5 Installation Guide, you should be able to
use the
catmaint job to convert the indexes to type 2, or to use ALTER
INDEX
....CONVERT TO TYPE 2. (topic 2.7.26)
Is the manual wrong?

Hi, Mike,
My suspicion is that you are the one who is using the indexes
when
you issue the alter and locking yourself out. CATMAINT should take
care of
that for you when you run it as part of the catalog conversion.

We are preparing to go to DB2 v6 on OS390 from V5.1. I am in
process of
cleaning up all type 1 indexes and have run into a snag.
I have 4 indexes that will not convert...
SELECT *
FROM SYSIBM.SYSINDEXES
WHERE INDEXTYPE <> '2';
---------+---------+---------+---------+--
NAME CREATOR TBNAME
---------+---------+---------+---------+--
DSNDXX01 SYSIBM SYSINDEXES
DSNKDX02 SYSIBM SYSPACKDEP
DSNPPH01 SYSIBM SYSPLAN
DSNGGX01 SYSIBM SYSPLANDEP
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

I have used the same as above on hundreds of other indexes with no
problems
(I am recovering the index after the alter)

00C900C6

(

Explanation: DB2 detected an uncommitted write on an index being
altered

to change the type during the same commit scope. This is not
allowed. +

Commit or rollback all changes to an index before alter index
convert type

is used to change the index type.

(

System Action: The alter index convert type of the index is not
allowed.

+

System Programmer Response: The requested operation is not
performed and
¢

SQLCODE -904 is issued. For more information, refer to the SQL code
in +

"Section 2. SQL Return Codes". Commit or rollback changes to the
index and

retry the Alter Index convert type.

I did a display database on these tablespaces and they are all in
RW status
with no claims/locks or anybody using them....

I have looked up all the codes and searched the archives with no
results I
do not understand what to do about this... I have bounced DB2 and
still get
same results... I am stuck. I am pretty green in DB2 so if the
answer is
obvious please be kind in telling me I am stupid.

Thanks in advance
Mike Lawrence

Michael S. Lawrence
Systems Programmer
[login to unmask email]

NOTE: This e-mail message may contain information that may be
privileged,
confidential, and exempt from disclosure. It is intended for use
only by
the person to whom it is addressed. If you have received this
message in
error, please do not forward or use this information in any way,
delete it
immediately, and contact the sender as soon as possible by
telephone at the
telephone number below or by the reply option. Thank you.

Linda Billings

Yes, the message does look very similar to the one that you got
for
your problem but there are two differences. First, Mike got a
reason code,
00C900C6, that indicates a very different reason for the
resource
unavailable. Also, Mike was only altering the index. You had
already
dropped your index and was in the process of reloading all the
keys. Mike
hadn't gotten that far yet. It is very important to check out all
reason
codes and SQLSTATE codes when reading an error message.

Tim Lowe

Linda,
I don't understand. Now, I have even more questions.
In order to accomplish the ALTER INDEX ..CONVERT TO TYPE 2 command,
DB2
would have to update the row in sysindexes, but the indexes on
sysindex
would not need to be changed. (unless the update process deletes
and
readds each of the associated index records??)
The dynamic bind and any locks on this index would not seem to
change the
index.
Invalidating plans and packages do not appear to have anything to
do with
updating indexes on sysindex.
Therefore, this would not appear to me to create an uncommitted
write to
the index that is being altered, and would not create this problem
(as
described in PQ09837, where this reason code was created).

Am I missing something?
Doesn't catmaint simply issue ALTER INDEX and REBUILD INDEX
statements for
each catalog index?
Could there be something else involved? (After each index was
altered, was
it rebuilt before going on to the alter of the next index?)
Has ANYONE else ever altered their catalog indexes to type 2
without using
catmaint?
Has PQ09837 made it impossible to do this, making the manual
incorrect?
Or, was the manual always wrong?

Thanks,
Tim

Wim Ruarus

what I don't understand is why you would bother altering and
recovering each
catalog index, when you have CATMAINT to take care of all.
In my opinion Linda is right when she says that you can't alter an
index
when the process you are running uses the same index you are trying
to
alter.
When DB2 wants to update a row in SYSINDEXES, it needs the index to
access
that row.

I for myself don't like to mess around in the catalog, so if IBM
provides a
utility to take care of catalog-maintenance I prefer to use it.
With my former employer we went from V4 to V5. After the migration
we used
CATMAINT to convert to type 2 indexes and it just worked fine.

Wim Ruarus
> ----------
> Van: [login to unmask email]:[login to unmask email]
> Antwoord naar: DB2 Data Base Discussion List
> Verzonden: donderdag 4 januari 2001 0:53
> Aan: [login to unmask email]
> Onderwerp: Re: -904 error on alter index
>
> Linda,
> I don't understand. Now, I have even more questions.
> In order to accomplish the ALTER INDEX ..CONVERT TO TYPE 2
command, DB2
> would have to update the row in sysindexes, but the indexes on
sysindex
> would not need to be changed. (unless the update process
deletes and
> readds each of the associated index records??)
> The dynamic bind and any locks on this index would not seem to
change the
> index.
> Invalidating plans and packages do not appear to have anything
to do with
> updating indexes on sysindex.
> Therefore, this would not appear to me to create an
uncommitted write to
> the index that is being altered, and would not create this
problem (as
> described in PQ09837, where this reason code was created).
>
> Am I missing something?
> Doesn't catmaint simply issue ALTER INDEX and REBUILD INDEX
statements for
> each catalog index?
> Could there be something else involved? (After each index was
altered,
> was
> it rebuilt before going on to the alter of the next
index?)
> Has ANYONE else ever altered their catalog indexes to type 2
without using
> catmaint?
> Has PQ09837 made it impossible to do this, making the manual
incorrect?
> Or, was the manual always wrong?
>
> Thanks,
> Tim
>
>
>
>
>
>

Linda,
I don't understand. Now, I have even more questions.
In order to accomplish the ALTER INDEX ..CONVERT TO TYPE 2 command,
DB2
would have to update the row in sysindexes, but the indexes on
sysindex
would not need to be changed. (unless the update process deletes
and
readds each of the associated index records??)
The dynamic bind and any locks on this index would not seem to
change the
index.
Invalidating plans and packages do not appear to have anything to
do with
updating indexes on sysindex.
Therefore, this would not appear to me to create an uncommitted
write to
the index that is being altered, and would not create this problem
(as
described in PQ09837, where this reason code was created).

Am I missing something?
Doesn't catmaint simply issue ALTER INDEX and REBUILD INDEX
statements for
each catalog index?
Could there be something else involved? (After each index was
altered, was
it rebuilt before going on to the alter of the next index?)
Has ANYONE else ever altered their catalog indexes to type 2
without using
catmaint?
Has PQ09837 made it impossible to do this, making the manual
incorrect?
Or, was the manual always wrong?

Thanks,
Tim

Linda Billings

After a bit of thought it seems reasonable that an S-lock would
still be
taken on the index for SYSINDEXES if the ALTER still used it to
locate the
row to be updated in SYSINDEXES. If Mike had any software that
accessed the
catalog and used SYSINDEX for some reason then some plans and
packages may
still be invalidated as a result of the alter and would need to be
rebound.
Does that sound plausible?

Linda,
I don't understand. Now, I have even more questions.
In order to accomplish the ALTER INDEX ..CONVERT TO TYPE 2 command,
DB2
would have to update the row in sysindexes, but the indexes on
sysindex
would not need to be changed. (unless the update process deletes
and
readds each of the associated index records??)
The dynamic bind and any locks on this index would not seem to
change the
index.
Invalidating plans and packages do not appear to have anything to
do with
updating indexes on sysindex.
Therefore, this would not appear to me to create an uncommitted
write to
the index that is being altered, and would not create this problem
(as
described in PQ09837, where this reason code was created).

Am I missing something?
Doesn't catmaint simply issue ALTER INDEX and REBUILD INDEX
statements for
each catalog index?
Could there be something else involved? (After each index was
altered, was
it rebuilt before going on to the alter of the next index?)
Has ANYONE else ever altered their catalog indexes to type 2
without using
catmaint?
Has PQ09837 made it impossible to do this, making the manual
incorrect?
Or, was the manual always wrong?

Thanks,
Tim

Tim Lowe

Linda,
I can see how an alter of any other index would have a problem once
the
index on sysindexes was altered, and was therefore placed into
recovery
pending status.
Likewise, until the index on sysindexes was rebuilt, I can see how
any
rebind would have a problem. (And, a lot of other software would
have
problems until this was done).
But, I do not understand how invalidating plans or packages would
be
related to the index on sysindexes.
And, since the reason code indicated that there was a "pending
write" to
the index on sysindex, then I do not see how invalidating plans or
packages
could be related to this problem.

However, I totally agree with you that the simplest solution is
just to use
catmaint.
It just seems curious to me that the installation guide indicated
that it
could be done either way. And, after so must time, with the
majority of
people on V5 (presumably already converted to type-2 indexes on
their
catalogs), that the installation guide would be wrong.
Perhaps I have too much faith in the manuals.
Or, maybe I am just debating a small point too far.
I surrender.

Thanks,
Tim

Linda Billings

Hi, Tim,
Well, it is a small point but often if we pursue small points
we
gain a better understanding on how the whole system works.
First, the pending write is probably not occurring on the index
but
on the table. An S-lock would still have to be taken on the index
if the
plan needs to use the index to locate the row to update on the
table.
Second, if Mike has any system monitoring type software or
catalog
query software of some type then the plans or packages used by that
system
could conceivably use the catalog tables and any indexes on those
tables if
the optimizer chooses. If any of those indexes is altered then a
plan or
package for that system would become invalid. An update would need
to be
made to SYSPLANS or SYSPACKAGE to reflect this condition. If that
update
needed to use the indexes for those tables then the problem occurs
again.

Linda,
I can see how an alter of any other index would have a problem once
the
index on sysindexes was altered, and was therefore placed into
recovery
pending status.
Likewise, until the index on sysindexes was rebuilt, I can see how
any
rebind would have a problem. (And, a lot of other software would
have
problems until this was done).
But, I do not understand how invalidating plans or packages would
be
related to the index on sysindexes.
And, since the reason code indicated that there was a "pending
write" to
the index on sysindex, then I do not see how invalidating plans or
packages
could be related to this problem.

However, I totally agree with you that the simplest solution is
just to use
catmaint.
It just seems curious to me that the installation guide indicated
that it
could be done either way. And, after so must time, with the
majority of
people on V5 (presumably already converted to type-2 indexes on
their
catalogs), that the installation guide would be wrong.
Perhaps I have too much faith in the manuals.
Or, maybe I am just debating a small point too far.
I surrender.

Thanks,
Tim

David A. Zelmer

The original question concerning this -904 had to do with 4
catalog
indexes. We also ran into this issue and a call to IBM support
resolved
the issue for us. We were told that these 4 indexes had to be
converted
by the CATMAINT process and could not be done via individual
ALTER
INDEX, REBUILD INDEX. Unfortunately, the IBM support person did
not
detail the reasons for this restriction.