SQL Queries - CAST Knowledge Base - Corruptions

Purpose of Query

You will find SQL queries to identify corruptions in CAST Knowledge Base as well as remediation when possible.

Check if you have one of the following corruptions

Error rendering macro ’toc’

[com.ctc.wstx.exc.WstxLazyException] com.ctc.wstx.exc.WstxUnexpectedCharException: Unexpected character ‘"’ (code 34) expected ‘=’ at [row,col {unknown-source}]: [1161,498]

Applicable in CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅
8.1.x✅
8.0.x✅
7.3.x✅
7.0.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server✅
Microsoft SQL Server✅
CSS2✅
Select count ( * ) 
from Acc 
where not exists (select 1 from FusAcc where FusAcc.IdAcc =  Acc.IdAcc)

 

Remediation

Insert into FusAcc (IdFus, IdAcc) 
select IdKey_Generator.nextval, KeyPar.IdKeyPar 
from KeyPar 
where not exists (select 1 from FusAcc where IdAcc = IdKeyPar) / commit /

Integrated on 3C

Select count ( * ) 
from Acc 
where not exists (select 1 from AccRaw where AccRaw.IdAcc = Acc.IdAcc)
 and not exists (select 1 from AccRaw where AccRaw.IdAccRaw = Acc.IdAcc)

Remediation

  1. Use the following query to identify the type of missing links

Select DISTINCT(ACCTYPLO) from Acc where not exists (select 1 from AccRaw where AccRaw.IdAcc = Acc.IdAcc) and not exists (select 1 from AccRaw where AccRaw.IdAccRaw = Acc.IdAcc)


   **Query result sample:**

   ```sql
1073741824

  1. If the type of missing link is equal to 1073741824:

    When the value is 1073741824 and only in this case, this means that only escalated links are wrongly saved into the KB, use the following script to clean these corruptions. Run the script in a SQL Session when connected with the KB user: For Oracle

create table WK_CLN_ACC as select distinct a.IdAcc from Acc a where not exists (select 1 from AccRaw ar where ar.IdAcc = a.IdAcc) and not exists (select 1 from AccRaw ar where ar.IdAccRaw = a.IdAcc) / insert into WK_CLN_ACC select distinct a.IdAcc from Acc a where not exists (select 1 from FusAcc f where f.IdAcc = a.IdAcc) / commit / insert into WK_CLN_ACC select distinct a.IdAcc from Acc a where not exists (select 1 from Keys where IdKey = IdClr) or not exists (select 1 from Keys where IdKey = IdCle) / commit / create table WK_CLN_ACCRAW as select ar.IdAcc, ar.IdAccRaw from AccRaw ar, WK_CLN_ACC da where da.IdAcc = ar.IdAcc / commit / insert into WK_CLN_ACCRAW select ar.IdAcc, ar.IdAccRaw from AccRaw ar, WK_CLN_ACC da where da.IdAcc = ar.IdAccRaw / commit / delete from Acc where IdAcc in (select IdAcc from WK_CLN_ACC) / commit / delete from AccBook where IdAcc in (select IdAcc from WK_CLN_ACC) / commit / create table WK_CLN_FUSACC as select distinct IdFus from FusAcc fa, WK_CLN_ACC da where da.IdAcc = fa.IdAcc and not exists (select 1 from FusAcc fa2, Acc a where fa2.IdFus = fa.IdFus and a.IdAcc = fa2.IdAcc) / commit / delete ObjInf where IdObj in (select IdFus from WK_CLN_FUSACC) / commit / delete ObjDsc where IdObj in (select IdFus from WK_CLN_FUSACC) / commit / delete FusAcc where IdFus in (select IdFus from WK_CLN_FUSACC) / commit / delete AccRaw where (IdAcc, IdAccRaw) in (select IdAcc,IdAccRaw from WK_CLN_ACCRAW) / commit / drop table WK_CLN_ACC / drop table WK_CLN_FUSACC / drop table WK_CLN_ACCRAW /


   **For SQL Server**

   ```sql
select distinct a.IdAcc into WK_CLN_ACC 
from Acc a
where not exists (select 1 from AccRaw ar where ar.IdAcc = a.IdAcc)
and not exists (select 1 from AccRaw ar where ar.IdAccRaw = a.IdAcc);insert into WK_CLN_ACC
select distinct a.IdAcc 
from Acc a
where not exists (select 1 from FusAcc f where f.IdAcc = a.IdAcc);insert into WK_CLN_ACC

 
select distinct a.IdAcc 
from Acc a
where not exists (select 1 from Keys where IdKey = IdClr)
or not exists (select 1 from Keys where IdKey = IdCle);
 
select ar.IdAcc, ar.IdAccRaw into WK_CLN_ACCRAW 
from AccRaw ar, WK_CLN_ACC da 
where da.IdAcc = ar.IdAcc;
 
insert into WK_CLN_ACCRAW 
select ar.IdAcc, ar.IdAccRaw 
from AccRaw ar, WK_CLN_ACC da 
where da.IdAcc = ar.IdAccRaw;
 
delete from Acc
where IdAcc in (select IdAcc from WK_CLN_ACC);
 
delete from AccBook
where IdAcc in (select IdAcc from WK_CLN_ACC);
 
select distinct IdFus into WK_CLN_FUSACC 
from FusAcc fa, WK_CLN_ACC da
where da.IdAcc = fa.IdAcc
and not exists (select 1 from FusAcc fa2, Acc a where fa2.IdFus = fa.IdFus and a.IdAcc = fa2.IdAcc);
 
delete ObjInf
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete ObjDsc
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete FusAcc
where IdFus in (select IdFus from WK_CLN_FUSACC);
 
drop table WK_CLN_ACC;drop table WK_CLN_FUSACC;drop table WK_CLN_ACCRAW;

For CSS

select distinct a.IdAcc into WK_CLN_ACC 
from Acc a 
where not exists (select 1 from AccRaw ar where ar.IdAcc = a.IdAcc)
and not exists (select 1 from AccRaw ar where ar.IdAccRaw = a.IdAcc);
 
