Can DASD allocation exceed max size of a partition of a partioning index?

Carl Castrianni

Can DASD allocation exceed max size of a partition of a partioning index?

Our DB2 z/OS version is V11.

I have a table controlled partitioned table with 24 partitions.  DSSIZE is zero, PGSIZE=4K and tablespace is not defined as LARGE so my understanding is that no partition of the tablespace can exceed 2 gig and also no partition of the partitioning index can exceed 2 gig.  Let me know if you disagree.

Here is my question.  When I look on DASD I see that 5 of the 24 partitions of the partitioning index are allocated at more than 2 gig (in our case those 5 show allocations of 45,000 tracks at 48K per track which is slightly larger than 2 gig as 2 gig would be 43,695 tracks).  However, none of them are using more than 2 gig of that space as tracks actually used is 43,690.  Is this an anomaly as I wasn't expecting to see any allocations greater than 2 gig?  I even tried creating a dummy table with 24 partitions and specified a PRIQTY > 2 gig on one of the index partitions but db2 only allocated 2 gig for that partition.

The reason for my question is I'm trying to come up with a query that will show me what db2 objects are almost at their max size and when I run my query for this index is shows it is at 102.9% of max size.

James Campbell

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Carl Castrianni)
PRIQTY, SECQTY, VSAM primary, secondary, and size of the last VSAM extent?

VSAM sometimes tries to be 'nice'.

James Campbell

On 11 Mar 2019 at 18:18, Carl Castrianni wrote:

>
> Our DB2 z/OS version is V11.
> I have a table controlled partitioned table with 24 partitions.  DSSIZE is zero, PGSIZE=4K and
> tablespace is not defined as LARGE so my understanding is that no partition of the tablespace
> can exceed 2 gig and also no partition of the partitioning index can exceed 2 gig.  Let me know if
> you disagree.
> Here is my question.  When I look on DASD I see that 5 of the 24 partitions of the partitioning
> index are allocated at more than 2 gig (in our case those 5 show allocations of 45,000 tracks at
> 48K per track which is slightly larger than 2 gig as 2 gig would be 43,695 tracks).  However, none
> of them are using more than 2 gig of that space as tracks actually used is 43,690.  Is this an
> anomaly as I wasn't expecting to see any allocations greater than 2 gig?  I even tried creating a
> dummy table with 24 partitions and specified a PRIQTY > 2 gig on one of the index partitions but
> db2 only allocated 2 gig for that partition.
> The reason for my question is I'm trying to come up with a query that will show me what db2
> objects are almost at their max size and when I run my query for this index is shows it is at
> 102.9% of max size.
>

---
This email has been checked for viruses by AVG.
https://www.avg.com

John Bucaria

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Carl Castrianni)
Hi Carl,

It is my understanding that for “traditional” partitioned tablespaces (not LARGE, PBR or PBG) the formula is as follows:
If NUMPARTS is … Maximum partition size is …
1 to 16 --> 4 GB
17 to 32 --> 2 GB
33 to 64 --> 1 GB

-John

From: Carl Castrianni <[login to unmask email]>
Sent: Monday, March 11, 2019 9:19 PM
To: [login to unmask email]
Subject: [DB2-L] - Can DASD allocation exceed max size of a partition of a partioning index?


Our DB2 z/OS version is V11.

I have a table controlled partitioned table with 24 partitions. DSSIZE is zero, PGSIZE=4K and tablespace is not defined as LARGE so my understanding is that no partition of the tablespace can exceed 2 gig and also no partition of the partitioning index can exceed 2 gig. Let me know if you disagree.

Here is my question. When I look on DASD I see that 5 of the 24 partitions of the partitioning index are allocated at more than 2 gig (in our case those 5 show allocations of 45,000 tracks at 48K per track which is slightly larger than 2 gig as 2 gig would be 43,695 tracks). However, none of them are using more than 2 gig of that space as tracks actually used is 43,690. Is this an anomaly as I wasn't expecting to see any allocations greater than 2 gig? I even tried creating a dummy table with 24 partitions and specified a PRIQTY > 2 gig on one of the index partitions but db2 only allocated 2 gig for that partition.

The reason for my question is I'm trying to come up with a query that will show me what db2 objects are almost at their max size and when I run my query for this index is shows it is at 102.9% of max size.

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

Philip Sevetson

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Carl Castrianni)
Carl,

DB2 will only format pages out to the addressability limit defined as a function of DSSIZE. However, _at least_ explicitly defined datasets can certainly be allocated larger than the DSSIZE or extended automatically beyond the DSSIZE boundary. We used to have that problem now and then back in the days of DB2V6-V7. I haven’t seen it in a long time, and can’t tell you for sure what the cause of the problem is in your case.

I’d recommend that you not worry a lot about it unless the wasted space becomes significant, either to your DB2 environment (messing up space calculations noticeably), or to your DASD budget.

--Phil Sevetson

From: Carl Castrianni [mailto:[login to unmask email]
Sent: Monday, March 11, 2019 9:19 PM
To: [login to unmask email]
Subject: [DB2-L] - Can DASD allocation exceed max size of a partition of a partioning index?


Our DB2 z/OS version is V11.

I have a table controlled partitioned table with 24 partitions. DSSIZE is zero, PGSIZE=4K and tablespace is not defined as LARGE so my understanding is that no partition of the tablespace can exceed 2 gig and also no partition of the partitioning index can exceed 2 gig. Let me know if you disagree.

Here is my question. When I look on DASD I see that 5 of the 24 partitions of the partitioning index are allocated at more than 2 gig (in our case those 5 show allocations of 45,000 tracks at 48K per track which is slightly larger than 2 gig as 2 gig would be 43,695 tracks). However, none of them are using more than 2 gig of that space as tracks actually used is 43,690. Is this an anomaly as I wasn't expecting to see any allocations greater than 2 gig? I even tried creating a dummy table with 24 partitions and specified a PRIQTY > 2 gig on one of the index partitions but db2 only allocated 2 gig for that partition.

The reason for my question is I'm trying to come up with a query that will show me what db2 objects are almost at their max size and when I run my query for this index is shows it is at 102.9% of max size.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

John Bucaria

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to John Bucaria)
Hi Carl,

