Scaling up Db2

You can run a script to add more memory and CPU to the Db2® service on Cloud Pak for Data to support high-availability or increase processing capacity.

1. Use the following command to edit the OpenShift® stateful set for the Db2 pod to set higher memory and CPU limits:

   oc edit sts c-db2-1603819662989-db2u

   You can find the correct stateful set (sts) in the project by using oc get sts | grep db2.

   In the following example sts, these entries set CPU to 12 vCPU and memory to 36 Gi:

   Limits:
     cpu:     12
     memory:  36Gi
   Requests:
     cpu:     12
     memory:  36Gi
   

   Limited privilege deployments

   To scale up in a limited-privilege deployment, you need to perform some computations and change the values in the same stateful set. Table 1 shows you how to calculate the values. The sysctls parameters are based on the following example:

   sysctls:
   - name: kernel.shmmni
     value: ""
   - name: kernel.shmmax
     value: ""
   - name: kernel.shmall
     value: ""
   - name: kernel.sem
     value: 
   - name: kernel.msgmni
     value: ""
   - name: kernel.msgmax
     value: ""
   - name: kernel.msgmnb
     value: ""

   Table 1. How to derive sysctls parameter values in a limited-privilege environment. The column "Example: Scaling up to 36 Gb" shows the result of calculating the value to set memory to 36 Gb.

   Parameter in sysctls	Value	How to calculate value	Example: Scaling up to 36 Gb
   kernel.shmmni	256 * ramInGB	ramInGb is the new Limits.memory you set in Gb	Value of kernel.shmmni = 9216
   kernel.shmmax	ramInBytes	ramInGb * (1024 * 1024 * 1024)	Value of kernel.shmmax = 38654705660
   kernel.shmall	2 * ( ramInBytes / pageSz )	To determine the pageSz, inside the db2u-0 container, run the getconf PAGE_SIZE command	kernel.shmall = 2 * ( 38654705660 / 4096 ) = 18874368
   kernel.sem	Value of semmsl, semmns, semopm, and semmni	See Table 2	Following examples in Table 2, kernel.sem == 250 2048000 100 9216
   kernel.msgmni	1024 * ramInGB	1024 * ramInGB	1024 * 36 == 36864
   kernel.msgmax	65536 (default)
   kernel.msgmnb	65536 (default)

   Table 2. Subcomponents of kernel.sem value

   Parameter	Value	Example
   semopm	100 (default)
   semmsl	250 (default)
   oltpNumbdb	8 (default)
   semmnsDefault	256000 (default)
   ipcmniLimitToUse	32760(default)
   semmni	Min(semmni, ipcmniLimitToUse)	semmni = Min(9216, 32760) == 9216
   semmns	Min(semmni * semmsl, oltpNumdb * semmnsDefault)	semmni = 256 * ramInGB = 256 * 36 == 9216 semmns = Min(9216 * 250, 8 * 256000) == Min(2304000, 2048000) == 2048000

   The final changes to the sysctl are shown below based on the above calculations, which are based on the memory limit that is applied to the pod (30Gb).

   sysctls:
   - name: kernel.shmmni
     value: "9216"
   - name: kernel.shmmax
     value: "38654705660"
   - name: kernel.shmall
     value: "18874368"
   - name: kernel.sem
     value: 250 2048000 100 9216
   - name: kernel.msgmni
     value: "36864"
   - name: kernel.msgmax
     value: "65536"
   - name: kernel.msgmnb
     value: "65536"

2. Exec into the Db2 pod.
3. Disable the Wolverine high availability monitoring process:

   sudo wvcli system disable -m "Disable HA before Db2 maintenance"

4. On the head pod of your Db2 cluster, run the following series of commands to:
   • Create the update_mem.sh script file.
   • Deactivate and stop Db2.
   • Disable any remote database connections.
   • Update the Db2 instance_memory configuration parameter to use the normalized percentage value that was derived from the higher system memory limit.
   • Re-run the autoconfigure command to start using updated instance_memory percentage and reapply any overrides.
   • Re-enable Db2 remote connections, start Db2, and activate the database.

   Replace dbname with the database name.

   #!/bin/bash
   source /etc/profile
   source /db2u/scripts/include/common_functions.sh
   source /db2u/scripts/include/db2_functions.sh
   
   # Get PID1 environ required for access OS envvar MEMORY_LIMIT
   export_pid1_env
   source /db2u/scripts/include/db2_memtune_functions.sh
   
   # Deactivate database and stop Db2
   db2 terminate
   db2 force applications all
   db2 deactivate db dbname
   db2stop force
   rah 'ipclean -a'
   
   # Disable remote connections
   db2set DB2COMM -null
   
   # db2start prior to activate db bludb and connection within db2_autoconf.clp
   db2start
   # Update the normalized instance_memory % value derived from higher MEMORY_LIMIT
   set_instance_memory
   db2stop
   
   # db2start prior to activate db bludb and connection within db2_autoconf.clp
   db2start
   # Re-run autoconfigure to start using updated instance_memory %, and reapply any overrides
   run_autoconfigure
   apply_cfg_setting_to_db2 "-all"
   
   # Re-enable Db2 remote connections, start Db2 and activate db
   db2stop force
   rah 'ipclean -a'
   db2set DB2COMM=TCPIP,SSL
   db2start
   db2 activate db dbname

5. Set permissions on the update_mem.sh script and run the script:

   chmod +x /db2u/tmp/update_mem.sh
   su - db2inst1 -c "/db2u/tmp/update_mem.sh"

   The script runs the function export_pid1_env, which parses the PID 1 environment file /proc/1/env to get the value of the operating system memory limit environment variable and make the needed adjustments.
6. Re-enable the Wolverine high availability monitoring process:

   sudo wvcli system enable -m "Enable HA after Db2 maintenance"