Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42

    Unanswered: Index Reorgs convert to (type 2) - v8

    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!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Posts
    448
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •