Why is a REBIND necessary when changing LOCKSIZE

Douwe van Sluis

Why is a REBIND necessary when changing LOCKSIZE
Dear list,

Yesterday we had a concurrency problem. The solution was to change from LOCKSIZE ANY to LOCKSIZE ROW. The ALTER was done, space STOP/START was even done. But it seemed that is was not working. One of my colleques remembered a presentation of Bonnie Baker in where there was mentioned something about changing LOCKSIZE and REBIND. We did the REBIND and problem solved. So far so good.
We found the follow in the SQL Ref about ALTER LOCKSIZE:
=
Let S denote an SQL statement that refers to a table in the table space:

* The LOCKSIZE change affects S if S is prepared and executed after the change. This includes dynamic statements and static statements that are not bound because of VALIDATE(RUN).
* If the size specified by the new LOCKSIZE is greater than the size of the old LOCKSIZE, the change affects S if S is a static statement that is executed after the change.

The hierarchy of lock sizes, starting with the largest, is as follows:
o table space lock
o table lock (only for segmented table spaces)
o page lock, row lock, and LOB lock (which are at the same level)
* In all other cases, LOCKSIZE has no effect on S until S is rebound.
=

We understand the above. In our case we changed from page to row, so the last * applies to our case. And it works.

But then we started discussing why a REBIND would be necessary. We failed to come up with a valuable answer.

Any body out there who knows why a REBIND is necessary when changing LOCKSIZE to a smaller "locking unit"?

Vriendelijke groet,
Douwe van Sluis

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Douwe van Sluis)
Perhaps the more pertinent question is "Why is a rebind NOT necessary when changing to a larger locking 'size'"?

I think I would have expected to rebind to make ANY change to locking size take effect - I didn't realise that it was another of those "it depends" things!

It seems a little inconsistent to me

Especially when you consider that changing to a LARGER locksize will have knock-on effects to concurrency and might be a good change to delay until a scheduled rebind, whereas a change to a smaller locking unit relieves concurrency and might be expected to take effect immediately

Except there is probably another, contradictory, very good reason that escapes me (and you) for now

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Douwe van Sluis
Sent: Fri 05/12/2008 07:18
To: [login to unmask email]
Subject: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE



Dear list,

Yesterday we had a concurrency problem. The solution was to change from LOCKSIZE ANY to LOCKSIZE ROW. The ALTER was done, space STOP/START was even done. But it seemed that is was not working. One of my colleques remembered a presentation of Bonnie Baker in where there was mentioned something about changing LOCKSIZE and REBIND. We did the REBIND and problem solved. So far so good.
We found the follow in the SQL Ref about ALTER LOCKSIZE:
=
Let S denote an SQL statement that refers to a table in the table space:

* The LOCKSIZE change affects S if S is prepared and executed after the change. This includes dynamic statements and static statements that are not bound because of VALIDATE(RUN).
* If the size specified by the new LOCKSIZE is greater than the size of the old LOCKSIZE, the change affects S if S is a static statement that is executed after the change.

The hierarchy of lock sizes, starting with the largest, is as follows:
o table space lock
o table lock (only for segmented table spaces)
o page lock, row lock, and LOB lock (which are at the same level)
* In all other cases, LOCKSIZE has no effect on S until S is rebound.
=

We understand the above. In our case we changed from page to row, so the last * applies to our case. And it works.

But then we started discussing why a REBIND would be necessary. We failed to come up with a valuable answer.

Any body out there who knows why a REBIND is necessary when changing LOCKSIZE to a smaller "locking unit"?

Vriendelijke groet,
Douwe van Sluis

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms




______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Adam Baldwin

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Phil Grainger)
....I agree 100% with Phil. I've asked the question within IBM (having found no
reason why via internal searches).... I'd love to know why increased
granularity in LOCKSIZE needs a rebind to take effect...

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Bell

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Adam Baldwin)
This is a Guess - I have never seen any documentation to support it.

I think they coded the lock request into the skeleton cursor table - you
know the pseudocode that executes from the EDM pool. And the code is smart
enough to handle an upward change in locksize from row to page but not the
other way. which means the locksize is embedded in the code at bind time.

Mike
HLS technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Adam Baldwin
Sent: Friday, December 05, 2008 8:08 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