Just realized I didn’t answer your question. The default piece size for an index is as follows:
- 2 GB (PIECESIZE 2 G) for indexes of table spaces created without the LARGE or DSSIZE option
- 4 GB (PIECESIZE 4 G) for indexes of table spaces created with the LARGE or DSSIZE option
- 4 GB (PIECESIZE 4 G) for auxiliary indexes

-John

From: Bucaria, John <[login to unmask email]>
Sent: Tuesday, March 12, 2019 10:04 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?

Hi Carl,

It is my understanding that for “traditional” partitioned tablespaces (not LARGE, PBR or PBG) the formula is as follows:
If NUMPARTS is … Maximum partition size is …
1 to 16 --> 4 GB
17 to 32 --> 2 GB
33 to 64 --> 1 GB

-John

From: Carl Castrianni <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, March 11, 2019 9:19 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Can DASD allocation exceed max size of a partition of a partioning index?


Our DB2 z/OS version is V11.

I have a table controlled partitioned table with 24 partitions. DSSIZE is zero, PGSIZE=4K and tablespace is not defined as LARGE so my understanding is that no partition of the tablespace can exceed 2 gig and also no partition of the partitioning index can exceed 2 gig. Let me know if you disagree.

Here is my question. When I look on DASD I see that 5 of the 24 partitions of the partitioning index are allocated at more than 2 gig (in our case those 5 show allocations of 45,000 tracks at 48K per track which is slightly larger than 2 gig as 2 gig would be 43,695 tracks). However, none of them are using more than 2 gig of that space as tracks actually used is 43,690. Is this an anomaly as I wasn't expecting to see any allocations greater than 2 gig? I even tried creating a dummy table with 24 partitions and specified a PRIQTY > 2 gig on one of the index partitions but db2 only allocated 2 gig for that partition.

The reason for my question is I'm trying to come up with a query that will show me what db2 objects are almost at their max size and when I run my query for this index is shows it is at 102.9% of max size.

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

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

Carl Castrianni

RE: Can DASD allocation exceed max size of a partition of a partioning index?
(in response to James Campbell)

This tablespace/index/table was created back in db2 V9 so guessing back then the create index would allow the vsam primary allocation to be greater than 2 gig for a tablespace with 24 partitions.

Priqty for the last 5 partitions of the index is 2,160,000 (which is 3000 cyl) and secqty is -1 with VSAM primary/secondary at 3000,300 cyl and the index partition is in 1 extent allocated at 3,000 cyls.  Note that 2 gig max is 2913 cyl.

When I created a mirror image table (using DB2 v11) at priqty of 2,160,000 it only allocated 2918 cyls (which is 2 gig) so guess V9 didn't do that.

I just did a runstats on the actual tablespace and index and am now more confused as the last 5 partitions of the index have fewer rows (i.e., entries) than other index partitions and the other index partitions are allocated at less than 2 gig.  The last 5 partitions show NACTIVE at 524,288 4K pages with 2,656,053 index entries while some of the other index partitions have 10,626,688 index entries and show NACTIVE at only using 324,000 4K pages.  This tabspace and indexes were recently reorged and freepage and pctfree at 0  with no updates since the reorg so would assume all index partitions are not wasting space.  This confuses me as to how index partitions with fewer rows can show having more NACTIVE pages than index partitions with 5 times the rows so this is making it difficult for me to come up with a query that tells me which index partitons have actually almost reached their max size.  The last 5 index partitions show 102.9 % of max size using my query and NACTIVE says they are using 524,288 (or 43,690 trks) so appears they are in jeopardy of exceeding the 2 gig limit of 43,695 trks while other partitions of this index have far more rows and are only at 62% of the 2 gig max.

I must be missing something. 

Carl Castrianni

RE: Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Carl Castrianni)

Thank you Phil for mentioning pre-formatted pages.

I looked at the definition of NACTIVE in the sysindexspacestats table and it says:

The number of active pages in the index space or partition. This value is equivalent to the number of pre-formatted pages.

Could it be that db2 pre-formatted 2 gig worth of pages when much fewer pages were actually used so that NACTIVE really doesn't show active pages (to me an active page is a page that has rows or had rows) but rather shows number of pre-formatted pages?  If so, how is one to figure out how close an index partition is to reaching the max 2 gig size for tablespaces with 24 partitions?

Philip Sevetson

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Carl Castrianni)
Carl,

The experiment for this is:

1) Create a tablespace using BP0 (or any other 4K BP) with a primary extent of at 1440 (two cylinders).

2) Create a table

3) View the NACTIVE in SYSTABLESPACE/SYSTABLEPART (do you have to run RUNSTATS to obtain that number?).


· If NACTIVE=3, then DB2 is not preformatting pages (Tablespace header page, space map page, and one data page are needed at time of creation).

· If NACTIVE=12, then DB2 is preformatting by track.

· If NACTIVE=180, then DB2 is preformatting by cylinder.

· If NACTIVE=360, then DB2 is preformatting an entire extent.

· For any other value, we need to rethink the questions, possibly by inserting a single row and checking NACTIVE again.

I’d do this, but I don’t have time this week (!) – can someone perform the experiment and report back?

--Phil

From: Carl Castrianni [mailto:[login to unmask email]
Sent: Tuesday, March 12, 2019 3:43 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?


Thank you Phil for mentioning pre-formatted pages.

I looked at the definition of NACTIVE in the sysindexspacestats table and it says:

The number of active pages in the index space or partition. This value is equivalent to the number of pre-formatted pages.

Could it be that db2 pre-formatted 2 gig worth of pages when much fewer pages were actually used so that NACTIVE really doesn't show active pages (to me an active page is a page that has rows or had rows) but rather shows number of pre-formatted pages? If so, how is one to figure out how close an index partition is to reaching the max 2 gig size for tablespaces with 24 partitions?

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Roy Boxwell

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Philip Sevetson)
Hi!



Created four TS’s in a test DB:

CREATE DATABASE ROYTEST

BUFFERPOOL BP0

INDEXBP BP0

STOGROUP SYSDEFLT

CCSID EBCDIC

;

COMMIT ;

CREATE TABLESPACE ROYTRK1 IN ROYTEST

USING STOGROUP SYSDEFLT

PRIQTY 48

SECQTY 0

ERASE NO

