Description:
Description
=========
A problem with null values in an existing database table may prevent TIBCO ActiveMatrix BPM from upgrading successfully to version 1.3.0.

Symptoms
========
Upgrade to version 1.3.0 fails with a "null column constraint" error, when the upgrade script attempts to insert data into the table "de_capabilityheld".

Environment
==========
TIBCO ActiveMatrix BPM 1.3.0 on any supported platform.

Cause
=====
The database upgrade scripts do not do not cater for non-qualified organization model capability assignments to resources (users).

Resolution
========
Before you start the upgrade, prepare the existing qualifier data so that non-null values are inserted to the new capability assignment table during the upgrade.
The same solution applies to all database types, but the SQL scripts vary according to the database dialect.

If you have already attempted an upgrade and it has failed, you must roll the ActiveMatrix BPM database back to the pre-upgrade backup that you had created (see the section "Checks Before Performing an Upgrade" in the TIBCO
ActiveMatrix BPM Installation and Configuration guide). You cannot simply run the SQL given below on top of a partial upgrade.

1. Apply the appropriate one of the following scripts.

Oracle Database Script

  ALTER TABLE de_qualifier MODIFY qualifier nvarchar2(400) NULL;

  INSERT INTO de_qualifier(parent_type, parent_id, qualifier, qualifier_n)
  (SELECT DISTINCT 1, rc.id, null, ' '
    FROM de_resourcecapability rc
    INNER JOIN de_namedentity c ON c.id = rc.capability_id
    LEFT OUTER JOIN de_qualifier q ON q.parent_type = 1 AND
    q.parent_id = rc.id
    WHERE q.qualifier_n IS NULL);


SQL Server Database Script

  ALTER TABLE amxbpm.de_qualifier ALTER COLUMN qualifier
  amxbpm.de_paramvalue NULL;
  GO

  INSERT INTO amxbpm.de_qualifier(parent_type, parent_id,
  qualifier, qualifier_n)
  (SELECT DISTINCT 1, rc.id, null, ' '
    FROM amxbpm.de_resourcecapability rc
    INNER JOIN amxbpm.de_namedentity c ON c.id = rc.capability_id
    LEFT OUTER JOIN amxbpm.de_qualifier q ON q.parent_type = 1
    AND q.parent_id = rc.id
    WHERE q.qualifier_n IS NULL);
    GO

2. Then follow the upgrade procedure described in "Upgrading Your
    System" in the TIBCO ActiveMatrix BPM Installation and
    Configuration guide.
Symptoms:

Cause:

TIBCO ActiveMatrix® BPM 1.3.0, upgrade may fail to complete successfully.