....I agree 100% with Phil. I've asked the question within IBM (having found
no
reason why via internal searches).... I'd love to know why increased
granularity in LOCKSIZE needs a rebind to take effect...

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.176 / Virus Database: 270.9.13/1828 - Release Date: 12/4/2008
9:55 PM

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Bell

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Mike Bell)
another later thought - I wonder if the lock avoidence code requires that
the lock be taken at a different point in the logic in the skeleton cursor.
I don't have enought memory or manuals to go back to what existed before
lock avoidence. That would almost make sense.

Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Douwe van Sluis
Sent: Friday, December 05, 2008 1:18 AM
To: [login to unmask email]
Subject: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

Dear list,

Yesterday we had a concurrency problem. The solution was to change from
LOCKSIZE ANY to LOCKSIZE ROW. The ALTER was done, space STOP/START was even
done. But it seemed that is was not working. One of my colleques remembered
a presentation of Bonnie Baker in where there was mentioned something about
changing LOCKSIZE and REBIND. We did the REBIND and problem solved. So far
so good.
We found the follow in the SQL Ref about ALTER LOCKSIZE:
=
Let S denote an SQL statement that refers to a table in the table space:

* The LOCKSIZE change affects S if S is prepared and executed after the
change. This includes dynamic statements and static statements that are not
bound because of VALIDATE(RUN).
* If the size specified by the new LOCKSIZE is greater than the size of
the old LOCKSIZE, the change affects S if S is a static statement that is
executed after the change.

The hierarchy of lock sizes, starting with the largest, is as follows:
o table space lock
o table lock (only for segmented table spaces)
o page lock, row lock, and LOB lock (which are at the same level)
* In all other cases, LOCKSIZE has no effect on S until S is rebound.
=

We understand the above. In our case we changed from page to row, so the
last * applies to our case. And it works.

But then we started discussing why a REBIND would be necessary. We failed to
come up with a valuable answer.

Any body out there who knows why a REBIND is necessary when changing
LOCKSIZE to a smaller "locking unit"?

Vriendelijke groet,
Douwe van Sluis

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.176 / Virus Database: 270.9.13/1828 - Release Date: 12/4/2008
8:05 AM

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Gerald Hodge

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Mike Bell)
If you examine the SMPe HOLD++ items from most DB2 maintenance, you will see
that rebinds are constantly requested. Careful reading of the requests
shows that very few of them have to do with access path changes, even though
the rebind poses that exposure. Most are for internal structures issues or
access to functions that will only be available after the maintenance
through a rebind. The easiest example of this is SPROC.

I not know the overall impact of avoiding these rebind request. I do know
that most of them are ignored.

Gerald Hodge
HLS Technologies, Inc.
www.hlstechnologies.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mike Bell
Sent: Friday, December 05, 2008 9:33 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

another later thought - I wonder if the lock avoidence code requires that
the lock be taken at a different point in the logic in the skeleton cursor.
I don't have enought memory or manuals to go back to what existed before
lock avoidence. That would almost make sense.

Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Douwe van Sluis
Sent: Friday, December 05, 2008 1:18 AM
To: [login to unmask email]
Subject: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

Dear list,

Yesterday we had a concurrency problem. The solution was to change from
LOCKSIZE ANY to LOCKSIZE ROW. The ALTER was done, space STOP/START was even
done. But it seemed that is was not working. One of my colleques remembered
a presentation of Bonnie Baker in where there was mentioned something about
changing LOCKSIZE and REBIND. We did the REBIND and problem solved. So far
so good.
We found the follow in the SQL Ref about ALTER LOCKSIZE:
=
Let S denote an SQL statement that refers to a table in the table space:

* The LOCKSIZE change affects S if S is prepared and executed after the
change. This includes dynamic statements and static statements that are not
bound because of VALIDATE(RUN).
* If the size specified by the new LOCKSIZE is greater than the size of
the old LOCKSIZE, the change affects S if S is a static statement that is
executed after the change.

The hierarchy of lock sizes, starting with the largest, is as follows:
o table space lock
o table lock (only for segmented table spaces)
o page lock, row lock, and LOB lock (which are at the same level)
* In all other cases, LOCKSIZE has no effect on S until S is rebound.
=

We understand the above. In our case we changed from page to row, so the
last * applies to our case. And it works.

But then we started discussing why a REBIND would be necessary. We failed to
come up with a valuable answer.

