Updating Query Optimizer configuration

In Query Optimizer, values for the following Db2 parameters are based on the configuration of the Presto (Java) and Presto (C++) engines.

SHEAPTHRES in dbm cfg
OPT_SORTHEAP in db cfg
OPT_BUFFPAGE in db cfg

If the following engine properties are changed or if there is a switch to another engine, then the Db2 parameters must be updated.

Presto single node

presto_singlenode_query_max_memory from wxdengine
presto_singlenode_query_max_total_memory_per_node from wxdengine
presto_singlenode_jvm_Xmx from wxdengine
presto_singlenode_resources_requests_memory from wxdengine

Presto multinode

query.max-memory in presto coordinator from wxd api
query.max-memory in presto worker from wxd api
query.max-memory-per-node in presto worker from wxd api
jvm.xmx in presto worker from wxd api
query.max-memory-per-node in presto coordinator from wxd api
jvm.xmx in presto coordinator from wxd api
presto_coordinator_query_max_memory from wxdengine
presto_worker_query_max_memory from wxdengine
presto_worker_query_max_total_memory_per_node from wxdengine
presto_worker_jvm_Xmx from wxdengine
presto_worker_resources_requests_memory from wxdengine

Presto (C++)

presto_coordinator_query_max_memory from wxdengine
prestissimo_worker_query_max_memory_per_node from wxdengine
prestissimo_worker_system_memory_gb
prestissimo_worker_resources_limits_memory
presto_coordinator_query_max_memory_per_node from wxdengine
presto_coordinator_jvm_Xmx from wxdengine
presto_coordinator_resources_requests_memory from wxdengine

Before you begin

Ensure Query Optimizer is installed. For details, see Installing Query Optimizer in version 2.0.0.
You have admin access to the cluster and have the privilege to run the update command.

Note: watsonx.data APIs cannot support updates in wxdengine.

About this task

In this task, we will update the Query Optimizer configuration.

Procedure

Set up the PROJECT_CPD_INSTANCE environment variable pointing to the namespace where watsonx.data is installed.
```
export PROJECT_CPD_INSTANCE=<wxd_namespace>
```

List the engines from the namespace.

oc get wxdengine -n ${PROJECT_CPD_INSTANCE}

Select one engine from the list and trigger the update.

oc patch wxdengine <wxdengine name>   --type=merge   -n ${PROJECT_CPD_INSTANCE}   -p '{ "spec": {
          "update_optimizerplus": "true"
      } }'