-904 error on alter index

Mike Lawrence

-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.



Smike Toppins

Re: -904 error on alter index
(in response to Mike Lawrence)
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

Re: -904 error on alter index
(in response to Mike Lawrence)
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.

Linda Billings
Enterprise Systems Programmer
Info-Tech Services
Department of Administration
State of Wisconsin

"The basis of optimism is sheer terror." - Oscar Wilde


-----Original Message-----
From: Mike Lawrence [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 12:44 PM
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]



Mike Lawrence

Re: -904 error on alter index
(in response to Linda Billings)
Great! Thanks Linda! I wondered if that would fix it or not..
Have a Great Day!
Mike

Michael S. Lawrence
Systems Programmer
[login to unmask email]
Wk # - (812) 228-2092
Fax # - (812) 228-2065


>>> [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.

Linda Billings
Enterprise Systems Programmer
Info-Tech Services
Department of Administration
State of Wisconsin

"The basis of optimism is sheer terror." - Oscar Wilde


-----Original Message-----
From: Mike Lawrence [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 12:44 PM
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]

=======================
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

Re: -904 error on alter index
(in response to Smike Toppins)
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?

Thanks,
Tim



"Billings, Linda"
<[login to unmask email] To: [login to unmask email]
ATE.WI.US> cc:
Sent by: DB2 Data Base Subject: Re: -904 error on alter index
Discussion List
<[login to unmask email]>


01/03/2001 12:55 PM
Please respond to DB2
Data Base Discussion
List






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.

Linda Billings
Enterprise Systems Programmer
Info-Tech Services
Department of Administration
State of Wisconsin

"The basis of optimism is sheer terror." - Oscar Wilde


-----Original Message-----
From: Mike Lawrence [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 12:44 PM
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]


visit






Linda Billings

Re: -904 error on alter index
(in response to Tim Lowe)
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

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 1:23 PM
To: [login to unmask email]
Subject: Re: -904 error on alter index


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?

Thanks,
Tim




"Billings, Linda"

<[login to unmask email] To: [login to unmask email]

ATE.WI.US> cc:

Sent by: DB2 Data Base Subject: Re: -904
error on alter index
Discussion List

<[login to unmask email]>





01/03/2001 12:55 PM

Please respond to DB2

Data Base Discussion

List









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.

Linda Billings
Enterprise Systems Programmer
Info-Tech Services
Department of Administration
State of Wisconsin

"The basis of optimism is sheer terror." - Oscar Wilde


-----Original Message-----
From: Mike Lawrence [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 12:44 PM
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]









http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



Mohammed Nayeem

Re: -904 error on alter index
(in response to Linda Billings)
Same error I am also facing currently in test region when I am trying to
re-create index after dropping. Looklike temp files are running out of space.

Thx
Nayeem



Linda Billings

Re: -904 error on alter index
(in response to Mohammed Nayeem)
Hello, Nayeem,

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.

Linda

-----Original Message-----
From: Mohammed Nayeem [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 1:47 PM
To: [login to unmask email]
Subject: Re: -904 error on alter index


Same error I am also facing currently in test region when I am trying to
re-create index after dropping. Looklike temp files are running out of
space.

Thx
Nayeem








Tim Lowe

Re: -904 error on alter index
(in response to Linda Billings)
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

Re: -904 error on alter index
(in response to Tim Lowe)
Tim,

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 Billings

Re: -904 error on alter index
(in response to Wim Ruarus)
Hi, Tim,
I don't know either. I am only guessing. Perhaps there is someone
who knows more than I do. I only know that we used CATMAINT and that
worked.

Linda

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 5:54 PM
To: [login to unmask email]
Subject: 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 Billings

Re: -904 error on alter index
(in response to Linda Billings)
Tim,

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

-----Original Message-----
From: Billings, Linda
Sent: Thursday, January 04, 2001 6:53 AM
To: 'DB2 Data Base Discussion List'
Subject: RE: -904 error on alter index


Hi, Tim,
I don't know either. I am only guessing. Perhaps there is someone
who knows more than I do. I only know that we used CATMAINT and that
worked.

Linda

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, January 03, 2001 5:54 PM
To: [login to unmask email]
Subject: 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








Tim Lowe

Re: -904 error on alter index
(in response to Linda Billings)
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

Re: -904 error on alter index
(in response to Tim Lowe)
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.

HTH,

Linda

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 04, 2001 10:45 AM
To: [login to unmask email]
Subject: Re: -904 error on alter index


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

Re: -904 error on alter index
(in response to Linda Billings)
Hi,

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.


My 2 cents worth,

Dave Zelmer
DB2 sysprog/DBA
Citibank, SD