Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2015
    Location
    Korea
    Posts
    54
    Provided Answers: 1

    Unanswered: DELETE / RUNSTAT / REORG not freeing disk space

    Hello All,
    New to DB2 world, so please dont mind my first Q if its too basic. We are trying to DELETE old data from few tables (biggest tables in our DB) - tables in DMS Large table space and then ran RUNSTAT and REORG on ALL tables but its not freeing filesystem space. Our instance dir filesystem (df output) is still sitting at same usage as before DELETE / RUNSTAT / REORG.

    1) Does that mean db2 do not really free filesystem space after DELETE / RUNSTAT / REORG ? or I missed steps or was it due to some problem in our side?

    2) What can be done to make sure the filesystem space ( for instance home dir) is not containing any junk data? Any thing we can check?

    Stella Z
    Last edited by StellaZhang; 05-26-15 at 11:06.

  2. #2
    Join Date
    May 2015
    Location
    Korea
    Posts
    54
    Provided Answers: 1

    DELETE / RUNSTAT / REORG not freeing disk space

    Hello All,
    New to DB2 world, so please dont mind my first Q if its too basic. We are trying to DELETE old data from few tables (biggest tables in our DB) - tables in DMS Large table space and then ran RUNSTAT and REORG on ALL tables but its not freeing filesystem space. Our instance dir filesystem is still sitting at same usage as before DELETE / RUNSTAT / REORG.
    1) Does that mean db2 do not really free filesystem space after DELETE / RUNSTAT / REORG ? or I missed steps or was it due to some problem in our side? 2) What can be done to make sure the filesystem space ( for instance home dir) is not containing any junk data? Any thing we can check?

    Stella Z

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    list tablespaces show detail or sysibmadm view to check Free pages..
    alter tablespace xxx reduce max (in v10) or resize ....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    May 2015
    Location
    Korea
    Posts
    54
    Provided Answers: 1
    Quote Originally Posted by przytula_guy View Post
    list tablespaces show detail or sysibmadm view to check Free pages..
    alter tablespace xxx reduce max (in v10) or resize ....
    Thanks Guy.
    Sorry, I forgot to mention that I already did ALTER TABLESPACE ..REDUCE MAX.

    So my questions are :

    1) Does that mean db2 do not really free filesystem/disk space after DELETE / RUNSTAT / REORG / ALTER TABLESPACE ..REDUCE MAX ? or I missed steps or was it due to some problem in our side?

    2) What can be done to make sure the filesystem/disk space ( for instance home dir) is not containing any junk data? Any thing we can check?

  5. #5
    Join Date
    May 2015
    Location
    Korea
    Posts
    54
    Provided Answers: 1
    MON_GET_TABLESPACE() shows :
    ---------------------------------------------------------------------------------
    TBSP_FREE_PAGES TBSP_PAGE_TOP TBSP_USABLE_PAGES
    ------------------------------ ------------------------- -------------------- -
    64 16692096 16692160 <--- at start before doing anything
    398400 16692096 16692160 <--- After DELETE / RUNSTAT / REORG
    380832 16672352 16674624 <--- After ALTER TABLESPACE REDUCE MAX

    The table involved here has BLOB column.
    I think one mistake I made was, I didn't use LONGLOBDATA keyword in my reorg command.
    REORG TABLE XXX USE TEMPSPACE LONGLOBDATA

    Do you think is that the reason why I am not able to see the reduce filesystem space (df command) after these operations?
    Or anything else wrt to my two questions in original post.

  6. #6
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Lower table space high water mark before reducing size (after REORG).

    Command for lowering high water mark

    alter tablespace <tablespace name> lower high water mark

  7. #7
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    I presume you are running on DB2 V9.7 and above, then you can use the command "alter tablespace <> lower high water mark", Then reduce the size of the Tablespace to see the difference in File system. If you are running on lower version, you need to run db2dart to see which tables is causing it and it will suggest to reorg in order to release the space.
    As a practice pl. mention your db2 version and OS version.
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is the table 1 tablespace or : data-index-long as we always use ..
    also you have better control over each tablespace and know what the space was used for
    db2Level /platform is a must for each question as indicated in : pinned entry : how to post a question..
    because options are different in different versions
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by StellaZhang View Post
    The table involved here has BLOB column.
    Usually the BLOBs are not REORGed. So you have to:
    - identify the tablespace where the BLOB is stored
    - REORG the table offline with LONGLOBDATA clause
    - ALTER TABLESPACE yourBlobTablespace REDUCE MAX
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  10. #10
    Join Date
    May 2015
    Location
    Korea
    Posts
    54
    Provided Answers: 1
    Thanks everyone.

    What I understand is Reorg, Runstat and Delete will not free up space in the Filesystem. Instead they're to optimize the database/tablespace.
    I was under wrong assumption that DELETE / RUNSTAT / REORG ( with LONGLOBDATA) will free up space in the Filesystem

    Can you confirm if my current understanding is correct now?

    Stella Z
    Last edited by StellaZhang; 05-27-15 at 07:45.

  11. #11
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Yes, DELETE + REORG does not free up space.
    However, DELETE + REORG + ALTER TABLESPACE does.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  12. #12
    Join Date
    May 2015
    Location
    Korea
    Posts
    54
    Provided Answers: 1
    Thanks Florin.

    Are you sure? doing below tasks will this free up space in the Filesystem?

    DELETE
    RUNSTAT
    REORG ( with LONGLOBDATA)
    ALTER TABLESPACE XXX REDUCE MAX

  13. #13
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    It does on my system. You didn't specify yours.
    Also, you should have a test environment where to try such things.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  14. #14
    Join Date
    May 2015
    Location
    Korea
    Posts
    54
    Provided Answers: 1
    Quote Originally Posted by aflorin27 View Post
    It does on my system. You didn't specify yours.
    Also, you should have a test environment where to try such things.
    Thanks again aflorin27.
    Not sure why it didnt in my case
    Can I please ask you to show me your testcase please?

    My senior told me, if you need to free up filesystem try to see this folders: db2dump, folders that containing old backups or log folders. You can clean this old log files.

  15. #15
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you should have a cleanup strategy and not cleaning files with eyes closed - if you really need space try to drop the db
    db2dump folder should be cleaned from files older than xx days - always rotate the diag file - easier to cleanup
    same for txt dmp bin in this folder
    for backups you should decide how long to keep - if restore for any reason needed - how far can we go back in time
    transaction logs should be archived and cleaned up together when cleaning up backup files...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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