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 | |
8.1.x | |
8.0.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS2 |
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