Topic
1 reply Latest Post - ‏2013-09-26T19:51:55Z by jcdelmo
DougG
DougG
9 Posts
ACCEPTED ANSWER

Pinned topic Need CQ to find field refs by copybook

‏2013-09-20T14:57:25Z |

I have been trying to find a way to allow our analysts to look for element references in RAA starting with a field/data element as defined in a copybook.  We make heavy use of the REPLACING option, so we have many Symbols and/or Data Elements related to a single copybook field.  Symbol search and Element search return data elements by program, not so easy to filter to copybook from those results.  Looking for an easy way to get field-level impact to code members.  For example, given field 'XXX'-ACCT-NO in copybook MASTFILE, how and where is this field referenced.  Ideally, would like the query to return:  count of the # programs where the element is referenced directly but not modified (ignore reference to parent elements); count of # programs where the element is referenced directly in a statement that modifies it; count of # of programs that the element is available in but never referenced/modified.  

From that point, it would be useful to be able to drill down into the statement details for References and Modifications, similar to the data you see on the Data Element Details page.

This has been a difficult task due to the RAA data model.  In addition, our database contains 42.5 million data elements and 4.3 million symbols.  

Wondered if anyone else has attempted to tackle looking at data elements this way?

  • jcdelmo
    jcdelmo
    343 Posts
    ACCEPTED ANSWER

    Re: Need CQ to find field refs by copybook

    ‏2013-09-26T19:51:55Z  in response to DougG

    The following query will give you a Custom Query start.   It can be enhance it, if you believe it is along the track you are pursuing. It uses the copybook name (as in DMHSRC06 from the IVT programs) and gives program, data element and ref/mod usage.

    Let me know if this is what you were going after...

    select cu.comp_unit_name as program
         , sym.symbol_text as data_Element
         , CASE dea.type_id
             WHEN 0 THEN 'Declared only'
             WHEN 1 THEN 'Explicit refs only'
             WHEN 2 THEN 'Explicit mods only'
             WHEN 3 THEN 'Explicit refs and mods'
             WHEN 10 THEN 'Implicit refs only'
             WHEN 20 THEN 'Implicit mods only'
             WHEN 30 THEN 'Implicit refs and mods'
             ELSE 'unknown'
           END as usage
      from $(SCHEMA).dmh_data_elmt_alas dea
      join $(SCHEMA).dmh_file file
        on dea.member_id = file.file_id
      join $(SCHEMA).dmh_symbol sym
        on dea.symbol_id = sym.symbol_id
      join $(SCHEMA).dmh_compile_unit cu
        on dea.comp_unit_id = cu.comp_unit_id
     where file.file_name like 'DMHSRC06'
     order by cu.comp_unit_name
            , dea.sequence

     

    Updated on 2013-09-26T19:52:14Z at 2013-09-26T19:52:14Z by jcdelmo