Creating separate storage for database transaction logs

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

Procedure

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

New deployment

Create a storage volume for transaction logs by specifying the storage volume in the Db2 Warehouse cluster custom resource definition (CR). Use the name activelogs for the storage volume, and for storageClassName specify the name of the storage class that you are using for data storage (the example below uses ocs-storagecluster-ceph-rbd for OpenShift® Container Platform).

The following example custom resource definition creates four storage areas for Db2 Warehouse for meta, data, backup, and active logs storage. The active logs storage definition is highlighted at the end of the CR:

apiVersion: db2u.databases.ibm.com/v1
kind: Db2uCluster
metadata:
  name: db2ucluster-sample
spec:
  license:
    accept: true
  account:
    privileged: true
    imagePullSecrets:
      - ibm-registry    
  version: "11.5.6.0"
  size: 1
  podConfig:
    db2u:
      resource:
        db2u:
          requests:
            cpu: 2
            memory: 4Gi
          limits:
            cpu: 2
            memory: 4Gi
  environment:
    dbType: db2oltp
    database:
      name: sampledb
      settings:
        dftPageSize: "16384"
        encrypt: "NO"      
    instance:
      password: cicdtest
      registry:
        DB2_4K_DEVICE_SUPPORT: "ON" 
      dbmConfig:
        DIAGSIZE: "100"                   
  addOns:    
    rest:     
      enabled: false
    graph:     
      enabled: false
  storage:
    - name: meta
      type: "create"
      spec:
        storageClassName: "ocs-storagecluster-cephfs"
        accessModes:
          - ReadWriteMany
        resources:
          requests:
            storage: 10Gi
    - name: data
      type: "template"
      spec:
        storageClassName: "ocs-storagecluster-ceph-rbd"
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 100Gi
    - name: backup
      type: "create"
      spec:
        storageClassName: "ocs-storagecluster-cephfs"
        accessModes:
          - ReadWriteMany
        resources:
          requests:
            storage: 100Gi
    - name: activelogs
      type: "template"
      spec:
        storageClassName: "ocs-storagecluster-ceph-rbd"
        accessModes:
        - ReadWriteOnce
        resources:
          requests:
            storage: 10Gi

When you deploy the Db2 Warehouse service, on the Transaction logs storage page of the web console specify the following values:
- Create new storage
- Use storage template
- Storage class: ocs-storagecluster-ceph-rbd
- Size: Your chosen value between 1 and 1,000 GiB.
  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.

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'
```