SQL Queries - Common SQL Queries - Corruptions - Corruptions on Module - Missing IFPUG Module

Purpose of Query

The following page provide steps to detect and then correct the issue of missing IFPUG Module from the knowledge schema. The query will be executed on the knowledge schema and central schema

Note that there is no Functional definition of the IFPUG module, this is a technical module related to Function point computing.

Applicable CAST Version

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x

Applicable RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS2

Query for CSS

Run the following query to detect the corruption (replace <central schema> with the name of the central schema, <local schema> with the name of the local schema)

SELECT K.idkey              AS IFPUG_MODULE_ID_local, 
       K.keynam             AS IFPUG_MODULE_NAME_local, 
       M.object_id          AS IFPUG_MODULE_ID_central, 
       M.object_name        AS IFPUG_MODULE_NAME_central, 
       TT_M.site_object_id  AS KB_IFPUG_OBJECT, 
       TT_IM.site_object_id AS KB_IFPUG_MODULE, 
       TT_M.site_id, 
       IM.object_id 
FROM   <central schema>.dss_central_selection S 
       JOIN <central schema>.dss_objects M 
         ON S.module_id = M.object_id 
            AND S.snapshot_id >= -1 
            AND S.compute_now = 1 
            AND M.object_type_id = 20000 
       JOIN <central schema>.dss_links L 
         ON M.object_id = L.previous_object_id 
       JOIN <central schema>.dss_objects IM 
         ON L.next_object_id = IM.object_id 
            AND L.link_type_id = 1 
            AND IM.object_type_id = 20051 
       JOIN <central schema>.dss_translation_table TT_M 
         ON M.object_id = TT_M.object_id 
            AND TT_M.site_id != 0 
       JOIN <central schema>.dss_translation_table TT_IM 
         ON IM.object_id = TT_IM.object_id 
       LEFT JOIN <local schema>.keys k 
              ON k.idkey = TT_M.site_object_id

Query result example

16253;"TESTAPP IFPUG objects";52;"TESTAPP IFPUG objects";16253;16254;641479922;53

Query result interpretation

The ID of the IFPUG module of the local Schema provided from the local schema, The name of the IFPUG module of the local Schema provided from the local schema, The ID of the IFPUG module of the central Schema provided from the central schema, The name of the IFPUG module of the central Schema provided from the central schema, The ID of the IFPUG Object of the local Schema provided from the central schema, Site Id

If the First two columns are empty, then the IFPUG Module is missing from the Knowledge schema,

To correct the corruption, proceed as follows

Backup the management, local and central schema

Run the following queries on the local schema:

INSERT INTO keys 
            ( 
                        idkey, 
                        keynam, 
                        keylib, 
                        keytyp, 
                        keysubtyp, 
                        keyclass, 
                        keyprop, 
                        objtyp, 
                        idusrdevpro, 
                        keydevdat, 
                        sqlowner 
            ) 
            VALUES 
            ( 
                        <kb_ifpug_objects>, 
                        <m.object_name>, 
                        '', 
                        'XXXXXX', 
                        -1,0,0,20000, 
                        '???', 
                        now (), 
                        '' 
            );

where <kb_ifpug_objects> is the ID of the IFPUG Object of the local Schema provided from the central schema, <m.object_name> is the name of the IFPUG module of the central Schema provided from the central schema.

INSERT INTO keydat 
            (idkey, 
             crdat, 
             anadat, 
             docdat, 
             flagdate) 
VALUES      (<kb_ifpug_objects>, 
             Now(), 
             To_date ('01/01/1970', 'MM/DD/YYYY'), 
             To_date ('01/01/1970', 'MM/DD/YYYY'), 
             To_date ('01/01/1970', 'MM/DD/YYYY'));

Where <kb_ifpug_objects>is the ID of the IFPUG Object of the local Schema provided from the central schema.

INSERT INTO setroot 
            ( 
                        idset, 
                        idroot 
            ) 
            VALUES 
            ( 
                        <kb_ifpug_objects>, 
                        <kb_ifpug_module> 
            );

where <kb_ifpug_objects> is the ID of the IFPUG Object of the local Schema provided from the central schema, <kb_ifpug_module> is the name of the IFPUG module of the local Schema provided from the central schema.

