Online reorg and super exclusive lock cause many problems

[Lara]

Hello freaks,

we have many problems with our online reorg and no idea how to resolve
it.

We had to do an online reorg beacause of 24 h online business.
We start the reorg-statements (table by table) in a loop with the
following statement:
reorg table xxx.yyy inplace allow write access.

Now each time the reorg is running the following will happen: The
reorg wants to set a super exclusive lock on a special big table
(xxx). The table is very big and many many users will access this
table all over the time.

We thougt, that there must be a queue or something like that, so that
the super exclusive lock can work and the reorg will go on, but we can
see that the reorg is waiting and waiting and waiting to get the lock
which will never happen (because of the other users?).

Is there any good solution without keeping the users out?
Is there any possibility to see which table is still in proccess of
reorg because they are startet asyncronous unfortunately.

Many thanks,
Lara

[Serge Rielau]

Hi Lara,

(Sidenote: In my experience "freak" in English has a much more negative
conotation that the almost loving "computer freak" used in German :-)

At least in DB2 for LUW (is that what you're on, or DB2 for zOS?)
I have never heard of such a thing as a "super exclusive lock" and
online reorg is designed through and through to work in an isolation
than less invasive than CS (e.g. it locks only the row it operates on).

In DB2 for zOS to teh best of my knowledge online reorg makes a copy,
so I imagine ther must be a blackout period for catchup of teh copy and
the swap.

So... can you give a few more details because at least I can't make
heads or tails of it.

Cheers
Serge

[Sean McKeough]

Hey, I like the freak moniker...

We do need to Z lock the tcb a few times during online reorg...I think
this happens once at the start, and once at the end of the reorg when
modifying some in memory table structures.

I'm not sure about our lock behavior though...will pass your note on to
the guys who wrote this, and post back here.

Serge Rielau wrote:

Hi Lara,

(Sidenote: In my experience "freak" in English has a much more negative
conotation that the almost loving "computer freak" used in German :-)

At least in DB2 for LUW (is that what you're on, or DB2 for zOS?)
I have never heard of such a thing as a "super exclusive lock" and
online reorg is designed through and through to work in an isolation
than less invasive than CS (e.g. it locks only the row it operates on).

In DB2 for zOS to teh best of my knowledge online reorg makes a copy,
so I imagine ther must be a blackout period for catchup of teh copy and
the swap.

So... can you give a few more details because at least I can't make
heads or tails of it.

Cheers
Serge

[Sean McKeough]

Here's the response:
"This lock is a 'special waiter' lock, designed spcifically to wait for
anyone with a conflicting lock to leave while never starving new lock
requests coming in. Basically, we wait until the last person who held a
conflicting lock BEFORE we made our request to release their lock, but
we should never wait for new lockers.

So someone out there isn't releasing their table lock - either a
connection never does commit/rollback or does only commits with hold so
that the table lock is never released."
Sean McKeough wrote:

Hey, I like the freak moniker...

We do need to Z lock the tcb a few times during online reorg...I think
this happens once at the start, and once at the end of the reorg when
modifying some in memory table structures.

I'm not sure about our lock behavior though...will pass your note on to
the guys who wrote this, and post back here.

Serge Rielau wrote:

Hi Lara,

(Sidenote: In my experience "freak" in English has a much more
negative conotation that the almost loving "computer freak" used in
German :-)

At least in DB2 for LUW (is that what you're on, or DB2 for zOS?)
I have never heard of such a thing as a "super exclusive lock" and
online reorg is designed through and through to work in an isolation
than less invasive than CS (e.g. it locks only the row it operates on).

In DB2 for zOS to teh best of my knowledge online reorg makes a copy,
so I imagine ther must be a blackout period for catchup of teh copy
and the swap.

So... can you give a few more details because at least I can't make
heads or tails of it.

Cheers
Serge

[Mark Townsend]

Sean McKeough wrote:

Here's the response:
"This lock is a 'special waiter' lock, designed spcifically to wait for
anyone with a conflicting lock to leave while never starving new lock
requests coming in. Basically, we wait until the last person who held a
conflicting lock BEFORE we made our request to release their lock, but
we should never wait for new lockers.

So someone out there isn't releasing their table lock - either a
connection never does commit/rollback or does only commits with hold so
that the table lock is never released."

Hmm - doesn't that sort of mean then that in a busy system the re-org
will never start ? Is this DB2 LUWser or Mainframe ?

[Lara]

Hi Serge,

thank you so much for the 'freak'-hint. I did not mean it negative at
all, in the feuture I will use experts :-)

We use db2 UBD V7.1 on Unix, and there IS a super exclusive lock, at
least the db monitor is telling that...

The effect at the end is the following: The lock is held and the
working users fill up the transaction logs and after a time the logs
are full, the reorg is hanging in a strange state of doing nothing,
even if the transactions are rolled back and the users are gone. All
we could do is 'force application' to kill the backend process and
this will make the db to crash and restart.

The IBM-Support told us, that there is no possibility to 'see' what
the db is doing exactly during the reorg, and there is no message
telling us 'table xyz is reorganized', going on with table abc. In
this case we had the possibility to start the reorg statements step by
step and we are not trapped in the asynchronous mechanism.

Many thanks for any help,
Lara
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2u*************@uni-berlin.de>...

Hi Lara,

(Sidenote: In my experience "freak" in English has a much more negative
conotation that the almost loving "computer freak" used in German :-)

At least in DB2 for LUW (is that what you're on, or DB2 for zOS?)
I have never heard of such a thing as a "super exclusive lock" and
online reorg is designed through and through to work in an isolation
than less invasive than CS (e.g. it locks only the row it operates on).

In DB2 for zOS to teh best of my knowledge online reorg makes a copy,
so I imagine ther must be a blackout period for catchup of teh copy and
the swap.

So... can you give a few more details because at least I can't make
heads or tails of it.

Cheers
Serge

[duoduo]

how big is your "special big table"? Is it partition table? I am
planning to use this online reorg and I also worry about my 110GB
table.

qw****@gmx.de (Lara) wrote in message news:<1c**************************@posting.google. com>...

Hello freaks,

we have many problems with our online reorg and no idea how to resolve
it.

We had to do an online reorg beacause of 24 h online business.
We start the reorg-statements (table by table) in a loop with the
following statement:
reorg table xxx.yyy inplace allow write access.

Now each time the reorg is running the following will happen: The
reorg wants to set a super exclusive lock on a special big table
(xxx). The table is very big and many many users will access this
table all over the time.

We thougt, that there must be a queue or something like that, so that
the super exclusive lock can work and the reorg will go on, but we can
see that the reorg is waiting and waiting and waiting to get the lock
which will never happen (because of the other users?).

Is there any good solution without keeping the users out?
Is there any possibility to see which table is still in proccess of
reorg because they are startet asyncronous unfortunately.

Many thanks,
Lara

[Lara]

Sorry, Version is 8.1 FixPack 6

[Serge Rielau]

Mark Townsend wrote:

Sean McKeough wrote:

Here's the response:
"This lock is a 'special waiter' lock, designed spcifically to wait
for anyone with a conflicting lock to leave while never starving new
lock requests coming in. Basically, we wait until the last person who
held a conflicting lock BEFORE we made our request to release their
lock, but we should never wait for new lockers.

So someone out there isn't releasing their table lock - either a
connection never does commit/rollback or does only commits with hold
so that the table lock is never released."

Hmm - doesn't that sort of mean then that in a busy system the re-org
will never start ? Is this DB2 LUWser or Mainframe ?

Actually no. BTW, Bruce Lindsay gave a very detailed talk on online
Reork a couple of years ago at a DB2 conference :-)
What happens is that the lock divides the transactions into old and new.
Ones all the _old_ ones are gone reorg can start its mery business.
I think it has to do with new transactions being aware that the a reorg
is going.
When reorging a row. New transactions will see the new row, old ones
will see the old version (or at least a tumbstone of it).
online-reorg is comparatively slow which is why it's also called
trickle-reorg.

Cheers
Serge

[Sean McKeough]

There is get snapshot for table support for reorg (you can see what
state the reorg is in).

What you need to do is see which applications are not releasing their
table locks.

Lara wrote:

Hi Serge,

thank you so much for the 'freak'-hint. I did not mean it negative at
all, in the feuture I will use experts :-)

We use db2 UBD V7.1 on Unix, and there IS a super exclusive lock, at
least the db monitor is telling that...

The effect at the end is the following: The lock is held and the
working users fill up the transaction logs and after a time the logs
are full, the reorg is hanging in a strange state of doing nothing,
even if the transactions are rolled back and the users are gone. All
we could do is 'force application' to kill the backend process and
this will make the db to crash and restart.

The IBM-Support told us, that there is no possibility to 'see' what
the db is doing exactly during the reorg, and there is no message
telling us 'table xyz is reorganized', going on with table abc. In
this case we had the possibility to start the reorg statements step by
step and we are not trapped in the asynchronous mechanism.

Many thanks for any help,
Lara
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2u*************@uni-berlin.de>...

Hi Lara,

(Sidenote: In my experience "freak" in English has a much more negative
conotation that the almost loving "computer freak" used in German :-)

At least in DB2 for LUW (is that what you're on, or DB2 for zOS?)
I have never heard of such a thing as a "super exclusive lock" and
online reorg is designed through and through to work in an isolation
than less invasive than CS (e.g. it locks only the row it operates on).

In DB2 for zOS to teh best of my knowledge online reorg makes a copy,
so I imagine ther must be a blackout period for catchup of teh copy and
the swap.

So... can you give a few more details because at least I can't make
heads or tails of it.

Cheers
Serge