Load DWH fails with ALTER TABLE DWH_INV_STATUS_REPORT MODIFY

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Text fields with increased size cause the Load Data Warehouse  job- Full to fail with ALTER TABLE DWH_INV_STATUS_REPORT MODIFY

Error message in bg-dwh.log:

ERROR 2021-06-29 09:53:11,764 [Meta Columns Modify - MOD_COL_SQL] dwh.event MOD_COL_SQL - org.pentaho.di.core.exception.KettleStepException:
Error while running this step!
Couldn't execute SQL: ALTER TABLE DWH_INV_STATUS_REPORT MODIFY TEST_FIELD VARCHAR(4000)
ERROR: syntax error at or near "MODIFY"
  Position: 35
                at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
                at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
                at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
Couldn't execute SQL: ALTER TABLE DWH_INV_STATUS_REPORT MODIFY TEST_FIELD VARCHAR(4000)

Environment

Release : Any, PostgreSQL

Cause

This happens when the field is increased for size and it's mismatched in the database.

Resolution

1. In Clarity go to Studio -> Status Report object -> Attributes
2. Find the attribute TEST_FIELD and exclude it from the Data Warehouse
3. Now run Load Data Warehouse job - Full Load
4. Once the job completes, check the field back in DWH
5. Run Load Data Warehouse job  - Full Load

Additional Information

See also:

• Load DWH Full - When to run to prevent failures
• Data Warehouse (DWH) known issues