Plans and Tablespaces

[login to unmask email]

Plans and Tablespaces
Can someone please give me an example of when a plan would be dependent on
a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a
tablespace, but I don't understand why. I understand the dependency on
views and tables because of the SQL in the plan, but not tablespaces.

Thanks,

Bill Moss
Northeast Utilities

Wayne Driscoll

Re: Plans and Tablespaces
(in response to mosswr@NU.COM)
A plan should be dependant upon all tablespaces that contain tables that the
plan is dependent upon. SYSPLANDEP is used to mark plans invalid when a
dependent object is dropped. Since a TS drop also drops the tables, the
plans will be invalidated. This begs the question regarding why SYSPLANDEP
doesn't list databases, but that one I can't answer.
Wayne Driscoll
Product Developer
Quest Software Inc.
[login to unmask email]
NOTE: All opinions are strictly my own.


-----Original Message-----
From: [login to unmask email] <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: [login to unmask email] <[login to unmask email]>
Date: Tuesday, October 05, 1999 8:43 AM
Subject: [DB2-L] Plans and Tablespaces


>Can someone please give me an example of when a plan would be dependent on
>a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a
>tablespace, but I don't understand why. I understand the dependency on
>views and tables because of the SQL in the plan, but not tablespaces.
>
>Thanks,
>
>Bill Moss
>Northeast Utilities
>

Peter Broad

Re: Plans and Tablespaces
(in response to Wayne Driscoll)
Bill;

I would think the plan is dependent upon the tablespace because that is
the physical object which contains one or more of your tables.

Peter

[login to unmask email] wrote:

> Can someone please give me an example of when a plan would be dependent on
> a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a
> tablespace, but I don't understand why. I understand the dependency on
> views and tables because of the SQL in the plan, but not tablespaces.
>
> Thanks,
>
> Bill Moss
> Northeast Utilities

[login to unmask email]

Re: Plans and Tablespaces
(in response to Peter Broad)
Hi Bill,

If you drop a tablespace, you also drop the table/view thereby
invalidating the plan/package.

Regards,

Michael Levine
Premier Data Services, Inc.


>Can someone please give me an example of when a plan would be dependent on
>a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a
>tablespace, but I don't understand why. I understand the dependency on
>views and tables because of the SQL in the plan, but not tablespaces.
>
>Thanks,
>
>Bill Moss
>Northeast Utilities
>
>

sanjay jain

Re: Plans and Tablespaces
(in response to Mike_Levine@TEKHELP.NET)
Bill,

If you drop the tablespace, then the table/views go too and the plan become
invalid. Hence the dependency....This is my understanding.

Sanjay

-----Original Message-----
From: [login to unmask email] <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: [login to unmask email] <[login to unmask email]>
Date: Tuesday, October 05, 1999 3:48 PM
Subject: Plans and Tablespaces


>Can someone please give me an example of when a plan would be dependent on
>a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a
>tablespace, but I don't understand why. I understand the dependency on
>views and tables because of the SQL in the plan, but not tablespaces.
>
>Thanks,
>
>Bill Moss
>Northeast Utilities

Ran Abeykoon

Re: Plans and Tablespaces
(in response to sanjay jain)
Bill,

In additions to the reasons that Wayne Driscoll pointed out, one other reason
that tablespaces are recorded in sysibm.sysplandep or sysibm.syspackdep table is
as follows. As per Section 5.7.5.5.1 of DB2 version 4.1 Administration Guide,
Modes of Locks Acquired for SQL Statements are dependent on the value of
LOCKSIZE for the target table.
Also, as per section 4.3.3.3 of DB2 Version 4.1 Release Guide, If you alter the
lock size for a table space, it is not necessary to rebind plans that access its
tables. But rebinding might be desirable to let DB2 consider the new lock size
when choosing an optimal access path.

Regards,

Ran Abeykoon
Technical Consultant/AMPLUS



Date: Tue, 5 Oct 1999 09:12:51 -0400
From: [login to unmask email]
Subject: Plans and Tablespaces

Can someone please give me an example of when a plan would be dependent on
a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a
tablespace, but I don't understand why. I understand the dependency on
views and tables because of the SQL in the plan, but not tablespaces.

Thanks,

Bill Moss
Northeast Utilities