Db2 storage groups

What a STOGROUP is

In the Db2 object hierarchy, a storage group is a set of volumes on disks that hold the data sets in which tables and indexes are stored. After you define a storage group, Db2 records it in the catalog: SYSIBM.SYSSTOGROUP has a row for each group, and SYSIBM.SYSVOLUMES has a row for each volume. That Db2 catalog is not the same as the integrated catalog facility (ICF) catalog that describes VSAM data sets to z/OS.

You create a storage group with CREATE STOGROUP. Installation defines a default group named SYSDEFLT. If you are authorized and do not take special steps to manage your own storage, you can still define tables, indexes, table spaces, and databases; Db2 can allocate auxiliary storage using SYSDEFLT.

1
2
3
CREATE STOGROUP MYSTOGRP
  VOLUMES (*)
  VCAT ALIASICF;

IBM recommends using storage groups whenever you can—explicitly or by accepting the default. Some organizations manage user-defined data sets themselves for tighter physical control; that approach is more complex and outside this beginner intro.

Volumes and SMS concepts at a high level

Classic CREATE STOGROUP examples list volume serial numbers. Modern shops usually hand placement to DFSMS (System Managed Storage). With SMS, you often specify VOLUMES (*) and supply SMS management, data, and storage classes so policies—not hard-coded volsers—decide where extents land.

Think of SMS classes as labeled shipping rules: how data is managed (migration, backup policies), what data set characteristics apply, and which storage performance tier is appropriate. Db2 asks for space through the STOGROUP; SMS and the storage team own the deep device details.

VCAT

The VCAT name on CREATE STOGROUP relates to the catalog alias used for the underlying data sets. You will see VCAT names in DBA standards documents; treat them as part of site naming, not something application SQL changes daily.

Performance hint (conceptual)

IBM notes you can assign frequently accessed objects to faster devices and seldom-used tables to slower ones by how you choose storage groups. With SMS, that often means different storage classes or groups aligned to workload tiers—not application developers picking disk addresses.

How tables and indexes use storage groups

Applications SELECT from tables. Allocation happens when table spaces and indexes are created (and when they extend). You name the storage group on CREATE TABLESPACE or CREATE INDEX, or you inherit defaults from the database or SYSDEFLT.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE APPDB
  STOGROUP MYSTOGRP
  BUFFERPOOL BP1
  INDEXBP BP2;
 
CREATE TABLESPACE EMPTS IN APPDB
  USING STOGROUP MYSTOGRP
  PRIQTY 100 SECQTY 100
  DEFINE YES;
 
CREATE INDEX HR.XEMP1
  ON HR.EMPLOYEE (EMPNO)
  USING STOGROUP MYSTOGRP
  PRIQTY 40 SECQTY 20;

CREATE DATABASE can establish a default STOGROUP for objects in that database. Individual spaces and indexes can override. PRIQTY/SECQTY style quantities (and SMS equivalents) influence how much space is requested—exact clauses evolve with Db2 versions and SMS usage.

• Table data — table space data sets allocated via the space’s STOGROUP
• Index data — index space data sets via the index’s STOGROUP
• Partitions — large partitioned designs can even use different storage groups per partition in advanced setups

What developers should remember

You rarely change STOGROUPs from application code. You do need to understand CREATE output and DBA review comments: “wrong STOGROUP” means the object’s data sets may land on the wrong storage tier or catalog alias relative to standards—not that SQL column types are wrong.

Querying the catalog

With authorization, you can SELECT from SYSIBM.SYSSTOGROUP and SYSIBM.SYSVOLUMES to see defined groups and volumes—useful when documenting an environment or verifying a name before CREATE.

Explain It Like I'm Five

Toys need shelves. A storage group is the rule sheet that says “put new boxes on the blue shelves” (or “ask the librarian where blue shelves are”—that librarian is like SMS). The toy list (table) does not mention shelves when you play. Grown-ups use the rule sheet when they build a new box (table space) or a notebook (index).

Exercises

1. Write a CREATE STOGROUP that uses VOLUMES (*) and a fictional VCAT name.
2. Why is the Db2 catalog description of a STOGROUP different from the ICF catalog entry for a VSAM data set?
3. Name two Db2 objects that allocate space through a storage group.
4. What is SYSDEFLT for?
5. In one sentence, explain SMS’s role when VOLUMES (*) is used.