When does an ALTER take effect

Karl Sniderman

When does an ALTER take effect
On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that this
all means that each secondary allocation should be 27 tracks. But if I look
at the VSAM LISTC for the dataset, it shows all secondary extents, including
some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA

Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]


Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently delete
the message and any prints or other copies. Thank you.

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

Steve Whittaker

Re: When does an ALTER take effect
(in response to Karl Sniderman)
Accordig to the manual:
Changes to secondary space allocation (secqty) take affect the next time db2 extends the dataset. The new value is NOT reflected in the catalog until you use a Reorg, Recover or Load Replace utility on the tablespace or partition.
This is in v7 but I think v6 is the same..


-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 2:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect


On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply that by 4, I get 1296. If I divide that by 48, I get 27. I think that this all means that each secondary allocation should be 27 tracks. But if I look at the VSAM LISTC for the dataset, it shows all secondary extents, including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I have to do to get it to take effect?
What am I misunderstanding?

TIA

Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]



Confidentiality: This message and any attachments are for the addressee only and may contain privileged or confidential information. If you have received this in error, please notify me immediately and permanently delete the message and any prints or other copies. Thank you.

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

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

[login to unmask email]

Re: When does an ALTER take effect
(in response to Steve Whittaker)
Hi Karl,

It should take effect as of the next creation of an extent. Take look at
SECQTYI on SYSTABLEPART. What does that say?

HTH,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 356-5317, Tie Line 8-697-5317

-----------------------------------------------------
Happiness is not around the corner.
Happiness is the Contour (SVT) of the road.
-----------------------------------------------------
The early bird gets the worm,
but the second mouse gets the cheese.
-----------------------------------------------------




"Sniderman, Karl"
<[login to unmask email] To: [login to unmask email]
K.COM> cc:
Sent by: DB2 Data Subject: When does an ALTER take effect
Base Discussion
List
<[login to unmask email]
ORG>


12/04/2003 02:48
PM
Please respond to
DB2 Database
Discussion list
at IDUG






On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that
this all means that each secondary allocation should be 27 tracks. But if
I look at the VSAM LISTC for the dataset, it shows all secondary extents,
including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA


Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]





Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently delete
the message and any prints or other copies. Thank you.


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

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

teldb2kals

Re: When does an ALTER take effect
(in response to damcon2@US.IBM.COM)
Hi Karl,

Changes get reflected in the integrated catalog(listcat) only after the
next time you run reorg/loadreplace/recover. The tablespace does use the
new SECQTY the next time it extends.

Regards,
Kals.

On Thu, 4 Dec 2003 13:48:20 -0600, Sniderman, Karl <[login to unmask email]>
wrote:

>On OS/390 DB2 V6
>I have a tablespace whose SECQTY was altered sometime in the past. The
>catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
>that by 4, I get 1296. If I divide that by 48, I get 27. I think that
this
>all means that each secondary allocation should be 27 tracks. But if I
look
>at the VSAM LISTC for the dataset, it shows all secondary extents,
including
>some recent ones, are all 3 tracks.
>I thought that an ALTER took immediate effect. Is this not so? What do I
>have to do to get it to take effect?
>What am I misunderstanding?
>
>TIA
>
>Karl Sniderman
>DB2 DBA
>Blue Cross Blue Shield of Oklahoma
>(918) 560-2068
>[login to unmask email]
>

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

Karl Sniderman

Re: When does an ALTER take effect
(in response to teldb2kals)
Well now I'm very confused. Two people wrote to me directly and said that
it will only take effect after a re-allocation of the dataset. You are
saying that that isn't necessary - aren't you? And Stephen Whittaker
quoted the manual as saying that it takes effect as of the next allocation.
It clearly doesn't take effect immediately - it hasn't taken effect.

SECQTYI has the same value as SQTY.

Can someone else clear this up?

