SEO

Apparently the maximum value of SECQTY for all tablespaces at a site I
am supporting is 131068 which is a pre version 6.2 maximum. Any
insight would be appreciated.

Spufi alter and catalogue queries:
********************************* Top of Data
**********************************
---------+---------+---------+---------+---------+---------+---------+---------+
select dbname, tsname, pqty*4, sqty*4 from sysibm.systablepart where
00010000
dbname='TESTDB' and tsname='TESTTS';
00020001
---------+---------+---------+---------+---------+---------+---------+---------+
DBNAME TSNAME

---------+---------+---------+---------+---------+---------+---------+---------+
TESTDB TESTTS 400000 131068

DSNE610I NUMBER OF ROWS DISPLAYED IS 1

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

---------+---------+---------+---------+---------+---------+---------+---------+
commit;
00030000
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

---------+---------+---------+---------+---------+---------+---------+---------+
ALTER TABLESPACE TESTDB.TESTTS SECQTY 200000;
00040000
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

---------+---------+---------+---------+---------+---------+---------+---------+
commit;
00041000
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

---------+---------+---------+---------+---------+---------+---------+---------+
select dbname, tsname, pqty*4, sqty*4 from sysibm.systablepart where
00050000
dbname='TESTDB' and tsname='TESTTS';
00060001
---------+---------+---------+---------+---------+---------+---------+---------+
DBNAME TSNAME

---------+---------+---------+---------+---------+---------+---------+---------+
TESTDB TESTTS 400000 131068

DSNE610I NUMBER OF ROWS DISPLAYED IS 1

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

---------+---------+---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE617I COMMIT PERFORMED, SQLCODE IS 0

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

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



V5 Manual:
If USING STOGROUP is specified, SECQTY has the default specified in the
description of USING STOGROUP. However, if ALTER TABLESPACE is being
used to convert from user-defined data sets to storage groups and
PRIQTY is specified, the default for SECQTY is either 10% of PRIQTY or
3 times the page size of the table space, whichever is
# larger (if this value exceeds 131068, the default is 131068.) If
SECQTY is specified, the secondary space allocation is at least n
kilobytes, where n is the value of integer except in these cases:
If the page size is 4KB and integer is greater than 131068, then n is
131068.
If the page size is 32KB and integer is greater than 131040, then n is
131040.

V 7 IBM Manual:
http://publib.boulder.ibm.com/cgi-bi...0041109152456&
If SECQTY is specified, the secondary space allocation is at least n
kilobytes, where n is the value of integer with the following
exceptions:
If integer is greater than 4194304 kilobytes, n is 4194304. A value of
0 for integer indicates that no data set can be extended.

<junk_bucket@hotmail.com> wrote in message
news:1113831423.262655.238600@z14g2000cwz.googlegr oups.com...
> Apparently the maximum value of SECQTY for all tablespaces at a site I
> am supporting is 131068 which is a pre version 6.2 maximum. Any
> insight would be appreciated.
>
What is the question?

Sorry for not stating my question clearly in my initial note.

Situation: My site which is version 7 has a maximum value of 131068
for secondary quantity(SECQTY) for a tablespace when the manual
indicates it should be 4194304.

Questions:

Am I reading the manual correctly and is this really a problem/issue?

Can anyone think why this would be the case?

What is the resolution to this if it is indeed a problem.


Information from my first note that may be of value:

Two references to online IBM manuals both version 5 and version 7 which
indicate a max value of 131068(for version 5) and 4194304(for version
7) for SECQTY.

SPUFI output in which I query the catalogue, alter the tablespace, and
then query the catalogue again. Despite altering the SECQTY to a
number above 131068 my subsequent query shows the value to be 131068 in
the catalogue.

"JB" <junk_bucket@hotmail.com> wrote in message
news:1113849316.415619.64770@g14g2000cwa.googlegro ups.com...
> Sorry for not stating my question clearly in my initial note.
>
> Situation: My site which is version 7 has a maximum value of 131068
> for secondary quantity(SECQTY) for a tablespace when the manual
> indicates it should be 4194304.
>
> Questions:
>
> Am I reading the manual correctly and is this really a problem/issue?
>
> Can anyone think why this would be the case?
>
> What is the resolution to this if it is indeed a problem.
>
>
> Information from my first note that may be of value:
>
> Two references to online IBM manuals both version 5 and version 7 which
> indicate a max value of 131068(for version 5) and 4194304(for version
> 7) for SECQTY.
>
> SPUFI output in which I query the catalogue, alter the tablespace, and
> then query the catalogue again. Despite altering the SECQTY to a
> number above 131068 my subsequent query shows the value to be 131068 in
> the catalogue.
>
I don't know the answer to your question, but I don't really understand why
you need such a large secondary quantity. Most times it is best to fit the
entire tablespace in the primary quantity. But if secondary quantities are
necessary, I believe that you get up to 127 secondary quantities if needed
(although that may be higher since I have not looked recently). That is, you
don't just get one secondary quantity.

