using List Tablespaces Show Detail to free pages

[shorti]

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.

[shorti]

Anyone have the expertise to answer any of these questions? I
appreciate the help.

[Frederik Engelen]

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

[shorti]

Post by Frederik Engelen

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.

[John]

Post by shorti

Post by Frederik Engelen

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.....
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?

[shorti]

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?

[John]

On Jan 25, 3:03 pm, shorti <***@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.

Post by shorti
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.

Post by shorti
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)

Post by shorti
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'

[shorti]

John,

Thank you for your answers!