FREEPAGE 0

PCTFREE 0

BUFFERPOOL BP0

LOCKSIZE ANY

SEGSIZE 4

LOCKMAX SYSTEM

CLOSE YES

CCSID EBCDIC

;

COMMIT ;

CREATE TABLESPACE ROYTRK2 IN ROYTEST

USING STOGROUP SYSDEFLT

PRIQTY 96

SECQTY 0

ERASE NO

FREEPAGE 0

PCTFREE 0

BUFFERPOOL BP0

LOCKSIZE ANY

SEGSIZE 4

LOCKMAX SYSTEM

CLOSE YES

CCSID EBCDIC

;

COMMIT ;

CREATE TABLESPACE ROYCYL1 IN ROYTEST

USING STOGROUP SYSDEFLT

PRIQTY 720

SECQTY 0

ERASE NO

FREEPAGE 0

PCTFREE 0

BUFFERPOOL BP0

LOCKSIZE ANY

SEGSIZE 4

LOCKMAX SYSTEM

CLOSE YES

CCSID EBCDIC

;

COMMIT ;

CREATE TABLESPACE ROYCYL2 IN ROYTEST

USING STOGROUP SYSDEFLT

PRIQTY 1440

SECQTY 0

ERASE NO

FREEPAGE 0

PCTFREE 0

BUFFERPOOL BP0

LOCKSIZE ANY

SEGSIZE 4

LOCKMAX SYSTEM

CLOSE YES

CCSID EBCDIC

;

COMMIT ;



Look in ISPF 3.4:



Enter "/" to select action Tracks

---------------------------------------------------------

DC10.DSNDBD.ROYTEST.ROYCYL1.I0001.A001 15

DC10.DSNDBD.ROYTEST.ROYCYL2.I0001.A001 30

DC10.DSNDBD.ROYTEST.ROYTRK1.I0001.A001 1

DC10.DSNDBD.ROYTEST.ROYTRK2.I0001.A001 3



So the weirdness begins... my two track space gets three... *strange*

A look in SYSTABLESPACE (before runstats)



SELECT NAME, NACTIVEF, NACTIVE

FROM SYSIBM.SYSTABLESPACE

WHERE DBNAME = 'ROYTEST'

ORDER BY 1

;

---------+---------+---------+---------+---------+---------+--

NAME NACTIVEF NACTIVE

---------+---------+---------+---------+---------+---------+--

ROYCYL1 -0.1000000000000000E+01 0

ROYCYL2 -0.1000000000000000E+01 0

ROYTRK1 -0.1000000000000000E+01 0

ROYTRK2 -0.1000000000000000E+01 0

DSNE610I NUMBER OF ROWS DISPLAYED IS 4



A look in SYSTABLESPACESTATS

SELECT NAME, NACTIVE , NPAGES , SPACE

FROM SYSIBM.SYSTABLESPACESTATS

WHERE DBNAME = 'ROYTEST'

ORDER BY 1

;

---------+---------+---------+---------+---------+---------+---------+-----

NAME NACTIVE NPAGES SPACE

---------+---------+---------+---------+---------+---------+---------+-----

ROYCYL1 180 0 720

ROYCYL2 360 0 1440

ROYTRK1 12 0 48

ROYTRK2 36 0 144

DSNE610I NUMBER OF ROWS DISPLAYED IS 4



After RUNSTATS:

Nothing changed as the spaces were “incomplete”

Created this table, changing the name and tablespace to match the tablespace of course, in all four:

CREATE TABLE ROYTEST.ROYTRK1

("XCOUNT" INTEGER NOT NULL

WITH DEFAULT

,"X01" INTEGER NOT NULL

WITH DEFAULT

,"X02" DATE NOT NULL

WITH DEFAULT

,"X03" TIME NOT NULL

WITH DEFAULT

,"XCOUNT3" CHAR( 254) FOR SBCS DATA NOT NULL

WITH DEFAULT

,"XCOUNT4" CHAR( 254) FOR SBCS DATA NOT NULL

WITH DEFAULT

)

IN "ROYTEST"."ROYTRK1"

AUDIT NONE

DATA CAPTURE NONE

CCSID EBCDIC

;

Then a RUNSTATS and then the SPUFI again:



SELECT NAME, NACTIVEF, NACTIVE 000

FROM SYSIBM.SYSTABLESPACE 000

WHERE DBNAME = 'ROYTEST' 000

ORDER BY 1 000

; 000

---------+---------+---------+---------+---------+---------+---------+-----

NAME NACTIVEF NACTIVE

---------+---------+---------+---------+---------+---------+---------+-----

ROYCYL1 +0.1800000000000000E+03 180

ROYCYL2 +0.3600000000000000E+03 360

ROYTRK1 +0.1200000000000000E+02 12

ROYTRK2 +0.3600000000000000E+02 36

DSNE610I NUMBER OF ROWS DISPLAYED IS 4

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

---------+---------+---------+---------+---------+---------+---------+-----

SELECT NAME, NACTIVE , NPAGES , SPACE 000

FROM SYSIBM.SYSTABLESPACESTATS 000

WHERE DBNAME = 'ROYTEST' 000

ORDER BY 1 000

; 000

---------+---------+---------+---------+---------+---------+---------+-----

NAME NACTIVE NPAGES SPACE

---------+---------+---------+---------+---------+---------+---------+-----

ROYCYL1 180 0 720

ROYCYL2 360 0 1440

ROYTRK1 12 0 48

ROYTRK2 36 0 144

DSNE610I NUMBER OF ROWS DISPLAYED IS 4



So it all works as designed... your only real hope is to use the LISTCAT method and then analyse the HI-A-RBA and HI-U-RBA:



ALLOCATION

SPACE-TYPE------CYLINDER HI-A-RBA--------28753920

SPACE-PRI--------------3 HI-U-RBA--------24059904

SPACE-SEC--------------3



Here you can see that after a REORG the Highest Used RBA is bit less than the Highest Allocated RBA.



Various people on the list have written REXX’s etc. that help in extracting this info from LISTCAT a typical LISTCAT I use looks like



LISTCAT ENTRY('DC10.DSNDBD.MVNXTEST.MVNXS02.I0001.A001') ALL



