using List Tablespaces Show Detail to free pages
in [DB2]
|
Prev: Collating seq of a table - copied from serverfault website
Next: SELECT INTO with ORDER BY and FETCH FIRST ONLY
From: shorti on 25 Jan 2010 17:03 On Jan 24, 6:58 pm, John <jgleip...(a)gmail.com> wrote: > > we are talking about extents here in the table space. You kind of > toss around the term memory, we are talking about extents that were > allocated to a table at one point and now they are not needed because > of delete activity. They are kept around (in pending delete state) to > rollback the transaction that executed the delete......... so when > they are no longer needed for roll back we can go back and mark them > as usable..... "list tablespaces show detail" isn't whacking any data > he is freeing up pages that were no longer needed and marked a > "pending delete". > > does that help?- Hide quoted text - > > - Show quoted text - John, I did use the term memory..it was my guess since I am not familiar with how this extent data is stored. But...it could be a file of sorts too. Since the write up used the work "free" I figured we were talking about memory since you dont normally "free" a file...you delete it. It makes no difference as the point is that DB2 places them in the "pending delete" state and does not eventually free it themselves. With the smallest level of understanding I have, these extents are probably no longer needed especially if I can recover from a backup, logs and history files. But, as I explained above, the write up does not explain enough for me to be sure. If its safe enough to use "list tablespaces show detail" then why doesnt DB2 free up these "no longer needed" pages periodically? Is there something in DB2 V8 that the user is required to run to free these (like the user must prune thier own history files)? Since I do not know why DB2 is hanging on to them and DB2 hasnt explained that a user is required to clean them up periodically then how do I know a sufficient amount of time has been exceeded where it is now safe to have them freed? Also, what transactions, other than dropping a table, fall under the "or any transaction that returns used extents back to the tablespace" ? Could this be whenever you delete records from a table?
From: John on 26 Jan 2010 19:32 On Jan 25, 3:03 pm, shorti <lbrya...(a)juno.com> wrote: From the link.... There are events that would trigger the scanning of the tablespace for "freeable" pending delete extents. For example, 1. When new space allocation request comes in, we will try to search for free space in the tablespace, if not, then we search for "freeable" pending delete extents, free them and use the space. 2. Running db2 "list tablespaces show detail" command would free up the "freeable" pending delete extents (under the covers). Use db2pd - db <dbname> -tablespaces before and after "list tablespaces show detail" command to verify how many extents were freed up. Specifically check 'PndFreePgs' column under Tablespace Statistics section. 3. In V9.5, online backup will attempt to free all "freeable" pending delete extents before starting. > John, I did use the term memory..it was my guess since I am not > familiar with how this extent data is stored. But...it could be a > file of sorts too. Since the write up used the work "free" I figured > we were talking about memory since you dont normally "free" a > file...you delete it. It makes no difference as the point is that DB2 > places them in the "pending delete" state and does not eventually free > it themselves. extents are contiguous pages of disk allocated for a specific table in the table space. It may not technically "free" them, but it will reuse them as needed.... see #1 from the link. After the extents are no longer needed for a rollback they just stay in a pending status, if I need to I can reuse them anytime. > > With the smallest level of understanding I have, these extents are > probably no longer needed especially if I can recover from a backup, > logs and history files. But, as I explained above, the write up does > not explain enough for me to be sure. If its safe enough to use "list > tablespaces show detail" then why doesnt DB2 free up these "no longer > needed" pages periodically? Is there something in DB2 V8 that the > user is required to run to free these (like the user must prune thier > own history files)? Since I do not know why DB2 is hanging on to them > and DB2 hasnt explained that a user is required to clean them up > periodically then how do I know a sufficient amount of time has been > exceeded where it is now safe to have them freed? Please note #1 when DB2 needs space in the tablespace it will look for "free" extents first then "pending" extents are used next. They are used by the system as needed. If you are running 9.5 or above the backup will clean them up. Think of DB2 as a teenager that doesn't always clean up after himself... there may be a lot of "pending" stuff around... it doesn't hurt anything other that irritate his parent (or DBA) > > Also, what transactions, other than dropping a table, fall under the > "or any transaction that returns used extents back to the > tablespace" ? Could this be whenever you delete records from a > table? Any transaction that deletes larger number of contiguous rows from the table. eg... delete from order where year_ordered = '1995'
From: shorti on 28 Jan 2010 13:57
On Jan 26, 5:32 pm, John <jgleip...(a)gmail.com> wrote: > On Jan 25, 3:03 pm, shorti <lbrya...(a)juno.com> wrote: John, Thank you for your answers! |