insert into WK_CLN_ACC 
select distinct a.IdAcc 
from Acc a 
where not exists (select 1 from FusAcc f where f.IdAcc = a.IdAcc);
 
insert into WK_CLN_ACC
select distinct a.IdAcc 
from Acc a
where not exists (select 1 from Keys where IdKey = IdClr)
or not exists (select 1 from Keys where IdKey = IdCle);
 
select ar.IdAcc, ar.IdAccRaw into WK_CLN_ACCRAW 
from AccRaw ar, WK_CLN_ACC da 
where da.IdAcc = ar.IdAcc;
 
insert into WK_CLN_ACCRAW 
select ar.IdAcc, ar.IdAccRaw 
from AccRaw ar, WK_CLN_ACC da 
where da.IdAcc = ar.IdAccRaw;
 
delete from Acc
where IdAcc in (select IdAcc from WK_CLN_ACC);
 
delete from AccBook
where IdAcc in (select IdAcc from WK_CLN_ACC);
 
select distinct IdFus into WK_CLN_FUSACC 
from FusAcc fa, WK_CLN_ACC da
where da.IdAcc = fa.IdAcc
and not exists (select 1 from FusAcc fa2, Acc a where fa2.IdFus = fa.IdFus and a.IdAcc = fa2.IdAcc);
 
delete from ObjInf
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete from ObjDsc
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete from FusAcc
where IdFus in (select IdFus from WK_CLN_FUSACC);
 
drop table WK_CLN_ACC;drop table WK_CLN_FUSACC;drop table WK_CLN_ACCRAW;

Note: This cleanup could also fix corruptions reported by the check for “objects incompletely saved” 3. Else If the type of missing link is not equal to 1073741824, then populate the table ACCRAW as follows:

INSERT INTO ACCRAW
         (IDACC, 
          IDACCRAW, 
          DIST) 
SELECT IDACC, 
    IDACC, 
    0 
FROM   ACC 
WHERE  NOT EXISTS (SELECT 1 
                FROM   ACCRAW 
                WHERE  ACCRAW.IDACC = ACC.IDACC) 
    AND NOT EXISTS (SELECT 1 
                    FROM   ACCRAW 
                    WHERE  ACCRAW.IDACCRAW = ACC.IDACC)

Integrated on 3C

 

Select count ( * ) 
from Acc 
where not exists (select 1 from Keys where IdKey = IdCle)

Remediation

For SQL Server:

select distinct kp.IdKeyPar into WK_CLN_KEYPAR 
from KeyPar kp
where not exists (select 1 from Keys k where k.IdKey = kp.IdKey)
or not exists (select 1 from Keys k where k.IdKey = kp.IdParent)
gogo

delete from KeyPar
where
IdKeyPar in (select IdKeyPar from WK_CLN_KEYPAR)
gogo
select distinct fa.IdFus into WK_CLN_FUSACC 
from FusAcc fa, WK_CLN_KEYPAR da
where da.IdKeyPar = fa.IdAcc
and not exists (select 1 from FusAcc fa2, KeyPar kp where fa2.IdFus = fa.IdFus and kp.IdKeyPar = fa2.IdAcc)
gogo

 
delete ObjInf
where IdObj in (select IdFus from WK_CLN_FUSACC)
gogo
delete ObjDsc
where IdObj in (select IdFus from WK_CLN_FUSACC)
gogo
delete FusAcc
where IdFus in (select IdFus from WK_CLN_FUSACC)
gogo
drop table WK_CLN_KEYPAR
go
drop table WK_CLN_FUSACC
go
select distinct a.IdAcc into WK_CLN_ACC 
from Acc a
where not exists (select 1 from AccRaw ar where ar.IdAcc = a.IdAcc)
and not exists (select 1 from AccRaw ar where ar.IdAccRaw = a.IdAcc)
go
insert into WK_CLN_ACC
select distinct a.IdAcc 
from Acc a
where not exists (select 1 from FusAcc f where f.IdAcc = a.IdAcc)
gogo
insert into WK_CLN_ACC
select distinct a.IdAcc 
from Acc a
where not exists (select 1 from Keys where IdKey = IdClr)
or not exists (select 1 from Keys where IdKey = IdCle)
gogo
select ar.IdAcc, ar.IdAccRaw into WK_CLN_ACCRAW 
from AccRaw ar, WK_CLN_ACC da
where da.IdAcc = ar.IdAcc
gogo
insert into WK_CLN_ACCRAW 
select ar.IdAcc, ar.IdAccRaw 
from AccRaw ar, WK_CLN_ACC da
where da.IdAcc = ar.IdAccRaw
gogo
delete from Acc
where IdAcc in (select IdAcc from WK_CLN_ACC)
gogo
delete from AccBook
where IdAcc in (select IdAcc from WK_CLN_ACC)
gogo
select distinct IdFus into WK_CLN_FUSACC 
from FusAcc fa, WK_CLN_ACC da
where da.IdAcc = fa.IdAcc
and not exists (select 1 from FusAcc fa2, Acc a where fa2.IdFus = fa.IdFus and a.IdAcc = fa2.IdAcc)
gogo
delete ObjInf
where IdObj in (select IdFus from WK_CLN_FUSACC)
gogo
delete ObjDsc
where IdObj in (select IdFus from WK_CLN_FUSACC)
gogo
delete FusAcc
where IdFus in (select IdFus from WK_CLN_FUSACC)
gogo

 
delete AccRaw
from WK_CLN_ACCRAW
where WK_CLN_ACCRAW.IdAcc =AccRaw.IdAcc
and WK_CLN_ACCRAW.IdAccRaw= AccRaw.IdAccRaw
gogo
drop table WK_CLN_ACC
go
drop table WK_CLN_FUSACC
go
drop table WK_CLN_ACCRAW
go

For CSS:

select distinct kp.IdKeyPar into WK_CLN_KEYPAR 
from KeyPar kp
where not exists (select 1 from Keys k where k.IdKey = kp.IdKey)
or not exists (select 1 from Keys k where k.IdKey = kp.IdParent);
 
delete from KeyPar
where IdKeyPar in (select IdKeyPar from WK_CLN_KEYPAR);
 