-----Original Message-----
From: Jackson Reavill [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 2:21 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect


Hi Karl,

It should take effect as of the next creation of an extent. Take look at
SECQTYI on SYSTABLEPART. What does that say?

HTH,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 356-5317, Tie Line 8-697-5317

-----------------------------------------------------
Happiness is not around the corner.
Happiness is the Contour (SVT) of the road.
-----------------------------------------------------
The early bird gets the worm,
but the second mouse gets the cheese.
-----------------------------------------------------




"Sniderman, Karl"
<[login to unmask email] To:
[login to unmask email]
K.COM> cc:
Sent by: DB2 Data Subject: When does an ALTER
take effect
Base Discussion
List
<[login to unmask email]
ORG>


12/04/2003 02:48
PM
Please respond to
DB2 Database
Discussion list
at IDUG






On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that
this all means that each secondary allocation should be 27 tracks. But if
I look at the VSAM LISTC for the dataset, it shows all secondary extents,
including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA


Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]





Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently delete
the message and any prints or other copies. Thank you.


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

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm
Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently delete
the message and any prints or other copies. Thank you.

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

andrea milligan

Re: When does an ALTER take effect
(in response to Philip Sevetson)
If you don't do something to cause the physical dataset to be re-created,
you won't get the new allocation amounts. The numbers in DB2 are just that
-- in DB2. To get them to the dataset, you have to reorg, unload/reload,
imagecopy/recover or something that causes the physical dataset to be
dropped and recreated.

-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 2:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect


On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that this
all means that each secondary allocation should be 27 tracks. But if I look
at the VSAM LISTC for the dataset, it shows all secondary extents, including
some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA

Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]



Confidentiality: This message and any attachments are for the addressee only
and may contain privileged or confidential information. If you have received
this in error, please notify me immediately and permanently delete the
message and any prints or other copies. Thank you.

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



Please see the following link for the BlueCross BlueShield of Tennessee E-mail disclaimer: http://www.bcbst.com/email_disclaimer.shtm


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

Philip Sevetson

Re: When does an ALTER take effect
(in response to Karl Sniderman)
Karl,

A change of PRIQTY is felt only when a new PRIQTY is requested; this
occurs only during REORG, LOAD/REPLACE, RECOVER, or exceeding the DSSIZE
specified for the tablespace.

A change of SECQTY takes effect _immediately_ for future extents, but is
_not_ applied to existing dataset extents. In order to change the size of
existing extents, you must perform an operation which will cause DB2 to
DELETE/DEFINE the VSAM datasets where it stores your data... i.e., REORG,
LOAD/REPLACE, or RECOVER.

The above advice is valid for DB2-defined ("STOGROUP-defined") datasets.
For User-defined ("VCAT-defined") datasets, the rules are the same but
more DBA intervention is required.

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






"Sniderman, Karl" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/04/2003 03:36 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: When does an ALTER take effect


Well now I'm very confused. Two people wrote to me directly and said that
it will only take effect after a re-allocation of the dataset. You are
saying that that isn't necessary - aren't you? And Stephen Whittaker
quoted the manual as saying that it takes effect as of the next
allocation.
It clearly doesn't take effect immediately - it hasn't taken effect.

SECQTYI has the same value as SQTY.

Can someone else clear this up?

-----Original Message-----
From: Jackson Reavill [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 2:21 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect


Hi Karl,

It should take effect as of the next creation of an extent. Take look at
SECQTYI on SYSTABLEPART. What does that say?

HTH,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 356-5317, Tie Line 8-697-5317

-----------------------------------------------------
Happiness is not around the corner.
Happiness is the Contour (SVT) of the road.
-----------------------------------------------------
The early bird gets the worm,
but the second mouse gets the cheese.
-----------------------------------------------------




"Sniderman, Karl"
<[login to unmask email] To:
[login to unmask email]
K.COM> cc:
Sent by: DB2 Data Subject: When does an
ALTER
take effect
Base Discussion
List
<[login to unmask email]
ORG>


12/04/2003 02:48
PM
Please respond to
DB2 Database
Discussion list
at IDUG






On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that
this all means that each secondary allocation should be 27 tracks. But if
I look at the VSAM LISTC for the dataset, it shows all secondary extents,
including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA


Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]





Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently
delete
the message and any prints or other copies. Thank you.


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

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm
Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently
delete
the message and any prints or other copies. Thank you.

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



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

[login to unmask email]

Re: When does an ALTER take effect
(in response to andrea milligan)
The PQTY and SECQTYI values are used whenever DB2 needs to allocate a VSAM
dataset.

The happens when it needs to add another extent or when you perform a
RECOVER, REORG or LOAD REPLACE all of which define new datasets.

SECQTYI is the new and improved value for SQTY. SQTY is limited to 32,767.
SECQTYI can hold a larger value and allows DB2 to allocate larger secondary
extents.

