Created by Unknown User (sse_20162), last modified by John McMahon on Aug 05, 2022
Purpose (problem description)

This page troubleshoot the problem of missing rows from Action Plan view. For example consider the below case where there are a total of 5492 items with  still violation- 2025 &  corrected - 2394 . When you click on export all you can find only 1980 items under still a violation and 2329 under corrected.

You may also obseve variable number of rows exported  each time you click on "export all" with the last row is incomplete.

Observed in CAST AIP
Release
Yes/No
8.3.x(tick)
Observed on RDBMS
RDBMS
Yes/No
CSS(tick)
Step by Step Scenario
  1. Launch Dashboard
  2. Goto Action Plan View
  3. Click on Export All button
Action plan
  1. If you are in 7.0.7, Check for errors in the tomcat log

    1. Activate SQL traces and check tomcat log, check if you have the following error message :

      [ERROR]:   stream error : null

      This issue happens once and we were not able to reproduce this issue at our side  with the Central base and the war

      As a workaround, you can use the below query to get the list of objects from the action plan list instead of using the "Export all" button :

      select distinct (select dso.OBJECT_FULL_NAME from <CENTRAL_BASE>.DSS_OBJECTS dso where dso.OBJECT_ID = action.F_OBJECT_ID),     
       action.F_OBJECT_ID,     
       action.F_METRIC_ID,     
/*=> Technical Criterion ID
       action.F_TC_METRIC_ID,  
<=*/
       case coalesce(  (select distinct 1 
                          from <CENTRAL_BASE>.DSS_METRIC_RESULTS 
                         where METRIC_ID = action.F_DETAIL_METRIC_ID 
                           and OBJECT_ID = action.F_OBJECT_ID 
                           and SNAPSHOT_ID = <SNAPSHOT_ID>)  , 0)
            when 1 then coalesce(   (select distinct 2 
                                       from <CENTRAL_BASE>.VIEWER_ACTION_PLANS /* N/A (same snapshot) */   
                                      where FIRST_SNAPSHOT_ID=<SNAPSHOT_ID> 
                                        and METRIC_ID=action.F_METRIC_ID 
                                        and OBJECT_ID=action.F_OBJECT_ID )            , 1) /* Still a violation */         
            when 0 then 3 /* Corrected violation */  end as F_ACTION_STATUS,     
       '550e8400e29b41d' as F_IGNORED,     
       action.F_ACTION,     
       action.F_DATE,     
       action.F_PRIORITY,     
       action.F_USER,    
       (select d.METRIC_DESCRIPTION 
          from <CENTRAL_BASE>.DSS_METRIC_DESCRIPTIONS d 
         where d.METRIC_ID = action.F_METRIC_ID 
           and d.DESCRIPTION_TYPE_ID = 0 
           and d.LANGUAGE = 'ENGLISH'), /* nom de la QR */    