select distinct fa.IdFus into WK_CLN_FUSACC 
from FusAcc fa, WK_CLN_KEYPAR da
where da.IdKeyPar = fa.IdAcc
and not exists (select 1 from FusAcc fa2, KeyPar kp where fa2.IdFus = fa.IdFus and kp.IdKeyPar = fa2.IdAcc);
 
delete from ObjInf
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete from ObjDsc
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete from FusAcc 
where IdFus in (select IdFus from WK_CLN_FUSACC);
 
drop table WK_CLN_KEYPAR;
 
drop table WK_CLN_FUSACC;
 
select distinct a.IdAcc into WK_CLN_ACC 
from Acc a
where not exists (select 1 from AccRaw ar where ar.IdAcc = a.IdAcc)
and not exists (select 1 from AccRaw ar where ar.IdAccRaw = a.IdAcc);
 
insert into WK_CLN_ACC
select distinct a.IdAcc 
from Acc a
where not exists (select 1 from FusAcc f where f.IdAcc = a.IdAcc);
 
insert into WK_CLN_ACC
select distinct a.IdAcc 
from Acc a
where not exists (select 1 from Keys where IdKey = IdClr)
or not exists (select 1 from Keys where IdKey = IdCle);
 
select ar.IdAcc, ar.IdAccRaw into WK_CLN_ACCRAW 
from AccRaw ar, WK_CLN_ACC da
where da.IdAcc = ar.IdAcc;
 
insert into WK_CLN_ACCRAW 
select ar.IdAcc, ar.IdAccRaw 
from AccRaw ar, WK_CLN_ACC da
where da.IdAcc = ar.IdAccRaw;
 
delete from Acc
where IdAcc in (select IdAcc from WK_CLN_ACC);
 
delete from AccBook
where IdAcc in (select IdAcc from WK_CLN_ACC);
 
select distinct IdFus into WK_CLN_FUSACC 
from FusAcc fa, WK_CLN_ACC da
where da.IdAcc = fa.IdAcc
and not exists (select 1 from FusAcc fa2, Acc a where fa2.IdFus = fa.IdFus and a.IdAcc = fa2.IdAcc);
 
delete from ObjInf
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete from ObjDsc
where IdObj in (select IdFus from WK_CLN_FUSACC);
 
delete from FusAcc
where IdFus in (select IdFus from WK_CLN_FUSACC);
 
delete from AccRaw a 
where exists ( select 1 from WK_CLN_ACCRAW w where w.IdAcc =a.IdAcc and w.IdAccRaw= a.IdAccRaw);
 
drop table WK_CLN_ACC;
 
drop table WK_CLN_FUSACC;
 
drop table WK_CLN_ACCRAW;

Note: this cleanup could also fix corruptions reported by the check for “objects incompletely saved” & Count missing Parents

Integrated on 3C

 

Count inconsistencies in TCC tables

select count ( * ) 
from DSS_DataFunction 
where not exists (select 1 from Keys where IdKey = MainTable_ID) 
select count ( * ) 
from DSS_DataFunction 
where IsInternal not in (0,1) 
select count ( * ) 
from DSS_DataFunction 
where User_IsInternal is not null
 and User_IsInternal not in (0,1) 
select count ( * ) 
from DSS_DataFunction 
where User_FP_Value is not null
 and User_FP_Value not between 1 and 100 
select count ( * )
 from DSS_DataFunction
 where Cal_MergeRoot_ID != 0
 and Cal_Flags != 4 
select count ( * )
 from DSS_DataFunction T
 where Cal_MergeRoot_ID != 0
 and not exists (select 1 from DSS_DataFunction T2 where T2.Object_ID = T.Cal_MergeRoot_ID) 
select count ( * )
 from DSS_DataFunction
 where Cal_Flags not in (0,1,2,4,8,9,10,128,129,130,136,137,138) 

select count ( * )
 from DSS_DataFunctionDetails D
 where not exists (select 1 from DSS_DataFunction T where T.Object_ID = D.Object_ID) 
select count ( * )
 from DSS_DataFunctionDetails D
 where not exists (select 1 from Keys where IdKey = Table_ID) 
select count ( * )
 from DSS_DataFunctionDetails
 where MergeFlags not in (0,1,2) 

select count ( * )
 from DSS_Transaction
 where not exists (select 1 from Keys where IdKey = Form_ID) 
select count ( * )
 from DSS_Transaction
 where IsInput not in (0,1) 
select count ( * )
 from DSS_Transaction
 where User_IsInput is not null
 and User_IsInput not in (0,1,2) 
select count ( * )
 from DSS_Transaction
 where User_FP_Value is not null
 and User_FP_Value not between 1 and 100 
select count ( * )
 from DSS_Transaction
 where Cal_MergeRoot_ID != 0
 and Cal_Flags != 4 
select count ( * )
 from DSS_Transaction T
 where Cal_MergeRoot_ID != 0
 and not exists (select 1 from DSS_Transaction T2 where T2.Object_ID = T.Cal_MergeRoot_ID) 
select count ( * )
 from DSS_Transaction
 where Cal_Flags not in (0,1,2,4,8,9,10,17,33,65,35,41,73,128,129,130,136,137,138,145,161,193,153,169,201) 

select count ( * )
 from DSS_TransactionRoots R
 where not exists (select 1 from DSS_Transaction T where T.Object_ID = R.Object_ID) 
select count ( * )
 from DSS_TransactionRoots R
 where not exists (select 1 from Keys where IdKey = Root_ID) 
select count ( * )
 from DSS_TransactionRoots
 where MergeFlags not in (0,1,2,3,4) 

select count ( * )
 from DSS_TransactionDetails D
 where not exists (select 1 from DSS_Transaction T where T.Object_ID = D.Object_ID) 
select count ( * )
 from DSS_TransactionDetails D
 where not exists (select 1 from Keys where IdKey = Child_ID) 
select count ( * )
 from DSS_TransactionDetails
 where ChildType not between 0 and 7

Remediation

  1. First, general cleanup solution: To repair the database, you can reset all parameters and restart configuration from the beginning. To remove all configuration, run those queries: 

