Repair duplicate Person Group sequences and missing defaults

Person Group data loaded directly with SQL can contain duplicate member sequences or no group default. The group might appear usable in its source environment, but validation can fail when Migration Manager moves it elsewhere. The bad sequence also makes workflow assignment order ambiguous.

The safest repair is through the Person Groups application. Use the DB2 queries below to inventory a large data set and prepare reviewed changes when manual repair is impractical.

Direct database update: these statements bypass Maximo validation, business rules, auditing and caches. Back up the database, rehearse against a restored copy, stop Maximo and integrations that can modify Person Groups, and have a rollback script. Run the preview queries first and execute generated statements only after reviewing them.

Understand the sequence problem

Suppose TESTGROUP contains these members:

The order is mostly clear, but sequence 2 is duplicated. ROW_NUMBER() can assign a unique sequence within each Person Group while retaining the existing order as far as possible:

SELECT
  PERSONGROUPTEAMID,
  PERSONGROUP,
  RESPPARTY,
  RESPPARTYSEQ,
  ROW_NUMBER() OVER (
    PARTITION BY PERSONGROUP
    ORDER BY
      CASE
        WHEN RESPPARTYSEQ IS NULL THEN 1
        ELSE 0
      END,
      RESPPARTYSEQ,
      PERSONGROUPTEAMID
  ) AS NEW_SEQUENCE
FROM
  PERSONGROUPTEAM
ORDER BY
  PERSONGROUP,
  NEW_SEQUENCE;

The partition restarts numbering for each group. Existing non-null sequences determine the order, and PERSONGROUPTEAMID provides a deterministic tie-breaker when two members have the same sequence. Null sequences follow the sequenced members.

For the example, the result is:

TESTP2 and TESTP3 had the same original value, so their primary-key order decides which comes first. Confirm that this matches the intended business priority before updating anything.

Find only groups that need sequence repair

This query identifies duplicate or missing member sequence values:

SELECT
  PERSONGROUP,
  RESPPARTYSEQ,
  COUNT(*) AS MEMBER_COUNT
FROM
  PERSONGROUPTEAM
GROUP BY
  PERSONGROUP,
  RESPPARTYSEQ
HAVING
  RESPPARTYSEQ IS NULL
  OR COUNT(*) > 1
ORDER BY
  PERSONGROUP,
  RESPPARTYSEQ;

Review those groups in the application. Person Group sequence affects how Maximo checks members for workflow assignments, including calendar, shift, organization and site context.

Generate sequence repair statements

Use the unique PERSONGROUPTEAMID in each generated WHERE clause because Maximo allows a person to appear more than once in a group for different organization or site contexts.

WITH SEQUENCED AS (
  SELECT
    PERSONGROUPTEAMID,
    PERSONGROUP,
    ROW_NUMBER() OVER (
      PARTITION BY PERSONGROUP
      ORDER BY
        CASE
          WHEN RESPPARTYSEQ IS NULL THEN 1
          ELSE 0
        END,
        RESPPARTYSEQ,
        PERSONGROUPTEAMID
    ) AS NEW_SEQUENCE
  FROM
    PERSONGROUPTEAM
)
SELECT
  'UPDATE PERSONGROUPTEAM SET RESPPARTYSEQ = ' ||
  VARCHAR(NEW_SEQUENCE) ||
  ', RESPPARTYGROUPSEQ = ' ||
  VARCHAR(NEW_SEQUENCE) ||
  ' WHERE PERSONGROUPTEAMID = ' ||
  VARCHAR(PERSONGROUPTEAMID) ||
  ';' AS UPDATE_SQL
FROM
  SEQUENCED
ORDER BY
  PERSONGROUP,
  NEW_SEQUENCE;

This query prints SQL; it does not change the database. Save the result as a reviewable script. If only a few groups are broken, restrict both the preview and generator to those groups rather than rewriting every membership.

The generated statements set both sequence columns to the same new value, keeping both values aligned. Confirm that this is correct for your implementation and any custom logic before execution.

Find groups without a default person

IBM documents that each populated Person Group should have a group default. This query returns populated groups for which no member has GROUPDEFAULT = 1:

SELECT
  PG.PERSONGROUP,
  PG.DESCRIPTION
FROM
  PERSONGROUP PG
WHERE
  EXISTS (
    SELECT
      1
    FROM
      PERSONGROUPTEAM PGT
    WHERE
      PGT.PERSONGROUP = PG.PERSONGROUP
  )
  AND NOT EXISTS (
    SELECT
      1
    FROM
      PERSONGROUPTEAM PGT
    WHERE
      PGT.PERSONGROUP = PG.PERSONGROUP
      AND PGT.GROUPDEFAULT = 1
  )
ORDER BY
  PG.PERSONGROUP;

Also look for groups with more than one group default:

SELECT
  PERSONGROUP,
  COUNT(*) AS DEFAULT_COUNT
FROM
  PERSONGROUPTEAM
WHERE
  GROUPDEFAULT = 1
GROUP BY
  PERSONGROUP
HAVING
  COUNT(*) > 1
ORDER BY
  PERSONGROUP;

Repair defaults in the Person Groups application when possible. Choosing a default is a business decision: it controls who receives an assignment when Maximo cannot select an appropriate member by calendar, shift, site or organization.

If the agreed rule is that sequence 1 must become the missing group default, review the candidate rows first:

SELECT
  PGT.PERSONGROUPTEAMID,
  PGT.PERSONGROUP,
  PGT.RESPPARTY,
  PGT.USEFORORG,
  PGT.USEFORSITE,
  PGT.RESPPARTYGROUPSEQ
FROM
  PERSONGROUPTEAM PGT
WHERE
  PGT.RESPPARTYGROUPSEQ = 1
  AND NOT EXISTS (
    SELECT
      1
    FROM
      PERSONGROUPTEAM EXISTING_DEFAULT
    WHERE
      EXISTING_DEFAULT.PERSONGROUP = PGT.PERSONGROUP
      AND EXISTING_DEFAULT.GROUPDEFAULT = 1
  )
ORDER BY
  PGT.PERSONGROUP;

After sequence repair and business approval, the original update can assign those candidates:

UPDATE
  PERSONGROUPTEAM PGT
SET
  GROUPDEFAULT = 1
WHERE
  PGT.RESPPARTYGROUPSEQ = 1
  AND NOT EXISTS (
    SELECT
      1
    FROM
      PERSONGROUPTEAM EXISTING_DEFAULT
    WHERE
      EXISTING_DEFAULT.PERSONGROUP = PGT.PERSONGROUP
      AND EXISTING_DEFAULT.GROUPDEFAULT = 1
  );

Do not run that update until every candidate has been reviewed. Sequence 1 might not be the correct operational default, particularly when the group contains site- or organization-specific members.

Validate before migrating again

After the approved repair:

1. Rerun the duplicate, null, missing-default and multiple-default queries.
2. Open affected groups in Maximo and save them without validation errors.
3. Confirm member ordering, group default, organization defaults and site defaults.
4. Test a representative workflow or assignment resolution path.
5. Export a small Migration Manager package and validate it in the target environment.
6. Keep the before-and-after extracts and executed SQL with the change record.

The tested Maximo and DB2 versions are unknown. Verify the statements against your installed versions before running them.

References

• IBM: Creating Person Groups
• IBM: Person Groups and workflow assignments
• IBM: Person Groups overview