/*=> Technical Criterion Name
           (select d.METRIC_DESCRIPTION 
          from <CENTRAL_BASE>.DSS_METRIC_DESCRIPTIONS d 
         where d.METRIC_ID = action.F_TC_METRIC_ID 
           and d.DESCRIPTION_TYPE_ID = 0 
           and d.LANGUAGE = 'ENGLISH'),
<=*/
           /* Technical criterion name */  
           action.F_METRIC_GROUP /* metric group of violation */  
  from  ( /* Part 1 is for Distributions */ select ap.OBJECT_ID as F_OBJECT_ID,   /* object selected for action */ 
                                                   ap.METRIC_ID as F_METRIC_ID,   /* distribution id */
                                                   mtt.METRIC_PARENT_ID as F_TC_METRIC_ID,     /* parent Technical Criterion id of distribution (may be more than one) */  
                                                   ap.METRIC_ID as F_DETAIL_METRIC_ID,   /* distribution id */     
                                                   ap.ACTION_DEF     as F_ACTION,   /* action comment */     
                                                   ap.SEL_DATE as F_DATE,     /* date of selection */     
                                                   ap.PRIORITY as F_PRIORITY,    /* action priority */     
                                                   ap.USER_NAME as F_USER,     /* user having selected object */     
                                                   5 as F_METRIC_GROUP      /* metric group for Distributions */ 
                                              from <CENTRAL_BASE>.VIEWER_ACTION_PLANS ap,
                                                   <CENTRAL_BASE>.DSS_METRIC_TYPE_TREES mtt,
                                                   (select distinct l.NEXT_OBJECT_ID,         
                                                                    ml.OBJECT_ID      /* useful ? */     
                                                               from <CENTRAL_BASE>.DSS_LINK_INFO l,         
                                                                    <CENTRAL_BASE>.DSS_LINK_INFO l2,        
                                                                    <CENTRAL_BASE>.DSS_MODULE_LINKS ml     
                                                              where l.PREVIOUS_OBJECT_ID = ml.MODULE_ID         
                                                                and l.LINK_TYPE_ID = 3         
                                                                and l.SNAPSHOT_ID = <SNAPSHOT_ID>         
                                                                and ml.OBJECT_TYPE_ID = 20000         
                                                                and ml.SNAPSHOT_ID = <SNAPSHOT_ID>         
                                                                and ml.OBJECT_ID in (select OBJECT_ID 
                                                                                       from <CENTRAL_BASE>.DSS_OBJECTS) /* user must see the module */         
                                                                and l2.NEXT_OBJECT_ID = ml.OBJECT_ID         
                                                                and l2.PREVIOUS_OBJECT_ID = <OBJECT_ID>         
                                                                and l2.LINK_TYPE_ID = 1         
                                                                and l2.SNAPSHOT_ID = <SNAPSHOT_ID>     ) theModule,     
                                                   <CENTRAL_BASE>.DSS_SNAPSHOTS firstsnap,     
                                                   <CENTRAL_BASE>.DSS_SNAPSHOTS cursnap 
                                             where ap.METRIC_ID = mtt.METRIC_ID     
                                               and firstsnap.SNAPSHOT_ID = ap.FIRST_SNAPSHOT_ID     
                                               and cursnap.SNAPSHOT_ID = <SNAPSHOT_ID>     
                                               and cursnap.FUNCTIONAL_DATE >= firstsnap.FUNCTIONAL_DATE     
                                               and ap.LAST_SNAPSHOT_ID = 1000000000     
                                               and ap.METRIC_ID in (select METRIC_ID 
                                                                      from <CENTRAL_BASE>.DSS_METRIC_TYPES 
                                                                     where METRIC_GROUP = 5)     
                                               and ap.OBJECT_ID + 0 = theModule.NEXT_OBJECT_ID     
            union all  
          /* Part 2 is for Quality Rules */ select ap.OBJECT_ID as F_OBJECT_ID,  
                                                   ap.METRIC_ID as F_METRIC_ID,  
                                                   mtt.METRIC_PARENT_ID  as F_TC_METRIC_ID,  
                                                   (select DMTT.METRIC_ID 
                                                      from <CENTRAL_BASE>.DSS_METRIC_TYPE_TREES DMTT 
                                                     where DMTT.METRIC_PARENT_ID = ap.METRIC_ID) as F_DETAIL_METRIC_ID,     
                                                   ap.ACTION_DEF as F_ACTION,     
                                                   ap.SEL_DATE as F_DATE,     
                                                   ap.PRIORITY as F_PRIORITY,     
                                                   ap.USER_NAME as F_USER,     
                                                   1 as F_METRIC_GROUP      /* metric group for Quality Rules */ 
                                              from <CENTRAL_BASE>.VIEWER_ACTION_PLANS ap,     
                                                   <CENTRAL_BASE>.DSS_METRIC_TYPE_TREES mtt,     
                                                   (select distinct l.NEXT_OBJECT_ID,         
                                                                    ml.OBJECT_ID     
                                                               from <CENTRAL_BASE>.DSS_LINK_INFO l,         
                                                                    <CENTRAL_BASE>.DSS_LINK_INFO l2,         
                                                                    <CENTRAL_BASE>.DSS_MODULE_LINKS ml     
                                                              where l.PREVIOUS_OBJECT_ID = ml.MODULE_ID         
                                                                and l.LINK_TYPE_ID = 3         
                                                                and l.SNAPSHOT_ID = <SNAPSHOT_ID>         
                                                                and ml.OBJECT_TYPE_ID     = 20000         
                                                                and ml.SNAPSHOT_ID = <SNAPSHOT_ID>         
                                                                and ml.OBJECT_ID in (select OBJECT_ID 
                                                                                       from <CENTRAL_BASE>.DSS_OBJECTS) /* user must see the module */         
                                                                and l2.NEXT_OBJECT_ID = ml.OBJECT_ID         
                                                                and l2.PREVIOUS_OBJECT_ID = <OBJECT_ID>         
                                                                and l2.LINK_TYPE_ID = 1         
                                                                and l2.SNAPSHOT_ID = <SNAPSHOT_ID>      ) theModule,     
                                                   <CENTRAL_BASE>.DSS_SNAPSHOTS firstsnap,     
                                                   <CENTRAL_BASE>.DSS_SNAPSHOTS cursnap 
                                             where ap.METRIC_ID = mtt.METRIC_ID     
                                               and firstsnap.SNAPSHOT_ID = ap.FIRST_SNAPSHOT_ID     
                                               and cursnap.SNAPSHOT_ID = <SNAPSHOT_ID>     
                                               and cursnap.FUNCTIONAL_DATE >= firstsnap.FUNCTIONAL_DATE     
                                               and ap.LAST_SNAPSHOT_ID = 1000000000      
                                               and ap.METRIC_ID in (select METRIC_ID 
                                                                      from <CENTRAL_BASE>.DSS_METRIC_TYPES 
                                                                     where METRIC_GROUP = 1)     
                                               and ap.OBJECT_ID + 0 = theModule.NEXT_OBJECT_ID    ) action 
  order by 9 asc

    2.  In the above query replace the <CENTRAL_BASE>,<SNAPSHOT_ID> with the name of central repository and snpshotID respectively & replace the <OBJECT_ID> with the value retrieved using the below query :

      Select OBJECT_ID from <CENTRAL_BASE>.dss_objects where object_type_id = -102 and object_name = '<MODULE_NAME>'
  2. If you are not having any error or if you are in different version, check if it is a corruption of VIEWER_ACTION_PLANS table

    1. The reason for this is could be that the VIEWER_ACTION_PLANS table is be corrupt. You can find duplicated rows in this table, hence in the dashboard the count shows a greater value. But the exported data is correct, as the duplicates will not be exported from dashboard.

      To confirm the existence of duplicates, run below query

      select ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF, count(1)
from VIEWER_ACTION_PLANS  ap
group by ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF
having count(1) > 1
order by ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF

    2. Run the below script on the Central Base to remove the duplicates

      update VIEWER_ACTION_PLANS  
set METRIC_ID = -METRIC_ID;

insert into VIEWER_ACTION_PLANS  (METRIC_ID, OBJECT_ID, FIRST_SNAPSHOT_DATE, LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF)
select distinct -ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, ap.SEL_DATE, ap.PRIORITY, ap.ACTION_DEF
from VIEWER_ACTION_PLANS  ap;

delete from VIEWER_ACTION_PLANS 
where METRIC_ID < 0;

       After running the script when you reload the page you can see the correct numbers for total,still violation & corrected items on the Action Plan Views

  3. If it is not a corruption of VIEWER_ACTION_PLANS table, contact CAST support with Relevant input

Relevant  input

  1. Sherlock output
  2. Tomcat logs with SQL traces
  3. Screenshot showing the number of rows in the dashboard
  4. Export file with less rows than expected
Notes/comments


Related Pages