DB2 V8 on Z/OS 1.9. space map issues

William Proctor

DB2 V8 on Z/OS 1.9. space map issues
Good Morning,

Thanks for all replies in advance. When doing reorg's this
weekend we ran out of space in the spacemap for one of our partitioned
dataset. The problem has been resolved but my question is how can we
tell in advance on how much of the space map is free? Are there queries
we can run to find out and keep track of this?









Bill Proctor

DB2 DBA

DB2 UDB V8 Database Administration for Z/OS

TG

Round Rock, TX 78683-3100

(800) 252-9743, ext. 4847

www.tgslc.org











______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Roger Miller

Re: DB2 V8 on Z/OS 1.9. space map issues
(in response to William Proctor)
What was the reason code for the situation? Having all of the messages and
full formatting of SQLDA makes this question possible to answer. When the
space map is full, DB2 allocates a new space map page. So this is trickier.

If you can allocate space, then you don't worry about space map pages.

Roger Miller, DB2 for z/OS

On Mon, 12 Jan 2009 09:38:34 -0600, Proctor, William
<[login to unmask email]> wrote:

>Good Morning,
>
> Thanks for all replies in advance. When doing reorg's this
>weekend we ran out of space in the spacemap for one of our partitioned
>dataset. The problem has been resolved but my question is how can we
>tell in advance on how much of the space map is free? Are there queries
>we can run to find out and keep track of this?
>
>
>
>
>
>
>
>
>
>Bill Proctor
>
>DB2 DBA
>
>DB2 UDB V8 Database Administration for Z/OS
>
>TG
>
>Round Rock, TX 78683-3100
>
>(800) 252-9743, ext. 4847
>
>www.tgslc.org
>
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

William Proctor

Re: DB2 V8 on Z/OS 1.9. space map issues
(in response to Roger Miller)
We were reorganizing a 14 partition table with 136 million rows in it. The reorg failed indicating that the page being added exceeded the spacemap page's capacity. I reduced the pctfree and the the reorg completed ok. I'm also expanding the number of partitions this weekend which will solve the problem of exceeding the space maps capacity. We have several tables that are pretty large for us and 3 of them we only reorganize when we have to. My concern is that during the week we could run into this problem causing an outage. I want to create an automated or semi automated check that will alert us if we are getting close to any limits like this so that we can avoid an outage.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Roger Miller
Sent: Tuesday, January 13, 2009 8:16 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V8 on Z/OS 1.9. space map issues

What was the reason code for the situation? Having all of the messages and
full formatting of SQLDA makes this question possible to answer. When the
space map is full, DB2 allocates a new space map page. So this is trickier.

If you can allocate space, then you don't worry about space map pages.

Roger Miller, DB2 for z/OS

On Mon, 12 Jan 2009 09:38:34 -0600, Proctor, William
<[login to unmask email]> wrote:

>Good Morning,
>
> Thanks for all replies in advance. When doing reorg's this
>weekend we ran out of space in the spacemap for one of our partitioned
>dataset. The problem has been resolved but my question is how can we
>tell in advance on how much of the space map is free? Are there queries
>we can run to find out and keep track of this?
>
>
>
>
>
>
>
>
>
>Bill Proctor
>
>DB2 DBA
>
>DB2 UDB V8 Database Administration for Z/OS
>
>TG
>
>Round Rock, TX 78683-3100
>
>(800) 252-9743, ext. 4847
>
>www.tgslc.org
>
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Paul Ogborne

Re: DB2 V8 on Z/OS 1.9. space map issues
(in response to William Proctor)
Hi,
If you are planning on making changes anyway then you might want to check out the?DSSIZE (I wouldn't use LARGE).
If the default was used at creation time (SYSIBM.SYSTABLESPACE.DSSIZE=0) then this limits how many partitions you can create and also the overall size of the tablespace to 64Gb.
Regards,
Paul.?


-----Original Message-----
From: Proctor, William <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, 14 Jan 2009 14:56
Subject: Re: [DB2-L] DB2 V8 on Z/OS 1.9. space map issues



We were reorganizing a 14 partition table with 136 million rows in it. The
reorg failed indicating that the page being added exceeded the spacemap page's
capacity. I reduced the pctfree and the the reorg completed ok. I'm also
expanding the number of partitions this weekend which will solve the problem of
exceeding the space maps capacity. We have several tables that are pretty large
for us and 3 of them we only reorganize when we have to. My concern is that
during the week we could run into this problem causing an outage. I want to
create an automated or semi automated check that will alert us if we are getting
close to any limits like this so that we can avoid an outage.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Roger Miller
Sent: Tuesday, January 13, 2009 8:16 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V8 on Z/OS 1.9. space map issues

What was the reason code for the situation? Having all of the messages and
full formatting of SQLDA makes this question possible to answer. When the
space map is full, DB2 allocates a new space map page. So this is trickier.

If you can allocate space, then you don't worry about space map pages.

Roger Miller, DB2 for z/OS

On Mon, 12 Jan 2009 09:38:34 -0600, Proctor, William
<[login to unmask email]> wrote:

>Good Morning,
>
> Thanks for all replies in advance. When doing reorg's this
>weekend we ran out of space in the spacemap for one of our partitioned
>dataset. The problem has been resolved but my question is how can we
>tell in advance on how much of the space map is free? Are there queries
>we can run to find out and keep track of this?
>
>
>
>
>
>
>
>
>
>Bill Proctor
>
>DB2 DBA
>
>DB2 UDB V8 Database Administration for Z/OS
>
>TG
>
>Round Rock, TX 78683-3100
>
>(800) 252-9743, ext. 4847
>
>www.tgslc.org
>
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
__________
____________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not already an
IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not already an
IDUG.org member, please register at http://www.idug.org/component/juser/register.html


________________________________________________________________________
AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html