Tablespace - DSSIZE and MAXPARTITIONS

Mike Martin

Tablespace - DSSIZE and MAXPARTITIONS
Hi all,

We have a tablespace that appears to have exceeded our supplied DSSIZE.

Listcat shows...

HI-A-RBA------4293918720
HI-U-RBA------4247470080

The following was used to create the tablespace...

MAXPARTITIONS 1
DSSIZE 4G

Questions:


1. The tablespace has never been reorg'd. Would that potentially give us more space? (I am guessing not based on the doc that I read)
2. Which would be better... increasing DSSIZE or increasing MAXPARTITIONS? (the dataset is extended format)

We are z/OS 2.2 with Db2 V11.

Thanks for any advice.

Mike Martin

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

Srinivas Adupa

Tablespace - DSSIZE and MAXPARTITIONS
(in response to Mike Martin)
Hey Mike,
I would choose to increase the MAXPARTITIONS and then do the online REORG
to reclaim the space.
By the way, it would've much better if you give the RTS info for the
tablespace. Still it's fine.
Doing like this would save you from this problem for more time.

Best regards,
Srini.

On Fri, 15 Mar 2019, 6:51 pm MARTIN, MIKE, <[login to unmask email]> wrote:

> Hi all,
>
>
>
> We have a tablespace that appears to have exceeded our supplied DSSIZE.
>
>
>
> Listcat shows…
>
>
>
> HI-A-RBA------4293918720
>
> HI-U-RBA------4247470080
>
>
>
> The following was used to create the tablespace…
>
>
>
> MAXPARTITIONS 1
>
> DSSIZE 4G
>
>
>
> Questions:
>
>
>
> 1. The tablespace has never been reorg’d. Would that potentially
> give us more space? (I am guessing not based on the doc that I read)
> 2. Which would be better… increasing DSSIZE or increasing
> MAXPARTITIONS? (the dataset is extended format)
>
>
>
> We are z/OS 2.2 with Db2 V11.
>
>
>
> Thanks for any advice.
>
>
>
> Mike Martin
> This email may contain confidential and privileged material for the sole
> use of the intended recipient. If you are not the intended recipient,
> please contact the sender and delete all copies. Any review or distribution
> by others is strictly prohibited. Personal emails are restricted by policy
> of the State Employees' Credit Union (SECU). Therefore SECU specifically
> disclaims any responsibility or liability for any personal information or
> opinions of the author expressed in this email.
>
> -----End Original Message-----
>

Phil Grainger

Tablespace - DSSIZE and MAXPARTITIONS
(in response to Mike Martin)
You can increase MAXPARTITIONS without a reorg, in Db2 11 changing DSSIZE does require a reorg

Do you WANT to reorg a 4GB tablespace? The answer to that might show you which direction to move