update dss_datafunction set cal_flags = 0, cal_mergeroot_id = 0, user_isinternal = null, user_fp_value = null  update dss_transaction set cal_flags = 0, cal_mergeroot_id = 0, user_isinput = null, user_fp_value = null

2. Second, specific cleanup solution: The queries used to fix the corrupted KB for the specific problem caused by filtering job: 

   ```sql
update dss_datafunction set user_isinternal = null where user_isinternal not in (0,1,2) 
update dss_datafunction set user_fp_value = null where user_fp_value > 50 
update dss_datafunction set cal_flags = 2, cal_mergeroot_id = 0 where cal_flags in (2,4,6) and object_id in (select cal_mergeroot_id from dss_datafunction) 
update dss_datafunction set cal_flags = 4 where cal_flags = 6 

update dss_transaction set user_isinput = null where user_isinput not in (0,1,2) 
update dss_transaction set user_fp_value = null where user_fp_value > 50 
update dss_transaction set cal_flags = 2, cal_mergeroot_id = 0 where cal_flags in (2,4,6) and object_id in (select cal_mergeroot_id from dss_transaction) 
update dss_transaction set cal_flags = 4 where cal_flags = 6

Those queries must be run in this order, some of them depend on the previous ones.  They were designed for specific problem caused by filtering job, but they can be run on any KB, because they don’t cause any change when everything is OK. Queries provided by GGA (CAST-29208)

To be integrated on 3C (CCC-37)

 

Count missing Parents

Select count(1) from KeyPar Where not exists (select 1 from Keys where Keys.IdKey = KeyPar.IdKey)
Select count(1) from KeyPar Where not exists (select 1 from Keys where Keys.IdKey = KeyPar.IdParent)
Select count(1) from KeyPar Where not exists (select 1 from FusAcc where IdAcc = KeyPar.IdKeyPar)

Remediation

Possible reasons

The corruption can be created when removing User Defined Module(s) UDM from the Snapshot Preparation Assistant Job.When processing a UDM, technical subsets are created as objects belongings to the UDM. When removing the UDM from SPA, the technical subsets objects are removed from table KEYS but remains referenced as parent in table KEYPAR.

Use the following queries to repair the corruption of the KB.

Delete from FusAcc 
where IdAcc in (select IdKeyPar from KeyPar where not exists (select 1 from Keys where IdKey = IdParent))
Commit
Delete from KeyPar 
where not exists (select 1 from Keys where IdKey = IdParent)
Commit

Integrated on 3C

 

Select count(1) 
from Tab 
Where Not exists (select 1 from KeyPar where IdKey = Tab.IdTab)

Remediation

Not available

Integrated on 3C

 

Count objects incompletely save

 Select count ( * )
 from Acc
 where not exists (select 1 from Keys where IdKey = IdClr)
 or not exists (select 1 from Keys where IdKey = IdCle)

 

Remediation

Not available

Integrated on 3C

Getting technical module having as parents technical modules

Following query provides the list of technical module having as parents technical modules.

select P.MODULE_ID, P.MODULE_NAME, M.MODULE_ID, M.MODULE_NAME
 from KeyPar KP, DSS_VIEW_MODULES M, DSS_VIEW_MODULES P
 where KP.IdKey = M.MODULE_ID
 and KP.IdParent = P.MODULE_ID

 

Remediation

This query must return 0 row. If this query returns 1 row or more, You must modify definition of the user defined module in order to not consider the objects provided by this query.

Integrated on 3C by support

Empty result procedure names in DSS_METRIC_VALUE_TYPES table

This following query must return 0 row on a healthy KB, otherwise it returns the wrong records in DSS_METRIC_VALUE_TYPES.

As a consequence of this corruption, the metric is not computed during the snapshot generation.

 --Following query detects empty result procedure names
select T.METRIC_ID, T.METRIC_NAME, V.METRIC_VALUE_INDEX, V.METRIC_VALUE_PROCEDURE_NAME, V.METRIC_AGGREGATE_OPERATOR, V.METRIC_VALUE_PROCEDURE_NAME_2, V.METRIC_AGGREGATE_OPERATOR_2
from DSS_METRIC_VALUE_TYPES V, DSS_METRIC_TYPES T
where V.METRIC_ID = T.METRIC_ID
and V.METRIC_VALUE_PROCEDURE_NAME is null
and V.METRIC_AGGREGATE_OPERATOR = 0
and T.METRIC_TYPE in ( 1, 3 )
and V.METRIC_VALUE_NAME != 'Grade'
and T.METRIC_GROUP not in ( 6, 99 )
and V.METRIC_VALUE_PROCEDURE_NAME_2 not like 'ADGCQ_%'

Remediation

Not available

 

Identify obsolete ‘External URL’ Objects

Context

Following error is encountered when running J2EE analysis

ORA-01422: exact fetch returns more than requested number of rows. ORA-06512: at “CAST_KB_NAME.I_GENPRO”, line 30 Object ‘External URLs’ cannot be saved

Check

The below query will return more than an IdKEy

SELECT IdKey from Keys 
where KeyClass = 8160
 and KeyNam = 'External URLs';

While the below query will return only a key

select * from anapro
 where IDPRO in ( IdKey1, IdKey2, etc ); -- where IdKEy1, IdKey2 are results of previous queries

This shows that only the object(s) of this above query is (are) still consistent.

or use the concanated query

SELECT IdKey 
from Keys k
 where KeyClass = 8160
 and KeyNam = 'External URLs'
and NOT EXISTS ( select 1 from Anapro ap where ap.IdPro = k.IdKey)

Remediation

In case of Oracle

Execute the following Query in a SQL Session for each obsolete object:

begin
    declare Err int;
                  Id int:=-1;
    begin
       Err:= InitConnection( Id, ' CASTCORP\FNG');
       Err:=Object_Delete (Obsolete_IdKey1,0);
   end;
end;

Replace CASTCORP\FNG by an existing User login in the KB and ‘Obsolete_IdKey1’ by the IdKey of obsolete object

Integrated on 3C by support

In case of SQL Server:

DECLARE    @return_value intEXEC    @return_value = [dbo].[Object_Delete]
        @IdObj = <Obsolete_IdKey1>,
        @IdPro = 0SELECT    'Return Value' = @return_value

Replace <Obsolete_IdKey1>  by the IdKey of obsolete object

In case of CSS:

Execute the following Query in a SQL Session for each obsolete object:

 select Object_Delete (<Obsolete_IdKey1> ,0)

Replace <Obsolete_IdKey1>  by the IdKey of obsolete object

 

Count duplicated object in SYNDSC

Having duplicated objects in this table can lead to a migration failure with the following error ORA-02437: cannot validate (KB_NAME.PK_SYNDSC) - primary key violated

h.2 Check

Execute the following query to identify the number of duplications

select count(*) 
from (select IdAcc,IdAccSyn from SynDsc group by IdAcc,IdAccSyn having count(1)> 1) count

Remediation

Execute the following Query in a SQL Session to remove duplications:

create table SynDscDup as select IdAccSyn, IdAcc, min(AccTypLo) AccTypLo, min (AccTypHi) AccTypHi from SynDsc
group by IdAccSyn, IdAcc
/
commit
/
truncate table SynDsc
/
insert into SynDsc select * from SynDscDup
/
commit
/
drop table SynDscDup
/

Integrated on 3C by support

Count duplicated object in APPSET

Having duplicate row in this table can lead to snapshot error in compute steps with following error: “ERROR: duplicate key value violates unique constraint “idx_appset_nam” Connection Error: insert into .APPSET(IDSET, IDSETNAM, IDJOB) values (?, ?, ?).”

Check

Run the below query on local schema to detect the duplicate object :

 select *
 from APPSET
 where idsetnam like '%IFPUG objects##'

Remediation

Detect the row that will be duplicated and delete that row from the local

  1. Run the below query on local schema to detect the duplicate object
select * from APPSET where idsetnam like '%IFPUG objects##'
  1. You’ll get a list of rows where the name of the application appears in idsetnam field as prefix. Simply delete the rows that correspond to the application you want to snapshot and you’ll not get the constraint error. Note down the idset of the returns and replace in the below query to delete the rows on your local schema.
 delete from APPSET
 where idset = <idset noted in above query>

Count duplicated object in Objects (GUID - only for AMT)

Having duplicated objects in this table can lead to a analysis failure on an Oracle RDBMS, with the ORA-02437 error: single-row subquery returns more than one row, reported in AMT_P_FILL_OBJ function, line 44.

It’s not worth to suppress them because this is the sign of a bug in the analyser side, so it must be fixed.

How to find duplicates

 select IdNam
 from Objects
 group by IdNam having count(1) > 1;

Remediation

Not available

This check is relevant when a crash occurs when trying to open a graphical view in Enlighten

The following query lists object that are not competely saved for the graphical views:

SELECT * 
FROM Mod 
WHERE NOT EXISTS (SELECT 1 FROM ModCom WHERE ModCom.IdMod = Mod.IdMod)

Remediation

Not available

Integrated on 3C by support

Missing project for SQL tables and views

Description :

Some SQL tables and views produced by the PL/SQL analyser are not linked to any project in the KB. They can be detected when the following queries returns a result > 0

 select count(*)
 from VEW
 where not exists (select 1 from ObjPro where IdObj = IdVew)
 and exists (select 1 from Keys where IdKey = IdDbe)
 
select count(*)
 from TAB
 where not exists (select 1 from ObjPro where IdObj = IdTab)
 and exists (select 1 from Keys where IdKey = IdDbe)

 select count(*)
 from TAB
 where not exists (select 1 from ObjPro where IdObj = IdTab)
 and exists (select 1 from Keys where IdKey = IdDbe)

Remediation

Theses corruptions can be corrected through executing the following queries on the KB

insert into ObjPro (IdObj, IdPro, Prop)
 select IdVew, IdDbe, 0
 from VEW
 where not exists (select 1 from ObjPro where IdObj = IdVew)
 and exists (select 1 from Keys where IdKey = IdDbe)


 insert into ObjPro (IdObj, IdPro, Prop)
 select IdTab, IdDbe, 0
 from TAB
 where not exists (select 1 from ObjPro where IdObj = IdTab)
 and exists (select 1 from Keys where IdKey = IdDbe)

Integrated on 3C by support

Unable to render Jira issues macro, execution error.

Check for potential duplicate IDs in Keys, Objects, Acc, AccRaw, AccSymb, FusAcc & KeyPar tables

The Keys, Objects, Acc, AccRaw, AccSymb, FusAcc & KeyPar tables share the same scope of IDs.

Therefore, for example, two objects, two links, but also an object and a link, cannot share the same ID.

When two objects, two links, two symbolic links, etc… share the same IDs, it generally throws an SQL error at the saving time (because of SQL consistency rules). However, when the ID is shared between an object and a link, no SQL error is thrown. But the corruption is still there…

The shared scope is implemented differently under SQL Server (use of the Parms table) and under Oracle and CSS (use of a sequence). Therefore, the SQL consistency queries will be different under those three platforms.

Check duplicate IDs accross tables

 When some of the following queries return some rows, the database is corrupted:

 -- Detects duplicate IDs between Keys & Acc tables
 select k.IdKey as Keys_Acc_DuplicateId
 from Keys k
 join Acc a
 on a.IdAcc = k.IdKey
 
-- Detects duplicate IDs between Keys & AccRaw tables
 select k.IdKey as Keys_AccRaw_DuplicateId
 from Keys k
 join AccRaw ar
 on ar.IdAcc = k.IdKey

 select k.IdKey as Keys_AccRaw_DuplicateId
 from Keys k
  join AccRaw ar
    on ar.IdAccRaw = k.IdKey

-- Detects duplicate IDs between Keys & AccSymb tables
 select k.IdKey as Keys_AccSymb_DuplicateId
 from Keys k
   join AccSymb a
   on a.IdAcc = k.IdKey

-- Detects duplicate IDs between Keys & FusAcc tables
 select k.IdKey as Keys_FusAcc_DuplicateId
  from Keys k
    join FusAcc fa
     on fa.IdFus = k.IdKey
 
  select kp.IdKeyPar as KeyPar_AccRaw_DuplicateId
  from KeyPar kp
    join AccRaw ar
     on ar.IdAccRaw = kp.IdKeyPar

-- Detects duplicate IDs between KeyPar & AccSymb tables  
  select kp.IdKeyPar as KeyPar_AccSymb_DuplicateId
  from KeyPar kp
    join AccSymb a
     on a.IdAcc = kp.IdKeyPar

-- Detects duplicate IDs between KeysPar & FusAcc tables
  select kp.IdKeyPar as KeyPar_FusAcc_DuplicateId
  from KeyPar kp
    join FusAcc fa
     on fa.IdFus = kp.IdKeyPar

Remediation

Not available

Duplicate IDs in Keys, Objects, Acc, AccRaw, AccSymb, FusAcc & KeyPar tables: SQL Server specifics

Possible reasons

From 7.0.8 up to 7.0.12 versions, under SQL Server, the links escalation process was not updating the Parms table, leading to massive potential duplicate between objects and links, and to frequent duplicate error between two objects or two links.

Context

Following error is encountered during analysis

SQL Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint ‘PK_ACCIDACC’. Cannot insert duplicate key in object ‘dbo.Acc’. The duplicate key value is (192086). (Severity 1, Msg No 2627). SQL Warning: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (severity 0, msg# 3621, line# 0). Procedure call failed: TEST_709_LOCAL..CACHE_FLUSHDATA,@I_IDSESSION Internal exception occurred during running listener CPersistingAction::ProcessPersistentInstance : Analysis saving could not be finalized

So far this has been reported in case of SQL Server bases and in CAST Version 7.0.8 & 7.0.9

Checking invalid shared IDs scope

When the following query return a row, the database is corrupted

 --Returns the maximum ID below which all the objects, links, etc... are certainly corrupted
  select IntVal as InvalidMaxId
   from Parms
  where Lib = 'Id'
    and IntVal < (select max(IdObj)
                   from (select max(IdKey) as IdObj
                           from Keys
                          union all
                         select max(IdKey)
                           from Objects
                          union all
                         select max(IdAcc)
                           from Acc
                          union all
                         select max(IdAcc)
                           from AccSymb
                          union all
                         select max(IdFus)
                           from FusAcc
                          union all
                         select max(IdKeyPar)
                           from KeyPar
                     ) as MaxIds
              )

Remediation

Prior to relaunch all the analysis of the KB, run the below statements on the corrupted local

 -- fix the shared IDs scope update Parms
    set IntVal = (select max(IdObj) + 1
                   from (select max(IdKey) as IdObj
                           from Keys
                          union all
                         select max(IdKey)
                           from Objects
                          union all
                         select max(IdAcc)
                           from Acc
                          union all
                         select max(IdAcc)
                           from AccSymb
                          union all
                         select max(IdFus)
                           from FusAcc
                          union all
                         select max(IdKeyPar)
                           from KeyPar
                     ) as MaxIds
                 )  where Lib = 'Id'
 
-- remove all rows from Acc
 truncate table Acc

-- remove all rows from AccRaw
 truncate table AccRaw

-- remove all rows from AccSymb
 truncate table AccSymb

-- remove all rows from FusAcc
 truncate table FusAcc

 

Consistency in the DSS_METRIC_RESULTS

If Total value lower than Detail value
select appli.OBJECT_NAME, dmt.METRIC_NAME, dmrd.METRIC_NUM_VALUE detail, dmr.METRIC_NUM_VALUE total
from DSS_METRIC_RESULTS dmr
join DSS_METRIC_TYPES dmt
on dmt.METRIC_ID = dmr.METRIC_ID
join DSS_OBJECTS appli
on appli.OBJECT_Id = dmr.OBJECT_ID
join DSS_METRIC_RESULTS dmrd
on dmrd.OBJECT_ID = dmr.OBJECT_ID
and dmrd.SNAPSHOT_ID = dmr.SNAPSHOT_ID
and dmrd.METRIC_ID = dmr.METRIC_ID
and dmr.METRIC_NUM_VALUE < dmrd.METRIC_NUM_VALUE
and dmr.METRIC_VALUE_INDEX = 2
and dmrd.METRIC_VALUE_INDEX = 1

Remediation

Not available

Integrated on 3C by support

If Total value is not calculated
select appli.OBJECT_NAME application, dmt.METRIC_NAME
from DSS_METRIC_RESULTS dmrd
join DSS_METRIC_TYPES dmt
on dmt.METRIC_ID = dmrd.METRIC_ID
and dmrd.METRIC_VALUE_INDEX = 1
 and (dmt.METRIC_ID & 1) = 0 /*only aggregate values*/
and dmt.METRIC_GROUP = 1 /*only QR*/
join DSS_OBJECTS appli
on appli.OBJECT_Id = dmrd.OBJECT_ID
where not exists (select 1
from DSS_METRIC_RESULTS dmr
where dmrd.OBJECT_ID = dmr.OBJECT_ID
and dmrd.SNAPSHOT_ID = dmr.SNAPSHOT_ID
and dmrd.METRIC_ID = dmr.METRIC_ID
and dmr.METRIC_VALUE_INDEX = 2)

Remediation

Not available

Integrated on 3C by support

If Detail value is not calculated
select appli.OBJECT_NAME application, dmt.METRIC_NAME
from DSS_METRIC_RESULTS dmr
join DSS_METRIC_TYPES dmt
on dmt.METRIC_ID = dmr.METRIC_ID
and dmr.METRIC_VALUE_INDEX = 2
and (dmt.METRIC_ID & 1) = 0 /*only aggregate values*/
and dmt.METRIC_GROUP = 1 /*only QR*/
join DSS_OBJECTS appli
on appli.OBJECT_Id = dmr.OBJECT_ID
where not exists (select 1
from DSS_METRIC_RESULTS dmrd
where dmrd.OBJECT_ID = dmr.OBJECT_ID
and dmrd.SNAPSHOT_ID = dmr.SNAPSHOT_ID
and dmrd.METRIC_ID = dmr.METRIC_ID
and dmrd.METRIC_VALUE_INDEX = 1)

Remediation

Not available

Integrated on 3C by support

 

Corruption detection between ANAJOB and KEYS

 Select k.*
 From Keys k, TypCat tc, Cat c
 Where c.CatNam = 'ANALYZER_JOB'
 And tc.IdCatParent = c.IdCat
 And k.ObjTyp = tc.IdTyp
 and IdKey not in ( select IdJob from AnaJob )

Remediation

Not available

Integrated on 3C by support

Corruption due to missing project in ProDep table

SELECT a.IdPro, 
       k.*    , 
       t.*
FROM   (SELECT DISTINCT IdPro
       FROM             Acc 
       ) 
       a
       JOIN Keys k
       ON     k.IdKey = a.IdPro
       JOIN Typ t
       ON     t.IdTyp = k.ObjTyp
WHERE  a.IdPro NOT IN 
       (SELECT IdPro
       FROM    ProDep 
       )

Remediation

Not available

Not Integrated on 3C

 

Corruption in anapro table

SELECT   IdJob, 
         COUNT(1)
FROM     (SELECT DISTINCT IdJob, 
                          JobTyp
         FROM             AnaPro 
         ) 
         a
GROUP BY IdJob
HAVING   COUNT(1) > 1
ORDER BY IdJob

Remediation

Not available

Not Integrated on 3C

SELECT COUNT(*)
FROM   AccRaw
WHERE  IdAcc NOT IN
       (SELECT IdAcc
       FROM    Acc
       )

Remediation

DELETE
FROM   AccRaw
WHERE  IdAcc NOT IN
       (SELECT IdAcc
       FROM    Acc
       )

Not Integrated on 3C

Corruption in AnaJob table

SELECT DISTINCT aj.idjob            , 
                ap.IdPro            , 
                k1.keynam AS Job_Nam, 
                T1.Typnam AS Typ_Job, 
                k2.keynam AS Pro_Nam, 
                T2.Typnam AS Typ_Pro, 
                COUNT(1)
FROM            AnaJob aj
                JOIN keys k1 
                ON              k1.idkey = aj.IdJob
                JOIN Typ T1 
                ON              k1.objtyp = T1.idTyp
                JOIN AnaPro ap 
                ON              ap.IdJob = aj.IdJob
                JOIN keys k2 
                ON              k2.idkey = ap.IdPro
                JOIN Typ T2 
                ON              k2.objtyp = T2.idTyp
                JOIN ObjPro op 
                ON              op.IdPro = ap.IdPro
                JOIN Keys k 
                ON              k.IdKey  = op.IdObj
                AND             k.ObjTyp = 138017 --SQL Tables
GROUP BY        aj.Idjob , 
                k1.keynam, 
                ap.IdPro , 
                k2.keynam, 
                T1.Typnam, 
                T2.Typnam
ORDER BY        t1.typnam

Remediation

Not available

Not Integrated on 3C

Corruption in DSS_OBJECT_TYPES & DSS_TECHNOLOGIES

SELECT dot.OBJECT_TYPE_ID , 
 dot.OBJECT_TYPE_NAME "dashboard technology", 
 dot2.OBJECT_TYPE_ID , 
 dot2.OBJECT_TYPE_NAME "project subset"
FROM DSS_TECHNOLOGIES dt
 JOIN DSS_OBJECT_TYPES dot
 ON dt.TECHNO_TYPE_ID = dot.OBJECT_TYPE_ID
 JOIN DSS_OBJECT_TYPES dot2
 ON dt.MODULE_TYPE_ID = dot2.OBJECT_TYPE_ID
ORDER BY 4

Remediation

In the result of the above query is you find technologies such as C# , javascript or html then this is a corruption caused by performing Load Meta model from disk via Servman.

To fix the issue follow th below steps

  • run component reinstall in Servman for KB & CB
  • perform a new snapshot without reanalysis
  • delete the wrong snapshot
  • check the dashboard & if you still see the issue i.e same object detected for two different object types then reconsolidate the snapshot

Not Integrated on 3C

Corruption of AMT metamodel due to categories or types sharing the same ID

SELECT   SHARED_CATEGORIES_OR_TYPES.ID       AS SHARED_ID       ,
         SHARED_CATEGORIES_OR_TYPES.NAME     AS SHARING_NAME    ,
         SHARED_CATEGORIES_OR_TYPES.METATYPE AS SHARING_METATYPE,
         stc.SHARING_ORDER
FROM     (SELECT c.IdCat    AS ID  ,
                 c.CatNam   AS NAME,
                 'Category' AS METATYPE
         FROM    Cat c
          
         UNION ALL
          
         SELECT t.IdTyp ,
                t.TypNam,
                'Type'
         FROM   Typ t
         ) 
         SHARED_CATEGORIES_OR_TYPES
         JOIN 
                  (SELECT  tc.ID    AS        SHARED_ID,
                           COUNT(1) AS        SHARING_ORDER
                  FROM     (SELECT c.IdCat  AS ID,
                                   c.CatNam AS NAME
                           FROM    Cat c
                            
                           UNION ALL
                            
                           SELECT t.IdTyp,
                                  t.TypNam
                           FROM   Typ t
                           ) 
                           tc
                  GROUP BY tc.ID
                  HAVING   COUNT(1) > 1 
                  ) 
                  stc
         ON       stc.SHARED_ID = SHARED_CATEGORIES_OR_TYPES.ID
ORDER BY SHARED_CATEGORIES_OR_TYPES.ID  , 
         SHARED_CATEGORIES_OR_TYPES.NAME, 
         SHARED_CATEGORIES_OR_TYPES.METATYPE;

Remediation

Not available. Contact support after having identified the categories with shared IDs

Not Integrated on 3C

Corruption of AMT metamodel due to categories or types sharing the same name

SELECT shared_categories_or_types.NAME AS shared_name , shared_categories_or_types.id AS sharing_id , shared_categories_or_types.metatype AS sharing_metatype, stc.sharing_order FROM (
       SELECT c.idcat    AS id , 
              c.catnam   AS NAME, 
              'Category' AS metatype 
       FROM   cat c 
       UNION ALL 
       SELECT t.idtyp , 
              t.typnam, 
              'Type' 
       FROM   typ t ) shared_categories_or_types JOIN 
( 
         SELECT   tc.NAME  AS shared_name, 
                  count(1) AS sharing_order 
         FROM     ( 
                         SELECT c.idcat  AS id, 
                                c.catnam AS NAME 
                         FROM   cat c 
                         UNION ALL 
                         SELECT t.idtyp, 
                                t.typnam 
                         FROM   typ t ) tc 
         GROUP BY tc.NAME 
         HAVING   count(1) > 1 ) stc ON stc.shared_name = shared_categories_or_types.NAME ORDER BY shared_categories_or_types.NAME, shared_categories_or_types.id , shared_categories_or_types.metatype;

Remediation

Not available. Contact support after having identified the categories with shared names.

For CAST Technical Support only please refer to the page - CMS Assessment model - AMT Inconsistency - Information - Fix duplicate categories or types or properties - CAST Internal

Not Integrated on 3C

Corruption of AMT metamodel due to properties sharing the same storage key (INF_TYPE,INF_SUB_TYPE)

SELECT   p.IdProp  AS SHARING_PROPERTY_ID  ,
         p.PropNam AS SHARING_PROPERTY_NAME,
         SHARED_PROPERTIES.INF_TYPE        ,
         SHARED_PROPERTIES.INF_SUB_TYPE    ,
         SHARED_PROPERTIES.SHARING_ORDER
FROM     (SELECT  it.IntVal  AS INF_TYPE    ,
                  ist.IntVal AS INF_SUB_TYPE,
                  COUNT(1)   AS SHARING_ORDER
         FROM     PropAttr ist
                  JOIN PropAttr it
                  ON       it.IdProp  = ist.IdProp
                  AND      it.AttrNam = 'INF_TYPE'
                  JOIN PropCat pc
                  ON       pc.IdProp = ist.IdProp
                  JOIN Prop p
                  ON       p.IdProp = ist.IdProp
         WHERE    ist.AttrNam       = 'INF_SUB_TYPE'
         GROUP BY it.IntVal, 
                  ist.IntVal
         HAVING   COUNT(1) > 1 
         ) 
         SHARED_PROPERTIES
         JOIN PropAttr ist
         ON       ist.AttrNam = 'INF_SUB_TYPE'
         AND      ist.IntVal  = SHARED_PROPERTIES.INF_SUB_TYPE
         JOIN PropAttr it
         ON       it.IdProp  = ist.IdProp
         AND      it.AttrNam = 'INF_TYPE'
         AND      it.IntVal  = SHARED_PROPERTIES.INF_TYPE
         JOIN PropCat pc
         ON       pc.IdProp = ist.IdProp
         JOIN Prop p
         ON       p.IdProp = ist.IdProp
ORDER BY SHARED_PROPERTIES.INF_TYPE, 
         SHARED_PROPERTIES.INF_SUB_TYPE;

Remediation

Contact support after having identified the properties sharing the same storage key

For CAST Technical Support only please refer to the page - CMS Assessment model - AMT Inconsistency - Information - Fix duplicate categories or types or properties - CAST Internal

Not Integrated on 3C

 

CAST installation is corrupted since the tables LinkTyp, ACCESSBIT which have to be filled during component installation are empty

select count(*) from LinkTyp
 
select count(*) from ACCESSBIT

Remediation

Perform a component re-install using the Servman to fill the tables

Not Integrated on 3C

Corruption due to Missing SPA job post migration

select IdJob from AnaJob where JobTyp = 3001

Remediation

If the query returns no row or < 0, it means that there is no SPA job. Perform migration again.

Not Integrated on 3C

Corruption due to ACC / ObjPro without Project

SELECT idkey
FROM   Keys
WHERE  IdKey IN
       ( SELECT IdPro
       FROM    (SELECT DISTINCT IdPro
               FROM             ObjPro
                 
               UNION
                 
               SELECT DISTINCT IdPro
               FROM            Acc
               ORDER BY        1
               )
               p
       WHERE   IdPro NOT IN
               ( SELECT IdPro
               FROM    AnaPro
                 
               UNION
                 
               SELECT IdPro
               FROM   ProDep
               WHERE  IdProMain IN
                      (SELECT IdPro
                      FROM    AnaPro
                      )
               )
       )

Remediation

If the query returns some rows then it means some objects present in the previous analysis are coming up on dashboard in the snapshot of current version-

Remediation -

  1. Get the id of the object that you are not expecting on dashboard

select object_id from dss_objects where object_name = ‘<object_name>’

2. Get the idpro of the objects by using the object\_id obtained from above query.

   ```sql
