SQL Queries - CAST Central Base - Corruptions - How to detect and correct corruptions in the table DSS_MODULE_LINKS

Purpose of Query

This page allows you to detect and correct corruptions in the table DSS_MODULE_LINKS.

Applicable CAST Version
Release
Yes/No
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(tick)
Microsoft SQL Server(tick)
CSS2(tick)
Query for CSS

The following query allows you to detect if there is any corruption in the table DSS_MODULE_LINKS

SELECT l.next_object_id AS FUNC_MODULE_ID, 
       l.snapshot_id 
FROM   dss_link_info l 
       JOIN dss_module_links ml 
         ON ml.object_id = l.next_object_id 
            AND ml.snapshot_id = l.snapshot_id 
WHERE  l.previous_object_id IN (SELECT o.object_id 
                                FROM   dss_objects o 
                                WHERE  o.object_type_id = -102) 
       AND l.link_type_id = 0 

 

Query result example
 280839;1

If the query do not return rows then there is no corruption, else refer to Fix corruption from DSS_MODULE_LINKS for fixing the corruption.

Fix corruption from DSS_MODULE_LINKS

Fix corruption from DSS_MODULE_LINKS

  • Take a backup of your central base (This is a recommended step)
  • Run the following queries on your central schema:
     

    DROP FUNCTION DSS_CLIMB_WORK2_OBJECT_TREE(I_SNAPSHOT_ID integer);

     

    CREATE 
    OR 
    replace FUNCTION dss_update_any_module_links( i_snapshot_id int ) 
    returns int -- o : ok 
    AS $$DECLARE errcode INT;L_LEVEL         int;L_COUNT         int;BEGIN 
      errcode := 0; 
      /* Empty data for this snapshot */ 
      delete 
      FROM   dss_module_links 
      WHERE  snapshot_id = i_snapshot_id; 
    
      create local temporary TABLE wk_dss_module_links ( module_id integer NOT NULL, object_id integer NOT NULL, object_type_id integer NOT NULL, tree_level integer NOT NULL );
      create local temporary TABLE wk_temp_objects ( module_id     integer NOT NULL, object_id integer NOT NULL );
      create INDEX wk_dss_module_links_idx 
      ON wk_dss_module_links 
                   ( 
                                tree_level 
                   ); 
      insert INTO wk_dss_module_links 
                  ( 
                              module_id, 
                              object_id, 
                              object_type_id, 
                              tree_level 
                  ) 
      SELECT * 
      FROM   ( 
                    SELECT m2t.next_object_id     AS module_id, 
                           m2t.previous_object_id AS object_id, 
                           p.object_type_id, 
                           1 -- level : 1 
                    FROM   dss_link_info a2m 
                    JOIN   dss_objects app 
                    ON     app.object_id = a2m.previous_object_id 
                    AND    app.object_type_id = -102 
                    JOIN   dss_link_info m2t 
                    ON     m2t.snapshot_id = i_snapshot_id 
                    AND    m2t.link_type_id = 1 
                    AND    a2m.next_object_id = m2t.previous_object_id 
                    JOIN   dss_objects p 
                    ON     p.object_id = m2t.previous_object_id 
                    WHERE  a2m.snapshot_id = i_snapshot_id 
                    AND    a2m.link_type_id IN (1,8) -- Include also IFPUG modules 
             ) AS wk ; 
    
      -- Insert links from technical modules to functional modules sliced by technology not yet registered
      insert INTO wk_dss_module_links 
                  ( 
                              module_id, 
                              object_id, 
                              object_type_id, 
                              tree_level 
                  ) 
      SELECT * 
      FROM   ( 
                    SELECT m2t.next_object_id     AS module_id, 
                           m2t.previous_object_id AS object_id, 
                           t.object_type_id, 
                           1 
                    FROM   dss_link_info a2s 
                    JOIN   dss_objects app 
                    ON     app.object_id = a2s.previous_object_id 
                    AND    app.object_type_id = -102 
                    JOIN   dss_link_info s2m 
                    ON     s2m.snapshot_id = i_snapshot_id 
                    AND    s2m.link_type_id = 1 
                    AND    a2s.next_object_id = s2m.previous_object_id 
                    JOIN   dss_link_info m2t 
                    ON     m2t.snapshot_id = i_snapshot_id 
                    AND    m2t.link_type_id = 1 
                    AND    s2m.next_object_id = m2t.previous_object_id 
                    JOIN   dss_objects t 
                    ON     t.object_id = m2t.previous_object_id 
                    WHERE  a2s.snapshot_id = i_snapshot_id 
                    AND    a2s.link_type_id = 2 ) AS wk ; 
    
      -- Insert links from technical modules to technical modules not yet registered 
      insert INTO wk_dss_module_links 
                  ( 
                              module_id, 
                              object_id, 
                              object_type_id, 
                              tree_level 
                  ) 
      SELECT * 
      FROM   ( 
                    SELECT m2t.next_object_id AS module_id, 
                           m2t.next_object_id AS object_id, 
                           p.object_type_id, 
                           0 -- level : 0 
                    FROM   dss_link_info a2m 
                    JOIN   dss_objects app 
                    ON     app.object_id = a2m.previous_object_id 
                    AND    app.object_type_id = -102 
                    JOIN   dss_link_info m2t 
                    ON     m2t.snapshot_id = i_snapshot_id 
                    AND    m2t.link_type_id = 1 
                    AND    a2m.next_object_id = m2t.previous_object_id 
                    JOIN   dss_objects p 
                    ON     p.object_id = m2t.next_object_id 
                    WHERE  a2m.snapshot_id = i_snapshot_id 
                    AND    a2m.link_type_id IN (1,8) -- Include also IFPUG modules 
             ) AS wk ; 
    
      -- Loop on functional modules until root objects (avoid weak links, link_type_id = 0) 
      l_count := 1; 
      l_level := 1; 
      while (l_count > 0) 
      loop 
      -- Create a set of objects 
      TRUNCATE TABLE wk_temp_objects; 
    
      insert INTO wk_temp_objects 
                  ( 
                              module_id, 
                              object_id 
                  ) 
      SELECT wk.module_id, 
             wk.object_id 
      FROM   wk_dss_module_links wk 
      WHERE  wk.tree_level = l_level; 
    
      l_level := l_level + 1; 
      -- Insert links of upper level 
      insert INTO wk_dss_module_links 
                  ( 
                              module_id, 
                              object_id, 
                              object_type_id, 
                              tree_level 
                  ) 
      SELECT DISTINCT * 
      FROM            ( 
                             SELECT tmp.module_id    AS module_id, 
                                    p.object_id      AS object_id, 
                                    p.object_type_id AS object_type_id, 
                                    l_level          AS tree_level 
                             FROM   wk_temp_objects tmp 
                             JOIN   dss_link_info l 
                             ON     l.next_object_id = tmp.object_id 
                             AND    l.snapshot_id = i_snapshot_id 
                             AND    NOT l.link_type_id IN (0,2) -- important! 
                             JOIN   dss_objects p 
                             ON     p.object_id = l.previous_object_id ) AS wk ; 
    
      get diagnostics l_count = row_count; 
    endLOOP;INSERT INTO dss_module_links 
                ( 
                            module_id, 
                            object_id, 
                            object_type_id, 
                            snapshot_id 
                ) 
    SELECT wk.module_id, 
           wk.object_id, 
           wk.object_type_id, 
           i_snapshot_id 
    FROM   wk_dss_module_links wk;DROP TABLE wk_dss_module_links;DROP TABLE wk_temp_objects;RETURN errcode;END;$$ language 'plpgsql'
    CREATE 
    OR 
    replace FUNCTION dss_any_func_module_links( i_snapshot_id int ) 
    returns int -- o : ok 
    AS $$DECLARE errcode INT;L_EXIST         int;BEGIN 
      errcode := 0; 
      l_exist := 0; 
      perform cast_log ('start DSS_UPDATE_FUNC_MODULE_LINKS'); 
      /* Empty data for this snapshot */ 
      delete 
      FROM   dss_func_module_links 
      WHERE  snapshot_id = i_snapshot_id; 
    
      truncate TABLE dss_work_obj_typ; 
    
      truncate TABLE dss_work_func_module_tree; 
    
      -- LINK_TYPE_ID: 1,4,6,7 
      insert INTO dss_work_obj_typ 
                  ( 
                              object_type_id, 
                              link_type_id 
                  ) 
      SELECT ot.object_type_id, 
             lt.link_type_id 
      FROM   dss_climb_link_types lt, 
             dss_object_types ot 
      WHERE  ot.object_group > 1; 
    
      -- First level of fonctionnal modules 
      insert INTO dss_work_func_module_tree 
                  ( 
                              object_id, 
                              object_parent_id, 
                              module_id, 
                              flat_level 
                  ) 
      SELECT o.object_id, 
             -1, 
             o.object_id, 
             0 
      FROM   dss_object_info o 
      JOIN   dss_link_info l 
      ON     l.next_object_id = o.object_id 
      AND    l.snapshot_id = i_snapshot_id 
      AND    l.link_type_id IN (1,8) 
      WHERE  o.object_type_id = 20000 -- functionnal module 
      AND    o.snapshot_id = i_snapshot_id ; 
    
      -- Insert the techno objects into the first level. 
      insert INTO dss_work_func_module_tree 
                  ( 
                              object_id, 
                              object_parent_id, 
                              module_id, 
                              flat_level 
                  ) 
      SELECT t3.object_id, 
             -1, 
             t1.module_id, 
             0 
      FROM   dss_object_types t4, 
             dss_object_info t3, 
             dss_links t2, 
             dss_work_func_module_tree t1 
      WHERE  t2.previous_object_id = t1.module_id 
      AND    t2.link_type_id = 2 -- techno link 
      AND    t2.next_object_id = t3.object_id 
      AND    t3.snapshot_id = i_snapshot_id 
      AND    t3.object_type_id = t4.object_type_id 
      AND    t4.object_group = 2 -- technologic objects 
             ; 
    
      -- get all hierarchical objects of functional modules(portfolio objects, organization objects, techno objects)
      select dss_climb_work_f_object_tree(i_snapshot_id) 
      INTO   errcode; 
    
      insert INTO dss_func_module_links 
                  ( 
                              module_id, 
                              object_id, 
                              object_type_id, 
                              snapshot_id 
                  ) 
      SELECT DISTINCT f.module_id, 
                      o.object_id, 
                      o.object_type_id, 
                      i_snapshot_id 
      FROM            dss_objects o, 
                      dss_work_func_module_tree f 
      WHERE           o.object_id = f.object_id; 
    
      truncate TABLE dss_work_obj_typ; 
    
      truncate TABLE dss_work_func_module_tree; 
    
      perform cast_log ('end DSS_UPDATE_FUNC_MODULE_LINKS'); 
      return errcode; 
    end;$$ language 'plpgsql'
    CREATE 
    OR 
    replace FUNCTION repair_module_links() 
    returns void AS $body$DECLARE errorcodeINT:= 0;L_SNAPSHOT_ID     int := -1;BEGIN 
      -- For all impacted snapshots 
      for l_snapshot_id IN 
      ( 
               SELECT   s.snapshot_id 
               FROM     dss_snapshots s 
               ORDER BY s.functional_date ) 
      loop perform dss_update_any_module_links (l_snapshot_id); 
      perform dss_any_func_module_links(l_snapshot_id); 
    endLOOP;RETURN;END;$body$ language plpgsql

     

    SELECT REPAIR_MODULE_LINKS();
    DROP FUNCTION  REPAIR_MODULE_LINKS();

 

 

Query for Oracle
Enter the SQL query
Query result example
 
Query result interpretation
 
Query for SQL server
Enter the SQL query
Query result example
 
Query result interpretation
 
Notes/comments
 

 

 

Related Pages