Symantec Identity Manager - 14.4 in-place upgrade Column name 'SCHED_NAME' does not exist in the target table or view.

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

After performing an in-place upgrade from IDM version 14.2 to 14.4

IDM on the first startup is failing to update the MS SQL DB.

Errors:

Column 'QRTZ_TRIGGERS.TRIGGER_NAME' is not the same length or scale as referencing column 'qrtz_simprop_triggers.TRIGGER_NAME' in foreign key 'FK__qrtz_simprop_tri__70C98096'. Columns participating in a foreign key relationship must be defined with the same length and scale.
Msg 1750, Level 16, State 0, Line 81
Could not create constraint. See previous errors.
Msg 1911, Level 16, State 1, Line 105
Column name 'SCHED_NAME' does not exist in the target table or view.

2021-11-22 16:18:20,766 ERROR [ims.tmt.CreateDatabaseSchema] ([ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)') Error in creating Task Persistence database schema. com.microsoft.sqlserver.jdbc.SQLServerException: Column name 'SCHED_NAME' does not exist in the target table or view.

Environment

Release : 14.4

Component : Identity Manager

Cause

In 14.4 the column name 'SCHED_NAME' is added. If the previous column sizes are lower (80) than the new required size (200) then this error is thrown. This happens when a client as an environment that started in the 12.5 version and has been upgraded throughout the years and maintained the same database / data within it and as such these column sizes were never updated.

Resolution

There are three viable solutions to resolving this issue at this time.

1) Prior to upgrading to 14.4 and the preferred method, you must drop all the quartz tables and then create them with the proper character length. Within your 14.1/14.2/14.3 Environment navigate to "\CA\Identity Manager\IAM Suite\Identity Manager\tools\db\taskpersistence\sqlserver\idm_db_sqlserver.sql"

Search for "-- Quartz 1.7.3 db sql begin" Copy all the SQL script to "-- Quartz 1.7.3 db sql end" and execute on the SQL server.

Then perform the 14.4 upgrade.

2) Since you are already 14.4, you can try to recreate all the tables for 14.4 compatibilities.

For Virtual Appliance version:

You must download the "Remote Tools" from the support site, if you do not have it, see the link below with the steps to download it.

https://techdocs.broadcom.com/us/en/symantec-security-software/identity-security/identity-suite/14-4/release-notes/Virtual-Appliance-Release-Notes/Latest-Remote-Tools.html

Navigate to "CA-IM_Tools\db\taskpersistence\sqlserver\idm_db_sqlserver.sql"

 

For on-premise version:

Navigate to "\CA\Identity Manager\IAM Suite\Identity Manager\tools\db\taskpersistence\sqlserver\idm_db_sqlserver.sql"

 

Search for "-- Quartz 1.7.3 db sql begin" Copy all the SQL script to "-- Quartz 2.3.1 db sql end" and execute on the SQL server.

 

3) Have a DBA, update the below character lengths manually.

Table
QRTZ_TRIGGERS
Column that had to be modified from 80 -> 200
TRIGGER_NAME
TRIGGER_GROUP

Table
QRTZ_JOB_DETAILS
Column that had to be modified from 80 -> 200
TRIGGER_NAME
TRIGGER_GROUP

Table
QRTZ_JOB_LISTENERS
Column that had to be modified from 80 -> 200
TRIGGER_NAME
TRIGGER_GROUP

Table
QRTZ_CRON_TRIGGERS
Column that had to be modified from 80 -> 200
TRIGGER_NAME
TRIGGER_GROUP

Table
QRTZ_SIMPLE_TRIGGERS
Column that had to be modified from 80 -> 200
TRIGGER_NAME
TRIGGER_GROUP

Table
QRTZ_TRIGGER_LISTENERS
Column that had to be modified from 80 -> 200
TRIGGER_NAME
TRIGGER_GROUP

Then run the quartz upgrade script that we provide out of the box.

File Location:

Virtual Appliance:

"CA-IM_Tools\db\taskpersistence\sqlserver\quartz_sqlserver_upgrade_231.sql"

 

On-premise:

"\CA\Identity Manager\IAM Suite\Identity Manager\tools\db\taskpersistence\sqlserver\quartz_sqlserver_upgrade_231.sql"

 

4) If the above does not work, then you can perform the steps from step 1 and then run the quartz_sqlserver_upgrade_231.sql script.