Installing a Db2 instance for Data Gate on Cloud Pak for Data

You must install, provision, and configure a Db2 or Db2 Warehouse instance on Cloud Pak for Data for each Data Gate instance.

About this task

A Data Gate instance requires either a Db2 or Db2 Warehouse instance depending on the type of target database you select when creating a Data Gate instance.

In either case, the following requirements and recommendations should be followed during Db2 installation and configuration to ensure correct operation and optimal performance of Data Gate:

Procedure

  1. Recommended: For the best Data Gate performance, create a Db2 or Db2 Warehouse service instance on a dedicated node with its own dedicated storage and set the storage volume type to hostPath.
    The hostPath storage volume type offers a lower I/O latency. Always aim for an extremely low synchronization latency and a high load throughput.

    Note that each database instance needs a different dedicated hostPath location on the host machine.

    For more information, see:
  2. Recommended: For the best Data Gate performance, after the Db2 or Db2 Warehouse instance is created, consider to increase the total size of the Db2 or Db2 Warehouse log file. Otherwise, the loading of tables might fail because the log file is too small.
    • For test and development systems, consider to increase the total size to about 3 GB by setting the value of the logfilsiz parameter to 50000 and the value of the logprimary parameter to 15.
    • For production systems, your Db2 administrator should adjust the settings based on the system capacity.
    • Change persistent logfilsiz and logprimary settings as follows:
      1. Determine the Db2ucluster CR name of your target database instance and edit it, then replace ${PROJECT_CPD_INST_OPERANDS} with your project name in the following sample command. Run the command:
        oc get db2ucluster -n ${PROJECT_CPD_INST_OPERANDS}
DB2UCLUSTER_ID=<db2ucluster_cr_name>
oc edit db2ucluster ${DB2UCLUSTER_ID} -n ${PROJECT_CPD_INST_OPERANDS}
      2. The database configuration (YAML file) is now in edit mode. Database configuration parameters are added under spec.environment.databases.dbConfig. Navigate to this section and add or change the logfilsiz and logprimary values as shown in the following example:
        spec:
  environment:
    database:
      name: BLUDB
      dbConfig:
        logprimary: "10"
        logfilsiz: "250000"
      3. Save and exit the YAML file. Changing Db2ucluster CR can avoid losing your logfilsiz and logprimary settings after an upgrade.
      4. The dbConfig changes of Db2ucluster CR will be detected, then applied automatically. You can monitor pod logs to verify the configurations are applied successfully. Run the command: 
        oc logs c-${DB2UCLUSTER_ID}-db2u-0 -n ${PROJECT_CPD_INST_OPERANDS}
        The output looks like: 
        {"level":"info","result":"stdout: Waiting for up to 180 sec until a change is detected in the
relevant configmaps... change detected\n(*) Applying DB cfg settings\nParameters that require
updating: EXTBL_LOCATION LOCKLIST LOGARCHMETH1 LOGBUFSZ LOGFILSIZ LOGPRIMARY MAXLOCKS\nParameters
that support online updating: LOGSECOND\nDB20000I The TERMINATE command completed successfully.\n(*)
Applying Db2 db cfg parameters for BLUDB\nupdate db cfg for BLUDB using
AUTO_DEL_REC_OBJ ON DFT_SCHEMAS_DCC NO EXTBL_LOCATION /mnt/blumeta0/db2/load;
/mnt/blumeta0/home;/mnt/bludata0/scratch;/mnt/external;/mnt/blumeta0/db2/backup
EXTBL_STRICT_IO NO LOCKLIST 500000 LOGARCHMETH1 OFF LOGBUFSZ 131070 LOGFILSIZ 250000 LOGPRIMARY 10
LOGSECOND 20 LOG_APPL_INFO NO LOG_DDL_STMTS NO MAXLOCKS 13 NUM_DB_BACKUPS
2 REC_HIS_RETENTN 7\nDB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.\nSQL1363W One or more of the parameters submitted for immediate
modification \nwere not changed dynamically. For these configuration parameters,
the database \nmust be shutdown and reactivated before the configuration parameter
changes \nbecome effective.\n\nforce application all\nDB20000I The FORCE APPLICATION
command completed successfully.\nDB21024I This command is asynchronous and may not be
effective immediately.\n\n\ndeactivate db BLUDB\nDB20000I The DEACTIVATE DATABASE
command completed successfully.\n\ndeactivate db BLUDB\nSQL1495W Deactivate database
is successful, however, there is still a \nconnection to the database.\n\nactivate db BLUDB\nDB20000I
The ACTIVATE DATABASE command completed successfully.\n\nDatabase was activated successfully\n
hostname: c-db2oltp-1717040404643209-db2u-0","caller":"[13]:db2.go:346:ApplyCfg()","timestamp":
"2024-05-30T16:31:39Z","message":"executed apply Db2 configuration cmd for setting db"}
    For more information, see:
  3. Recommended: To further optimize the performance after the creation of the Db2 instance, consider increasing the values of the locklist and maxlock parameters. This is to avoid that too many row locks are replaced with a lock of the entire table while tables are being synchronized (a process called lock escalation).
    • For test and development systems, keep the default value Automatic.
    • For production systems, change the value of the locklist parameter to 500000 and the value of the maxlocks parameter to 13.
    For more information, see:
  4. Recommended: To minimize the impact of operational disruptions caused by upgrades, consider a high-availability setup. Such a setup requires an additional Db2 or Db2 Warehouse instance.
    For more information, see: