We recently converted all of our indexes from type 1 to type 2 due to the additional functionality of version 8.1.5 from 7.x. While I was running the index reorg statements, on one of our biggest tables, the index reorg failed due to running out of space and said that TEMPSPACE1 had filled up.

I was under the impression that during the reorg, db2 uses the Index tablespace to do its work in and should it fill up, just increase the size of the lv - extendlv/alter tbspace. In fact, I had to do this for a couple of our larger tables to get them to convert.

Any idea of why Db2 would not use the table's index tb space to do its work instead of TEMPSPACE? Also, shouldn't increasing the size of the Index tb space for the table help when I attempt the reorg again?

Thx!

It seems like a lot of wasted space if each index tablespace had to have enough room to reorg its indexes. It is more efficient to use a common work area, like a system temporary tablespace.

If your system temporary tablespace is not large enough to reorg an index, it is likely too small, especially in a data warehouse application. If possible, make the system temporary tablespace at least as large as the largest table that could be scanned with a tablespace scan. If you have multiple simultaneous users scanning a tablespace, it needs to be larger.

Obviously, for SMS tablespaces, you just need sufficient room on the drive for it to expand as needed.

Reorgization also do sorting for indexes and the purpose of the temporary
tablespace is do that sorting as efficintly as possible, generating no log information, don't care about formating the pages, coalesce the space when done automatically and so on.Infact , all the current DBMS made the temporary tablespace for that purpose by creating special routine for writing data in the temporary tablespace.



did you use the CONVERT clause for reorg?

regards,

mujeeb

Ok, so, it looks like the index reorg for this large table used the index tablespace and then did its sorting operation in Tempspace1.

It sounds like to me that if we increase the jfs file space for Tempspace1 (SMS)-(already at 8 gig) that should be the answer. That way db2 will have more room in its temp files to do the sorting. Do we all agree?

To answer mujeeb's question - yes, the convert clause was used at the end of the index reorg command.

Thx