Activating Query optimizer in IBM watsonx.data

To enable Query optimizer, you must activate Query optimizer manager through the web console.

Before you begin

Make sure at least one Presto (C++) engine is provisioned and active within your IBM® 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 Software Hub 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

This topic describes how to activate and install the Query optimizer in IBM watsonx.data by using the install-components command. To install or activate the Query optimizer, you must generate and apply cluster-scoped resources and then install the required operators. Use the cpd-cli manage case-download command to generate cluster-scoped resources for Query optimizer installation and upgrade. Some resources, such as Custom Resource Definitions (CRDs), are applied to the cluster only once. Other resources, such as cluster role bindings, must be applied to the operator namespace for each instance of Software Hub. This procedure follows the same model that is used for creating cluster-scoped resources for other Software Hub components. For more information on case-download options, and the cluster scoped resources refer to https://www.ibm.com/docs/en/software-hub/5.3.x?topic=hub-creating-cluster-scoped-resources

Procedure

Use the cpd-cli manage case-download command to generate cluster-scoped resources for the Query optimizer installation or upgrade.

Note: Starting with IBM Software Hub 5.3.0, the apply-olm command is deprecated. You must now use the install-components workflow, which requires multiple commands instead of the single command used in earlier releases.
```
cpd-cli manage case-download \
  --release=${VERSION} \
  --components=wxd_query_optimizer \
  --cluster_resources=true \
  --operator_ns=${PROJECT_CPD_INST_OPERATORS}
```
Change to the directory that contains the generated YAML files. The default location of the work directory is:
```
cpd-cli-workspace/olm-utils-workspace/work
```

Apply the cluster-scoped resources from the cluster_scoped_resources.yaml file:

oc apply -f cluster_scoped_resources.yaml \
--server-side \
--force-conflicts

Install the operators for Query optimizer:

cpd-cli manage install-components \
--license_acceptance=true \
--components=wxd_query_optimizer \
--release=${VERSION} \
--operator_ns=${PROJECT_CPD_INST_OPERATORS} \
--instance_ns=${PROJECT_CPD_INST_OPERANDS} \
--block_storage_class=${STG_CLASS_BLOCK} \
--file_storage_class=${STG_CLASS_FILE} \
--image_pull_prefix=${IMAGE_PULL_PREFIX} \
--image_pull_secret=${IMAGE_PULL_SECRET}

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.

Attention: Db2U in watsonx.data runs with elevated privileges and does not support limited privileges.
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 by setting similar values to Db2. See Changing load balancer timeout settings.
3. Update the process IDs limits by setting similar values to Db2. See Changing the process IDs limit.
4. 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
```
5. 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
Remember: This step is applicable only for activating Query optimizer with elevated privileges.
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 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 synchronize 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.