Connecting to a remote Db2 instance

Data Gate supports connecting to a remote Db2 instance, which can reside in a different Cloud Pak for Data cluster or on a remote external instance (CP4D cluster or in an external cluster/environment). Among the supported architectures for remote instances, PPC64LE is included. Note: Remote Db2 instances must be set up and configured manually prior to integration. If you use a remote Db2 database, you do not need a Db2 instance in the Cloud Pak for Data environment in which you create your Data Gate instance.

1. Set up the secure network connection: To have a secure TCP/IP connection between the Data Gate instance and the remote target database, it is necessary to enable TLS for inbound connections in the Db2 instance (see this link), and add the TLS certificate into a secret so that the Data Gate instance can use the certificate that has been configured in the remote target database. Create a new OpenShift secret with the required certificate files (reuse the tls.crt file for the ca.crt entry if using a self-signed certificate):

   oc create secret generic "${TARGET_DB2_CERT_SECRET_NAME}" \
     -n "${INSTANCE_NAMESPACE}" \
     --from-file=tls.crt="${PATH_TO_TLS_CRT}" \
     --from-file=ca.crt="${PATH_TO_CA_CRT}"

   where the variable INSTANCE_NAMESPACE is defined as the namespace in which the Data Gate instance will be created and TARGET_DB2_CERT_SECRET_NAME stands for the secret name. Note that this secret name will be used in the API call described bellow.

2. Configure the required user permissions in the target database:
   1. Connect to the machine where the target Db2 service is running. Log in as the instance user, that is, the user who owns the instance, and has all the privileges associated with that role.
   2. Save the following script to a file, for example db2_permissions.sh:

      #!/usr/bin/env bash
      
      # Check if 2 inputs are provided
      if [ "$#" -ne 2 ]; then
         echo "Usage $0 <non_instance_user> <database_name>"
         exit 1
      fi
      
      echo "input user is: $1"
      echo "input databse name is: $2"
      
      db2 connect to $2;
      db2 GRANT WLMADM ON DATABASE TO USER $1;
      db2 GRANT DBADM WITH DATAACCESS ON DATABASE TO USER $1;
      db2 GRANT EXECUTE ON FUNCTION SYSPROC.ADMIN_GET_ENCRYPTION_INFO TO USER $1;

   3. By running the following command, make the script file executable:

      chmod +x db2_permissions.sh

   4. Provide a non-instance user and database name as input parameters as you run the script. A non-instance user does not own the instance, and therefore has limited privileges when compared with the instance owner. In the following example, the user name db2user1 and database name TESTDB are specified:

      ./db2_permissions.sh db2user1 TESTDB

