Space Management with DB2 UDB for z/OS

Purpose

Space management might not at first seem to be an area of relevance for high availability.  However, if neglected, it can lead to downtime. For example, if a database object needs to expand, but is not able to do so, applications cannot continue writing to the database and you quickly have to make more space available.

You can manage space for the SAP system with DB2 UDB for z/OS using either of the following:

·        DB2 itself, known as "DB2-managed data"

·        Data Facility Storage Management Subsystem (DFSMS), which is system-managed storage, known as "SMS-managed data"

SAP does not support the DB2 method "user-managed data" for the SAP system.

Storage Management Subsystem (SMS) is the IBM automated approach to managing auxiliary storage such as disk space. It uses software programs to manage data security, placement, migration, backup, recall, recovery, and deletion. Using these functions, SMS makes sure that current data is available when needed and obsolete data is removed from storage.

In this section, both SMS-managed and DB2-managed data are described. Both options provide a high degree of automation. We recommend you to use SMS-managed data if possible.

With DB2 UDB for z/OS the data to be managed consists of:

·        SAP and DB2 system data (tablespaces and indexspaces)

·        DB2 bootstrap dataset (BSDS)

·        Catalog and directory data

·        Image copies (that is, database backups)

·        Archive logs

All of these can be managed by SMS. However, with both SMS-managed and DB2-managed data it is still possible to run out of space:

·        With SMS-managed data, an SMS storage group can fill up.

·        With DB2-managed data, a DB2 stogroup (that is, a storage group) can fill up.

In both cases, a dataset can reach its maximum number of extents, when you must provide more space as described below.

Process Flow

...

       1.      If all the volumes in a DB2 stogroup (DB2-managed data only) are full:

...

                            a.      You add additional volumes by executing the following SQL statement:

ALTER STOGROUP <FULL_STOGROUP> ADD VOLUMES (<VOLID_NEW1>, <VOLID_NEW2>, ...)

                            b.      You prevent this happening again by using transaction DB02 to check each DB2 stogroup for sufficient free space.

A DB2 stogroup is a collection of one or more volumes (that is, disks). If all the volumes in a stogroup are full, any transaction that writes data to tables or indexes associated with that stogroup fails.

       2.      If an SMS storage group (SMS-managed data only) is full:

...

                            a.      You add additional volume(s) to the full storage group by using the DFSMS application or the appropriate DFSMS commands.

                            b.      You prevent this happening again by checking each SMS storage group for sufficient free space. Depending on the granularity of your SMS storage groups, you can significantly reduce the number of objects to check for available free space, compared to DB2-managed data.

An SMS storage group is a collection of one or more volumes. If all the volumes in an SMS storage group are full, transactions fail when attempting to write data to tables or indexes in that storage group.

       3.      If the maximum number of dataset extents (both SMS and DB2-managed data) is reached:

...

                            a.      You reorganize the tablespace as follows:

                                                  i.       You increase the PRIQTY and SECQTY parameters of the tablespace using ALTER TABLESPACE (or ALTER INDEX). Set these parameters so that less than 20 extents are used.

                                                ii.       You use REORG TABLESPACE (or REORG INDEX) for the relevant tablespace. DB2 UDB for OS/390 supports online reorganization (that is, the SAP system is running during this action).

                            b.      You prevent this happening again by regularly checking that the number of tablespace and indexspace extents remains well below the maximum of 255. You can do this using either of the following monitors in the Computing Center Management System (CCMS):

§         Database monitor

§         Alert Monitor

A DB2 tablespace consists of one or more datasets used to store DB2 tables. When a tablespace is created, DB2 allocates disk space as defined by the PRIQTY (primary quantity) parameter of the tablespace. Each time more space is needed, DB2 allocates additional disk space as defined by the SECQTY (secondary quantity) parameter. The same applies for indexspaces.  The maximum number of extents is 255 (but prior to OS/390 version 2 release 4 the maximum number of extents was 119).

If the maximum dataset size of 2 GB is reached, DB2 automatically creates an additional dataset for this tablespace, again starting with PRIQTY followed by SECQTY as needed. This is repeated up to the maximum number of datasets per tablespace, which is 32 for segmented tablespaces and for each partition of a partitioned large tablespace (which can have up to 254 partitions).

       4.      You monitor your tablespaces and indexes carefully with a suitable monitor, such as those mentioned above. Check for scattering of rows in a tablespace or indexspace caused by updates. Scattering causes performance degradation.

       5.      If you need to reorganize the tablespace or index, you choose offline or online reorganization:

Ў        Offline reorganization

The DB2 offline reorganization command unloads the data from the tablespace, sorts the data in clustering sequence, and then reloads the data with the desired free space. The indexes are synchronized with the tablespace data. During the unload phase, the SAP system has read-only access to the data in the tablespace. During the other phases, the SAP system has no access to the data. Due to the integrated nature of the SAP applications this means that the SAP system needs to be stopped during an offline reorganization.

Ў        Online reorganization

The DB2 online reorganization allows the SAP system read-write access to the data during most of the time it is running. The online reorganization makes a "shadow copy" of the tablespace and reorganizes this copy. After the reorganization of the shadow copy is complete, DB2 reproduces changes from the original tablespace to the shadow copy using the DB2 recovery log. During this action, the SAP system has full read-write access to the data. During the very last updates to the shadow copy, DB2 only allows read-only data access.

After this, DB2 switches from the original dataset to the shadow copy. During the switch phase, the SAP system has no access to the tablespace. If you need to reorganize SAP tablespaces or indexes, use online reorganization because this increases the availability of SAP data.

       6.      If required, you separate tables.

Most DB2 utilities, such as REORG, operate with tablespaces rather than tables. DB2 monitoring is better supported for tablespaces than for tables. Therefore, if a tablespace in a multi-table tablespace becomes very large, grows rapidly in size, has to be reorganized frequently, or is accessed very often, you can move it to a single-table tablespace. Moving tables from a multi-table tablespace to a single-table tablespace involves moving the table data. Depending on the table size, this can cause significant downtime.

Since Release 4.5A, all SAP tables that are not SAP-buffered are in single-table tablespaces by default. Therefore, you very rarely need to separate tables.

See also:

SAP Database Administration Guide for SAP NetWeaver on IBM DB2 UDB for z/OS