Take care of I and J datasets and of course remember the A B C D E rule for partitioned objects and also CLONE objects with the INSTANCE... all so easy... I am sure you could make it more complicated if you really really tried!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, March 12, 2019 9:29 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?



Carl,



The experiment for this is:

1) Create a tablespace using BP0 (or any other 4K BP) with a primary extent of at 1440 (two cylinders).

2) Create a table

3) View the NACTIVE in SYSTABLESPACE/SYSTABLEPART (do you have to run RUNSTATS to obtain that number?).



* If NACTIVE=3, then DB2 is not preformatting pages (Tablespace header page, space map page, and one data page are needed at time of creation).

* If NACTIVE=12, then DB2 is preformatting by track.

* If NACTIVE=180, then DB2 is preformatting by cylinder.

* If NACTIVE=360, then DB2 is preformatting an entire extent.

* For any other value, we need to rethink the questions, possibly by inserting a single row and checking NACTIVE again.



I’d do this, but I don’t have time this week (!) – can someone perform the experiment and report back?



--Phil



From: Carl Castrianni [mailto:[login to unmask email]
Sent: Tuesday, March 12, 2019 3:43 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?



Thank you Phil for mentioning pre-formatted pages.

I looked at the definition of NACTIVE in the sysindexspacestats table and it says:

The number of active pages in the index space or partition. This value is equivalent to the number of pre-formatted pages.

Could it be that db2 pre-formatted 2 gig worth of pages when much fewer pages were actually used so that NACTIVE really doesn't show active pages (to me an active page is a page that has rows or had rows) but rather shows number of pre-formatted pages? If so, how is one to figure out how close an index partition is to reaching the max 2 gig size for tablespaces with 24 partitions?



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

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

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

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Philip Sevetson)



In Reply to Philip Sevetson:

· If NACTIVE=3, then DB2 is not preformatting pages (Tablespace header page, space map page, and one data page are needed at time of creation).

One data page is needed at creation for what? I don't think so. I am not sure about when pages for compression dictionary are allocated but I think not yet if partition has or TS has one. Data pages might be allocated when a Table is created and possibly in Segsize groups, since spacemap is segment based, or maybe after data is Inserted only. John Campbell presentations on this topic usually have good detail, I think.

As a performance tuner, I don't usually care much about NACTIVEF quantity of total pages including preformatted pages, although I can see is important to others. NPAGESF for each table is telling me about Data Pages containing data so much more interesting to me. TS Scan does not have to read (Getpage) every page of the NACTIVEF number, as I believe it looks at the spacemap to show segments containing data for the table and pages, or at least used to, especially when a TS had multiple tables in it and we only wanted one of them. No point to read all segments for the wrong table. I see TS scan of table with 5 rows doing 2 Getpages, I presume one is to look at the spacemap, and the other is to look at the data page shown by the spacemap. Prefetching is another more complex issue.

I can see though we don't want DB2 going on exhaustive searches looking for freespace, using Spacemaps and then trying it on, if spacemap suggests a page might have enough space.  I have seen really bad Insert performance on occasion when hunting for freespace for an unusual row length and hitting pages already locked as a problem as well. Append is obviously only good if we know when we have run out. Hopefully most Range partitioning schemes are designed not to run out or increase to larger sizing if necessary. 

I don't know the best way to determine how full is the partition, and seems even more complex for an index partition, however I assume one has to compare NPAGES and NLEAF from SYSTABLESPACESTATS and SYSINDEXSTATS to maximum sizes and make sure there is enough leeway (including higher index levels), especially just after a Reorg seems like a good time to recheck too, that we have enough for Table Append (if used) and for index page splits. That was without any experience on the topic so I don't know what is considered best practice.  Just make partitions large size capable to ensure no chance to run out I guess. 

Preformatting space is complex and so far have not had to worry about the exact mechanisms for it. It has been a bigger worry when DB2 is recycling around spacemaps hunting for potential freespace for Insert or Update, and then for pages not locked by another thread, before deciding to extend.

I think if NACTIVE is a bit bigger than expected no big deal, but much larger than expected, worth thinking about. However I assume it does not really tell us how full is the Tablespace or Index. I am interested to hear more though, if I am wrong on this, since I don't know the limits of preformatting for tablespaces and indexes.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 13, 2019 - 12:48 PM (Europe/Berlin)

Philip Sevetson

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Roy Boxwell)
Roy,

Thanks – very useful to see the behavior. Clearly, NACTIVE/NACTIVEF refers to formatted pages, as opposed to pages containing table data.

--Phil

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, March 13, 2019 4:22 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?

Hi!

Created four TS’s in a test DB:
CREATE DATABASE ROYTEST
BUFFERPOOL BP0
INDEXBP BP0
STOGROUP SYSDEFLT
CCSID EBCDIC
;
COMMIT ;
CREATE TABLESPACE ROYTRK1 IN ROYTEST
USING STOGROUP SYSDEFLT
PRIQTY 48
SECQTY 0
ERASE NO
FREEPAGE 0
PCTFREE 0
BUFFERPOOL BP0
LOCKSIZE ANY
SEGSIZE 4
LOCKMAX SYSTEM
CLOSE YES
CCSID EBCDIC
;
COMMIT ;
CREATE TABLESPACE ROYTRK2 IN ROYTEST
USING STOGROUP SYSDEFLT
PRIQTY 96
SECQTY 0
ERASE NO
FREEPAGE 0
PCTFREE 0
BUFFERPOOL BP0
LOCKSIZE ANY
SEGSIZE 4
LOCKMAX SYSTEM
CLOSE YES
CCSID EBCDIC
;
COMMIT ;
CREATE TABLESPACE ROYCYL1 IN ROYTEST
USING STOGROUP SYSDEFLT
PRIQTY 720
SECQTY 0
ERASE NO
FREEPAGE 0
PCTFREE 0
BUFFERPOOL BP0
LOCKSIZE ANY
SEGSIZE 4
LOCKMAX SYSTEM
CLOSE YES
CCSID EBCDIC
;
COMMIT ;
CREATE TABLESPACE ROYCYL2 IN ROYTEST
USING STOGROUP SYSDEFLT
PRIQTY 1440
SECQTY 0
ERASE NO
FREEPAGE 0
PCTFREE 0
BUFFERPOOL BP0
LOCKSIZE ANY
SEGSIZE 4
LOCKMAX SYSTEM
CLOSE YES
CCSID EBCDIC
;
COMMIT ;