Any body out there who knows why a REBIND is necessary when changing
LOCKSIZE to a smaller "locking unit"?

Vriendelijke groet,
Douwe van Sluis

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.176 / Virus Database: 270.9.13/1828 - Release Date: 12/4/2008
8:05 AM

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Edward Long

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Gerald Hodge)
This is a very interesting discussion.
So, I wonder, is this relevant to LOB tablespaces? My experience, where I have altered the lob tablespace lock size to tablespace, just to unload the data with a statically bound program, is no.

But then comes the second question, so what about when the job ends and I alter the locksize back.

Any programs bound during the locksize tablespace interval would still, if this theory holds as described so far, be running at locksize tablespace despite the alter back to page or row. That would be bad.

Its not intuitive to me why this is treated differently than say a reorg or a change in page size. If I change the page size of a tablespace I don't have to rebind everything to access the data. Making the locksize parameter be treated differently is not obviously a good thing to me.


Edward Long


--- On Fri, 12/5/08, Gerald Hodge <[login to unmask email]> wrote:

> From: Gerald Hodge <[login to unmask email]>
> Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE
> To: [login to unmask email]
> Date: Friday, December 5, 2008, 12:09 PM
> If you examine the SMPe HOLD++ items from most DB2
> maintenance, you will see
> that rebinds are constantly requested. Careful reading of
> the requests
> shows that very few of them have to do with access path
> changes, even though
> the rebind poses that exposure. Most are for internal
> structures issues or
> access to functions that will only be available after the
> maintenance
> through a rebind. The easiest example of this is SPROC.
>
> I not know the overall impact of avoiding these rebind
> request. I do know
> that most of them are ignored.
>
> Gerald Hodge
> HLS Technologies, Inc.
> www.hlstechnologies.com
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf
> Of Mike Bell
> Sent: Friday, December 05, 2008 9:33 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Why is a REBIND necessary when
> changing LOCKSIZE
>
> another later thought - I wonder if the lock avoidence code
> requires that
> the lock be taken at a different point in the logic in the
> skeleton cursor.
> I don't have enought memory or manuals to go back to
> what existed before
> lock avoidence. That would almost make sense.
>
> Mike
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf
> Of Douwe van Sluis
> Sent: Friday, December 05, 2008 1:18 AM
> To: [login to unmask email]
> Subject: [DB2-L] Why is a REBIND necessary when changing
> LOCKSIZE
>
> Dear list,
>
> Yesterday we had a concurrency problem. The solution was to
> change from
> LOCKSIZE ANY to LOCKSIZE ROW. The ALTER was done, space
> STOP/START was even
> done. But it seemed that is was not working. One of my
> colleques remembered
> a presentation of Bonnie Baker in where there was mentioned
> something about
> changing LOCKSIZE and REBIND. We did the REBIND and problem
> solved. So far
> so good.
> We found the follow in the SQL Ref about ALTER LOCKSIZE:
> =
> Let S denote an SQL statement that refers to a table in the
> table space:
>
> * The LOCKSIZE change affects S if S is prepared and
> executed after the
> change. This includes dynamic statements and static
> statements that are not
> bound because of VALIDATE(RUN).
> * If the size specified by the new LOCKSIZE is greater
> than the size of
> the old LOCKSIZE, the change affects S if S is a static
> statement that is
> executed after the change.
>
> The hierarchy of lock sizes, starting with the
> largest, is as follows:
> o table space lock
> o table lock (only for segmented table spaces)
> o page lock, row lock, and LOB lock (which are at
> the same level)
> * In all other cases, LOCKSIZE has no effect on S until
> S is rebound.
> =
>
> We understand the above. In our case we changed from page
> to row, so the
> last * applies to our case. And it works.
>
> But then we started discussing why a REBIND would be
> necessary. We failed to
> come up with a valuable answer.
>
> Any body out there who knows why a REBIND is necessary when
> changing
> LOCKSIZE to a smaller "locking unit"?
>
> Vriendelijke groet,
> Douwe van Sluis
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While
> at the site, you
> can also access the IDUG Online Learning Center, Tech
> Library and Code
> Place, see the latest IDUG conference information and much
> more. If you
> have not yet signed up for Basic Membership in IDUG,
> available at no cost,
> click on Member Services at http://www.idug.org/lsms
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com
> Version: 8.0.176 / Virus Database: 270.9.13/1828 - Release
> Date: 12/4/2008
> 8:05 AM
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While
> at the site, you
> can also access the IDUG Online Learning Center, Tech
> Library and Code
> Place, see the latest IDUG conference information and much
> more. If you
> have not yet signed up for Basic Membership in IDUG,
> available at no cost,
> click on Member Services at http://www.idug.org/lsms
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The DB2-L list archives, FAQ, and delivery
> preferences are at http://www.idug.org/lsidug under the
> Listserv tab. While at the site, you can also access the
> IDUG Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information and much more.
> If you have not yet signed up for Basic Membership in IDUG,
> available at no cost, click on Member Services at
> http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Adam Baldwin

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Edward Long)
....I'm still waiting for a detailed explanation as to why the rebind is necessary
when the granularity of locksize is increased.... Why does an alter in one
direction require a rebind whilst in the other it's not necessary. I've continued
looking elsewhere for an explanation but without success.
Maybe someone has responded to Douwe off list?

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Kimble

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Adam Baldwin)
I haven't seen one either. The one thought I had was that standard lock
escalation types of processes/code can be used for the row to page, page to
table changes. There is no standard process to go the other direction.

