Reclaiming space for tablespaces in temp database

Mark McCormack

Reclaiming space for tablespaces in temp database
Someone in my company has noticed that one of the tablespaces in our temp
database has grown to about 10 times the size of the other temp
tablespaces. We suspect that this giant space was used once during some
special processing.

The question is how to reclaim the dasd space that is not needed now.
Reorg and copy/recover are not allowed on temp tablespaces. The only thing
we can think of is drop/recreate. Since that would put an x lock on the
DBD, it could only be done at a time where standalone access is possible.

Does anyone else try to reclaim space in temp tablespaces ?
If so, how do you do it ?
Any advice would be appreciated.

Mark

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Reclaiming space for tablespaces in temp database
(in response to Mark McCormack)
DROP/RECREATE is the only way I'm aware of, too.

REORG isn't allowed on the TEMP DB tablespaces, and LOAD can't work
because there are no catalogued tables in the tablespaces. DELETE/DEFINE
should cause problems because the tablespace pages are (presumably)
formatted by DB2.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






Mark McCormack <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/21/2004 02:40 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Reclaiming space for tablespaces in temp database


Someone in my company has noticed that one of the tablespaces in our temp
database has grown to about 10 times the size of the other temp
tablespaces. We suspect that this giant space was used once during some
special processing.

The question is how to reclaim the dasd space that is not needed now.
Reorg and copy/recover are not allowed on temp tablespaces. The only
thing
we can think of is drop/recreate. Since that would put an x lock on the
DBD, it could only be done at a time where standalone access is possible.

Does anyone else try to reclaim space in temp tablespaces ?
If so, how do you do it ?
Any advice would be appreciated.

Mark

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: Reclaiming space for tablespaces in temp database
(in response to Philip Sevetson)
And the way to prevent it is too not allow secondary extents on them to
begin with. We went with creating 20 temp tablespaces with 0 for the
secqty. Some others on the list have said the same for their systems,
except let one be able to take extents. As long as you are not
experiencing continued growth is reclaiming that space a very big deal?
Do you need the dasd space bad enough to drop and recreate them or can
you live with it?

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/21/04 2:55:46
PM >>>

DROP/RECREATE is the only way I'm aware of, too.

REORG isn't allowed on the TEMP DB tablespaces, and LOAD can't work
because there are no catalogued tables in the tablespaces.
DELETE/DEFINE should cause problems because the tablespace pages are
(presumably) formatted by DB2.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]





Mark McCormack <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/21/2004 02:40 PM
Please respond to DB2 Database Discussion list at IDUG

To: [login to unmask email]
cc:
Subject: [DB2-L] Reclaiming space for tablespaces in
temp database


Someone in my company has noticed that one of the tablespaces in our
temp
database has grown to about 10 times the size of the other temp
tablespaces. We suspect that this giant space was used once during
some
special processing.

The question is how to reclaim the dasd space that is not needed now.
Reorg and copy/recover are not allowed on temp tablespaces. The only
thing
we can think of is drop/recreate. Since that would put an x lock on
the
DBD, it could only be done at a time where standalone access is
possible.

Does anyone else try to reclaim space in temp tablespaces ?
If so, how do you do it ?
Any advice would be appreciated.

Mark

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

"MMS <fhmail.firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris White

Re: Reclaiming space for tablespaces in temp database
(in response to Dave Nance)
To answer your main question about reclaiming the space for just one of
your temp tablespaces...

1. -STOP DB(DSNDB07) SPACE(<just the one with extents>)
2. Delete/reallocate the dataset using non-DB2 methods (IDCAMS or SMS
managed, whatever)
3. -START DB(DSNDB07) SPACE(whatever the affected TS was)