Look in ISPF 3.4:

Enter "/" to select action Tracks
---------------------------------------------------------
DC10.DSNDBD.ROYTEST.ROYCYL1.I0001.A001 15
DC10.DSNDBD.ROYTEST.ROYCYL2.I0001.A001 30
DC10.DSNDBD.ROYTEST.ROYTRK1.I0001.A001 1
DC10.DSNDBD.ROYTEST.ROYTRK2.I0001.A001 3

So the weirdness begins... my two track space gets three... *strange*
A look in SYSTABLESPACE (before runstats)

SELECT NAME, NACTIVEF, NACTIVE
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'ROYTEST'
ORDER BY 1
;
---------+---------+---------+---------+---------+---------+--
NAME NACTIVEF NACTIVE
---------+---------+---------+---------+---------+---------+--
ROYCYL1 -0.1000000000000000E+01 0
ROYCYL2 -0.1000000000000000E+01 0
ROYTRK1 -0.1000000000000000E+01 0
ROYTRK2 -0.1000000000000000E+01 0
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

A look in SYSTABLESPACESTATS
SELECT NAME, NACTIVE , NPAGES , SPACE
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = 'ROYTEST'
ORDER BY 1
;
---------+---------+---------+---------+---------+---------+---------+-----
NAME NACTIVE NPAGES SPACE
---------+---------+---------+---------+---------+---------+---------+-----
ROYCYL1 180 0 720
ROYCYL2 360 0 1440
ROYTRK1 12 0 48
ROYTRK2 36 0 144
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

After RUNSTATS:
Nothing changed as the spaces were “incomplete”
Created this table, changing the name and tablespace to match the tablespace of course, in all four:
CREATE TABLE ROYTEST.ROYTRK1
("XCOUNT" INTEGER NOT NULL
WITH DEFAULT
,"X01" INTEGER NOT NULL
WITH DEFAULT
,"X02" DATE NOT NULL
WITH DEFAULT
,"X03" TIME NOT NULL
WITH DEFAULT
,"XCOUNT3" CHAR( 254) FOR SBCS DATA NOT NULL
WITH DEFAULT
,"XCOUNT4" CHAR( 254) FOR SBCS DATA NOT NULL
WITH DEFAULT
)
IN "ROYTEST"."ROYTRK1"
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
;
Then a RUNSTATS and then the SPUFI again:

SELECT NAME, NACTIVEF, NACTIVE 000
FROM SYSIBM.SYSTABLESPACE 000
WHERE DBNAME = 'ROYTEST' 000
ORDER BY 1 000
; 000
---------+---------+---------+---------+---------+---------+---------+-----
NAME NACTIVEF NACTIVE
---------+---------+---------+---------+---------+---------+---------+-----
ROYCYL1 +0.1800000000000000E+03 180
ROYCYL2 +0.3600000000000000E+03 360
ROYTRK1 +0.1200000000000000E+02 12
ROYTRK2 +0.3600000000000000E+02 36
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+-----
SELECT NAME, NACTIVE , NPAGES , SPACE 000
FROM SYSIBM.SYSTABLESPACESTATS 000
WHERE DBNAME = 'ROYTEST' 000
ORDER BY 1 000
; 000
---------+---------+---------+---------+---------+---------+---------+-----
NAME NACTIVE NPAGES SPACE
---------+---------+---------+---------+---------+---------+---------+-----
ROYCYL1 180 0 720
ROYCYL2 360 0 1440
ROYTRK1 12 0 48
ROYTRK2 36 0 144
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

So it all works as designed... your only real hope is to use the LISTCAT method and then analyse the HI-A-RBA and HI-U-RBA:

ALLOCATION
SPACE-TYPE------CYLINDER HI-A-RBA--------28753920
SPACE-PRI--------------3 HI-U-RBA--------24059904
SPACE-SEC--------------3

Here you can see that after a REORG the Highest Used RBA is bit less than the Highest Allocated RBA.

Various people on the list have written REXX’s etc. that help in extracting this info from LISTCAT a typical LISTCAT I use looks like

LISTCAT ENTRY('DC10.DSNDBD.MVNXTEST.MVNXS02.I0001.A001') ALL

Take care of I and J datasets and of course remember the A B C D E rule for partitioned objects and also CLONE objects with the INSTANCE... all so easy... I am sure you could make it more complicated if you really really tried!

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, March 12, 2019 9:29 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?

Carl,

The experiment for this is:

1) Create a tablespace using BP0 (or any other 4K BP) with a primary extent of at 1440 (two cylinders).

2) Create a table

3) View the NACTIVE in SYSTABLESPACE/SYSTABLEPART (do you have to run RUNSTATS to obtain that number?).


• If NACTIVE=3, then DB2 is not preformatting pages (Tablespace header page, space map page, and one data page are needed at time of creation).

• If NACTIVE=12, then DB2 is preformatting by track.

• If NACTIVE=180, then DB2 is preformatting by cylinder.

• If NACTIVE=360, then DB2 is preformatting an entire extent.

• For any other value, we need to rethink the questions, possibly by inserting a single row and checking NACTIVE again.

I’d do this, but I don’t have time this week (!) – can someone perform the experiment and report back?

--Phil

From: Carl Castrianni [mailto:[login to unmask email]
Sent: Tuesday, March 12, 2019 3:43 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?


Thank you Phil for mentioning pre-formatted pages.

I looked at the definition of NACTIVE in the sysindexspacestats table and it says:

The number of active pages in the index space or partition. This value is equivalent to the number of pre-formatted pages.

Could it be that db2 pre-formatted 2 gig worth of pages when much fewer pages were actually used so that NACTIVE really doesn't show active pages (to me an active page is a page that has rows or had rows) but rather shows number of pre-formatted pages? If so, how is one to figure out how close an index partition is to reaching the max 2 gig size for tablespaces with 24 partitions?

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

James Campbell

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to Philip Sevetson)
"z/OS V1R10 includes a change in control area (CA) size
selection for all new allocations of VSAM data sets on any
volume type (not only extended address volumes). The system
selects a control area size of 1, 3, 5, 7, 9, or 15 tracks."