select  * from ObjPro where IdObj = '<object_id'

select object_id from dss_objects where object_name = 'getname'
Result -
"object_id"
2708943

select  * from ObjPro where IdObj = 2708943
Result -
"idobj";"idpro";"prop"
2708943;2708302;0

SELECT idkey
FROM   Keys
WHERE  IdKey IN
       ( SELECT IdPro
       FROM    (SELECT DISTINCT IdPro
               FROM             ObjPro
                 
               UNION
                 
               SELECT DISTINCT IdPro
               FROM            Acc
               ORDER BY        1
               )
               p
       WHERE   IdPro NOT IN
               ( SELECT IdPro
               FROM    AnaPro
                 
               UNION
                 
               SELECT IdPro
               FROM   ProDep
               WHERE  IdProMain IN
                      (SELECT IdPro
                      FROM    AnaPro
                      )
               )
       )

Check if the idpro obtained in the above query exists in the results of the query used to detect corruption. If yes then -

  1. Create a SQL tool in the content enrichment tab of the application.
  2. Copy the SQL query in the attached clean_up_query.txt and set the search path to local database.
  3. Run the tool.
  4. See that the object is not present in the Keys table
  5. Run the snapshot and see that the objects are not present in the dashboard.

Not Integrated on 3C

Corruptions on Datafunctions

The following page consists of all the SQL queries which have to be executed on Knowledge Database that is Analysis Service database related to datafunctions. For instance these queries provide information such as getting the list of datafunctions that present a self merge, etc:

SQL Queries - CAST Knowledge Base - Corruptions - Corruptions on datafunctions

SQL Queries - CAST Knowledge Base - Corruptions - Corruptions on datafunctions