I think it was way back in version 4 of DB2 it became possible to change PRIQTY and SECQTY on tablespaces and indexes without having to perform a STOP. This has made life easier for a lot of us DB2 freaks. One of the real good details about changing SECQTY on the fly is that DB2 will honour the new SECQTY as soon as the DB2 dataset has to extent. If a DB2 dataset already has extented several times you may slow down the extention frequency considerably by increasing the value of SECQTY. How much you want to increase SECQTY is up to you and the seriousness of the situation.

By using ALTER SECQTY you may avoid running out of space or worst case delay the time where you run out of space. It can be done without doing anything else. You can avoid running a REORG or moving the dataset using ADRDSSU. Before you use the SECQTY possibility there are a few details you may want to know. First of all a LISTCAT of the DB2 dataset will show the secondary allocation for the dataset at creation time no matter what you have changed SECQTY to and no matter how many extents DB2 has taken using the new SECQTY value. You can see the effect of changing SECQTY on the individual extents. Secondly it is very annoying and very good to know that ALTER INDEX must be performed on the index name and not on DBNAME.SPACENAM as is the case for tablespaces.

Click here to see the syntax for ALTER TABLESPACE and here to see the syntax for ALTER INDEX. The syntax is from DB2 version 7. And now some examples:

ALTER TABLESPACE MYDB.MYTS SECQTY 720;

will change SECQTY to one cylinder on tablespace MYDB.MYTS. An

ALTER INDEX MYUSER.MYSUPERINDEX SECQTY 480;

will change SECQTY on index MYUSER.MYSUPERINDEX to 10 tracks.