3. Set up the required database configuration in the target database:
   1. Connect to the machine where the target Db2 service is running. Log in as the instance user that is, the user who owns the instance, and has all the privileges associated with that role.
   2. Select the script based on the environment (Db2 or Db2wh) and save the script to a file, for example configure-db2-registry.sh.

      
      (Script for setting registry variable for Db2)
      db2set DB2_WORKLOAD=ANALYTICS_ACCELERATOR
      db2set DB2_SELECTIVITY="ALL,AJSEL,UNIQUE_COL_FF ON"
      db2set DB2_APPENDERS_PER_PAGE=1
      db2set DB2MAXFSCRSEARCH=1
      db2set DB2COMM=TCPIP,SSL
      echo "Db2 registry configuration completed successfully."
      
      (Script for setting registry variable for Db2wh)
      db2set DB2_WORKLOAD=ANALYTICS_ACCELERATOR
      db2set DB2_SELECTIVITY="ALL,AJSEL"
      db2set DB2_EXT_TABLE_SETTINGS=COMM_BUFFER_SIZE:1113840
      db2set DB2CODEPAGE=1208
      db2set DB2_CDE_REDUCED_LOGGING=TRANSITION_THRESHOLD:320
      db2set DB2_RUNTIME_DEBUG_FLAGS=SECTION_LEVEL_LOB
      db2set DB2_SECTION_SCRATCH_BUFFER_SIZE=512K
      db2set DB2LOCK_TO_RB=STATEMENT
      db2set DB2COMPOPT=CDE_NEQN_ENABLE
      db2set DB2_ATM_CMD_LINE_ARGS="-include-manual-tables -low-priority-update-systables"
      db2set DB2_CDE_DICTIONARY_CACHE_CLEANUP_SCAN_LIST_INTERVAL=30
      db2set DB2_CDE_DICTIONARY_CACHE_CLEANUP_INTERVAL=60
      db2set DB2_APPENDERS_PER_PAGE=1
      db2set DB2_EXTENDED_OPTIMIZATION="COL_RTABLE_UD_THR 0,XGBPART ON FULL NONHDIR,ENABLE_OLAP2AGG ON,NI2NE_WITH_NULLS OUTER,COLJOIN 1,NEQN_DECORR ON"
      db2set DB2_REDUCED_OPTIMIZATION="COL_RTS OFF,EGAD 0"
      db2set DB2_CDE_DATA_SETTINGS=VECTORIZED_INSERT:YES
      db2set DB2_CDE_COMPRESSION_SETTINGS="HISTOGRAM_MAX_VARSTRING_SZ:1000"
      db2set DB2_CDE_DCC=no
      db2set DB2_TCG_DEFAULT_OPTIONS="set percentile_cont_spill on"
      db2set DB2_CORRELATED_PREDICATES="CGS_CARD_BOUND ON"
      db2set DB2_SQB_EXTENTMOVEMENT_BUFFER_SIZE=16384
      db2set DB2_COL_RUNPROF_HSJOIN="DUPPAYLOADSZ_LIMIT ON"
      db2set DB2_ADVIS_OPTIONS="COL_PART ON"
      echo "Db2 Warehouse registry configuration completed successfully."
      

   3. Make the script file executable by running the following command:

      chmod +x configure-db2-registry.sh

   4. Execute the script:

      ./configure-db2-registry.sh

   5. Install the Memory Table function using the following SQL statement:

      db2 "CALL SYSPROC.SYSINSTALLOBJECTS('ANACC','C','','')"

   6. Restart the database instance so that the changes can take effect.
4. Verify the Data Gate installation:
   1. Verify that the Data Gate pods are running.

      To determine the namespace and instance name of a deployed Data Gate instance, run:

      oc get datagateinstanceservice -A

      Example output:

      
      NAMESPACE      NAME                   AGE
      cpd-instance   dg1772799584217084     10m
      
      - Namespace: `cpd-instance`  
      - Instance CR name: `dg1772799584217084`

   2. Retrieve the numeric Data Gate instance ID by running::

      oc get datagateinstanceservice <instance-cr-name> -n <namespace> \
        -o jsonpath='{.spec.data_gate_instance_id}'
      

      Example:

      oc get datagateinstanceservice dg1772799584217084 -n cpd-instance \
        -o jsonpath='{.spec.data_gate_instance_id}'
      

      Example output:

      1772799584217084
      

   3. Check whether all Data Gate pods are running:

      oc get pods -n <namespace> | grep <instance-id>

      Example:

      oc get pods -n cpd-instance | grep 1772799584217084

      Example output:

      
      dg-1772799584217084-data-gate-5c8cc5cfd7-n62k5   5/5   Running   0   2d20h
      dg-1772799584217084-ui-6fdf9f7646-xt75m          1/1   Running   0   2d20h
      

      Ensure that all listed pods show a status of Running.

   4. Verify access to the Data Gate user interface.

      a) Open the Data Gate UI from the platform console.

      b) Select the installed Data Gate instance.

      c) Confirm that the Source database pairing screen is displayed. The presence of the pairing screen indicates that the Data Gate instance is successfully initialized and you are ready to configure the source database connection.

      Expected result:

      - All Data Gate pods are in the Running state.
      - The Data Gate UI loads successfully and displays the Source database pairing screen.