Activating Query Optimizer in IBM watsonx.data version 2.0.1 and later

You can install and activate Query Optimizer in IBM® watsonx.data version 2.0.1 and later by following the instructions in this topic.

If you are upgrading from an existing IBM watsonx.data version 2.0.0 to 2.0.1 or later , then go to step 4 to activate Query Optimizer.

Before you begin

Install watsonx.data, see Installing watsonx.data.
If you are using a private container registry, mirror images for Query Optimizer dependencies by using export components=wxd_query_optimizer. For more information, see Mirroring IBM Cloud Pak for Data images to a private container registry.
Create storage and associate catalog, see Adding storage.
Create schemas and tables under the registered catalog, see Creating schemas and Creating tables.
Run the steps in Setting up IBM Cloud File Storage (ibmc-file-gold-gid storage class) by Db2 warehouse if you are using the IBM file gold storage class for the Query Optimizer install.

About this task

Follow the steps to activate Query Optimizer in watsonx.data.

Procedure

Run the following command to install Query Optimizer in the instance namespace by using cpd-cli command:

cpd-cli manage apply-olm --components=wxd_query_optimizer --release=${VERSION} --cpd_operator_ns=${PROJECT_CPD_INST_OPERATORS} --license_acceptance=true

Update the global settings on the cluster to configure node settings for the operator:

Note: Updating the global settings is not required if it is already set for other services.
1. Pause machine config pool (mcp) updates for changing CRI-O pids_limit with a KubeletConfig by running the following command:
```
oc patch --type=merge --patch='{"spec":{"paused":true}}' machineconfigpool/master

oc patch --type=merge --patch='{"spec":{"paused":true}}' machineconfigpool/worker
```
2. Update the load balancer timeout. See Changing load balancer timeout settings.
3. Update process IDs limits. See Changing the process IDs limit.
4. Update kernel parameter settings. See Changing kernel parameter settings.
5. Run the following command to resume mcp updates:
```
oc patch --type=merge --patch='{"spec":{"paused":false}}' machineconfigpool/master

oc patch --type=merge --patch='{"spec":{"paused":false}}' machineconfigpool/worker
```
6. Run the following command to check the status of mcp:
```
watch "oc get mcp"
```
  Note: You must wait until all the nodes show status as UPDATED: True

Run the following command to create a configmap to use elevated privileges in the instance namespace:

oc apply -f - <<EOF
apiVersion: v1
data:
  DB2U_RUN_WITH_LIMITED_PRIVS: "false"
kind: ConfigMap
metadata:
  name: db2u-product-cm
  namespace: ${PROJECT_CPD_INST_OPERANDS}
EOF

Log in to watsonx.data console.
From the navigation menu, select Configurations and click the Query Optimizer Manager tile.
Click Activate and confirm the activation and restarting of the engines in the Activate query optimizer window.

Note: Query Optimizer takes approximately 20 minutes to deploy and sync over metadata for all Hive and Iceberg catalogs and schemas. This time may vary based on the metadata size to be synced.

Note: Verify that all expected tables have been synced. If tables are found missing during the automatic syncing process, you can manually sync the tables. See Manually syncing Query Optimizer.

Note: You can click Cancel activation to cancel the deployment of Query Optimizer Manager during the deployment.