Mark A Kimble
Consultant - Database Management | HSBC Technology & Services
100 Mittel Drive, Wood Dale, IL 60191
__________________________________________________________

Phone 630-5213287
Mobile [login to unmask email]
Email [login to unmask email]
Web Site HTSN - Data Center - Data Mgmt
__________________________________________________________



Adam Baldwin
<[login to unmask email]
BM.COM> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: Why is a REBIND necessary when
changing LOCKSIZE

12/10/2008 08:17
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






....I'm still waiting for a detailed explanation as to why the rebind is
necessary
when the granularity of locksize is increased.... Why does an alter in one
direction require a rebind whilst in the other it's not necessary. I've
continued
looking elsewhere for an explanation but without success.
Maybe someone has responded to Douwe off list?

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms
-----------------------------------------
******************************************************************
This message originated from the Internet. Its originator may or
may not be who they claim to be and the information contained in
the message and any attachments may or may not be accurate.
******************************************************************


-----------------------------------------
******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Mark Kimble)
It looks like one of these "internal workings of DB2 are not really open for discussion" type of questions. And, to be fair, I can understand why IBM are reluctant to allow too much of the internals of DB2 to be discussed in public (otherwise there'd be a DB2 POP manual)

I'm with Adam on wanting to understand how EVERYTHING works (or doesn't) and the apparent answer of "because" sounds too much like my mother to be of any use :)

But it looks like that's the answer we are stuck with (well, "It depends" doesn't really fit the question this time does it!!)

Phil Grainger
CA
Senior Principal Product Manager
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Adam Baldwin
Sent: 10 December 2008 14:17
To: [login to unmask email]
Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

....I'm still waiting for a detailed explanation as to why the rebind is necessary
when the granularity of locksize is increased.... Why does an alter in one
direction require a rebind whilst in the other it's not necessary. I've continued
looking elsewhere for an explanation but without success.
Maybe someone has responded to Douwe off list?

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Chad Walmer

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Phil Grainger)
Here is my understanding taken directly from an explanation provided by Bonnie Baker:

When you alter from a locksize of page to table/tablespace, the package is invalidated and will auto rebind (if you have that enabled) to pick up the new locksize. When you alter from a locksize of table/tablespace to page, the package is not invalidated and it continues to lock at the tablespace level. The reasoning behind this is the larger lock size ensures that there is not an integrity exposure (although you may have a concurrency problem.) But when you go from page to tablespace, you need to immediately start using the more restrictive tablespace lock or you will have an integrity exposure with other threads.

So as Mike Bell and others have suggested, the lock size of the object is stored in the package (CT/PT) at bind time and is part of the instructions sent to the RDS when accessing the object.

Chad Walmer

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Adam Baldwin
Sent: Wednesday, December 10, 2008 9:17 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

....I'm still waiting for a detailed explanation as to why the rebind is necessary
when the granularity of locksize is increased.... Why does an alter in one
direction require a rebind whilst in the other it's not necessary. I've continued
looking elsewhere for an explanation but without success.
Maybe someone has responded to Douwe off list?

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Disclaimer: This e-mail message is intended only for the personal use of
the recipient(s) named above. If you are not an intended recipient, you
may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete
the original message.

