Creating separate storage for database transaction logs in Db2 Warehouse

You can dedicate a separate storage area for database transaction logs on the Db2® Warehouse pod to reduce I/O bottlenecks and improve performance.

About this task

You can calculate a recommended space for active log storage by using the value of several Db2 Warehouse configuration parameters. The size should be greater than LOGFILSIZ (in 4K pages) * (LOGPRIMARY + LOGSECONDARY). The following default values are set in the container:

Log file size (4KB)          (LOGFILSIZ) = 50000
Number of primary log files      (LOGPRIMARY) = 20
Number of secondary log files    (LOGSECOND) = 30

So a calculation that uses the default values would be (50000*4) * (20 + 30) KB = 10 GB. A recommended allocation for active log storage based on this calculation might be 20 GiB to allow extra space for large workloads.

Procedure

Follow one of these procedures depending on whether you are creating log storage for a new deployment or an existing deployment:
  • New deployment

    In the web client, if you select the Separate locations for all data option during deployment you can set the properties for Transaction logs storage.

    Specify the following values:

    • Create new storage
    • Use storage template
    • Storage class: Choose manage-nfs-storage, ocs-storagecluster-ceph-rbd, or portworx-db2-rwo-sc.
    • Size: Your chosen value between 1 and 1,000 GiB.
  • Existing deployment
    1. Patch the db2ucluster object to add the path to the transaction logs:
      oc patch db2ucluster db2oltp-test --type 'json' -p '[{ "op": "add", "path": "/spec/storage/-", "value": {"name": "activelogs", "spec": {"accessModes": ["ReadWriteOnce"], "resources": {"requests": {"storage": "20Gi"}},"storageClassName": "ocs-storagecluster-ceph-rbd"},"type": "create"} }]'

      In the example, a volume with the name activelogs will be added to the Db2 Warehouse engine pod. In the example, the db2ucluster name is db2oltp-test, storage size for the transaction log path is 20 Gi, and the storage class that is used is ocs-storagecluster-ceph-rbd. Update the values based on your environment.

      When db2ucluster is patched, the Db2 Warehouse pods restart and you can see the mounted path as /mnt/logs/active/. You can confirm the pod is mounted by looking up the engine pod description or by running the mount command in the container.

    2. While logged in as db2inst1, run a shell function in the pod to set the log path:
      /bin/bash -c "source /db2u/scripts/include/db2_functions.sh && update_transactional_logpath dbname"

      Where dbname is the name of the database for which you are enabling transaction log storage.

    3. To check whether the function ran successfully, run this command to confirm the new log path:
      db2 get db cfg for bludb | grep -i 'Path to log files'