creating clustered index, error

The program Issued the following statement: CREATE CLUSTER INDEX m6895_x01 ON m6895 (ssn) And got the following error: Database name (DBNAME): sentryprod Editing table: M6895 Adding ssn index to table at 200505110512.... SQL error: -502, While: mbedit: execute create ssn index sqlerrd[1]: ISAM error code = 0. sqlerrd[2]: Number of rows processed = 0. sqlerrd[4]: Offset of error into SQL statement = 45. sqlerrd[5]: Row id of last row after insert = 0. finderr 502 -502 Cannot cluster index. While the database server was building a new copy of the table with rows in clustered sequence, it got an unexpected error. Check the accompanying ISAM error code for more information. Typical causes include a shortage of disk space. Question: Does making a clustered index cause the table to totally rebuild ? I guess it does, since it has to reorder the whole table by the column specified. So what does it do, make an internal copy of the table in the same dbspace ? I am trying to figure out where I was probably running short of dbspace to make this clustered index. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ========================

Hi, It does recreate the table after the index creation. 1 - index creation. 2 - table creation based on index, 2nd table copy. 3 - table drop, 1st copy. If I remember correctly, all these steps will be done using temp space, index et 2nd copy of table, and then flush to disk. I think that I used to take the size of the table X 2, to set the temp space. (table 10G X 2 = 20G of temp space min.) Diane -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Floyd Welle.... Sent: Wednesday, May 11, 2005 9:20 AM To: ids@iiug.org Subject: creating clustered index, error [4904] The program Issued the following statement: CREATE CLUSTER INDEX m6895_x01 ON m6895 (ssn) And got the following error: Database name (DBNAME): sentryprod Editing table: M6895 Adding ssn index to table at 200505110512.... SQL error: -502, While: mbedit: execute create ssn index sqlerrd[1]: ISAM error code = 0. sqlerrd[2]: Number of rows processed = 0. sqlerrd[4]: Offset of error into SQL statement = 45. sqlerrd[5]: Row id of last row after insert = 0. finderr 502 -502 Cannot cluster index. While the database server was building a new copy of the table with rows in clustered sequence, it got an unexpected error. Check the accompanying ISAM error code for more information. Typical causes include a shortage of disk space. Question: Does making a clustered index cause the table to totally rebuild ? I guess it does, since it has to reorder the whole table by the column specified. So what does it do, make an internal copy of the table in the same dbspace ? I am trying to figure out where I was probably running short of dbspace to make this clustered index. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ========================

Like Diane said, but not in temp space. It will recreate in the dbspace where it currently resides. j. ----- Original Message ----- From: "Floyd Welle...." <fwellers@yahoo.com> To: <ids@iiug.org> Sent: Wednesday, May 11, 2005 10:20 AM Subject: creating clustered index, error [4904] > The program Issued the following statement: > CREATE CLUSTER INDEX m6895_x01 ON m6895 (ssn)> > And got the following error: > > Database name (DBNAME): sentryprod > > Editing table: M6895 > > Adding ssn index to table at 200505110512.... > > SQL error: -502, While: mbedit: execute create ssn index> > sqlerrd[1]: ISAM error code = 0. > > sqlerrd[2]: Number of rows processed = 0. > > sqlerrd[4]: Offset of error into SQL statement = 45. > > sqlerrd[5]: Row id of last row after insert = 0. > > > > finderr 502 > > -502 Cannot cluster index. > > > > While the database server was building a new copy of the table with > > rows in clustered sequence, it got an unexpected error. Check the > > accompanying ISAM error code for more information. Typical causes > > include a shortage of disk space. > > Question: Does making a clustered index cause the table to totally rebuild ? I guess it does, since it has to reorder the whole table by the column specified. > > So what does it do, make an internal copy of the table in the same dbspace ? I am trying to figure out where I was probably running short of dbspace to make this clustered index. > > > > Thanks, > > floyd > > > > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com > Home: 703-430-0805 > Cell: 703-477-6045 > ======================== > > > > >