Fixing incorrect Db2 configuration settings after an upgrade

For some older Data Gate versions, the configuration settings of the Db2 target database are not correctly migrated during an upgrade of a Data Gate instance. If this is the case, you must correct the settings settings of the target database manually.

About this task

You have installed and provisioned a Data Gate instance in Cloud Pak for Data version 4.7, 4.8, or 5.0 and have recently updgraded to a newer Data Gate version in Cloud Pak for Data 5.0.x or 5.1.0.

However, the configuration settings of the Db2 target database have not been correctly migrated during the upgrade.

The Db2 configuration settings applied during the upgrade are different from the settings that were used in conjunction with the older Data Gate instance.

In addition, the newer version requires more settings for the Db2 target database, and some of the additional values are not set during the upgrade, and are thus missing from the configuration.

You might encounter one or more of the following situations after the Data Gate upgrade:

DB2_SELECTIVITY is not set, which causes table loads to fail. You see the following error message:

[AQT10050E]
Unable to load tables. An internal error occurred on the '<data-gate-instance-name>' accelerator 
or Db2 Data Gate instance: ODBC operation SQLExecDirect failed with code -1 when executing 
statement: /* IBM_DWA */ CREATE OR REPLACE VIEW ... WHERE "DWA_Partition_ID (hidden)" 
<= 0 SELECTIVITY 1: <Diagnostics> <SQLSTATE>428E5</SQLSTATE> <SQLCODE>-20046</SQLCODE> 
<Tokens num="1"> <Token>n_ID (hidden)" <= 0 </Token> </Tokens> 
<Message>[IBM][CLI Driver][DB2/LINUXZ64] SQL20046N SELECTIVITY clause following "n_ID (hidden)" 
<= 0 " can only be specified for a valid user-defined predicate. 
SQLSTATE=428E5</Message> </Diagnostics>

DB2LOCK_TO_RB is not set to STATEMENT, which has the following effect: Queries against the Db2 target database return results that are different from the results returned by the same query against the Db2 for z/OS source database.
PAGE_AGE_TRGT_MCR, DB2MAXFSCRSEARCH, DB2_SELECTIVITY, DB2_STATISTICS, or DB2_APPENDERS_PER_PAGE are set to wrong values. This results in performance degradations with regard to table loads, the speed at which row changes are replicated, and the speed of query execution in the Db2 target database.
DB2_WAITFORDATA_LIBNAME is set to a wrong or empty value. This causes the failure of queries against the Db2target database.

Procedure

From the command line of the Cloud Pak for Data system where your Data Gate instance is installed, determine the Db2 target database instance that is used by the Data Gate instance:
```
oc get datagateinstanceservice | awk '{print $1 " " $7}'
```
This command returns the Data Gate instance name and the target database name, for example:
```
NAME TARGET_DATABASE
dg1730137307043920 db2oltp-1713769437245949
```

Determine the Db2 target database pod by using the value of TARGET_DATABASE value returned by the previous command:

❯ oc get pods | grep db2oltp-1713769437245949

The target database pod name is returned. For example:

c-db2oltp-1713769437245949-db2u-0   1/1     Running   1    38d
c-db2oltp-1713769437245949-etcd-0   1/1     Running   1    38d

Open a shell in the Db2 target database pod, for example:

oc exec -it c-db2oltp-1713769437245949-db2u-0 bash

This command is confirmed by a message similar to this one:

Defaulted container "db2u" out of: db2u, init-labels (init), init-kernel (init)
[db2uadm@c-db2oltp-1713769437245949-db2u-0 /]$

Inside the container, run the following commands to change to the Data Gate Db2 configuration folder. Example where db2inst1 is the name of the target database instance:
```
su - db2inst1
cd /mnt/blumeta0/home/db2inst1/config_db2u
```
Still inside the container shell, run the appropriate scripts for your target database:
- For a Db2 target database:
```
./db_cfg_row_store.sh
./db2set_dg.sh
```
- For a Db2 Warehouse target database:
```
./dbm_cfg_analytics.sh
./db_cfg_analytics.sh
./db2set_analytics.sh
```
Still inside the container shell, restart your target database:
```
./restart_db2.sh
```
To verify the adjusted settings, run the following commands:
```
db2set
db2 get db cfg for bludb
db2 get dbm cfg
```
Compare the screen output with the values in the two tables in Changes to the target database and make sure the values match.
(Upgrading Data Gate to 5.1.3 and target Database from version lower than 11.5.6.0): If you upgrade Data Gate to version 5.1.3 and your target Database (Db2 or Db2 Warehouse) from version lower than 11.5.6.0 to a newer one, you need to apply additionally the following setting in Db2 or Db2 Warehouse:
```
oc get datagateinstanceservice | awk '{print $1 " " $7}'
```
This command returns the Data Gate instance name and the target database name, for example:
```
NAME TARGET_DATABASE
dg1730137307043920 db2oltp-1713769437245949
```

Determine the Db2 target database pod by using the value of TARGET_DATABASE value returned by the previous command:

oc get pods | grep db2oltp-1713769437245949

The target database pod name is returned. For example:

c-db2oltp-1713769437245949-db2u-0   1/1     Running   1    38d
c-db2oltp-1713769437245949-etcd-0   1/1     Running   1    38d

Open a shell in the Db2 target database pod, for example:

oc exec -it c-db2oltp-1713769437245949-db2u-0 bash

This command is confirmed by a message similar to this one:

Defaulted container "db2u" out of: db2u, init-labels (init), init-kernel (init)
[db2uadm@c-db2oltp-1713769437245949-db2u-0 /]$

Determine the Db2 target database pod by using the value of TARGET_DATABASE value returned by the previous command:

oc get pods | grep db2oltp-1713769437245949

The target database pod name is returned. For example:

c-db2oltp-1713769437245949-db2u-0   1/1     Running   1    38d
c-db2oltp-1713769437245949-etcd-0   1/1     Running   1    38d

Inside the container, run the following commands to change to the Data Gate Db2 configuration folder. Example where db2inst1 is the name of the target database instance:

su - db2inst1
cd /mnt/blumeta0/home/db2inst1/config_db2u
db2set DB2_WAITFORDATA_LIBNAME=/mnt/blumeta0/home/db2inst1/config_db2u/waitfordata/libs/libwaitForDataSharedLib.so
libpath="$(db2set | grep DB2LIBPATH | awk -F'=' '{print $2}')"

If the libpath is empty then:

db2set DB2LIBPATH=/mnt/blumeta0/home/db2inst1/config_db2u/waitfordata/libs

if the libpath is not empty:

db2set DB2LIBPATH="/mnt/blumeta0/home/db2inst1/config_db2u/waitfordata/libs:${libpath}"

You can check whether the above worked by:

db2set -all

and check the values of DB2_WAITFORDATA_LIBNAME and DB2LIBPATH.