This e-mail expresses views only of the sender, which are not to be
attributed to Rite Aid Corporation and may not be copied or distributed
without this statement.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Adam Baldwin

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Chad Walmer)
I know that this information is stored in the SPT and I understand Bonnie's
reasoning but it still leaves me with a question. You go from page to
tablespace and no binds / rebinds are necessary - I'm talking about explicit
rather than auto. But when you go from tablespace to page, only those
packages that are explicitly rebound use the new lock size. What I question is
that this leaves a situation where LOCKRULE in SYSTABLESPACE can only be
guaranteed to apply to those packages that have been rebound since the last
alter, and, as not all alters require a rebind, this leaves potentially incomplete
info in the catalog.

On the other hand, it's been a long day already and I'm sure that I'm missing
the obvious!

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Douwe van Sluis

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Adam Baldwin)
Hi Adam,

No answer so far that explain why.
The question that still puzzles me is "why is LOCKSIZE kind of locked
into the bind process".
I always try to explain things by the way DB2 is working, but this one
leaves me puzzled.

If I get an answer off-list, I will post the answer on the list.

Vriendelijke groet,
Douwe van Sluis



-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Adam Baldwin
Verzonden: woensdag 10 december 2008 15:17
Aan: [login to unmask email]
Onderwerp: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE


....I'm still waiting for a detailed explanation as to why the rebind is
necessary
when the granularity of locksize is increased.... Why does an alter in
one
direction require a rebind whilst in the other it's not necessary. I've
continued
looking elsewhere for an explanation but without success.
Maybe someone has responded to Douwe off list?

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Chad Walmer

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Douwe van Sluis)
Adam,
I suspect you'll never get the reason that it was coded this way unless the developer speaks up (assuming they are even around or remember :)). I think you might be getting too caught up in the way it works. Once you understand that it is stored in the static package, then it doesn't really matter what is in the catalog (except for dynamic queries and I suspect those that are placed in the dynamic statement cache may work the same way as well.) So, if you ever change the locksize and you want to guarantee that everything is using the value stored in the catalog, then rebind all dependent packages.

Ultimately, I guess what you are saying is that you would prefer that the dependent packages get invalidated no matter which way you go. The developer obviously felt at the time that it was better to keep running with table locks and not invalidate the package when altering to a less restrictive row or page lock. Neither seems wrong or right to me - it's just the way it is.

Chad

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Adam Baldwin
Sent: Wednesday, December 10, 2008 12:43 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

I know that this information is stored in the SPT and I understand Bonnie's
reasoning but it still leaves me with a question. You go from page to
tablespace and no binds / rebinds are necessary - I'm talking about explicit
rather than auto. But when you go from tablespace to page, only those
packages that are explicitly rebound use the new lock size. What I question is
that this leaves a situation where LOCKRULE in SYSTABLESPACE can only be
guaranteed to apply to those packages that have been rebound since the last
alter, and, as not all alters require a rebind, this leaves potentially incomplete
info in the catalog.

On the other hand, it's been a long day already and I'm sure that I'm missing
the obvious!

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Disclaimer: This e-mail message is intended only for the personal use of
the recipient(s) named above. If you are not an intended recipient, you
may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete
the original message.

This e-mail expresses views only of the sender, which are not to be
attributed to Rite Aid Corporation and may not be copied or distributed
without this statement.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Edward Long

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Chad Walmer)
To me, I don't see why locksize is stored in the package at all. Page size isn't and it has, arguably, more of an impact on performance than locksize.

The current design, assuming our understanding is correct, violates the basic design principal of transparency. This is true, in my opinion, because the catalog may not contain complete and accurate information about this rather important parameter yet it appears to.

The current design requires us to analyze all zillion of our packages to see when they were bound.


Edward Long


--- On Wed, 12/10/08, Chad A. Walmer <[login to unmask email]> wrote:

