Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Trimming database table

    Hi,
    I'm new to DB2. We have a database (db2 7.2, solaris 8) which was not maintained for several months, execpt for deleteing of obsolete records. Now we find that even though older recs are deleted the file sizes are only increasing (> 2Gb) . I understand that we need to do a REORG to free up unused space. There are some 3 or more files occupying large amount of diskspace. We know that one particular table is causing all this. It has a CLOB column in it. Question is: 1. How do we reorg the table without consuming a similar additional amount of disk space.
    2. Will dropping the table and then recreating it without any data reduce the filesizes (i,e. truncate ) ?


    thanks in advance

    Johar

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Tables exist in tablespaces. You need to give us more information about the tablespaces such as SMS/DMS, size, high water mark, etc.

    DB2 list tablespaces show detail
    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
    Aug 2001
    Location
    UK
    Posts
    4,650
    Looks like you are using SMS tablespaces ... If it is possible, dropping and recreating is the best way to go about ... Otherwise, you should do a REORG .. Given your scenario, REORG may not want as much space as it occupies now ... It may want the amount of actual data currently in the table + obviously some overhead ... Just my guess ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2005
    Posts
    2
    Hi,

    Here's the output :

    db2 => list tablespaces show detail

    Tablespaces for Current Database

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1850
    Useable pages = 1850
    Used pages = 1850
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1

    Tablespace ID = 1
    Name = TEMPSPACE1
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1
    Useable pages = 1
    Used pages = 1
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1

    Tablespace ID = 2
    Name = USERSPACE1
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1234521
    Useable pages = 1234521
    Used pages = 1234521
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1



    The problem here is the table was never reorg'd. The table needs to hold only a few weeks data which may come to less than a 100 MB. In that case will REORG require all that additional GB space?


    Tks & regds

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Do the reorg and it will reclaim the space.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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