Carol Sutfin
Corporate DBA
AmSouth Bank
(205)261-5214
[login to unmask email]




"Sniderman,
Karl" To: [login to unmask email]
<[login to unmask email] cc:
OK.COM> Subject: Re: When does an ALTER take effect
Sent by: "DB2
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/04/2003 02:36
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





Well now I'm very confused. Two people wrote to me directly and said that
it will only take effect after a re-allocation of the dataset. You are
saying that that isn't necessary - aren't you? And Stephen Whittaker
quoted the manual as saying that it takes effect as of the next allocation.
It clearly doesn't take effect immediately - it hasn't taken effect.

SECQTYI has the same value as SQTY.

Can someone else clear this up?

-----Original Message-----
From: Jackson Reavill [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 2:21 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect


Hi Karl,

It should take effect as of the next creation of an extent. Take look at
SECQTYI on SYSTABLEPART. What does that say?

HTH,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 356-5317, Tie Line 8-697-5317

-----------------------------------------------------
Happiness is not around the corner.
Happiness is the Contour (SVT) of the road.
-----------------------------------------------------
The early bird gets the worm,
but the second mouse gets the cheese.
-----------------------------------------------------




"Sniderman, Karl"
<[login to unmask email] To:
[login to unmask email]
K.COM> cc:
Sent by: DB2 Data Subject: When does an ALTER
take effect
Base Discussion
List
<[login to unmask email]
ORG>


12/04/2003 02:48
PM
Please respond to
DB2 Database
Discussion list
at IDUG






On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that
this all means that each secondary allocation should be 27 tracks. But if
I look at the VSAM LISTC for the dataset, it shows all secondary extents,
including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA


Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]





Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently delete
the message and any prints or other copies. Thank you.


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

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

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

-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm
Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently delete
the message and any prints or other copies. Thank you.

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

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

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

Harold Lee

Re: When does an ALTER take effect
(in response to csutfin@AMSOUTH.COM)
You need to reorg the tablespace.

-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 12:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect


On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that this
all means that each secondary allocation should be 27 tracks. But if I look
at the VSAM LISTC for the dataset, it shows all secondary extents, including
some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA

Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]



Confidentiality: This message and any attachments are for the addressee only
and may contain privileged or confidential information. If you have received
this in error, please notify me immediately and permanently delete the
message and any prints or other copies. Thank you.

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


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

Karl Sniderman

Re: When does an ALTER take effect
(in response to Harold Lee)
OK. Thank you all very much.
Karl

-----Original Message-----
From: Duane Lee - EGOVX [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 3:30 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect


You need to reorg the tablespace.

-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 12:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect


On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that this
all means that each secondary allocation should be 27 tracks. But if I look
at the VSAM LISTC for the dataset, it shows all secondary extents, including
some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA

Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]



Confidentiality: This message and any attachments are for the addressee only
and may contain privileged or confidential information. If you have received
this in error, please notify me immediately and permanently delete the
message and any prints or other copies. Thank you.

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

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

Confidentiality: This message and any attachments are for the addressee
only and may contain privileged or confidential information. If you have
received this in error, please notify me immediately and permanently delete
the message and any prints or other copies. Thank you.

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

Linda Billings

Re: When does an ALTER take effect
(in response to Karl Sniderman)
Hi, Karl,
OK, I'll add even more confusion to all of this. Here is a quote from
the V6 SQL Reference manual says, "Changes to the secondary space allocation
(SECQTY) take effect the next time DB2 extends the data set; however, the
new value is not reflected in the integrated catalog until you use the
REORG, RECOVER, or LOAD REPLACE utility on the table space or partition."
I am not sure what this means. It almost reads like the catalog, which
is what you are querying when you perform a LISTC, doesn't contain the
correct value until you perform some utility that will recreate the
underlying VSAM data set.
Another thing to consider is 48K the correct value to use when
calculating tracks for the disk volume that this tablespace is on?
The V6 SQL Reference Manual also says "This clause (SECQTY) can be
specified only if the data set is managed by DB2, and if one of the
following is true: USING STOGROUP is specified, A USING clause is not
specified." So the question must be asked, is this a DB2-controlled data
set?
Linda Billings
Database Administrator
State of Wisconsin
Department of Administration
Division of Enterprise Technology
Bureau of Development and Operations
-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 1:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect



On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that this
all means that each secondary allocation should be 27 tracks. But if I look
at the VSAM LISTC for the dataset, it shows all secondary extents, including
some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

TIA

Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]



Confidentiality: This message and any attachments are for the addressee only
and may contain privileged or confidential information. If you have received
this in error, please notify me immediately and permanently delete the
message and any prints or other copies. Thank you.

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


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

Philip Sevetson

Re: When does an ALTER take effect
(in response to Linda Billings)
Linda,
"The integrated catalog" is NOT the same thing as the DB2 Catalog.
Integrated catalog is where MVS looks to find physical al-locations of
existing datasets. It contains the names and aliases of datasets and the
physical information about them. The TSO LISTCAT command reads the data
in the integrated catalog.

Does that make things any clearer?

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






"Billings, Linda" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/04/2003 05:18 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: When does an ALTER take effect


Hi, Karl,
OK, I'll add even more confusion to all of this. Here is a quote from the
V6 SQL Reference manual says, "Changes to the secondary space allocation (SECQTY) take effect the next
time DB2 extends the data set; however, the new value is not reflected in
the integrated catalog until you use the REORG, RECOVER, or LOAD REPLACE
utility on the table space or partition."
I am not sure what this means. It almost reads like the catalog, which is
what you are querying when you perform a LISTC, doesn't contain the
correct value until you perform some utility that will recreate the
underlying VSAM data set.
Another thing to consider is 48K the correct value to use when calculating
tracks for the disk volume that this tablespace is on?
The V6 SQL Reference Manual also says "This clause (SECQTY) can be
specified only if the data set is managed by DB2, and if one of the
following is true: USING STOGROUP is specified, A USING clause is not specified." So the
question must be asked, is this a DB2-controlled data set?
Linda Billings
-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 1:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect

On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that
this all means that each secondary allocation should be 27 tracks. But if
I look at the VSAM LISTC for the dataset, it shows all secondary extents,
including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?


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

Linda Billings

Re: When does an ALTER take effect
(in response to Philip Sevetson)
Hi, Phil,
Thanks, but I already knew that. When an alter is performed, the change
is made in the DB2 catalog. When Karl performs a LISTC, he is querying the
ICF catalog. It sounds like the manual is saying that the size of the
secondary quantity is not reflected in the ICF catalog until after some
utility such as a reorg is performed against the tablespace. That seems a
bit strange to me and that was what I was commenting on when I said I wasn't
sure what that meant.

Regards,

Linda Billings

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 4:24 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect



Linda,
"The integrated catalog" is NOT the same thing as the DB2 Catalog.
Integrated catalog is where MVS looks to find physical al-locations of
existing datasets. It contains the names and aliases of datasets and the
physical information about them. The TSO LISTCAT command reads the data in
the integrated catalog.

Does that make things any clearer?

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





"Billings, Linda" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>


12/04/2003 05:18 PM
Please respond to DB2 Database Discussion list at IDUG



To: [login to unmask email]
cc:
Subject: Re: When does an ALTER take effect



Hi, Karl,
OK, I'll add even more confusion to all of this. Here is a quote from
the V6 SQL Reference manual says, "Changes to the secondary space allocation
(SECQTY) take effect the next time DB2 extends the data set; however, the
new value is not reflected in the integrated catalog until you use the
REORG, RECOVER, or LOAD REPLACE utility on the table space or partition."
I am not sure what this means. It almost reads like the catalog, which
is what you are querying when you perform a LISTC, doesn't contain the
correct value until you perform some utility that will recreate the
underlying VSAM data set.
Another thing to consider is 48K the correct value to use when
calculating tracks for the disk volume that this tablespace is on?
The V6 SQL Reference Manual also says "This clause (SECQTY) can be
specified only if the data set is managed by DB2, and if one of the
following is true: USING STOGROUP is specified, A USING clause is not
specified." So the question must be asked, is this a DB2-controlled data
set?

Linda Billings
-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 1:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect



On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that this
all means that each secondary allocation should be 27 tracks. But if I look
at the VSAM LISTC for the dataset, it shows all secondary extents, including
some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

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


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

Randy Bright

Re: When does an ALTER take effect
(in response to Linda Billings)
Let me see if I can help clear up some of the confusion.