> From: Chad A. Walmer <[login to unmask email]>
> Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE
> To: [login to unmask email]
> Date: Wednesday, December 10, 2008, 3:29 PM
> Adam,
> I suspect you'll never get the reason that it was coded
> this way unless the developer speaks up (assuming they are
> even around or remember :)). I think you might be getting
> too caught up in the way it works. Once you understand that
> it is stored in the static package, then it doesn't
> really matter what is in the catalog (except for dynamic
> queries and I suspect those that are placed in the dynamic
> statement cache may work the same way as well.) So, if you
> ever change the locksize and you want to guarantee that
> everything is using the value stored in the catalog, then
> rebind all dependent packages.
>
> Ultimately, I guess what you are saying is that you would
> prefer that the dependent packages get invalidated no matter
> which way you go. The developer obviously felt at the time
> that it was better to keep running with table locks and not
> invalidate the package when altering to a less restrictive
> row or page lock. Neither seems wrong or right to me -
> it's just the way it is.
>
> Chad
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Adam Baldwin
> Sent: Wednesday, December 10, 2008 12:43 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Why is a REBIND necessary when
> changing LOCKSIZE
>
> I know that this information is stored in the SPT and I
> understand Bonnie's
> reasoning but it still leaves me with a question. You go
> from page to
> tablespace and no binds / rebinds are necessary - I'm
> talking about explicit
> rather than auto. But when you go from tablespace to page,
> only those
> packages that are explicitly rebound use the new lock size.
> What I question is
> that this leaves a situation where LOCKRULE in
> SYSTABLESPACE can only be
> guaranteed to apply to those packages that have been
> rebound since the last
> alter, and, as not all alters require a rebind, this leaves
> potentially incomplete
> info in the catalog.
>
> On the other hand, it's been a long day already and
> I'm sure that I'm missing
> the obvious!
>
> Cheers, Adam
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The DB2-L list archives, FAQ, and delivery
> preferences are at http://www.idug.org/lsidug under the
> Listserv tab. While at the site, you can also access the
> IDUG Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information and much more.
> If you have not yet signed up for Basic Membership in IDUG,
> available at no cost, click on Member Services at
> http://www.idug.org/lsms
>
> Disclaimer: This e-mail message is intended only for the
> personal use of
> the recipient(s) named above. If you are not an intended
> recipient, you
> may not review, copy or distribute this message. If you
> have received this
> communication in error, please notify us immediately by
> e-mail and delete
> the original message.
>
> This e-mail expresses views only of the sender, which are
> not to be
> attributed to Rite Aid Corporation and may not be copied or
> distributed
> without this statement.
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The DB2-L list archives, FAQ, and delivery
> preferences are at http://www.idug.org/lsidug under the
> Listserv tab. While at the site, you can also access the
> IDUG Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information and much more.
> If you have not yet signed up for Basic Membership in IDUG,
> available at no cost, click on Member Services at
> http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Adam Baldwin

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Edward Long)
Thanks for all of the various comments. I may be getting too caught up in this
but then I like to know how and why things work as they do. One of our
clients requested and alter of locksize from ANY to ROW and they wanted to
know why the change implicated a REBIND..... and I wasn't satisfied with
saying "Because it does".

This prompted me to think further. I knew, and accept, that the lock size is
stored at bind time. I also accept that one should do the rebinds after the
alter in either direction....but I work in the real world. The fact that the
catalog as the visible repository of subsystem information may
hold "inaccurate" data worries me. I'd like to be able to determine the locksize
from the catalog and "know" that all packages with a dependency on that
object are using it.

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Larry Jardine

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Adam Baldwin)
I think you need to alter your view of the catalog. There are several
values in the catalog that may or may not reflect reality depending
external factors (like REORG or RUNSTATS). A few examples from
SYSIBM.SYSTABLEPART:

Column When the value reflects reality
PQTY after REORG
SQTY true for the "next" extent; not past extents
CARDF after runstats
COMPRESS after REORG

Larry Jardine
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Adam Baldwin
Sent: Thursday, December 11, 2008 2:44 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

Thanks for all of the various comments. I may be getting too caught up
in this
but then I like to know how and why things work as they do. One of our
clients requested and alter of locksize from ANY to ROW and they wanted
to
know why the change implicated a REBIND..... and I wasn't satisfied with

saying "Because it does".

