The DB2 STOSPACE utility

What STOSPACE measures

Db2-managed objects (USING STOGROUP) are linear VSAM data sets. The ICF catalog knows primary and secondary allocation. STOSPACE asks that catalog “how many kilobytes are allocated?” and stores the answer. It does not read table pages, does not compress data, and does not change PRIQTY or SECQTY. Phases are UTILINIT, STOSPACE, and UTILTERM. IBM documents that STOSPACE can run concurrently with other utilities: the target is catalog columns, not a drain of the user table space.

Allocated space is not the same as used space. A table space can have a 100-cylinder data set that is only 10% full. STOSPACE reports the 100 cylinders (as kilobytes). SYSTABLESPACESTATS.NACTIVE and similar RTS columns describe pages that contain data. RUNSTATS describes data distribution for SQL access paths. Capacity planning needs all three views; STOSPACE is the allocated-DASD view.

STOSPACE syntax

The control statement is short. You name one storage group or all of them.

1
2
3
STOSPACE STOGROUP (PAYSTO);
 
STOSPACE STOGROUP (*);

• STOGROUP (name) — update catalog space figures for every Db2-managed table space and index assigned to that storage group, and for the storage-group row itself
• STOGROUP (*) — the same for every storage group defined to the subsystem, including IBM defaults if they exist on this system

There is no TABLESPACE keyword. You do not list partitions. If an object was created USING VCAT, it is not “in” a STOGROUP, so this utility will not attribute its DASD to a group. To measure those data sets you use IDCAMS LISTCAT, DFSMS reports, or vendor space tools—not STOSPACE.

1
2
3
4
5
//STOSP   EXEC DSNUPROC,SYSTEM=DB2T,UID='DBA.STOSPACE'
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  STOSPACE STOGROUP (*)
/*

SYSIN and SYSPRINT are required. No COPYDDN, no SYSUT1. Schedule the job after large LOAD, REORG, or growth weekends so SPACE does not lag reality by months. A common standard is weekly on high-activity STOGROUPs and monthly with (*).

Catalog columns STOSPACE updates

SPACE and SPACEF

SPACE is an INTEGER number of kilobytes. On a partitioned index, SYSINDEXES.SPACE is the total for all partitions that are defined in a storage group. If the true allocated value is larger than SPACE can hold, Db2 uses SPACEF (a floating column) instead. Queries that only select SPACE can under-report huge table spaces. Prefer:

1
2
3
4
5
6
7
SELECT NAME, SPACE, SPACEF, STATSTIME
FROM SYSIBM.SYSSTOGROUP
ORDER BY NAME;
 
SELECT DBNAME, NAME, SPACE, SPACEF
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'PAYDB';

STATSTIME

SYSIBM.SYSSTOGROUP.STATSTIME is the timestamp of the last successful STOSPACE for that group. If STATSTIME is years old, your SPACE numbers are a snapshot of an old ICF catalog, not tonight’s DASD. Older catalogs also had SPCDATE (yyddd); modern reporting should use STATSTIME.

SYSINDEXES.SPACE in words

IBM: the number of kilobytes of DASD allocated to the index as determined by the last STOSPACE. The value is 0 if the index is not related to a storage group, or if STOSPACE has not been run. For a partitioned index space, it is the total for all partitions defined in a storage group. That “0” is the same story as on table spaces: user-managed, never-run, or nothing allocated that the ICF catalog still shows.

Reading zero SPACE without dropping the group

After STOSPACE, SPACE = 0 on SYSSTOGROUP looks like “nothing lives here.” Three innocent explanations exist before you DROP STOGROUP:

• No table space or index partition still names that STORNAME—confirm with SYSTABLEPART and SYSINDEXPART, not SPACE alone
• DFSMShsm (or equivalent) migrated data sets to ML2/tape; the ICF catalog may show migrated status with no current DASD allocation even though the objects still belong to the STOGROUP
• STOSPACE has never been run, so SPACE is still the default 0

1
2
3
4
5
6
7
8
9
10
SELECT ST.NAME, ST.SPACE, ST.SPACEF, ST.STATSTIME
FROM SYSIBM.SYSSTOGROUP ST
WHERE ST.SPACE = 0
  AND ST.SPACEF = 0
  AND NOT EXISTS (
        SELECT 1 FROM SYSIBM.SYSTABLEPART TP
        WHERE TP.STORNAME = ST.NAME)
  AND NOT EXISTS (
        SELECT 1 FROM SYSIBM.SYSINDEXPART IP
        WHERE IP.STORNAME = ST.NAME);

Only if that query returns the group and you have checked HSM and pending CREATE/DEFINE NO objects should you consider DROP STOGROUP. DEFINE NO table spaces have no data set until the first insert; STOSPACE will not invent allocation that does not exist yet.

Availability, work files, and temporary objects

STOSPACE updates catalog rows, so other jobs that hammer catalog indexes can time out the same way CATMAINT can—just far less dramatically, because STOSPACE is a catalog-column update, not a catalog-structure change. It does not put user table spaces in UTRW beyond ordinary catalog locking.

You can STOSPACE storage groups that contain objects in a work-file or declared-global temporary-table database (AS TEMP). IBM explicitly allows STOSPACE (and REPAIR DBD) on those temporary objects; LOAD, COPY, and REORG cannot run on declared temporary tables. That matters in shops that size DSNDB07 STOGROUPs and want catalog SPACE to match the work-file volumes.

STOSPACE is in the Db2 Utilities Suite, unlike REPORT and QUIESCE which are in the base set. Catalog and directory objects still run without the suite; user STOGROUP maintenance is a suite utility.

STOSPACE versus other space numbers

• PRIQTY / SECQTY on CREATE/ALTER are allocation requests in KB. Actual VSAM extents can differ after extends, REORG, or SMS.
• STOSPACE SPACE is allocated KB from the ICF catalog after the last STOSPACE run.
• RTS NACTIVE / SPACE (SYSTABLESPACESTATS) is in-use and Db2-maintained while the object is open; it does not require STOSPACE.
• IDCAMS LISTCAT is the z/OS source of truth at this instant; STOSPACE is a copy of that truth into SQL-queryable catalog tables.

If SPACE and LISTCAT disagree, STOSPACE is stale—rerun it. If SPACE and RTS disagree, that is expected: allocated versus used. If you need to free DASD, REORG with a smaller PRIQTY, DROP unused objects, or migrate—STOSPACE will only report the new allocation after you do the work.

Explain It Like I'm Five

A storage group is a labeled shelf in the warehouse. STOSPACE is a person with a tape measure who writes “this shelf uses 40 boxes of space” on a clipboard (the Db2 catalog). The clipboard is not the boxes. Measuring does not throw boxes away and does not count how full each box is—that is a different clipboard (RTS). If the warehouse robot moved boxes to the basement (HSM migrate), the tape measure might say zero even though the labels still belong to that shelf. RUNSTATS is counting crayons inside the boxes so the teacher can plan lessons; STOSPACE is only how much floor space the boxes take.

Exercises

1. Write a STOSPACE job for one application STOGROUP and a second statement for all groups.
2. Query SYSSTOGROUP for STATSTIME older than 30 days. What does that imply for SPACE?
3. Explain why SYSINDEXES.SPACE can be 0 for a COPY YES index that you know has a VSAM data set.
4. Compare STOSPACE SPACE, RTS NACTIVE, and PRIQTY for one table space and write one sentence on each number.
5. A DBA wants to DROP a STOGROUP because SPACE is 0. List the checks you would run first.