When DB2 does an IDCAMS DEFINE of a DB2 object dataset, it uses the PRIQTY
and SECQTY (or SECQTYI) values for the primary and secondary allocation
values. The ICF catalog (what TSO LISTCAT reads) reflects these values and
is, at that point, in sync with the DB2 catalog.

When you ALTER the SECQTY value in DB2, it does not change the secondary
quantity in the ICF catalog. So now the DB2 catalog SECQTY value is not is
sync with the ICF catalog secondary quantity value.

When DB2 extends a VSAM dataset, it does not use the secondary allocation
value in the ICF catalog, it uses the value in SECQTY in the DB2 catalog.
So the result is that any extents taken before the ALTER was done will be
the original SECQTY size, but any extents taken after the ALTER will be the
new SECQTY. Therefore the ALTER of SECQTY does take effect immediately, but
existing extents will not change is size.

If you do a LISTCAT before the alter, then ALTER the SECQTY in the DB2
catalog, followed by some insert processing that causes additional extents,
and finally another LISTCAT, you will see the first extents will be smaller
than the ones generated by the post-ALTER inserts.

When you run a utility (such as REORG or LOAD) that deletes and re-defines
the VSAM datasets, DB2 uses the current values in the DB2 catalog to do the
IDCAMS DEFINE, so now all extents taken during or after the utility will be
the same size (the ALTERed size) and the DB2 catalog is again in sync with
the ICF catalog. Until, of course, you ALTER it again.

So the statements in the manuals are correct, ALTER of SECQTY does take
effect immediately for any new extents acquired, but if you want the ICF
catalog to reflect the new SECQTY, you must run a utility to DELETE and
DEFINE the object.

Now to add back a little confusion, you must remember that any extend
request can be satisfied in as many as five actual extents. Just be aware
that it is possible that when you look at a LISTCAT listing, you may see
extents listed that are smaller than any SECQTY size that has ever been on
the DB2 or ICF catalogs. This is normal and to be expected.

I hope this helps.

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
[login to unmask email] <mailto:[login to unmask email]>