This prompted me to think further. I knew, and accept, that the lock
size is
stored at bind time. I also accept that one should do the rebinds after
the
alter in either direction....but I work in the real world. The fact that
the
catalog as the visible repository of subsystem information may
hold "inaccurate" data worries me. I'd like to be able to determine the
locksize
from the catalog and "know" that all packages with a dependency on that
object are using it.

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Bell

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Larry Jardine)
What DB2 runs from is the directory - execution does not require the catalog
at all. I assume you have never had a DBD get out of sync with the catalog
- it was caused by some MVS paging issues - I think and then we got to spend
some quality time with IBM support. Because it was a test subsystem, it was
easier to drop and rebuild but we had to use REPAIR to rebuild the DBD to
even drop everything.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Adam Baldwin
Sent: Thursday, December 11, 2008 1:44 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE

Thanks for all of the various comments. I may be getting too caught up in
this but then I like to know how and why things work as they do. One of our
clients requested and alter of locksize from ANY to ROW and they wanted to
know why the change implicated a REBIND..... and I wasn't satisfied with
saying "Because it does".

This prompted me to think further. I knew, and accept, that the lock size is
stored at bind time. I also accept that one should do the rebinds after the
alter in either direction....but I work in the real world. The fact that the
catalog as the visible repository of subsystem information may hold
"inaccurate" data worries me. I'd like to be able to determine the locksize
from the catalog and "know" that all packages with a dependency on that
object are using it.

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Edward Long

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Mike Bell)
Even if those are off, it doesn't create a help desk event. If Locksize is off, it can and will.

Edward Long


--- On Thu, 12/11/08, Jardine, Lawrence J <[login to unmask email]> wrote:

> From: Jardine, Lawrence J <[login to unmask email]>
> Subject: Re: [DB2-L] Why is a REBIND necessary when changing LOCKSIZE
> To: [login to unmask email]
> Date: Thursday, December 11, 2008, 7:58 AM
> I think you need to alter your view of the catalog. There
> are several
> values in the catalog that may or may not reflect reality
> depending
> external factors (like REORG or RUNSTATS). A few examples
> from
> SYSIBM.SYSTABLEPART:
>
> Column When the value reflects reality
> PQTY after REORG
> SQTY true for the "next" extent; not past
> extents
> CARDF after runstats
> COMPRESS after REORG
>
> Larry Jardine
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On
> Behalf Of Adam Baldwin
> Sent: Thursday, December 11, 2008 2:44 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Why is a REBIND necessary when
> changing LOCKSIZE
>
> Thanks for all of the various comments. I may be getting
> too caught up
> in this
> but then I like to know how and why things work as they do.
> One of our
> clients requested and alter of locksize from ANY to ROW and
> they wanted
> to
> know why the change implicated a REBIND..... and I
> wasn't satisfied with
>
> saying "Because it does".
>
> This prompted me to think further. I knew, and accept, that
> the lock
> size is
> stored at bind time. I also accept that one should do the
> rebinds after
> the
> alter in either direction....but I work in the real world.
> The fact that
> the
> catalog as the visible repository of subsystem information
> may
> hold "inaccurate" data worries me. I'd like
> to be able to determine the
> locksize
> from the catalog and "know" that all packages
> with a dependency on that
> object are using it.
>
> Cheers, Adam
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The
> DB2-L list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While
> at the site,
> you can also access the IDUG Online Learning Center, Tech
> Library and
> Code Place, see the latest IDUG conference information and
> much more.
> If you have not yet signed up for Basic Membership in IDUG,
> available at
> no cost, click on Member Services at
> http://www.idug.org/lsms
> This e-mail may contain confidential or privileged
> information. If
> you think you have received this e-mail in error, please
> advise the
> sender by reply e-mail and then delete this e-mail
> immediately.
> Thank you. Aetna
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The DB2-L list archives, FAQ, and delivery
> preferences are at http://www.idug.org/lsidug under the
> Listserv tab. While at the site, you can also access the
> IDUG Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information and much more.
> If you have not yet signed up for Basic Membership in IDUG,
> available at no cost, click on Member Services at
> http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Adam Baldwin

Re: Why is a REBIND necessary when changing LOCKSIZE
(in response to Edward Long)
Ok, ok, to stop a deluge of comments re the catalog / directory I accept that
my comment was a bit "wooly". Obviously stats etc are not necessarily
accurate... My comment re locksize is that we're talking about an attribute
defined with DDL. In the case of statistics we know that a plan/package
bound prior to the current statistics wont be using them. In the case of a
tablespace with LOCKSIZE altered to give more granularity, we don't know
which plans/packages bound before the ALTEREDTS are using the current
locksize. (Just by looking at the catalog). It's a moot point.

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms