Applications Systems

Instance monitor stops due to a duplicate key object on the CONSPA table

The following error is displayed after an instance has stopped monitoring in DPA: Duplicate Key in object 'ignite.CONSPA_X'. The duplicate key value is ('XXXXXXX, Y).

First published date

11/12/2018 7:26 PM

Last published date

11/12/2018 7:26 PM

Overview

The following error is displayed after an instance stops monitoring in DPA:

Duplicate Key in object 'ignite.CONSPA_X'. The duplicate key value is ('XXXXXXX, Y)

Product section

Database Performance Analyzer

Cause

DPA uses the CONSPT table to determine if there are any records of the plan. If it is not found in the table, it is flagged for insertion.

Resolution

The results of the following query return records that already have plan data in CONSPH and CONSPA, which indicates what DPA is trying to insert as duplicate keys.

  1. Create a backup of your DPA repository.
  2. Run the following statement to determine if there are any records with plan data in CONSPH or CONSPA. Replace <X> with the instance ID that has the issue in COND.
    01select a.PLAN_HASH_VALUE,
    02       h.PLAN_HASH_VALUE as 'CONSPH',
    03       p.PLAN_HASH_VALUE as 'CONSPT'
    04  from conspa_<X> a
    05  left outer join consph_<X> h
    06               on h.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE
    07  left outer join conspt_<X> p
    08               on p.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE
    09where h.PLAN_HASH_VALUE is null
    10    or p.PLAN_HASH_VALUE is null
    11;

    To copy the query, move your mouse over it and click the View Source icon in the upper-right corner.

    The query above should return 0 rows.

  3. If any rows are returned, run the following query to delete them, replacing <X> with the instance ID:
    01delete from CONSPH_<X>
    02where PLAN_HASH_VALUE IN
    03(
    04select a.PLAN_HASH_VALUE
    05  from CONSPA_<X> a
    06  left outer join CONSPH_<X> h
    07               on h.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE
    08  left outer join CONSPT_<X> p
    09               on p.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE
    10where h.PLAN_HASH_VALUE is null
    11    or p.PLAN_HASH_VALUE is null
    12)
    13;
    14 
    15delete from CONSPA_<X>
    16where PLAN_HASH_VALUE IN
    17(
    18select a.PLAN_HASH_VALUE
    19  from CONSPA_<X> a
    20  left outer join CONSPH_<X> h
    21               on h.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE
    22  left outer join CONSPT_<X> p
    23               on p.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE
    24where h.PLAN_HASH_VALUE is null
    25    or p.PLAN_HASH_VALUE is null
    26)
    27;
  4. Restart DPA and the Ignite PI Server service.