DB2 10.5 for Linux, UNIX, and Windows

SYSTOOLSPACE and SYSTOOLSTMPSPACE table spaces

The SYSTOOLSPACE table space is a user data table space used by the DB2® administration tools and some SQL administrative routines for storing historical data and configuration information.

The following tools and SQL administrative routines use the SYSTOOLSPACE table space:
  • ADMIN_COPY_SCHEMA procedure
  • ADMIN_DROP_SCHEMA procedure
  • ADMIN_MOVE_TABLE procedure
  • ADMIN_MOVE_TABLE_UTIL procedure
  • Administrative task scheduler
  • ALTOBJ procedure
  • Automatic Reorganization (including the db.tb_reorg_req health indicator)
  • Automatic Statistics Collection (including the db.tb_runstats_req health indicator)
  • Configure Automatic Maintenance wizard
  • db2look command
  • GET_DBSIZE_INFO procedure
  • Storage Management tool
  • SYSINSTALLOBJECTS procedure

The SYSTOOLSPACE table space is created the first time any of the tools and SQL administrative routines listed previously are used. The db2look command, administrative task scheduler, ALTOBJ, ADMIN_COPY_SCHEMA, and ADMIN_DROP_SCHEMA procedures are exceptions; the SYSTOOLSPACE table space must be created before you can use them.

The SYSTOOLSTMPSPACE table space is a user temporary table space used by the REORGCHK_TB_STATS, REORGCHK_IX_STATS and the ADMIN_CMD procedures for storing temporary data. The SYSTOOLSTMPSPACE table space will be created the first time any of these procedures is invoked (except for ADMIN_CMD).

Note:
  1. If the DB2 registry variable DB2_WORKLOAD is set to SAP, neither the SYSTOOLSPACE nor the SYSTOOLSTMPSPACE will be created automatically.
  2. The Health Monitor and the db.tb_reorg_req ("Reorganization Required") and db.tb_runstats_req ("Statistics Collection Required") health indicators are enabled by default on all new databases. These two health indicators are evaluated by the Health Monitor approximately every two hours. This means that the SYSTOOLSPACE and SYSTOOLSTMPSPACE table spaces are created automatically for new databases after they have been active for two hours unless the Health Monitor or these health indicators are explicitly disabled.
  3. The automatic statistics collection feature is enabled by default on all new databases. This feature is evaluated approximately every two hours. This means that the SYSTOOLSPACE and SYSTOOLSTMPSPACE table spaces are created automatically for new databases after they have been active for two hours unless the automatic statistic collection feature is explicitly disabled.

If the default definition for either table space is not preferred, you can create the table spaces manually (or drop and recreate them if they have already been created automatically). The table space definitions may vary (for example, you can use a DMS or SMS table space, or you can enable or disable automatic storage), however the table spaces must be created in the IBMCATGROUP database partition group. If you attempt to create them in any other database partition group, error SQL1258N will be returned.

Example

Following is an example of how to create the SYSTOOLSPACE and SYSTOOLSTMPSPACE table spaces manually.
CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP 
  MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP
  EXTENTSIZE 4

CREATE USER TEMPORARY TABLESPACE SYSTOOLSTMPSPACE IN IBMCATGROUP 
  MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP
  EXTENTSIZE 4
By default, the use of SYSTOOLSTMPSPACE will be granted to PUBLIC as long as the database is not created using restricted access.
When you create a database, a default storage group named IBMSTOGROUP is automatically created. However, a database created with the AUTOMATIC STORAGE NO clause, does not have a default storage group. If a database has no storage groups, you can create a storage group using the CREATE STOGROUP statement. The following example shows how to create a storage group on Windows operating systems:
CREATE STOGROUP MYSTOGROUP ON 'D:\', 'E:\' SET AS DEFAULT