Stogroup question - DB2 v8

Scott Hodgin

Stogroup question - DB2 v8
This past weekend, I alter a bunch of indexes to use a different stogroup
with a bunch of packs assigned to it, but I did not reorg the indexes.

I've recently had some jobs blowing up due to inability to extend datasets
for various indexes.

I'm puzzled. So I have to reorg/rebuild the indexes to get db2 to tabke
advantage of the new stogroup. The original stogroup that the index was
assigned to was a single pack which is now full. I would have thought the
extend request would have just looked on another pack assigned to the new
stogroup.

Thanks,

Scott Hodgin
Database Administrator, (MCTS-SQL Server 2005)
South Carolina Farm Bureau Insurance Company
[login to unmask email] <mailto:[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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Chuck Massaro

Re: Stogroup question - DB2 v8
(in response to Scott Hodgin)
Scott,

Altering the stogroup is considered altering a storage attribute. As a
general rule, changing a storage attribute requires the data to be moved,
either manually or via one of three utilities (see below). One exception
would be SECQTY.

From the DB2 for z/OS v8 SQL Reference manual, pg 462 states:

<snip>

Altering storage attributes: The USING, PRIQTY, SECQTY, and ERASE clauses
define the storage attributes of the index or partition. If you specify
the USING or ERASE clause when altering storage attributes, the index or
partition must be in the stopped state when the ALTER INDEX statement is
executed. A STOP DATABASE...SPACENAM... command can be used to stop the
index or partition.

If the catalog name changes, the changes take effect after you move the
data and start the index or partition using the START
DATABASE...SPACENAM... command. The catalog name can be implicitly or
explicitly changed by the ALTER INDEX statement. The catalog name also
changes when you move the data to a different device. See the procedures
for moving data in Part 2 (Volume 1) of DB2 Administration Guide. 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 index or partition.

Changes to the other storage attributes take effect the next time you use
the REORG, RECOVER, or LOAD REPLACE utility on the index or partition. If
you change the primary space allocation parameters or erase rule, you can
have the changes take effect earlier if you move the data before you start
the index or partition.

<snip>

HTH, Chuck

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Avram Friedman

Re: Stogroup question - DB2 v8
(in response to Chuck Massaro)
Stogroup question - DB2 v8Recovering the index would also work.

This is a small snip from the SQL reference indicating when changes take place

Altering storage attributes: The USING, PRIQTY, SECQTY, and ERASE clauses define the storage attributes of the index or partition. If you specify the USING or ERASE clause when altering storage attributes, the index or partition must be in the stopped state when the ALTER INDEX statement is executed. A STOP DATABASE...SPACENAM... command can be used to stop the index or partition.

If the catalog name changes, the changes take effect after you move the data and start the index or partition using the START DATABASE...SPACENAM... command. The catalog name can be implicitly or explicitly changed by the ALTER INDEX statement. The catalog name also changes when you move the data to a different device. See the procedures for moving data in DB2 Administration Guide.

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 index or partition. Changes to the other storage attributes take effect the next time you use the REORG, RECOVER, or LOAD REPLACE utility on the index or partition. If you change the primary space allocation parameters or erase rule, you can have the changes take effect earlier if you move the data before you start the index or partition.





Fir the operational circumstance you some other fix options include

Adding volumes to the STOGROUP via ALTER STOGROUP

Adding volumes via SMS adjustments

Freeing space on the volume by moving other datasets.



Best wishes for the new year.



Av Friedman

----- Original Message -----
From: Hodgin, Scott<mailto:[login to unmask email]>
Sent: Friday, December 29, 2006 1:28 AM
Subject: Stogroup question - DB2 v8


This past weekend, I alter a bunch of indexes to use a different stogroup with a bunch of packs assigned to it, but I did not reorg the indexes.

I've recently had some jobs blowing up due to inability to extend datasets for various indexes.

I'm puzzled. So I have to reorg/rebuild the indexes to get db2 to tabke advantage of the new stogroup. The original stogroup that the index was assigned to was a single pack which is now full. I would have thought the extend request would have just looked on another pack assigned to the new stogroup.

Thanks,

Scott Hodgin<>

Database Administrator, (MCTS-SQL Server 2005)

South Carolina Farm Bureau Insurance Company

[login to unmask email]<mailto:[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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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