http://www-01.ibm.com/support/docview.wss?uid=isg1II14638

Did ROYTRK2 get a CA of 3 tracks? Because, since allocations are in CA's ....

James Campbell


>  
> From: Boxwell, Roy [mailto:[login to unmask email]
> Sent: Wednesday, March 13, 2019 4:22 AM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of a partioning index?
>  
> Hi!
>  
> Created four TS´s in a test DB:
>     CREATE DATABASE ROYTEST            
>            BUFFERPOOL BP0              
>            INDEXBP    BP0              
>            STOGROUP   SYSDEFLT         
>            CCSID EBCDIC                
> ;                                      
>   COMMIT ;                             
>     CREATE TABLESPACE ROYTRK1 IN ROYTEST
>            USING STOGROUP SYSDEFLT     
>                  PRIQTY         48     
>                  SECQTY          0     
>            ERASE NO                    
>            FREEPAGE     0              
>            PCTFREE      0              
>            BUFFERPOOL BP0              
>            LOCKSIZE   ANY              
>            SEGSIZE      4              
>            LOCKMAX SYSTEM              
>            CLOSE      YES              
>            CCSID   EBCDIC              
> ;                                      
> COMMIT ;                               
>     CREATE TABLESPACE ROYTRK2 IN ROYTEST
>            USING STOGROUP SYSDEFLT     
>                  PRIQTY         96     
>                  SECQTY          0     
>            ERASE NO                    
>            FREEPAGE     0              
>            PCTFREE      0               
>            BUFFERPOOL BP0              
>            LOCKSIZE   ANY              
>            SEGSIZE      4              
>            LOCKMAX SYSTEM              
>            CLOSE      YES              
>            CCSID   EBCDIC              
> ;                                      
> COMMIT ;                               
>     CREATE TABLESPACE ROYCYL1 IN ROYTEST
>            USING STOGROUP SYSDEFLT     
>                  PRIQTY        720     
>                  SECQTY          0     
>            ERASE NO                    
>            FREEPAGE     0              
>            PCTFREE      0              
>            BUFFERPOOL BP0              
>            LOCKSIZE   ANY              
>            SEGSIZE      4              
>            LOCKMAX SYSTEM              
>            CLOSE      YES              
>            CCSID   EBCDIC              
> ;                                      
> COMMIT ;                               
>     CREATE TABLESPACE ROYCYL2 IN ROYTEST
>            USING STOGROUP SYSDEFLT     
>                  PRIQTY       1440     
>                  SECQTY          0     
>            ERASE NO                    
>            FREEPAGE     0              
>            PCTFREE      0              
>            BUFFERPOOL BP0               
>            LOCKSIZE   ANY              
>            SEGSIZE      4              
>            LOCKMAX SYSTEM              
>            CLOSE      YES              
>            CCSID   EBCDIC              
> ;                                      
> COMMIT ;                               
>  
> Look in ISPF 3.4:
>  
> Enter "/" to select action                        Tracks
> ---------------------------------------------------------
> DC10.DSNDBD.ROYTEST.ROYCYL1.I0001.A001                 15
> DC10.DSNDBD.ROYTEST.ROYCYL2.I0001.A001                 30
> DC10.DSNDBD.ROYTEST.ROYTRK1.I0001.A001                  1
> DC10.DSNDBD.ROYTEST.ROYTRK2.I0001.A001                  3
>  
> So the weirdness begins... my two track space gets three...  *strange*
> A look in SYSTABLESPACE (before runstats)
>  
> SELECT NAME, NACTIVEF, NACTIVE                               
> FROM SYSIBM.SYSTABLESPACE                                    
> WHERE DBNAME = 'ROYTEST'                                     
> ORDER BY 1                                                   
> ;                                                            
> ---------+---------+---------+---------+---------+---------+--
> NAME                                     NACTIVEF      NACTIVE
> ---------+---------+---------+---------+---------+---------+--
> ROYCYL1                   -0.1000000000000000E+01            0
> ROYCYL2                   -0.1000000000000000E+01            0
> ROYTRK1                   -0.1000000000000000E+01            0
> ROYTRK2                   -0.1000000000000000E+01            0
> DSNE610I NUMBER OF ROWS DISPLAYED IS 4                       
>  
> A look in SYSTABLESPACESTATS
> SELECT NAME, NACTIVE , NPAGES , SPACE                                  
> FROM SYSIBM.SYSTABLESPACESTATS                                         
> WHERE DBNAME = 'ROYTEST'                                                
> ORDER BY 1                                                             
> ;                                                                      
> ---------+---------+---------+---------+---------+---------+---------+-----
> NAME                          NACTIVE       NPAGES                    SPACE
> ---------+---------+---------+---------+---------+---------+---------+-----
> ROYCYL1                           180            0                      720
> ROYCYL2                           360            0                     1440
> ROYTRK1                            12            0                       48
> ROYTRK2                            36            0                      144
> DSNE610I NUMBER OF ROWS DISPLAYED IS 4                                    
>  
> After RUNSTATS:
> Nothing changed as the spaces were "incomplete"
> Created this table, changing the name and tablespace to match the tablespace of
> course, in all four:
>     CREATE TABLE ROYTEST.ROYTRK1                                       
>     ("XCOUNT"             INTEGER                               NOT NULL
>     WITH DEFAULT                                                        
>     ,"X01"                INTEGER                               NOT NULL
>     WITH DEFAULT                                                       
>     ,"X02"                DATE                                  NOT NULL
>     WITH DEFAULT                                                        
>     ,"X03"                TIME                                  NOT NULL
>     WITH DEFAULT                                                       
>     ,"XCOUNT3"            CHAR(  254)            FOR SBCS DATA  NOT NULL
>     WITH DEFAULT                                                       
>     ,"XCOUNT4"            CHAR(  254)            FOR SBCS DATA  NOT NULL
>     WITH DEFAULT                                                       
>     )                                                                   
>     IN  "ROYTEST"."ROYTRK1"                                            
>     AUDIT NONE                                                         
>     DATA CAPTURE NONE                                                   
>     CCSID EBCDIC                                                       
> ;                                                                      
> Then a RUNSTATS and then the SPUFI again:
>  
> SELECT NAME, NACTIVEF, NACTIVE                                          000
> FROM SYSIBM.SYSTABLESPACE                                               000
> WHERE DBNAME = 'ROYTEST'                                                000
> ORDER BY 1                                                              000
> ;                                                                       000
> ---------+---------+---------+---------+---------+---------+---------+-----
> NAME                                     NACTIVEF      NACTIVE            
> ---------+---------+---------+---------+---------+---------+---------+-----
> ROYCYL1                   +0.1800000000000000E+03          180            
> ROYCYL2                   +0.3600000000000000E+03          360            
> ROYTRK1                   +0.1200000000000000E+02           12            
> ROYTRK2                   +0.3600000000000000E+02           36            
> DSNE610I NUMBER OF ROWS DISPLAYED IS 4                                    
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100               
> ---------+---------+---------+---------+---------+---------+---------+-----
> SELECT NAME, NACTIVE , NPAGES , SPACE                                   000
> FROM SYSIBM.SYSTABLESPACESTATS                                          000
> WHERE DBNAME = 'ROYTEST'                                                000
> ORDER BY 1                                                              000
> ;                                                                       000
> ---------+---------+---------+---------+---------+---------+---------+-----
> NAME                          NACTIVE       NPAGES                    SPACE
> ---------+---------+---------+---------+---------+---------+---------+-----
> ROYCYL1                           180            0                      720
> ROYCYL2                           360            0                     1440
> ROYTRK1                            12            0                       48
> ROYTRK2                            36            0                      144
> DSNE610I NUMBER OF ROWS DISPLAYED IS 4                                    
>  
> So it all works as designed... your only real hope is to use the LISTCAT method and
> then analyse the HI-A-RBA and HI-U-RBA:
>  
> ALLOCATION                                             
>   SPACE-TYPE------CYLINDER     HI-A-RBA--------28753920
>   SPACE-PRI--------------3     HI-U-RBA--------24059904
>   SPACE-SEC--------------3                             
>  
> Here you can see that after a REORG the Highest Used RBA is bit less than the Highest
> Allocated RBA.
>  
> Various people on the list have written REXX´s etc. that help in extracting this info from
> LISTCAT a typical LISTCAT I use looks like
>  
> LISTCAT ENTRY('DC10.DSNDBD.MVNXTEST.MVNXS02.I0001.A001') ALL
>  
> Take care of I and J datasets and of course remember the A B C D E rule for partitioned
> objects and also CLONE objects with the INSTANCE... all so easy... I am sure you
> could make it more complicated if you really really tried!
>  
> Roy Boxwell
>
> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]
> Web   http://www.seg.de
> Link zur Datenschutzerklärung
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Ulf Heinrich
>  

<rest snipped>

---
This email has been checked for viruses by AVG.
https://www.avg.com

Roy Boxwell

Can DASD allocation exceed max size of a partition of a partioning index?
(in response to James Campbell)
Bingo!



The TRK1 has



CI/CA-----------------12



Whereas the TRK2 has



CI/CA-----------------36



You learn something new every day!



Now I need a beer....



Roy Boxwell



SOFTWARE ENGINEERING GmbH and SEGUS Inc.

-Product Development-



Heinrichstrasse 83-85

40239 Duesseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: [login to unmask email]

Web http://www.seg.de

Link zur Datenschutzerklärung



Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert, Ulf Heinrich





-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Friday, March 15, 2019 5:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition of
a partioning index?



"z/OS V1R10 includes a change in control area (CA) size selection for all
new allocations of VSAM data sets on any volume type (not only extended
address volumes). The system selects a control area size of 1, 3, 5, 7, 9,
or 15 tracks."



http://www-01.ibm.com/support/docview.wss?uid=isg1II14638
http://www-01.ibm.com/support/docview.wss?uid=isg1II14638



Did ROYTRK2 get a CA of 3 tracks? Because, since allocations are in CA's
....



James Campbell





>

> From: Boxwell, Roy [ <mailto:[login to unmask email]>
mailto:[login to unmask email]

> Sent: Wednesday, March 13, 2019 4:22 AM

> To: <mailto:[login to unmask email]> [login to unmask email]

> Subject: [DB2-L] - RE: Can DASD allocation exceed max size of a partition
of a partioning index?

>

> Hi!

>

> Created four TS´s in a test DB:

> CREATE DATABASE ROYTEST

> BUFFERPOOL BP0

> INDEXBP BP0

> STOGROUP SYSDEFLT

> CCSID EBCDIC ;

> COMMIT ;

> CREATE TABLESPACE ROYTRK1 IN ROYTEST

> USING STOGROUP SYSDEFLT

> PRIQTY 48

> SECQTY 0

> ERASE NO

> FREEPAGE 0

> PCTFREE 0

> BUFFERPOOL BP0

> LOCKSIZE ANY

> SEGSIZE 4

> LOCKMAX SYSTEM

> CLOSE YES

> CCSID EBCDIC ; COMMIT ;

> CREATE TABLESPACE ROYTRK2 IN ROYTEST

> USING STOGROUP SYSDEFLT

> PRIQTY 96

> SECQTY 0

> ERASE NO

> FREEPAGE 0

> PCTFREE 0

> BUFFERPOOL BP0

> LOCKSIZE ANY

> SEGSIZE 4

> LOCKMAX SYSTEM

> CLOSE YES

> CCSID EBCDIC ; COMMIT ;

> CREATE TABLESPACE ROYCYL1 IN ROYTEST

> USING STOGROUP SYSDEFLT

> PRIQTY 720

> SECQTY 0

> ERASE NO

> FREEPAGE 0

> PCTFREE 0

> BUFFERPOOL BP0

> LOCKSIZE ANY

> SEGSIZE 4

> LOCKMAX SYSTEM

> CLOSE YES

> CCSID EBCDIC ; COMMIT ;

> CREATE TABLESPACE ROYCYL2 IN ROYTEST

> USING STOGROUP SYSDEFLT

> PRIQTY 1440

> SECQTY 0

> ERASE NO

> FREEPAGE 0

> PCTFREE 0

> BUFFERPOOL BP0

> LOCKSIZE ANY

> SEGSIZE 4

> LOCKMAX SYSTEM

> CLOSE YES

> CCSID EBCDIC ; COMMIT ;

>

> Look in ISPF 3.4:

>

> Enter "/" to select action Tracks

> ---------------------------------------------------------

> DC10.DSNDBD.ROYTEST.ROYCYL1.I0001.A001 15

> DC10.DSNDBD.ROYTEST.ROYCYL2.I0001.A001 30

> DC10.DSNDBD.ROYTEST.ROYTRK1.I0001.A001 1

> DC10.DSNDBD.ROYTEST.ROYTRK2.I0001.A001 3

>

> So the weirdness begins... my two track space gets three... *strange*

> A look in SYSTABLESPACE (before runstats)

>

> SELECT NAME, NACTIVEF, NACTIVE FROM SYSIBM.SYSTABLESPACE WHERE DBNAME

> = 'ROYTEST'

> ORDER BY 1 ;

> ---------+---------+---------+---------+---------+---------+--

> NAME NACTIVEF NACTIVE

> ---------+---------+---------+---------+---------+---------+--

> ROYCYL1 -0.1000000000000000E+01 0

> ROYCYL2 -0.1000000000000000E+01 0

> ROYTRK1 -0.1000000000000000E+01 0

> ROYTRK2 -0.1000000000000000E+01 0

> DSNE610I NUMBER OF ROWS DISPLAYED IS 4

>

> A look in SYSTABLESPACESTATS

> SELECT NAME, NACTIVE , NPAGES , SPACE FROM SYSIBM.SYSTABLESPACESTATS

> WHERE DBNAME = 'ROYTEST'

> ORDER BY 1 ;

>
---------+---------+---------+---------+---------+---------+---------+-----

> NAME NACTIVE NPAGES

> SPACE

>
---------+---------+---------+---------+---------+---------+---------+-----

> ROYCYL1 180 0

> 720

> ROYCYL2 360 0

> 1440

> ROYTRK1 12 0

> 48

> ROYTRK2 36 0

> 144 DSNE610I NUMBER OF ROWS DISPLAYED IS 4

>

> After RUNSTATS:

> Nothing changed as the spaces were "incomplete"

> Created this table, changing the name and tablespace to match the

> tablespace of course, in all four:

> CREATE TABLE ROYTEST.ROYTRK1

> ("XCOUNT" INTEGER NOT

> NULL

> WITH DEFAULT

> ,"X01" INTEGER NOT

> NULL

> WITH DEFAULT

> ,"X02" DATE NOT

> NULL

> WITH DEFAULT

> ,"X03" TIME NOT

> NULL

> WITH DEFAULT

> ,"XCOUNT3" CHAR( 254) FOR SBCS DATA NOT

> NULL

> WITH DEFAULT

> ,"XCOUNT4" CHAR( 254) FOR SBCS DATA NOT

> NULL

> WITH DEFAULT

> )

> IN "ROYTEST"."ROYTRK1"

> AUDIT NONE

> DATA CAPTURE NONE

> CCSID EBCDIC ; Then a RUNSTATS and then the SPUFI again:

>

> SELECT NAME, NACTIVEF, NACTIVE

> 000 FROM SYSIBM.SYSTABLESPACE


> 000 WHERE DBNAME = 'ROYTEST'


> 000 ORDER BY 1


> 000 ;


> 000

>
---------+---------+---------+---------+---------+---------+---------+-----

> NAME NACTIVEF NACTIVE

>
---------+---------+---------+---------+---------+---------+---------+-----

> ROYCYL1 +0.1800000000000000E+03 180

> ROYCYL2 +0.3600000000000000E+03 360

> ROYTRK1 +0.1200000000000000E+02 12

> ROYTRK2 +0.3600000000000000E+02 36

> DSNE610I NUMBER OF ROWS DISPLAYED IS 4 DSNE616I STATEMENT EXECUTION

> WAS SUCCESSFUL, SQLCODE IS 100

>
---------+---------+---------+---------+---------+---------+---------+-----

> SELECT NAME, NACTIVE , NPAGES , SPACE

> 000 FROM SYSIBM.SYSTABLESPACESTATS


> 000 WHERE DBNAME = 'ROYTEST'


> 000 ORDER BY 1


> 000 ;


> 000

>
---------+---------+---------+---------+---------+---------+---------+-----

> NAME NACTIVE NPAGES

> SPACE

>
---------+---------+---------+---------+---------+---------+---------+-----

> ROYCYL1 180 0

> 720

> ROYCYL2 360 0

> 1440

> ROYTRK1 12 0

> 48

> ROYTRK2 36 0

> 144 DSNE610I NUMBER OF ROWS DISPLAYED IS 4

>

> So it all works as designed... your only real hope is to use the

> LISTCAT method and then analyse the HI-A-RBA and HI-U-RBA:

>

> ALLOCATION

> SPACE-TYPE------CYLINDER HI-A-RBA--------28753920

> SPACE-PRI--------------3 HI-U-RBA--------24059904

> SPACE-SEC--------------3

>

> Here you can see that after a REORG the Highest Used RBA is bit less

> than the Highest Allocated RBA.

>

> Various people on the list have written REXX´s etc. that help in

> extracting this info from LISTCAT a typical LISTCAT I use looks like

>

> LISTCAT ENTRY('DC10.DSNDBD.MVNXTEST.MVNXS02.I0001.A001') ALL

>

> Take care of I and J datasets and of course remember the A B C D E

> rule for partitioned objects and also CLONE objects with the

> INSTANCE... all so easy... I am sure you could make it more complicated if
you really really tried!

>

> Roy Boxwell

>

> SOFTWARE ENGINEERING GmbH and SEGUS Inc.

> -Product Development-

>

> Heinrichstrasse 83-85

> 40239 Duesseldorf/Germany

> Tel. +49 (0)211 96149-675

> Fax +49 (0)211 96149-32

> Email: <mailto:[login to unmask email]> [login to unmask email]

> Web http://www.seg.de http://www.seg.de

> Link zur Datenschutzerklärung

>

> Software Engineering GmbH

> Amtsgericht Düsseldorf, HRB 37894

> Geschäftsführung: Gerhard Schubert, Ulf Heinrich

>



<rest snipped>



---

This email has been checked for viruses by AVG.

https://www.avg.com https://www.avg.com



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



Attachments

  • smime.p7s (5.1k)