Increasing table space and log file sizes before upgrade

Before you start upgrading your Db2® server, you must ensure that you have a sufficient amount of free space on your system catalog table space and temporary table space, and enough log space to upgrade your databases.

Before you begin

Ensure that you have SYSCTRL or SYSADM authority to be able to increase the size of table spaces and log space.

About this task

Additional considerations are required in partitioned database environments to increase table space sizes because table spaces span across database partitions. Also, you only need to increase the log space in the catalog database partition server.

Procedure

Note: If you are upgrading to version 11.5.6 or later, you can skip steps 5, 6, and 7.

To increase the size of your table spaces and log space:

Connect to the database you want to upgrade:
```
   db2 CONNECT TO sample
```

Determine your table space disk usage by issuing the following query:

db2 "SELECT SUBSTR(TBSP_NAME,1,15) NAME, TBSP_TYPE TYPE,
     TBSP_AUTO_RESIZE_ENABLED AUTO_RESIZE, TBSP_NUM_CONTAINERS CONTAINERS, 
     TBSP_TOTAL_PAGES TOTAL_PGS, TBSP_USED_PAGES USED_PGS, TBSP_FREE_PAGES FREE_PGS,
     TBSP_MAX_SIZE MAX_SZ, TBSP_PAGE_SIZE PG_SZ
     FROM SYSIBMADM.TBSP_UTILIZATION
     WHERE TBSP_CONTENT_TYPE IN ('ANY','SYSTEMP')"

NAME            TYPE AUTO_RESIZE CONTAINERS TOTAL_PGS USED_PGS FREE_PGS MAX_SZ PG_SZ
--------------- ---- ----------- ---------- --------- -------- -------- ------ -----
SYSCATSPACE     DMS             1         1      8192     7576       612     -1  8192
TEMPSPACE1      SMS             -         1        10       10         0      -  8192

  2 record(s) selected.

Take note of the number of containers, total pages, used pages, free pages, MAXSIZE, and page size.

Increase the size of the system catalog table spaces using one of the following options:
- If you have an SMS table space, ensure that you have at leastthe same amount of used pages available as free disk space; in this example, about 60 MB.
- If you have a DMS table space and the number of used pages is greater than the number of free pages, use the following formula to calculate the number of pages to increase per container:
```
   number_of_pages = ( used_pages - free_pages ) /
                       number_of_containers_in_SYSCATSPACE
```
  Then use the following command to increase the size of all containers in the system catalog table space:
```
   db2 ALTER TABLESPACE SYSCATSPACE EXTEND (ALL number_of_pages)
```
- If you have a DMS table space with AUTORESIZE enabled and MAXSIZE is set to NONE, ensure that you have at least twice the amount of used pages available in free disk space. If MAXSIZE is set to an integer value that is less than twice the amount of used pages, then you need to increase MAXSIZE using the ALTER TABLESPACE statement as shown in the following example:
```
   db2 "ALTER TABLESPACE SYSCATSPACE
              MAXSIZE (2*used_pages_in_SYSCATSPACE*page_size/1024) K"
```
In our example, the query results in the previous step shows that SYSCATSPACE is a DMS table space with AUTORESIZE enabled and a MAXSIZE value of -1 which indicates unlimited maximum size. Therefore, you must have twice the amount of used pages available in free disk space.
Increase the size of the temporary table spaces using one of the following options:
- If you have an SMS table space you only need to ensure that you have at least twice the amount of total pages for the system catalog table space in free disk space; in this example, about 128 MB.
- If you have a DMS table space, use the following formula to calculate the number of pages to increase per container:
```
   number_of_pages = ( number_of_total_pages_in_SYSCATSPACE  ) /
                       number_of_containers_in_TEMPSPACE1
```
  Use the following command to increase the size of all containers in the temporary table space:
```
   db2 ALTER TABLESPACE TEMPSPACE1 EXTEND (ALL number_of_pages)
```
- If you have a DMS table space with AUTORESIZE enabled and MAXSIZE is set to NONE, ensure that you have at least twice the amount of total pages for the system catalog table space in free disk space. If MAXSIZE is set to an integer value that is less than twice the amount of total pages for the system catalog table space, then you need to increase MAXSIZE using the ALTER TABLESPACE statement:
```
   db2 "ALTER TABLESPACE TEMPSPACE1 
              MAXSIZE (2*total_pages_in_SYSCATSPACE*page_size/1024) K"
```

Determine the current log space size using the GET DATABASE CONFIGURATION command. The following example shows how to record the values for logfilsiz, logprimary, and logsecond database configuration parameters on Linux® and UNIX operating systems:

   db2 GET DB CFG FOR sample |grep '(LOG[FPS]'| tee logsize.txt
    Log file size (4KB)                         (LOGFILSIZ) = 1000
    Number of primary log files                (LOGPRIMARY) = 3
    Number of secondary log files               (LOGSECOND) = 2

Increase your log space size using the following commands:

   db2 UPDATE DB CFG FOR sample using LOGSECOND 
       (current_value of LOGPRIMARY + current_value of LOGSECOND) * 2

If you already have a large log space, you might not need to increase it.

Optional: Enable infinite active logging instead of increasing the log space, by setting logsecond to -1 and enabling archive logging. Infinite active logging allows an active unit of work to span the primary logs and archive logs, effectively allowing a transaction to use an infinite number of log files. You should be aware that if the upgrade fails, the time to roll back the transactions will depend on how many archived logs need to be retrieved. The following command shows an example on how to enable archive logging to disk and infinite logging:
```
   db2 UPDATE DB CFG FOR sample using LOGARCHMETH1 DISK:archive-dir 
   db2 UPDATE DB CFG FOR sample using LOGSECOND -1
```
where archive-dir is the directory to archive the log files.
All applications must disconnect from this database before the new values become effective.