-----Original Message-----
From: Billings, Linda [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 4:31 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect


Hi, Phil,
Thanks, but I already knew that. When an alter is performed, the change
is made in the DB2 catalog. When Karl performs a LISTC, he is querying the
ICF catalog. It sounds like the manual is saying that the size of the
secondary quantity is not reflected in the ICF catalog until after some
utility such as a reorg is performed against the tablespace. That seems a
bit strange to me and that was what I was commenting on when I said I wasn't
sure what that meant.

Regards,

Linda Billings

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 4:24 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect



Linda,
"The integrated catalog" is NOT the same thing as the DB2 Catalog.
Integrated catalog is where MVS looks to find physical al-locations of
existing datasets. It contains the names and aliases of datasets and the
physical information about them. The TSO LISTCAT command reads the data in
the integrated catalog.

Does that make things any clearer?

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





"Billings, Linda" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>


12/04/2003 05:18 PM
Please respond to DB2 Database Discussion list at IDUG



To: [login to unmask email]
cc:
Subject: Re: When does an ALTER take effect



Hi, Karl,
OK, I'll add even more confusion to all of this. Here is a quote from
the V6 SQL Reference manual says, "Changes to the secondary space allocation
(SECQTY) take effect the next time DB2 extends the data set; however, the
new value is not reflected in the integrated catalog until you use the
REORG, RECOVER, or LOAD REPLACE utility on the table space or partition."
I am not sure what this means. It almost reads like the catalog, which
is what you are querying when you perform a LISTC, doesn't contain the
correct value until you perform some utility that will recreate the
underlying VSAM data set.
Another thing to consider is 48K the correct value to use when
calculating tracks for the disk volume that this tablespace is on?
The V6 SQL Reference Manual also says "This clause (SECQTY) can be
specified only if the data set is managed by DB2, and if one of the
following is true: USING STOGROUP is specified, A USING clause is not
specified." So the question must be asked, is this a DB2-controlled data
set?

Linda Billings
-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 1:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect



On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The
catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply
that by 4, I get 1296. If I divide that by 48, I get 27. I think that this
all means that each secondary allocation should be 27 tracks. But if I look
at the VSAM LISTC for the dataset, it shows all secondary extents, including
some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I
have to do to get it to take effect?
What am I misunderstanding?

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

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


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

Michael Ebert

Re: When does an ALTER take effect
(in response to Randy Bright)
And to add some of the confusion back in again: when DB2 hands the
allocation request (for a primary or secondary extent) to IDCAMS, it will
first round the value UP to a multiple of the pagesize. IDCAMS may then do
another set of rounding because IDCAMS allocates space in CA chunks: CA
size is the minimum of (priqty, secqty) rounded up to tracks but not more
than one cylinder. The pri/secqty allocated by IDCAMS will be rounded up
to a CA multiple.
For this reason, you cannot e.g. get a one CYL pri/secqty (CYL,(1,1)) if
your pagesize is 32KB (first rule above) - it will get rounded to
(CYL,(2,2)). Also, you cannot get one that is e.g. (TRK,(2,3))
irrespective of pagesize because of rule 2: it will get rounded up to
(TRK,(2,4)).
If you have an old version of OS/390 (maybe 2001 maintenance level), there
is a bug whereby the secondary extent will be one allocation unit (TRK or
CYL) bigger than actually requested.
With a small allocation (27 tracks in this case), it is very unlikely that
IDCAMS will require more than one extent to satisfy the request. The main
thing to remember is that while the next extent will get allocated using
the new DB2 PRIQTY (subject to the rules above), it will not change the
VSAM file secqty you get from a LISTCAT. You have to look at the size of
the individual extent (or simply compare the size of the file before/after
the extent was added).
And as a last remark, if you want to change the primary qty of the VSAM
file, then a REORG is usually overkill. An ADRDSSU COPY will do it as
well. With the new disk hardware, this usually takes only a couple of
seconds no matter what the size of the file is. The only disadvantage is
that you will need to STOP the table/indexspace(-partition).

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Let me see if I can help clear up some of the confusion.

When DB2 does an IDCAMS DEFINE of a DB2 object dataset, it uses the PRIQTY
and SECQTY (or SECQTYI) values for the primary and secondary allocation
values. The ICF catalog (what TSO LISTCAT reads) reflects these values
and is, at that point, in sync with the DB2 catalog.

When you ALTER the SECQTY value in DB2, it does not change the secondary
quantity in the ICF catalog. So now the DB2 catalog SECQTY value is not
is sync with the ICF catalog secondary quantity value.

When DB2 extends a VSAM dataset, it does not use the secondary allocation
value in the ICF catalog, it uses the value in SECQTY in the DB2 catalog.
So the result is that any extents taken before the ALTER was done will be
the original SECQTY size, but any extents taken after the ALTER will be
the new SECQTY. Therefore the ALTER of SECQTY does take effect
immediately, but existing extents will not change is size.

If you do a LISTCAT before the alter, then ALTER the SECQTY in the DB2
catalog, followed by some insert processing that causes additional
extents, and finally another LISTCAT, you will see the first extents will
be smaller than the ones generated by the post-ALTER inserts.

When you run a utility (such as REORG or LOAD) that deletes and re-defines
the VSAM datasets, DB2 uses the current values in the DB2 catalog to do
the IDCAMS DEFINE, so now all extents taken during or after the utility
will be the same size (the ALTERed size) and the DB2 catalog is again in
sync with the ICF catalog. Until, of course, you ALTER it again.

So the statements in the manuals are correct, ALTER of SECQTY does take
effect immediately for any new extents acquired, but if you want the ICF
catalog to reflect the new SECQTY, you must run a utility to DELETE and
DEFINE the object.

Now to add back a little confusion, you must remember that any extend
request can be satisfied in as many as five actual extents. Just be aware
that it is possible that when you look at a LISTCAT listing, you may see
extents listed that are smaller than any SECQTY size that has ever been on
the DB2 or ICF catalogs. This is normal and to be expected.

I hope this helps.

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
[login to unmask email]


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

Phil Grainger

Re: When does an ALTER take effect
(in response to Michael Ebert)
Also, a 3 track secondary can mean that your pageset has (at some time) gone into "minimal allocation". This is when there is insufficient space on a device to allocate a complete secondary and, instead of failing the extend, DB2 just grabs smaller pieces of DASD

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Sniderman, Karl
Sent: Thursday, December 04, 2003 7:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect


On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in the past. The catalog display for the TABLESPACEPART shows SQTY as 324. If I multiply that by 4, I get 1296. If I divide that by 48, I get 27. I think that this all means that each secondary allocation should be 27 tracks. But if I look at the VSAM LISTC for the dataset, it shows all secondary extents, including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this not so? What do I have to do to get it to take effect?
What am I misunderstanding?

TIA

Karl Sniderman
DB2 DBA
Blue Cross Blue Shield of Oklahoma
(918) 560-2068
[login to unmask email]



Confidentiality: This message and any attachments are for the addressee only and may contain privileged or confidential information. If you have received this in error, please notify me immediately and permanently delete the message and any prints or other copies. Thank you.

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


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

Wayne Driscoll

Re: When does an ALTER take effect
(in response to Phil Grainger)
Another thing to keep in mind is that even if DB2, when it issues the
media manager commands to extend the database is that if DB2 asks for
new extent that is (numbers made up) to be 20 cylinders in size, but the
largest extent on the volume is 5 cyliniders, then the extent will only
be 5 cylinders.
Wayne Driscoll
Sr. Software Developer
Quest Software
[login to unmask email]
NOTE: All opinions are strictly my own. EMail Address in sig must be
modified.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Billings, Linda
Sent: Thursday, December 04, 2003 4:31 PM
To: [login to unmask email]
Subject: Re: [DB2-L] When does an ALTER take effect


Hi, Phil,
Thanks, but I already knew that. When an alter is
performed, the change is made in the DB2 catalog. When Karl performs a
LISTC, he is querying the ICF catalog. It sounds like the manual is
saying that the size of the secondary quantity is not reflected in the
ICF catalog until after some utility such as a reorg is performed
against the tablespace. That seems a bit strange to me and that was
what I was commenting on when I said I wasn't sure what that meant.

Regards,

Linda Billings

-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 4:24 PM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect



Linda,
"The integrated catalog" is NOT the same thing as the
DB2 Catalog. Integrated catalog is where MVS looks to find physical
al-locations of existing datasets. It contains the names and aliases of
datasets and the physical information about them. The TSO LISTCAT
command reads the data in the integrated catalog.

Does that make things any clearer?

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





"Billings, Linda" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

12/04/2003 05:18 PM
Please respond to DB2 Database Discussion list at IDUG



To: [login to unmask email]
cc:
Subject: Re: When does an ALTER take effect



Hi, Karl,
OK, I'll add even more confusion to all of this.
Here is a quote from the V6 SQL Reference manual says, "Changes to the
secondary space allocation (SECQTY) take effect the next time DB2
extends the data set; however, the new value is not reflected in the
integrated catalog until you use the REORG, RECOVER, or LOAD REPLACE
utility on the table space or partition."
I am not sure what this means. It almost reads like
the catalog, which is what you are querying when you perform a LISTC,
doesn't contain the correct value until you perform some utility that
will recreate the underlying VSAM data set.
Another thing to consider is 48K the correct value
to use when calculating tracks for the disk volume that this tablespace
is on?
The V6 SQL Reference Manual also says "This clause
(SECQTY) can be specified only if the data set is managed by DB2, and if
one of the following is true: USING STOGROUP is specified, A USING
clause is not specified." So the question must be asked, is this a
DB2-controlled data set?

Linda Billings
-----Original Message-----
From: Sniderman, Karl [mailto:[login to unmask email]
Sent: Thursday, December 04, 2003 1:48 PM
To: [login to unmask email]
Subject: When does an ALTER take effect


On OS/390 DB2 V6
I have a tablespace whose SECQTY was altered sometime in
the past. The catalog display for the TABLESPACEPART shows SQTY as 324.
If I multiply that by 4, I get 1296. If I divide that by 48, I get 27.
I think that this all means that each secondary allocation should be 27
tracks. But if I look at the VSAM LISTC for the dataset, it shows all
secondary extents, including some recent ones, are all 3 tracks.
I thought that an ALTER took immediate effect. Is this
not so? What do I have to do to get it to take effect?
What am I misunderstanding?


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


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


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

Linda Billings

Re: When does an ALTER take effect
(in response to Wayne Driscoll)
Thanks for the explanation, Michael and Randy. When I read the manual's
explanation of secondary extents, it almost sounded like the DB2 size would
be used in the extent, but the old size would not be recorded in the ICF
catalog. That didn't make sense. Your explanations do.

Have a nice weekend.

Linda Billings
Database Administrator
State of Wisconsin
Department of Administration
Division of Enterprise Technology
Bureau of Development and Operations
[login to unmask email] <mailto:[login to unmask email]>

"The problem with people who have no vices is that generally you can be
pretty sure they're going to have some pretty annoying virtues." - Elizabeth
Taylor

-----Original Message-----
From: Michael Ebert [mailto:[login to unmask email]
Sent: Friday, December 05, 2003 2:37 AM
To: [login to unmask email]
Subject: Re: When does an ALTER take effect



And to add some of the confusion back in again: when DB2 hands the
allocation request (for a primary or secondary extent) to IDCAMS, it will
first round the value UP to a multiple of the pagesize. IDCAMS may then do
another set of rounding because IDCAMS allocates space in CA chunks: CA size
is the minimum of (priqty, secqty) rounded up to tracks but not more than
one cylinder. The pri/secqty allocated by IDCAMS will be rounded up to a CA
multiple.
For this reason, you cannot e.g. get a one CYL pri/secqty (CYL,(1,1)) if
your pagesize is 32KB (first rule above) - it will get rounded to
(CYL,(2,2)). Also, you cannot get one that is e.g. (TRK,(2,3)) irrespective
of pagesize because of rule 2: it will get rounded up to (TRK,(2,4)).
If you have an old version of OS/390 (maybe 2001 maintenance level), there
is a bug whereby the secondary extent will be one allocation unit (TRK or
CYL) bigger than actually requested.
With a small allocation (27 tracks in this case), it is very unlikely that
IDCAMS will require more than one extent to satisfy the request. The main
thing to remember is that while the next extent will get allocated using the
new DB2 PRIQTY (subject to the rules above), it will not change the VSAM
file secqty you get from a LISTCAT. You have to look at the size of the
individual extent (or simply compare the size of the file before/after the
extent was added).
And as a last remark, if you want to change the primary qty of the VSAM
file, then a REORG is usually overkill. An ADRDSSU COPY will do it as well.
With the new disk hardware, this usually takes only a couple of seconds no
matter what the size of the file is. The only disadvantage is that you will
need to STOP the table/indexspace(-partition).

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Let me see if I can help clear up some of the confusion.

When DB2 does an IDCAMS DEFINE of a DB2 object dataset, it uses the PRIQTY
and SECQTY (or SECQTYI) values for the primary and secondary allocation
values. The ICF catalog (what TSO LISTCAT reads) reflects these values and
is, at that point, in sync with the DB2 catalog.

When you ALTER the SECQTY value in DB2, it does not change the secondary
quantity in the ICF catalog. So now the DB2 catalog SECQTY value is not is
sync with the ICF catalog secondary quantity value.

When DB2 extends a VSAM dataset, it does not use the secondary allocation
value in the ICF catalog, it uses the value in SECQTY in the DB2 catalog.
So the result is that any extents taken before the ALTER was done will be
the original SECQTY size, but any extents taken after the ALTER will be the
new SECQTY. Therefore the ALTER of SECQTY does take effect immediately, but
existing extents will not change is size.

If you do a LISTCAT before the alter, then ALTER the SECQTY in the DB2
catalog, followed by some insert processing that causes additional extents,
and finally another LISTCAT, you will see the first extents will be smaller
than the ones generated by the post-ALTER inserts.

When you run a utility (such as REORG or LOAD) that deletes and re-defines
the VSAM datasets, DB2 uses the current values in the DB2 catalog to do the
IDCAMS DEFINE, so now all extents taken during or after the utility will be
the same size (the ALTERed size) and the DB2 catalog is again in sync with
the ICF catalog. Until, of course, you ALTER it again.

So the statements in the manuals are correct, ALTER of SECQTY does take
effect immediately for any new extents acquired, but if you want the ICF
catalog to reflect the new SECQTY, you must run a utility to DELETE and
DEFINE the object.

Now to add back a little confusion, you must remember that any extend
request can be satisfied in as many as five actual extents. Just be aware
that it is possible that when you look at a LISTCAT listing, you may see
extents listed that are smaller than any SECQTY size that has ever been on
the DB2 or ICF catalogs. This is normal and to be expected.

I hope this helps.

Randy Bright
Architect, DB2 Utilities
BMC Software, Inc.
<mailto:[login to unmask email]> [login to unmask email]

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


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