INSERT INTO appset 
            (idset, 
             idsetnam, 
             idjob) 
VALUES      (kb_ifpug_objects, 
             Concat ('<application_name>', ' IFPUG objects##'), 
             0);

where '<application_name>' is the name of the application, as defined in Cast-MS

Query for Oracle

Run the following query to detect the corruption (replace <central schema> with the name of the central schema, <local schema> with the name of the local schema)

SELECT K.idkey              AS IFPUG_MODULE_ID_local, 
       K.keynam             AS IFPUG_MODULE_NAME_local, 
       M.object_id          AS IFPUG_MODULE_ID_central, 
       M.object_name        AS IFPUG_MODULE_NAME_central, 
       TT_M.site_object_id  AS KB_IFPUG_OBJECT, 
       TT_IM.site_object_id AS KB_IFPUG_MODULE, 
       TT_M.site_id, 
       IM.object_id 
FROM   test_application_central.dss_central_selection S 
       JOIN test_application_central.dss_objects M 
         ON S.module_id = M.object_id 
            AND S.snapshot_id >= -1 
            AND S.compute_now = 1 
            AND M.object_type_id = 20000 
       JOIN test_application_central.dss_links L 
         ON M.object_id = L.previous_object_id 
       JOIN test_application_central.dss_objects IM 
         ON L.next_object_id = IM.object_id 
            AND L.link_type_id = 1 
            AND IM.object_type_id = 20051 
       JOIN test_application_central.dss_translation_table TT_M 
         ON M.object_id = TT_M.object_id 
            AND TT_M.site_id != 0 
       JOIN test_application_central.dss_translation_table TT_IM 
         ON IM.object_id = TT_IM.object_id 
       LEFT JOIN test_application_local.keys k 
              ON k.idkey = TT_M.site_object_id

Query result example

16253;"TESTAPP IFPUG objects";52;"TESTAPP IFPUG objects";16253;16254;641479922;53

Query result interpretation

The ID of the IFPUG module of the local Schema provided from the local schema, The name of the IFPUG module of the local Schema provided from the local schema, The ID of the IFPUG module of the central Schema provided from the central schema, The name of the IFPUG module of the central Schema provided from the central schema, The ID of the IFPUG Object of the local Schema provided from the central schema, Site Id

If the First two column are empty, then the IFPUG Module is missing from the Knowledge schema,

To correct the corruption, proceed as follow:

Backup the management, local and central schema

Run the following queries on the local schema:

INSERT INTO keys 
            ( 
                        idkey, 
                        keynam, 
                        keylib, 
                        keytyp, 
                        keysubtyp, 
                        keyclass, 
                        keyprop, 
                        objtyp, 
                        idusrdevpro, 
                        keydevdat, 
                        sqlowner 
            ) 
            VALUES 
            ( 
                        <kb_ifpug_objects>, 
                        <m.object_name>, 
                        '', 
                        'XXXXXX', 
                        -1,0,0,20000, 
                        '???', 
                        <current date>, 
                        '' 
            );

where <kb_ifpug_objects> is the ID of the IFPUG Object of the local Schema provided from the central schema, <m.object_name> is the name of the IFPUG module of the central Schema provided from the central schema

INSERT INTO keydat 
            (idkey, 
             crdat, 
             anadat, 
             docdat, 
             flagdate) 
VALUES      (kb_ifpug_objects, 
             <Current date >, 
             To_date ('01/01/1970', 'MM/DD/YYYY'), 
             To_date ('01/01/1970', 'MM/DD/YYYY'), 
             To_date ('01/01/1970', 'MM/DD/YYYY'));

Where <kb_ifpug_objects>is the ID of the IFPUG Object of the local Schema provided from the central schema

INSERT INTO setroot 
            ( 
                        idset, 
                        idroot 
            ) 
            VALUES 
            ( 
                        <kb_ifpug_objects>, 
                        <kb_ifpug_module> 
            );

where <kb_ifpug_objects> is the ID of the IFPUG Object of the local Schema provided from the central schema, <kb_ifpug_module> is the name of the IFPUG module of the local Schema provided from the central schema

INSERT INTO appset 
            (idset, 
             idsetnam, 
             idjob) 
VALUES      (kb_ifpug_objects, 
             Concat ('<application_name>', ' IFPUG objects##'), 
             0);

