Tablespaces

The Operations Analytics Predictive Insights database component deploys 4 tablespaces (TSAA_RDF, TSAA_SMALL, TSAA_LARGE and TSAA_HUGE). The following is recommended for production deployments:

  • Spread tablespaces across multiple disk arrays for optimal performance.
  • Use Disk RAID level 10 for all database file systems.
  • Use a disk storage group for database file systems.

Modifying storage group settings

To setup and configure a disk storage group when using multiple filesystems:

  1. As database admin user (db2inst1 by default) display current table spaces and the number of containers:

    db2 connect to SCAPIDB

    db2 list tablespaces show detail | egrep “Name|Tablespace ID|Number of containers”

  2. List details of current storage group configuration:

    db2 list tablespace containers for 5 show detail

    Where 5 is the number of containers.

  3. To add filesystems to a storage group:

    db2 ALTER STOGROUP IBMSTOGROUP ADD ‘/home/db2data1′,’/home/db2data2′,’/home/db2data3’

    Where /home/db2data1-3 are filesystems you which to add to your storage group.

    db2 ALTER TABLESPACE %tablespace_name REBALANCE

    Where %tablespace_name is the tablespace you have added a filesystem to.

  4. Stop DB2 and restart.

    db2 connect to SCAPIDB

    db2 force applications all

    db2stop

    db2start

    Note, it will take some time for rebalance to fully complete post stop/start if there is data already in your database. You can proceed as normal but performance may be impacted while this is running.

  5. To delete a storage group entry run the following:

    ALTER STOGROUP IBMSTOGROUP DROP ‘/home/db2inst1 – Where /home/db2inst1 is the filesystems you which to delete from your storage group.

  6. Stop and restart DB2 for changes to take effect.

Transaction logs

The following recommendations are made in relation to transaction logs:

  • Allocate transaction logs their own file system and disk array.
  • Do not place your transaction logs on a shared file system with your database instance files.
  • Allow a minimum of 10GB of free space for your transaction logs.

Moving Transaction logs

To move transaction logs to a new location, do the following:

  1. As the database admin user, db2inst1 by default, identify the current location of the transaction logs:

    db2 connect to SCAPIDB

    db2 get db cfg for SCAPIDB | egrep “NEWLOGPATH”

  2. Change the “NEWLOGPATH” location as follows:

    db2 connect to SCAPIDB

    db2 update database configuration for SCAPIDB using NEWLOGPATH /%TRANS_LOGS

    Where %TRANS_LOGS is the location to which you want to move the transactions logs.

  3. Stop and restart the database:

    db2 connect to SCAPIDB

    db2 force applications all

    db2stop

    db2start

  4. Complete the following steps to verify the changes:

    db2 connect to SCAPIDB

    db2 get db cfg for SCAPIDB | grep “Path to log files”

    Where SCAPIDB is the database instance name for SCA-PI


Sizing Transaction logs

You need to size transaction logs to avoid errors and possible performance impacts.

To display and change transaction log settings, run the following as the database admin user, db2inst1 by default:

To display current settings run the following commands:

db2 connect to SCAPIDB

db2 get db cfg for SCAPIDB | egrep “LOGBUFSZ|LOGFILSIZ|LOGPRIMARY|LOGSECOND”

It is recommended that you change transaction log settings as follows:

db2 update db cfg for SCAPIDB using logbufsz 256

db2 update db cfg for SCAPIDB using logprimary 13

db2 update db cfg for SCAPIDB using logsecond 50

db2 update db cfg for SCAPIDB using logfilsiz 40000

Note: The above numbers are recommended as a starting point. Periodic system audits and analysis are recommended to ensure optimal performance and identify any future growth needs.

After you make changes you must stop and restart the database with the following commands:

db2 connect to SCAPIDB

db2 force applications all

db2stop

db2start

Note, it may take a few minutes to see the transaction logs in the NEWLOGPATH location.

Join The Discussion

Your email address will not be published. Required fields are marked *