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 14 Jan 2010 17:38 Hello, I have some machines at V8.2 (on AIX) that are showing large amounts of "Pending Delete" extents. I have used "list tablespaces show detail" to free these up on a couple of machine. However, I have some questions about how we can avoid getting into this situation and whether it is safe to free these up in this manner. Questions: 1) The write up I found regarding where these Pending Delete extents come from says: "When a table is dropped, or any transaction that returns used extents back to the tablespace...This is done to protect the scenario when some of these transactions need to be rolled back," The tables in these tablespaces are not normally dropped so are they talking about transactions that are not being committed? I would assume no since we show none of the normal signs of holding a transaction open (like active logs not closing, etc). So, what sort of transactions would these be that are completed but may need to be rolled back? 2) Is it safe to free these using "list tablespaces show detail" ? On another very old post on an off subject this person writes: "Note that this is not necessarily cheap. To provide accurate information to the user, issuing the "list tablespaces show detail" command will cause us to free any pending delete extents. This will result in page fixing (possibly I/O into the bufferpool of space map pages), log records being written and so on. " I do not understand what that means but it doesnt sound good =). Also, if there are pending transactions that may require a rollback, freeing them sounds dangerous?! Keep in mind we will be doing this on a running customer machine. 3) are there any commands or tools that might gather more information about these extents...when they went into "pending delete" or what transactions they are linked to? Maybe something is tied into the history file or ?? Maybe they came about from some one-time operation that we did a long time ago and just need to be cleaned up...I just need to find more info on it in order to figure out how we can prevent it. Knowing when and how often they are accumulating might give us some clue. Thanks in advance.
From: shorti on 19 Jan 2010 16:02 Anyone have the expertise to answer any of these questions? I appreciate the help.
From: Frederik Engelen on 20 Jan 2010 07:59 On Jan 19, 10:02 pm, shorti <lbrya...(a)juno.com> wrote: > Anyone have the expertise to answer any of these questions? I > appreciate the help. I believe everyone is still baffled by the assumption that a 'list tablespaces...' would make changes to your tablespaces. I know I still am... That aside, I don't have the necessary experience on 8.2 to help you, sorry. -- Frederik Engelen
From: shorti on 20 Jan 2010 15:51 On Jan 20, 5:59 am, Frederik Engelen <engelenfrede...(a)gmail.com> wrote: > On Jan 19, 10:02 pm, shorti <lbrya...(a)juno.com> wrote: > > > Anyone have the expertise to answer any of these questions? I > > appreciate the help. > > I believe everyone is still baffled by the assumption that a 'list > tablespaces...' would make changes to your tablespaces. I know I still > am... > > That aside, I don't have the necessary experience on 8.2 to help you, > sorry. > > -- > Frederik Engelen Ahh..yes Frederik..I am as baffled as you are. However, it is not an assumption. I did this on one of the machines that was showing 400k pages of Pending Deletes and it freed them up. This reduced our compressed backups by 200MB in size. It also doesnt make changes to the tablespaces...it seems to free up memory that is marked as a "Pending Delete" state. This memory seems to be certain conditions where DB2 feels it should hold on to the memory Just in case..... There is a short DB2 write about this : http://www-01.ibm.com/support/docview.wss?uid=swg21394023 It doesnt answer the questions Ive been looking for above. It is actually down right scary to think a command like "list tablespace show detail" can do something like free memory. This does not exist at DB2 V9+ since DB2 now releases this type of memory before an online backup...or so I read. But, I dont know if it just releases it all or selects what to release (i.e. release anything before a certain time). In V8 I was hoping there was some way to determine when the memory was marked pending and if you can selectively release memory. In other words, if I know I did a backup at 2PM today I should be able to release any pending prior to that timestamp.
From: John on 24 Jan 2010 20:58
On Jan 20, 1:51 pm, shorti <lbrya...(a)juno.com> wrote: > On Jan 20, 5:59 am, Frederik Engelen <engelenfrede...(a)gmail.com> > wrote: > > > On Jan 19, 10:02 pm, shorti <lbrya...(a)juno.com> wrote: > > > > Anyone have the expertise to answer any of these questions? I > > > appreciate the help. > > > I believe everyone is still baffled by the assumption that a 'list > > tablespaces...' would make changes to your tablespaces. I know I still > > am... > > > That aside, I don't have the necessary experience on 8.2 to help you, > > sorry. > > > -- > > Frederik Engelen > > Ahh..yes Frederik..I am as baffled as you are. However, it is not an > assumption. I did this on one of the machines that was showing 400k > pages of Pending Deletes and it freed them up. This reduced our > compressed backups by 200MB in size. It also doesnt make changes to > the tablespaces...it seems to free up memory that is marked as a > "Pending Delete" state. This memory seems to be certain conditions > where DB2 feels it should hold on to the memory Just in case..... > > There is a short DB2 write about this : > > http://www-01.ibm.com/support/docview.wss?uid=swg21394023 > > It doesnt answer the questions Ive been looking for above. It is > actually down right scary to think a command like "list tablespace > show detail" can do something like free memory. > > This does not exist at DB2 V9+ since DB2 now releases this type of > memory before an online backup...or so I read. But, I dont know if it > just releases it all or selects what to release (i.e. release anything > before a certain time). In V8 I was hoping there was some way to > determine when the memory was marked pending and if you can > selectively release memory. In other words, if I know I did a backup > at 2PM today I should be able to release any pending prior to that > timestamp. From the link....... "When a table is dropped, or any transaction that returns used extents back to the tablespace, the extents are put in an intermediate state called the "pending delete" state. This is done to protect the scenario when some of these transactions need to be rolled back, then we can immediately put these pending delete extents to in-use state. Otherwise, newer transactions will be able to use these extents and overwrite the content with their own data, making the rollback of the earlier transactions impossible. Once the extents had been marked as pending delete, we will mark them free again when we can be sure no existing transactions require them to be marked as pending delete anymore." 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? |