Topic
  • 1 reply
  • Latest Post - ‏2013-09-26T19:59:35Z by jcdelmo
BenoitEbner
BenoitEbner
17 Posts

Pinned topic Continuation conversation John D and Paul P

‏2013-09-24T14:45:49Z |

Continuation conversation about rectification type in dmh_file with Paul

Response from John D:

The following two SQL statements should reset the FILE_TYPE_CD attribute for those files that are included in another, but processed as main programs in your client's RAA data base.

The 1st one deletes the PROGRAM assets that were made from the FILEs incorrectly classified as MAIN files.

The 2nd one resets the FILE_TYPE_CD  ( from MAIN )  to INCL for those same files.

This should set the data base tables as though the classification scan properly processed the include files in the first place.

However, as with any SQL that modifies RAA's meta-data externally there may be side-effects.

The work item will remain open until we figure out how to address this issue in the RAA scanners themselves...


SQL #1
delete
  from dmh.dmh_compile_unit
 where comp_unit_id in (select ci.comp_unit_id
                          from dmh.dmh_cu_include ci
                         where ci.incl_type_id = 0
                           and ci.member_id in (select member_id
                                                  from dmh.dmh_cu_include
                                                 where incl_type_id != 0))


SQL #2
update dmh.dmh_file file2
   set file_type_cd = 'INCL'
 where file2.file_id in (select file.file_id
                           from dmh.dmh_cu_include ci
                           join dmh.dmh_file file
                             on ci.member_id = file.file_id
                          where ci.incl_type_id = 0
                            and ci.member_id in (select member_id
                                                   from dmh.dmh_cu_include
                                                  where incl_type_id != 0))

 

Question :

the result of sql delete is delete cascade, delete in dmh_compile_unit and in dmh_cu_inclue ?

I do not understand the order of update which should update the type at INCL in dmh.file

the nested select  (select file.file_id
                           from dmh.dmh_cu_include ci

                           join dmh.dmh_file file
                             on ci.member_id = file.file_id
                          where ci.incl_type_id = 0
                            and ci.member_id   

is not correct

why test ci.incl_type_id = 0 ?

thanks for your answer
 

  • jcdelmo
    jcdelmo
    347 Posts

    Re: Continuation conversation John D and Paul P

    ‏2013-09-26T19:59:35Z  

    The DMH_CU_INCLUDE table always has a row for the main file of the program.  The main file is indicated with a INCL_TYPE_ID = 0.

    Since all the include files that we are interested in changing where classified (incorrectly) as MAIN, we first want to get rid of the compile unit rows they would have generated --and-- than we want to set their INCL_TYPE_CD to 'INCL' (instead of 'MAIN').

    Otherwise, we would have orphaned DMH_COMPILE_UNIT rows.