Upgrading Query Optimizer

Because schema names that are registered in Query optimizer manager can be difficult to identify, first list the schema names explicitly:

ExecuteWxdQueryOptimizer '
SELECT DISTINCT TABSCHEMA
FROM SYSCAT.KEYCOLUSE
WHERE CONSTNAME IN (
SELECT CONSTNAME
FROM SYSCAT.TABCONST
WHERE TYPE = 'P'
)';

From the output, identify the schema that you want to use in the following queries.

Generate DDL statements for NOT NULL constraints:

-- ========================================
-- DDL Generation Query for NOT NULL 
-- Schema: iceberg_data.IMIO_SF1_NO_PART
-- ========================================

ExecuteWxdQueryOptimizer '
  select 'ALTER TABLE ' || tabschema || '.' || tabname  || ' ALTER COLUMN ' || COLNAME  || ' SET NOT NULL '  AS DDL_STATEMENT
  from syscat.columns where  tabschema = 'iceberg_data.IMIO_SF1_NO_PART' AND NULLS= 'N' ';

Generate DDL statements for primary keys:

-- ========================================
-- DDL Generation Query for Primary Keys (INFORMATIONAL)
-- Schema: iceberg_data.IMIO_SF1_NO_PART
-- ========================================
ExecuteWxdQueryOptimizer '
SELECT
'ALTER TABLE ' || RTRIM(TABSCHEMA) || '.' || RTRIM(TABNAME) ||
' ADD CONSTRAINT ' || RTRIM(CONSTNAME) ||
' PRIMARY KEY (' ||
LISTAGG(RTRIM(COLNAME), ', ') WITHIN GROUP (ORDER BY COLSEQ) ||
') NOT ENFORCED' AS DDL_STATEMENT
FROM SYSCAT.KEYCOLUSE
WHERE TABSCHEMA = 'iceberg_data.IMIO_SF1_NO_PART'
AND CONSTNAME IN (
SELECT CONSTNAME
FROM SYSCAT.TABCONST
WHERE TABSCHEMA = 'iceberg_data.IMIO_SF1_NO_PART'
AND TYPE = 'P'
)
GROUP BY TABSCHEMA, TABNAME, CONSTNAME
ORDER BY TABSCHEMA, TABNAME';

Generate DDL statements for foreign keys:

-- ========================================
-- DDL Generation Query for Foreign Keys (INFORMATIONAL)
-- Schema: iceberg_data.IMIO_SF1_NO_PART
-- ========================================
ExecuteWxdQueryOptimizer '
SELECT
'ALTER TABLE ' || RTRIM(r.TABSCHEMA) || '.' || RTRIM(r.TABNAME) ||
' ADD CONSTRAINT ' || RTRIM(r.CONSTNAME) ||
' FOREIGN KEY (' ||
LISTAGG(RTRIM(k.COLNAME), ', ') WITHIN GROUP (ORDER BY k.COLSEQ) ||
') REFERENCES ' || RTRIM(r.REFTABSCHEMA) || '.' || RTRIM(r.REFTABNAME) ||
' ON DELETE ' ||
CASE r.DELETERULE
WHEN 'A' THEN 'NO ACTION'
WHEN 'C' THEN 'CASCADE'
WHEN 'N' THEN 'SET NULL'
WHEN 'R' THEN 'RESTRICT'
ELSE 'NO ACTION'
END ||
' ON UPDATE ' ||
CASE r.UPDATERULE
WHEN 'A' THEN 'NO ACTION'
WHEN 'R' THEN 'RESTRICT'
ELSE 'NO ACTION'
END || ' NOT ENFORCED' AS DDL_STATEMENT
FROM SYSCAT.REFERENCES r
JOIN SYSCAT.KEYCOLUSE k
ON r.CONSTNAME = k.CONSTNAME
AND r.TABSCHEMA = k.TABSCHEMA
AND r.TABNAME = k.TABNAME
WHERE r.TABSCHEMA = 'iceberg_data.IMIO_SF1_NO_PART'
GROUP BY r.CONSTNAME, r.TABSCHEMA, r.TABNAME, r.REFTABSCHEMA, r.REFTABNAME, r.DELETERULE, r.UPDATERULE
ORDER BY r.CONSTNAME';

Save the generated DDL statements so that you can reuse them after the upgrade.

Use the cpd-cli manage case-download command to generate cluster-scoped resources for the Query optimizer manager upgrade:

cpd-cli manage case-download \
  --release=${VERSION} \
  --components=wxd_query_optimizer \
  --cluster_resources=true \
  --operator_ns=${PROJECT_CPD_INST_OPERATORS}

cpd-cli-workspace/olm-utils-workspace/work

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

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} \
--param-file='/tmp/work/override.yaml' \
--upgrade=true

This step reactivates Query optimizer manager after the watsonx.data upgrade is complete.

ExecuteWxdQueryOptimizer 'select service_level,
release_num,
ptf
from sysibmadm.ENV_INST_INFO'