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.

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

1. Connect to the database you want to upgrade:

      db2 CONNECT TO sample

2. 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.

   If you are upgrading from Version 8.1, use the following command: db2 LIST TABLESPACES SHOW DETAIL

3. 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 least the 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"

     The automatic resizing of table spaces is available since DB2 UDB Version 8 FixPak 9.

   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.
4. 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"

5. 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

6. 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.
7. 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 rollback 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.