where '<application_name>' is the name of the application, as defined from Cast-MS

Query for SQL Server

Run the following query to detect the corruption (replace <central schema> with the name of the central schema, <local schema> with the name of the local schema)

SELECT K.idkey              AS IFPUG_MODULE_ID_local, 
       K.keynam             AS IFPUG_MODULE_NAME_local, 
       M.object_id          AS IFPUG_MODULE_ID_central, 
       M.object_name        AS IFPUG_MODULE_NAME_central, 
       TT_M.site_object_id  AS KB_IFPUG_OBJECT, 
       TT_IM.site_object_id AS KB_IFPUG_MODULE, 
       TT_M.site_id, 
       IM.object_id 
FROM   test_application_central.dss_central_selection S 
       JOIN test_application_central.dss_objects M 
         ON S.module_id = M.object_id 
            AND S.snapshot_id >= -1 
            AND S.compute_now = 1 
            AND M.object_type_id = 20000 
       JOIN test_application_central.dss_links L 
         ON M.object_id = L.previous_object_id 
       JOIN test_application_central.dss_objects IM 
         ON L.next_object_id = IM.object_id 
            AND L.link_type_id = 1 
            AND IM.object_type_id = 20051 
       JOIN test_application_central.dss_translation_table TT_M 
         ON M.object_id = TT_M.object_id 
            AND TT_M.site_id != 0 
       JOIN test_application_central.dss_translation_table TT_IM 
         ON IM.object_id = TT_IM.object_id 
       LEFT JOIN test_application_local.keys k 
              ON k.idkey = TT_M.site_object_id

Query result example

16253;"TESTAPP IFPUG objects";52;"TESTAPP IFPUG objects";16253;16254;641479922;53

Query result interpretation

The ID of the IFPUG module of the local Schema provided from the local schema, The name of the IFPUG module of the local Schema provided from the local schema, The ID of the IFPUG module of the central Schema provided from the central schema, The name of the IFPUG module of the central Schema provided from the central schema, The ID of the IFPUG Object of the local Schema provided from the central schema, Site Id

If the First two column are empty, then the IFPUG Module is missing from the Knowledge schema,

To correct the corruption, proceed as follow:

Backup the management, local and central schema

Run the following queries on the local schema:

INSERT INTO keys 
            ( 
                        idkey, 
                        keynam, 
                        keylib, 
                        keytyp, 
                        keysubtyp, 
                        keyclass, 
                        keyprop, 
                        objtyp, 
                        idusrdevpro, 
                        keydevdat, 
                        sqlowner 
            ) 
            VALUES 
            ( 
                        <kb_ifpug_objects>, 
                        <m.object_name>, 
                        '', 
                        'XXXXXX', 
                        -1,0,0,20000, 
                        '???', 
                        <current date>, 
                        '' 
            );

where <kb_ifpug_objects> is the ID of the IFPUG Object of the local Schema provided from the central schema, <m.object_name> is the name of the IFPUG module of the central Schema provided from the central schema

INSERT INTO keydat 
            (idkey, 
             crdat, 
             anadat, 
             docdat, 
             flagdate) 
VALUES      (kb_ifpug_objects, 
             <Current date >, 
             To_date ('01/01/1970', 'MM/DD/YYYY'), 
             To_date ('01/01/1970', 'MM/DD/YYYY'), 
             To_date ('01/01/1970', 'MM/DD/YYYY'));

where <kb_ifpug_objects>is the ID of the IFPUG Object of the local Schema provided from the central schema

INSERT INTO setroot 
            ( 
                        idset, 
                        idroot 
            ) 
            VALUES 
            ( 
                        <kb_ifpug_objects>, 
                        <kb_ifpug_module> 
            );

where <kb_ifpug_objects> is the ID of the IFPUG Object of the local Schema provided from the central schema, <kb_ifpug_module> is the name of the IFPUG module of the local Schema provided from the central schema

INSERT INTO appset 
            (idset, 
             idsetnam, 
             idjob) 
VALUES      (kb_ifpug_objects, 
             Concat ('<application_name>', ' IFPUG objects##'), 
             0);

where '<application_name>' is the name of the application, as defined from Cast-MS

Notes/comments

This page is referenced in TG with functional context.

Related Pages