The point is... even though you can't get DB2 to reallocate the problem TS
while people are using DSNDB07, you can take the individual problem TS off-
line (i.e. STOP it, but don't DROP it) and do whatever you want to
reallocate the underlying dataset, and then bring it back on-line for use
(i.e. START it). This process leaves the sibling temp tablespaces
available for use while you MANUALLY reallocate the one dataset you want to
work on.

To expand on David Nance's comments... we do everything he said plus we
CREATEd several "extra" or dummy temp tablespaces that have no underlying
dataset (we CREATEd them, STOP'd them and deleted their datasets, then left
the TS's in STOP status). Those TS's are "on deck" waiting for when we
might need them. In a crunch, I just allocate a linear dataset with the
right name, and then START the matching TS. Voila! More capacity without
a DSNDB07 outage. All the temp TS's (or all but one if you still prefer
the possibility for expansion) are defined with secondary quantity of
zero. Even without the "dummy" or on-deck temp TS's, you can
STOP/REALLOCATE/START any individal temp TS without dropping it.

These techniques have worked well in a very busy production environment for
many years.

Chris White
Sr. Technology Specialist
Caterpillar Inc.


These are just my opinions, not my employer's and blah, blah, blah...
Merry Christmas or whatever you celebrate (or not) anyway!

===<snip>===>

On Tue, 21 Dec 2004 15:52:01 -0500, David Nance <[login to unmask email]> wrote:

>And the way to prevent it is too not allow secondary extents on them to
>begin with. We went with creating 20 temp tablespaces with 0 for the
>secqty. Some others on the list have said the same for their systems,
>except let one be able to take extents. As long as you are not
>experiencing continued growth is reclaiming that space a very big deal?
>Do you need the dasd space bad enough to drop and recreate them or can
>you live with it?
>
>Dave Nance
>First Health Services, Corp.
>(804)527-6841
>
>
>>>> [login to unmask email] 12/21/04 2:55:46
>PM >>>
>
>DROP/RECREATE is the only way I'm aware of, too.
>
>REORG isn't allowed on the TEMP DB tablespaces, and LOAD can't work
>because there are no catalogued tables in the tablespaces.
>DELETE/DEFINE should cause problems because the tablespace pages are
>(presumably) formatted by DB2.
>
>--Phil Sevetson
>Database Administration
>Wakefern Food Corporation CISD
>mailto:[login to unmask email]
>
>
>
>
>
>Mark McCormack <[login to unmask email]>
>Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>12/21/2004 02:40 PM
>Please respond to DB2 Database Discussion list at IDUG
>
> To: [login to unmask email]
> cc:
> Subject: [DB2-L] Reclaiming space for tablespaces in
>temp database
>
>
>Someone in my company has noticed that one of the tablespaces in our
>temp
>database has grown to about 10 times the size of the other temp
>tablespaces. We suspect that this giant space was used once during
>some
>special processing.
>
>The question is how to reclaim the dasd space that is not needed now.
>Reorg and copy/recover are not allowed on temp tablespaces. The only
>thing
>we can think of is drop/recreate. Since that would put an x lock on
>the
>DBD, it could only be done at a time where standalone access is
>possible.
>
>Does anyone else try to reclaim space in temp tablespaces ?
>If so, how do you do it ?
>Any advice would be appreciated.
>
>Mark
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm
>
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm
>
>"MMS <fhmail.firsthealth.com>" made the following annotations.
>---------------------------------------------------------------------------
---
>This message, including any attachments, is intended solely for the use
>of the named recipient(s) and may contain confidential and/or
>privileged information. Any unauthorized review, use, disclosure or
>distribution of this communication(s) is expressly prohibited.
>If you are not the intended recipient, please contact the sender by
>reply e-mail and destroy any and all copies of the original message.
>Thank you.
>
===
=
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark McCormack

Reclaiming space for tablespaces in temp database
(in response to Chris White)
Chris,

Thanks for your reply. Let me make sure I am not confused. I am familiar
with your suggestions as they apply to db=DSNDB07, which is used for
created temporary tables. I am asking about a temp database (created AS
TEMP), used for declared temporary tables.

We use secqty=0 for DSNDB07. Things can overflow from one ts to another.
It is different for a declared temporary table in a temp database. It must
exist entirely in one ts. So we must allow expansion when necessary. We
have had a one-time conversion which used a giant declared temporary table,
where the ts went into many secondary extents. We want to reclaim the dasd
space that is no longer being used.

If I were to -stop the ts in the temp database, delete/redefine the vsam
data set, -start the ts again, then I would give an unformatted vsam data
set to DB2. Will DB2 handle this correctly? Or will it balk when it
cannot find header and space map pages?

Mark

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Reclaiming space for tablespaces in temp database
(in response to Mark McCormack)
Mark,

I'm pretty sure that we've been seeing Declared Temporary tables
overflowing from one tablespace to another in our Temp DB... we've had
full-tablespace conditions which can't be explained any other way.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






Mark McCormack <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/22/2004 11:18 AM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Reclaiming space for tablespaces in temp database


Chris,

Thanks for your reply. Let me make sure I am not confused. I am familiar
with your suggestions as they apply to db=DSNDB07, which is used for
created temporary tables. I am asking about a temp database (created AS
TEMP), used for declared temporary tables.

We use secqty=0 for DSNDB07. Things can overflow from one ts to another.
It is different for a declared temporary table in a temp database. It
must
exist entirely in one ts. So we must allow expansion when necessary. We
have had a one-time conversion which used a giant declared temporary
table,
where the ts went into many secondary extents. We want to reclaim the
dasd
space that is no longer being used.

If I were to -stop the ts in the temp database, delete/redefine the vsam
data set, -start the ts again, then I would give an unformatted vsam data
set to DB2. Will DB2 handle this correctly? Or will it balk when it
cannot find header and space map pages?

Mark

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark McCormack

Reclaiming space for tablespaces in temp database
(in response to Philip Sevetson)
Phil,

Thanks for your advice, and I don't want to beat this topic to death. My
statement about table overflow is based on what I found in the DB2 Admin
Guide on the IBM web site. I don't normally quote from IBM manuals, but it
states it better than I can.

Chris,

Are you sure that the vsam delete / define process works for temp
databases, or is it only usable for DSNDB07 ? It would be more convenient
than drop / recreate.

Mark
------------------------------------------------------------------------------------------------------
DB2 UDB for OS/390 and z/OS V7 Administration Guide June, 2003
2.3.3 Distinctions between DB2 base tables and temporary tables
The following comes from table 11

under declared temporary tables
------------------------------
The table is stored in segmented table spaces in the TEMP database (a
database that is defined AS TEMP). The table cannot span table spaces.
Therefore, the size of the table is limited by the table space size (as
determined by the primary and secondary space allocation values specified
for the table space's data sets) and the shared usage of the table space
among multiple users. When the table space is full, an error occurs for the
SQL operation.

under created temporary tables
-------------------------------
The table is stored in table spaces in the work file database. The table
can span work file table spaces. Therefore, the size of the table is
limited by the number of available work file table spaces, the size of
each table space and the number of data set extents that are allowed for
the table spaces. Unlike the other types of tables, created temporary
tables do not reach size limitations as easily.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris White

Re: Reclaiming space for tablespaces in temp database
(in response to Mark McCormack)
Mark,

I'm the confused one. You are right... I was talking about DSNDB07. Sorry
about that.

Chris

===<snip>===>
On Wed, 22 Dec 2004 11:18:18 -0500, Mark McCormack
<[login to unmask email]> wrote:

>Chris,
>
>Thanks for your reply. Let me make sure I am not confused. I am familiar
>with your suggestions as they apply to db=DSNDB07, which is used for
>created temporary tables. I am asking about a temp database (created AS
>TEMP), used for declared temporary tables.
>
>We use secqty=0 for DSNDB07. Things can overflow from one ts to another.
>It is different for a declared temporary table in a temp database. It must
>exist entirely in one ts. So we must allow expansion when necessary. We
>have had a one-time conversion which used a giant declared temporary table,
>where the ts went into many secondary extents. We want to reclaim the dasd
>space that is no longer being used.
>
>If I were to -stop the ts in the temp database, delete/redefine the vsam
>data set, -start the ts again, then I would give an unformatted vsam data
>set to DB2. Will DB2 handle this correctly? Or will it balk when it
>cannot find header and space map pages?
>
>Mark
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm