Troubleshooting

  • SQL :Error: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.USM_CONFIGURATION' with unique index 'IX_USM_CONFIGURATION'. The duplicate key value is (<NULL>, Affinium).

    1.Drop the Index of USM_CONFIGURATIONDROP INDEX [USM_CONFIGURATION].IX_USM_CONFIGURATION;

    DROP INDEX [USM_CONFIGURATION[.IX1_USM_CONFIGURATION;

    DROP INDEX [USM_CONFIGURATION[.IX2_USM_CONFIGURATION;

    OR

    DROP INDEX IX_USM_CONFIGURATION;

    DROP INDEX IX1_USM_CONFIGURATION;

    DROP INDEX IX2_USM_CONFIGURATION;

    2. Create⌂ the Index for⌂USM_CONFIGURATION

    CREATE UNIQUE INDEX IX_USM_CONFIGURATION ON

    USM_CONFIGURATION (PARENT_ID asc, INTERNAL_NAME asc);

    CREATE UNIQUE INDEX IX1_USM_CONFIGURATION ON

    USM_CONFIGURATION (NS_THREAD asc, NS_LEFT asc);

    CREATE UNIQUE INDEX IX2_USM_CONFIGURATION ON

    USM_CONFIGURATION (NS_THREAD asc, NS_RIGHT asc);

  • DB2 – Constraint violation errors: DB2 data migration from old base environment to 11.1 (clean environment) should be done with help of DBA. Before proceeding with upgrade steps y should ensure that all the database tables, constraints, indexes etc. are migrated successfully. For DB2 system tables import in clean 11.1 will cause issues with AUTO ID increment on certain tables. Due to these errors there will be related to constraint violation error.

    This step should be executed only if the “Upgrading the Platform configuration" step fails with errors related to constraint violation error:

    SELECT MAX(ID)+ 1 FROM USM_CONFIGURATION;

    ALTER TABLE USM_CONFIGURATION ALTER COLUMN ID RESTART WITH NNNN; --here put the result for the above query

    SELECT MAX(ID)+ 1 FROM USM_ROLE;

    ALTER TABLE USM_ROLE ALTER COLUMN ID RESTART WITH NNNN; -- here put the result for the above query

    SELECT MAX(ID)+ 1 FROM USM_DB_RESOURCE_BUNDLE;

    ALTER TABLE USM_DB_RESOURCE_BUNDLE ALTER COLUMN ID RESTART WITH NNNN; --here put the result for the above query

    SELECT MAX(ID)+ 1 FROM USM_AUDIT;

    ALTER TABLE USM_AUDIT ALTER COLUMN ID RESTART WITH NNNN; -- here put the result for the above query

    SELECT MAX(ID)+ 1 FROM USM_ALERT;

    ALTER TABLE USM_ALERT ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_ALERT_SUBSCRIPTION;

    ALTER TABLE USM_ALERT_SUBSCRIPTION ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_NOTICE;

    ALTER TABLE USM_NOTICE ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_DASHBOARD;

    ALTER TABLE USM_DASHBOARD ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_DASHBOARD_PORTLET;

    ALTER TABLE USM_DASHBOARD_PORTLET ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_DASH_PORT_IFRAME_DET;

    ALTER TABLE USM_DASH_PORT_IFRAME_DET ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_DASH_PORT_PREF_MAP;

    ALTER TABLE USM_DASH_PORT_PREF_MAP ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_DASH_MANAGE_RIGHTS;

    ALTER TABLE USM_DASH_MANAGE_RIGHTS ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_PORT_QUICKLINK_PREF;

    ALTER TABLE USM_PORT_QUICKLINK_PREF ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_PERSONALIZATION;

    ALTER TABLE USM_PERSONALIZATION ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_OBJECT_TYPE;

    ALTER TABLE USM_OBJECT_TYPE ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USM_OBJECT_ATTR;

    ALTER TABLE USM_OBJECT_ATTR ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USCH_TASK_NOTIFICATION;

    ALTER TABLE USCH_TASK_NOTIFICATION ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 FROM USCH_RUN_NOTIFICATION;

    ALTER TABLE USCH_RUN_NOTIFICATION ALTER COLUMN ID RESTART WITH NNNN;

    SELECT MAX(TASKID)+ 1 FROM USCH_TASK;

    ALTER TABLE USCH_TASK ALTER COLUMN TASKID RESTART WITH NNNN;

    SELECT MAX(RUNID)+ 1 FROM USCH_RUN;

    ALTER TABLE USCH_RUN ALTER COLUMN RUNID RESTART WITH NNNN;

    SELECT MAX(RUNEXCLUSIONID)+ 1 FROM USCH_RUN_EXCLUSION;

    ALTER TABLE USCH_RUN_EXCLUSION ALTER COLUMN RUNEXCLUSIONID RESTART WITH NNNN;

    SELECT MAX(ID)+ 1 from USM_ALERT_TYPE;

    ALTER TABLE USM_ALERT_TYPE ALTER COLUMN ID RESTART WITH NNNN

    You must execute the 'commit;' statement after the tables are altered.

  • DB2 – In case your DBA observed Unica Platform system tables constraints are not imported in the 11.1 system database, then you can execute following query: It will create missing constraints. For constraints that are already present, it shows errors that can be ignored.

    Execute the script file: <PLATFORM_HOME> /db/ ManagerSchema_DB2_CreateFKConstraints.sql

  • If the temp table is ON in the source DB, it can create to Temp table related issues in the target setup.
  • The user DB used in Target setup should be the same as the source. If a different User DataSource is used, then the existing flowcharts will not get executed due to table mappings differences.
  • Oracle export database schema using "exp" utility does not export tables with no rows.Note: Before executing these scripts, review them with your DBA, this approach is used by engineering, you can take the appropriate approach as suggested by your DBAs.In this case, you can execute the scripts underneath to enable the export of tables with no records.SELECT 'ALTER TABLE '||table_name||' ALLOCATE EXTENT;' FROM user_tables WHERE segment_created = 'NO';This script will provide SQL scripts as output that should be executed in the schema that needs to be exported.
  • Oracle – Constraint violation errors: Oracle data migration from old base environment to 11.1 (clean environment) must be done with help of a DBA. Before proceeding with upgrade steps please ensure that all the database tables, constraints, indexes and so on are migrated successfully. In Oracle the following error is displayed:
    ORA-00001: unique constraint (UNICA_PLATFORM.SYS_C0038815) violated insert into USM_CONFIGURATION_VALUES (VERSION, PREDEFINED, SELECTED, STRING_VALUE, NUMERIC_VALUE, DATE_VALUE, CONFIGURATION_ID, CONFIGURATION_ORDER, ENVIRONMENT_ID, USER_ID) values (", ", ", ", ", ", ", ", ", ")
    The following steps should be executed only if the “Upgrading the Unica Platform configuration" step fails with errors related to constraint violation error:
    1. Create a backup of the USM_CONFIGURATION and USM_CONFIGURATION_VALUES tables.
    2. Create a 'full export' of the configuration: ./configTool.sh/bat -x -f full_export.xml
    3. Truncate the USM_CONFIGURATION_VALUES table: truncate table usm_configuration_values; commit;
    4. Delete from USM_CONFIGURATION table: delete from usm_configuration; commit;
    5. Import the "full_export.xml" file: ./configTool.sh/bat -i -p "Affinium" -f full_export.xml
    6. Reattempt the failing command.