The maximum number of extents is 254-256 irrc and has been for awhile.
Sometimes very large tablespaces, even partitioned, need to go into
several extents e.g. a history table with a large varchar field that
tracks every event for every policy for a multi-billion dollar
insurance company over the course of the last 100 years. You can't
always get what you need in the primary and need a large amount of
secondary space.

A friend of mine from another v7 z/os site queried his catalogues and
was unable to find a value higher than 131068. Is this the real v7
z/os limit?

A catalogue query from my first note:
select dbname, tsname, pqty*4, sqty*4 from sysibm.systablepart where
dbname='TESTDB' and tsname='TESTTS';

"JB" <junk_bucket@hotmail.com> wrote in message
news:1113851378.018342.248300@g14g2000cwa.googlegr oups.com...
> The maximum number of extents is 254-256 irrc and has been for awhile.
> Sometimes very large tablespaces, even partitioned, need to go into
> several extents e.g. a history table with a large varchar field that
> tracks every event for every policy for a multi-billion dollar
> insurance company over the course of the last 100 years. You can't
> always get what you need in the primary and need a large amount of
> secondary space.
>
> A friend of mine from another v7 z/os site queried his catalogues and
> was unable to find a value higher than 131068. Is this the real v7
> z/os limit?
>
> A catalogue query from my first note:
> select dbname, tsname, pqty*4, sqty*4 from sysibm.systablepart where
> dbname='TESTDB' and tsname='TESTTS';
>
I believe that the limit on extents is from VSAM. I do recall that it was
increased from 127 awhile ago, but could not remember what the current max
size is. I understand that "several extents" are necessary, but it seems to
me that 255 should be enough, even if the size is limited to 131068. Maybe
the primary should be larger.

Hello,

to support secondary quantity values above 32767 4k-pages (131.068 kB), two
new integer columns with name SECQTYI were added to catalog tables
SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART in DB2 z/OS Version 6. This
columns act like an "overflow" field for the columns SECQTY that can only
hold values up to 32767 because of their SMALLINT datatype nature. See the
SQL Reference Appendix D (DB2 Catalog Tables) for further details...

Regards - Walter SCHNEIDER.


<junk_bucket@hotmail.com> schrieb im Newsbeitrag
news:1113831423.262655.238600@z14g2000cwz.googlegr oups.com...
> Apparently the maximum value of SECQTY for all tablespaces at a site I
> am supporting is 131068 which is a pre version 6.2 maximum. Any
> insight would be appreciated.
>
> Spufi alter and catalogue queries:
> ********************************* Top of Data
> **********************************
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> select dbname, tsname, pqty*4, sqty*4 from sysibm.systablepart where
> 00010000
> dbname='TESTDB' and tsname='TESTTS';
> 00020001
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> DBNAME TSNAME
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> TESTDB TESTTS 400000 131068
>
> DSNE610I NUMBER OF ROWS DISPLAYED IS 1
>
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> commit;
> 00030000
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> ALTER TABLESPACE TESTDB.TESTTS SECQTY 200000;
> 00040000
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> commit;
> 00041000
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> select dbname, tsname, pqty*4, sqty*4 from sysibm.systablepart where
> 00050000
> dbname='TESTDB' and tsname='TESTTS';
> 00060001
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> DBNAME TSNAME
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> TESTDB TESTTS 400000 131068
>
> DSNE610I NUMBER OF ROWS DISPLAYED IS 1
>
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
> DSNE617I COMMIT PERFORMED, SQLCODE IS 0
>
> DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
>
> ---------+---------+---------+---------+---------+---------+---------+----
-----+
>
>
>
> V5 Manual:
> If USING STOGROUP is specified, SECQTY has the default specified in the
> description of USING STOGROUP. However, if ALTER TABLESPACE is being
> used to convert from user-defined data sets to storage groups and
> PRIQTY is specified, the default for SECQTY is either 10% of PRIQTY or
> 3 times the page size of the table space, whichever is
> # larger (if this value exceeds 131068, the default is 131068.) If
> SECQTY is specified, the secondary space allocation is at least n
> kilobytes, where n is the value of integer except in these cases:
> If the page size is 4KB and integer is greater than 131068, then n is
> 131068.
> If the page size is 32KB and integer is greater than 131040, then n is
> 131040.
>
> V 7 IBM Manual:
>
http://publib.boulder.ibm.com/cgi-bi...0041109152456&
> If SECQTY is specified, the secondary space allocation is at least n
> kilobytes, where n is the value of integer with the following
> exceptions:
> If integer is greater than 4194304 kilobytes, n is 4194304. A value of
> 0 for integer indicates that no data set can be extended.
>

Thank you Mr. Schneider! This was exactly the information I was looking
for. Despite attempting to google the information and search the IBM
manuals I was not able to come up with this answer.

Apparently our version of DBArtisan, 7.3.1 irrc, uses the SQTY as
opposed to the SECQTYI from the catalogue to reverse engineer the DDL.





SYSIBM.SYSTABLEPART table
http://publib.boulder.ibm.com/cgi-bi...0041109152456&