Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: LIST TABLESPACES vs SYSCAT.TABLES

    DB2 8.2 for SAP on AIX 5.3

    Hey all,

    I was just trying to figure out why there is a descrepency between the result of a LIST TABLESPACES SHOW DETAIL (which shows a particular tablespace has:
    Total pages = 1275000
    Useable pages = 1274976
    Used pages = 935072
    Free pages = 339904

    Versus
    Code:
    db2 "select SUBSTR(TBSPACE,1,10) AS TABLESPACE, sum(CARD) as CARD, sum(NPAGES) as NPAGES, sum(FPAGES) as FPAGES from syscat.tables where TBSPACEID = 14 group by TBSPACE"
    Which gives:
    TABLESPACE CARD NPAGES FPAGES
    ---------- -------------------- ----------- -----------
    MS1#PROTD 1592543 37169 37294

    I've run stats on all tables.

    What am I missing here?
    Last edited by meehange; 04-18-07 at 22:17.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by meehange

    What am I missing here?
    Um... Indexes and LOBs?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by n_i
    Um... Indexes and LOBs?
    Nope, sorry...I should have said... all the indexes are in another tablespace. I don't believe there are any LOB's and in fact most of the tables in this tablespace are empty. The largest just got emptied out (7.5M rows deleted).

    db2dart /lhwm yields:

    Step #95: Object ID = 1034

    => EXPORT all data from table, drop the table, disconnect, reconnect,
    recreate the table, and LOAD the data back into the table.

    Table: SAPR3.DBTABLOG

    DAT object size: 2
    INX object size: 0
    LF object size: 55040
    LOB object size: 0
    LOBA object size: 0
    BMP object size: 0

    Total size of object parts: 55042
    Minimum number of extents that will move by this operation: 55042

    Current highwater mark: 58440
    Desired highwater mark: 0
    Number of used extents in tablespace: 58441
    Number of free extents below original HWM: 20364
    Number of free extents below desired HWM: 0
    Number of free extents below current HWM: 0

    Now I see that it's suggesting that I drop and recreate the table, but I'm reluctant to do that until I understand why it's necessary....
    Any ideas?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    There are a lot of information that we don't know:
    • is this the only table in the tablespace? (other tables could occupy space)
    • what kind of tablespace is that? (DMS tablespaces have things like a free space map)
    • have you run RUNSTATS recently? (if not, then the values in SYSCAT.TABLES may just be outdated)
    • was the table reorganized? (that would free up empty pages again)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by stolze
    There are a lot of information that we don't know:[*] is this the only table in the tablespace? (other tables could occupy space)
    No, there are several hundred tables in the tablespace, most are empty or close to empty. The largest has just had all it's rows deleted (7.5Mil) but the space doesn't seem to be freeing up.

    [*] what kind of tablespace is that? (DMS tablespaces have things like a free space map)
    It's a DMS but the difference in space is far too large to be a mere overhead.

    [*] have you run RUNSTATS recently? (if not, then the values in SYSCAT.TABLES may just be outdated)
    Runstats has been run very recently.

    [*] was the table reorganized? (that would free up empty pages again)
    That's the problem, reorgs are not freeing up the space as I would have thought.

    This problem has occurred on several systems now. I have managed to free up the space with the following procedure:

    1. Rows deleted
    2. Runstats on all tables in tablespace
    3. Reorg all tables in tablespace
    (this does not free up space)
    4. Disconnect all application (I really don't want to do this on the production systems)
    5. db2dart <DBID> /lhwm (giving the tablespace ID and 0 as the params)
    This suggests a reorg with no access using the longlobdata option
    6. Grep the output to get the tablenames in order
    7. Create/run reorg statements based on the grep output (for some reason it only seems to work if the reorgs are done in the order advised by db2dart???)
    --- the space is STILL not freed up----
    8. db2stop (for some reason a FORCE is always required, I can't do this on Prod)/db2start
    9. Runstats on all tables in tablespace

    List tablespaces now shows the HWM and used pages has dropped and the tablespace can be resized (lowered by 90%)

    So trhe problem/question is:

    -How much of the above is Voodoo admin (ie stuff I'm doing because I think it's necessary)
    -Why do I need to restart the instance to free the space, is this normal and can I avoid it?

    Cheers,
    Last edited by meehange; 04-24-07 at 05:16. Reason: missed a bit

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by meehange
    The largest has just had all it's rows deleted (7.5Mil) but the space doesn't seem to be freeing up.
    Have you reorg'd that table to reclaim this free space? (This is one of the task that reorg does.)

    Runstats has been run very recently.
    On all tables in the tablespace?

    That's the problem, reorgs are not freeing up the space as I would have thought.
    In which table have you done the reorg? On all tables or just one?

    This problem has occurred on several systems now. I have managed to free up the space with the following procedure:

    1. Rows deleted
    2. Runstats on all tables in tablespace
    3. Reorg all tables in tablespace
    (this does not free up space)
    4. Disconnect all application (I really don't want to do this on the production systems)
    5. db2dart <DBID> /lhwm (giving the tablespace ID and 0 as the params)
    This suggests a reorg with no access using the longlobdata option
    6. Grep the output to get the tablenames in order
    7. Create/run reorg statements based on the grep output (for some reason it only seems to work if the reorgs are done in the order advised by db2dart???)
    --- the space is STILL not freed up----
    8. db2stop (for some reason a FORCE is always required, I can't do this on Prod)/db2start
    9. Runstats on all tables in tablespace
    2. completely unnecessary step only adding overhead; you can collect statistics on the fly during reorg
    3. does free up space; how do you do the reorg? (options)
    7. repeats work from step 3. You could determine the tables with LONG/LOB columns before step 3 and use the LONGLOBDATA option in 3. already
    8. why do you recycle db2? And if there are still some connections, you should figure out which ones those are
    9. is again unnecessary if you collect statistics during the reorg phase
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by stolze
    Have you reorg'd that table to reclaim this free space? (This is one of the task that reorg does.)



    On all tables in the tablespace?
    Yes.
    A 'reorg table <tablename> allow read access' run on all tables in the tablespace did not free up the space. It seems that only after a reorg with longlobdata option is run and then the instance restarted that the space is freed up and the HWM reduced.


    2. completely unnecessary step only adding overhead; you can collect statistics on the fly during reorg
    3. does free up space; how do you do the reorg? (options)
    7. repeats work from step 3. You could determine the tables with LONG/LOB columns before step 3 and use the LONGLOBDATA option in 3. already
    8. why do you recycle db2? And if there are still some connections, you should figure out which ones those are
    9. is again unnecessary if you collect statistics during the reorg phase
    2. I'm on DB2 LUW 8.2, and I can't see any way collect stats on the fly during REORG. I was under the impression that best practice was runstats, reorg, runstats....no?

    3. I have tried a classic reorg and an inplace reorg...neither seemed to do the trick.

    7. Is there any harm in just specifying LONGLOBDATA for all tables in the tablespace? If not I might just do this.

    8. It seems to me (and I have by no means rigorously tested this, since once the space is freed up I can't really recreate the exact situation again) that the space and HWM are only changed AFTER a recycle, which is the main problem here since I can't easily do a recycle when I move on to my production systems.

    Cheers

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by meehange
    A 'reorg table <tablename> allow read access' run on all tables in the tablespace did not free up the space. It seems that only after a reorg with longlobdata option is run and then the instance restarted that the space is freed up and the HWM reduced.
    Did you also reorg all indexes on those tables? If not, then indexes may occupy space.

    2. I'm on DB2 LUW 8.2, and I can't see any way collect stats on the fly during REORG. I was under the impression that best practice was runstats, reorg, runstats....no?
    You are right. I mixed that up with REORGCHK.

    3. I have tried a classic reorg and an inplace reorg...neither seemed to do the trick.
    As I said before, REORG frees unused space. I suggest that you pick one table and work through that first. Trying to handle all tables at once just complicates things.

    7. Is there any harm in just specifying LONGLOBDATA for all tables in the tablespace? If not I might just do this.
    It may slow down the reorg process.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jul 2004
    Posts
    306
    Knut,

    All indexes are in a seperate tablespace. The space is being freed up and the HWM lowered, but only after an instance restart. Obviously this isn't desirable for 24/7 production systems. If I want an outage I have to explain why DB2 isn't operating as expected.....

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The HWM is lowered by RUNSTATS and/or REORG. But there are a few situations where it can't be lowered. Here is a nice summary in HWM and when it can/cannot be lowered by DB2:
    http://www-1.ibm.com/support/docview...id=swg21006526
    http://www.ruban.de/DB2_luw/HWM_redu...reduction.html

    Another question I have is why you want to lower the HWM? You delete a lot of data from the table. Wouldn't you expect the table to fill again at some later point in time? If so, then it doesn't make much sense to lower HWM and deallocate the already allocated extends. Allocating them again results in unnecessary overhead. So you should have another, compelling reason...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jul 2004
    Posts
    306
    Knut

    These tables will not fill again to the same degree and will fill vey slowly, also other tablespaces are growing a lot faster and are in need of space. So I have to redistribute the space.

    I understand that runstats/reorg will sometimes not reduce the HWM but this has happenned on 5 seperate DB's so far, so it looks like it always happens to these systems...perhaps it's an issue with SAP, I don't know.
    My main concern is that I always need to restart the instance, and while I can do this on m test/dev systems...it's a real problem for my 24/7 systems (20 databases on 5 servers!!)

Posting Permissions

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