(That said, if this tablespace is NOT read only, it really ought to be reorg'd once in a while - it MIGHT gain you space, but equally it might USE more space depending on how freespace has been used. But for performance reasons it is dangerous to ignore reorgs)
________________________________





[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Phil Grainger
Principal Enablement Manager

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png][https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]










































From: MARTIN, MIKE [mailto:[login to unmask email]
Sent: 15 March 2019 13:05
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - Tablespace - DSSIZE and MAXPARTITIONS

Hi all,

We have a tablespace that appears to have exceeded our supplied DSSIZE.

Listcat shows...

HI-A-RBA------4293918720
HI-U-RBA------4247470080

The following was used to create the tablespace...

MAXPARTITIONS 1
DSSIZE 4G

Questions:


1. The tablespace has never been reorg'd. Would that potentially give us more space? (I am guessing not based on the doc that I read)
2. Which would be better... increasing DSSIZE or increasing MAXPARTITIONS? (the dataset is extended format)

We are z/OS 2.2 with Db2 V11.

Thanks for any advice.

Mike Martin
This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (49.7k)
  • image002.png (6.7k)
  • image003.png (3.7k)
  • image004.png (<1k)

Mike Martin

Tablespace - DSSIZE and MAXPARTITIONS
(in response to Srinivas Adupa)
Thanks everyone for the help!

Srini, what should I look for – in the runtime stats that will tell me how full the tablespace is?

Mike Martin

From: Srinivas Adupa <[login to unmask email]>
Sent: Friday, March 15, 2019 9:30 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Tablespace - DSSIZE and MAXPARTITIONS

Hey Mike,
I would choose to increase the MAXPARTITIONS and then do the online REORG to reclaim the space.
By the way, it would've much better if you give the RTS info for the tablespace. Still it's fine.
Doing like this would save you from this problem for more time.

Best regards,
Srini.

On Fri, 15 Mar 2019, 6:51 pm MARTIN, MIKE, <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hi all,

We have a tablespace that appears to have exceeded our supplied DSSIZE.

Listcat shows…

HI-A-RBA------4293918720
HI-U-RBA------4247470080

The following was used to create the tablespace…

MAXPARTITIONS 1
DSSIZE 4G

Questions:


1. The tablespace has never been reorg’d. Would that potentially give us more space? (I am guessing not based on the doc that I read)
2. Which would be better… increasing DSSIZE or increasing MAXPARTITIONS? (the dataset is extended format)

We are z/OS 2.2 with Db2 V11.

Thanks for any advice.

Mike Martin
This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

-----End Original Message-----

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

Srinivas Adupa

Tablespace - DSSIZE and MAXPARTITIONS
(in response to Mike Martin)
Hey Mike,
Well, my intention is not about the space that is occupied by the
tablespace through RTS tables.
If your question is only about knowing space occupied by your tablespace,
its easy. Just multiply the TRACKS (that you can see in 3.4 of ISPF) with
48. This would be the value of KB. For ex: if the number of tracks are
10000 for part 1 dataset, then 10000 * 48 = 480000 KB is the size occupied
by your tablespace for part 1. Since you've only 1 partition at the moment,
I dont go deeper than this.

From RTS tables: we can get to know much more information as I was
expecting that you would've performed RUNSTATS if not REORG.
Ex:
STATSLASTTIME
STATSINSERTS
STATSDELETES
STATSUPDATES
These columns
In 1 sentence, I would
Its a kind of long answer that I would give: Lets see the below points:
According to you the tablespace has NEVER been reorg'ed. But I believe, you
would've performed RUNSTATS or COPY on the tablespace before.

Question 1: Is this tablespace

On Fri, Mar 15, 2019 at 10:12 PM MARTIN, MIKE <[login to unmask email]> wrote:

> Thanks everyone for the help!
>
>
>
> Srini, what should I look for – in the runtime stats that will tell me how
> full the tablespace is?
>
>
>
> Mike Martin
>
>
>
> *From:* Srinivas Adupa <[login to unmask email]>
> *Sent:* Friday, March 15, 2019 9:30 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Tablespace - DSSIZE and MAXPARTITIONS
>
>
>
> Hey Mike,
>
> I would choose to increase the MAXPARTITIONS and then do the online REORG
> to reclaim the space.
>
> By the way, it would've much better if you give the RTS info for the
> tablespace. Still it's fine.
>
> Doing like this would save you from this problem for more time.
>
>
>
> Best regards,
>
> Srini.
>
>
>
> On Fri, 15 Mar 2019, 6:51 pm MARTIN, MIKE, <[login to unmask email]> wrote:
>
> Hi all,
>
>
>
> We have a tablespace that appears to have exceeded our supplied DSSIZE.
>
>
>
> Listcat shows…
>
>
>
> HI-A-RBA------4293918720
>
> HI-U-RBA------4247470080
>
>
>
> The following was used to create the tablespace…
>
>
>
> MAXPARTITIONS 1
>
> DSSIZE 4G
>
>
>
> Questions:
>
>
>
> 1. The tablespace has never been reorg’d. Would that potentially
> give us more space? (I am guessing not based on the doc that I read)
> 2. Which would be better… increasing DSSIZE or increasing
> MAXPARTITIONS? (the dataset is extended format)
>
>
>
> We are z/OS 2.2 with Db2 V11.
>
>
>
> Thanks for any advice.
>
>
>
> Mike Martin
>
> This email may contain confidential and privileged material for the sole
> use of the intended recipient. If you are not the intended recipient,
> please contact the sender and delete all copies. Any review or distribution
> by others is strictly prohibited. Personal emails are restricted by policy
> of the State Employees' Credit Union (SECU). Therefore SECU specifically
> disclaims any responsibility or liability for any personal information or
> opinions of the author expressed in this email.
>
>
> -----End Original Message-----
> This email may contain confidential and privileged material for the sole
> use of the intended recipient. If you are not the intended recipient,
> please contact the sender and delete all copies. Any review or distribution
> by others is strictly prohibited. Personal emails are restricted by policy
> of the State Employees' Credit Union (SECU). Therefore SECU specifically
> disclaims any responsibility or liability for any personal information or
> opinions of the author expressed in this email.
>
> -----End Original Message-----
>
>

--
*Thanks & Regards,*
*Srinivas Adupa*

Srinivas Adupa

Tablespace - DSSIZE and MAXPARTITIONS
(in response to Srinivas Adupa)
*Resending the mail with full content as the previous mail is not full: *
Hey Mike,
Well, my intention is not about the space that is occupied by the
tablespace through RTS tables.
If your question is only about knowing space occupied by your tablespace,
its easy. Just multiply the TRACKS (that you can see in 3.4 of ISPF) with
48. This would be the value of KB. For ex: if the number of tracks are
10000 for part 1 dataset, then 10000 * 48 = 480000 KB is the size occupied
by your tablespace for part 1. Since you've only 1 partition at the moment,
I dont go deeper than this.

From RTS tables: we can get to know much more information as I was
expecting that you would've performed RUNSTATS if not REORG.
Ex:
STATSLASTTIME => when the RUNSTATS were done
STATSINSERTS => How many rows are INSERTED from last runstats time
STATSDELETES => How many rows are DELETED from last runstats time
STATSUPDATES => How many rows are UPDATED from last runstats time

If you have STATSDELETES with good number (say > 5% to 10% of total count),
then lets do the REORG once. This would save us some space.
If your Tablespace is created with COMPRESS YES, then this would be a good
news to you to save even more space. <<Please do not think of compressing
the tablespace if its not - take the opinion from your DBA>>.

Hope this helps.

Best regards,
Srini.


On Fri, Mar 15, 2019 at 10:22 PM Srinivas Adupa <[login to unmask email]>
wrote:

> Hey Mike,
> Well, my intention is not about the space that is occupied by the
> tablespace through RTS tables.
> If your question is only about knowing space occupied by your tablespace,
> its easy. Just multiply the TRACKS (that you can see in 3.4 of ISPF) with
> 48. This would be the value of KB. For ex: if the number of tracks are
> 10000 for part 1 dataset, then 10000 * 48 = 480000 KB is the size occupied
> by your tablespace for part 1. Since you've only 1 partition at the moment,
> I dont go deeper than this.
>
> From RTS tables: we can get to know much more information as I was
> expecting that you would've performed RUNSTATS if not REORG.
> Ex:
> STATSLASTTIME
> STATSINSERTS
> STATSDELETES
> STATSUPDATES
> These columns
> In 1 sentence, I would
> Its a kind of long answer that I would give: Lets see the below points:
> According to you the tablespace has NEVER been reorg'ed. But I believe,
> you would've performed RUNSTATS or COPY on the tablespace before.
>
> Question 1: Is this tablespace
>
> On Fri, Mar 15, 2019 at 10:12 PM MARTIN, MIKE <[login to unmask email]>
> wrote:
>
>> Thanks everyone for the help!
>>
>>
>>
>> Srini, what should I look for – in the runtime stats that will tell me
>> how full the tablespace is?
>>
>>
>>
>> Mike Martin
>>
>>
>>
>> *From:* Srinivas Adupa <[login to unmask email]>
>> *Sent:* Friday, March 15, 2019 9:30 AM
>> *To:* [login to unmask email]
>> *Subject:* [DB2-L] - RE: Tablespace - DSSIZE and MAXPARTITIONS
>>
>>
>>
>> Hey Mike,
>>
>> I would choose to increase the MAXPARTITIONS and then do the online REORG
>> to reclaim the space.
>>
>> By the way, it would've much better if you give the RTS info for the
>> tablespace. Still it's fine.
>>
>> Doing like this would save you from this problem for more time.
>>
>>
>>
>> Best regards,
>>
>> Srini.
>>
>>
>>
>> On Fri, 15 Mar 2019, 6:51 pm MARTIN, MIKE, <[login to unmask email]> wrote:
>>
>> Hi all,
>>
>>
>>
>> We have a tablespace that appears to have exceeded our supplied DSSIZE.
>>
>>
>>
>> Listcat shows…
>>
>>
>>
>> HI-A-RBA------4293918720
>>
>> HI-U-RBA------4247470080
>>
>>
>>
>> The following was used to create the tablespace…
>>
>>
>>
>> MAXPARTITIONS 1
>>
>> DSSIZE 4G
>>
>>
>>
>> Questions:
>>
>>
>>
>> 1. The tablespace has never been reorg’d. Would that potentially
>> give us more space? (I am guessing not based on the doc that I read)
>> 2. Which would be better… increasing DSSIZE or increasing
>> MAXPARTITIONS? (the dataset is extended format)
>>
>>
>>
>> We are z/OS 2.2 with Db2 V11.
>>
>>
>>
>> Thanks for any advice.
>>
>>
>>
>> Mike Martin
>>
>> This email may contain confidential and privileged material for the sole
>> use of the intended recipient. If you are not the intended recipient,
>> please contact the sender and delete all copies. Any review or distribution
>> by others is strictly prohibited. Personal emails are restricted by policy
>> of the State Employees' Credit Union (SECU). Therefore SECU specifically
>> disclaims any responsibility or liability for any personal information or
>> opinions of the author expressed in this email.
>>
>>
>> -----End Original Message-----
>> This email may contain confidential and privileged material for the sole
>> use of the intended recipient. If you are not the intended recipient,
>> please contact the sender and delete all copies. Any review or distribution
>> by others is strictly prohibited. Personal emails are restricted by policy
>> of the State Employees' Credit Union (SECU). Therefore SECU specifically
>> disclaims any responsibility or liability for any personal information or
>> opinions of the author expressed in this email.
>>
>> -----End Original Message-----
>


--
*Thanks & Regards,*
*Srinivas Adupa*

Mike Martin

Tablespace - DSSIZE and MAXPARTITIONS
(in response to Srinivas Adupa)
Thanks Srini,

I see why you were interested in the runstats. (I had thought your interest in runstats was for determining free space)

Thank you for your help.

Mike Martin

From: Srinivas Adupa <[login to unmask email]>
Sent: Friday, March 15, 2019 1:04 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Tablespace - DSSIZE and MAXPARTITIONS

Resending the mail with full content as the previous mail is not full:
Hey Mike,
Well, my intention is not about the space that is occupied by the tablespace through RTS tables.
If your question is only about knowing space occupied by your tablespace, its easy. Just multiply the TRACKS (that you can see in 3.4 of ISPF) with 48. This would be the value of KB. For ex: if the number of tracks are 10000 for part 1 dataset, then 10000 * 48 = 480000 KB is the size occupied by your tablespace for part 1. Since you've only 1 partition at the moment, I dont go deeper than this.

From RTS tables: we can get to know much more information as I was expecting that you would've performed RUNSTATS if not REORG.
Ex:
STATSLASTTIME => when the RUNSTATS were done
STATSINSERTS => How many rows are INSERTED from last runstats time
STATSDELETES => How many rows are DELETED from last runstats time
STATSUPDATES => How many rows are UPDATED from last runstats time

If you have STATSDELETES with good number (say > 5% to 10% of total count), then lets do the REORG once. This would save us some space.
If your Tablespace is created with COMPRESS YES, then this would be a good news to you to save even more space. <<Please do not think of compressing the tablespace if its not - take the opinion from your DBA>>.

Hope this helps.

Best regards,
Srini.


On Fri, Mar 15, 2019 at 10:22 PM Srinivas Adupa <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hey Mike,
Well, my intention is not about the space that is occupied by the tablespace through RTS tables.
If your question is only about knowing space occupied by your tablespace, its easy. Just multiply the TRACKS (that you can see in 3.4 of ISPF) with 48. This would be the value of KB. For ex: if the number of tracks are 10000 for part 1 dataset, then 10000 * 48 = 480000 KB is the size occupied by your tablespace for part 1. Since you've only 1 partition at the moment, I dont go deeper than this.

From RTS tables: we can get to know much more information as I was expecting that you would've performed RUNSTATS if not REORG.
Ex:
STATSLASTTIME
STATSINSERTS
STATSDELETES
STATSUPDATES
These columns
In 1 sentence, I would
Its a kind of long answer that I would give: Lets see the below points:
According to you the tablespace has NEVER been reorg'ed. But I believe, you would've performed RUNSTATS or COPY on the tablespace before.

Question 1: Is this tablespace

On Fri, Mar 15, 2019 at 10:12 PM MARTIN, MIKE <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Thanks everyone for the help!

Srini, what should I look for – in the runtime stats that will tell me how full the tablespace is?

Mike Martin

From: Srinivas Adupa <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Friday, March 15, 2019 9:30 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Tablespace - DSSIZE and MAXPARTITIONS

Hey Mike,
I would choose to increase the MAXPARTITIONS and then do the online REORG to reclaim the space.
By the way, it would've much better if you give the RTS info for the tablespace. Still it's fine.
Doing like this would save you from this problem for more time.

Best regards,
Srini.

On Fri, 15 Mar 2019, 6:51 pm MARTIN, MIKE, <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hi all,

We have a tablespace that appears to have exceeded our supplied DSSIZE.

Listcat shows…

HI-A-RBA------4293918720
HI-U-RBA------4247470080

The following was used to create the tablespace…

MAXPARTITIONS 1
DSSIZE 4G

Questions:


1. The tablespace has never been reorg’d. Would that potentially give us more space? (I am guessing not based on the doc that I read)
2. Which would be better… increasing DSSIZE or increasing MAXPARTITIONS? (the dataset is extended format)

We are z/OS 2.2 with Db2 V11.

Thanks for any advice.

Mike Martin
This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

-----End Original Message-----
This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

-----End Original Message-----

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

Steven Lamb

RE: Tablespace - DSSIZE and MAXPARTITIONS
(in response to Mike Martin)

There appear to be some issues in this area - we've had an index partition grow to 20 Gb, even though its theoretical limit was 8 Gb. We have a PMR open.

We also came across a previously undocumented "feature" concerning compressed indexes. The calculation to work out the maximum size for an index partition doesn't apply to compressed indexes - they always use a 4Kb page size, giving a maximum partition size of 8 Gb, regardless of the other factors.

Bruce Williamson

RE: Tablespace - DSSIZE and MAXPARTITIONS
(in response to Mike Martin)

While you are considering whether to increase MAXPARTITIONS or DSSIZE, take into consideration the doomed to fail REORG of single partitions of PBGs where the data can’t fit back into the original partition typically due to reclaiming free space but also potentially for compressed tablespaces if you don’t specify KEEPDICTIONARY YES.

DSNU1170I … DSNURWAP - UNABLE TO EXTEND PARTITION-BY-GROWTH TABLESPACE database.tabspace BECAUSE OF PARTITION RANGE.

Reorganization of partition-by-growth table spaces

To circumvent this issue you typically have to REORG all partitions anyway so whether you’re doing one big one or many small ones in parallel is of no consequence but if you don’t do database housekeeping then it’s a moot point.

IMHO Increasing DSSIZE will result in less administrative headaches

Cheers
Bruce


In Reply to Mike Martin:

Thanks Srini,

I see why you were interested in the runstats. (I had thought your interest in runstats was for determining free space)

Thank you for your help.

Mike Martin

P.S. Want to make a difference to DB2 but don't know how? Join the RFE Community?

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and vote on community proposals