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 | ✅ |
| 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);
```sql
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