Topic
  • 8 replies
  • Latest Post - ‏2012-05-21T14:51:52Z by E12590
E12590
E12590
23 Posts

Pinned topic SQL to find PL/I procedurename for a specific stmt.

‏2012-04-16T15:43:25Z |
We are running RAA v6.0.0.8 on zOS, and would like to find out from which procedure a specific stmt is in.

In program details we can find the compile unit called and the parameters, but how do we find the Procedure name (this is regarding PL/I) this call stmt is in?
Updated on 2012-05-21T14:51:52Z at 2012-05-21T14:51:52Z by E12590
  • SystemAdmin
    SystemAdmin
    849 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-04-17T01:01:46Z  
    I am not a PL/I expert, but I hope I can help you. First you need to do some reverse engineering of actual meta data. Take a small program and play with its collected meta data.

    If PL/I procedures inside a program are equivalent in handling to what is done by RAA for COBOL, i would expect the following to apply.

    Look for statements type =5027 (PL/I PROCEDURE) in the statement table. Then join with the data use table. One of the rows should have a component type id =34 (Procedure label). The related Id in the row will point to a row in the CU LABEL table, which in turn will have the key for the symbol corresponding to the label name.

    Try to follow the above logic and see if you can list the procedure names. Once you have that, the answer to your problem is the closest immediate preceding the statement.

    Custom Queries shaking the statement and data use tables have to be written carefully because the tables are huge and DB2 gets into scans very easily. It may take you a few reworks to get quick performance.

    You may want to check in the CQ +Sample Collection. I think it has a CQ for paragraphs in a program that probably has somehow similar logic.
  • jcdelmo
    jcdelmo
    366 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-04-17T14:28:08Z  
    RAA does not store all PLI block and statement information in its DMH_CU_STMT table. It also does not store a link between a statement and its containing procedure.

    Without being sure exactly what you are after or why, I would like to share the following query (which I often use to look at RAA's statement view of a program) which may help you. It assumes you have the COMP_UNIT_ID of the program of interest:

    
    select substr(file.file_name, 1, 10) , stmt.file_line_no , st.stmt_type_id , st.description from dmh.dmh_cu_stmt stmt join dmh.dmh_file file on stmt.member_id = file.file_id join dmh.dmh_stmt_type st on stmt.stmt_type_id = st.stmt_type_id where stmt.comp_unit_id = ? order by stmt.source_line_no
    
  • E12590
    E12590
    23 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-04-17T15:47:05Z  
    • jcdelmo
    • ‏2012-04-17T14:28:08Z
    RAA does not store all PLI block and statement information in its DMH_CU_STMT table. It also does not store a link between a statement and its containing procedure.

    Without being sure exactly what you are after or why, I would like to share the following query (which I often use to look at RAA's statement view of a program) which may help you. It assumes you have the COMP_UNIT_ID of the program of interest:

    <pre class="jive-pre"> select substr(file.file_name, 1, 10) , stmt.file_line_no , st.stmt_type_id , st.description from dmh.dmh_cu_stmt stmt join dmh.dmh_file file on stmt.member_id = file.file_id join dmh.dmh_stmt_type st on stmt.stmt_type_id = st.stmt_type_id where stmt.comp_unit_id = ? order by stmt.source_line_no </pre>
    What I'm after is when from a PL/I program we call some specific modules/programs, to find out from which procedure they are called and what the contents is for some of the parameters.
    We have the select stmt for COBOL programs.

    An example:
    main: PROC options (main);
    dcl specpgm ext entry;
    dcl parm01 char (08) init('aaaaa '),
    parm02 char (02) init('01');
    ...
    call subproc(parm01, parm02);

    subproc: proc(p1,p2);
    dcl p1 char (08),
    p2 char (02);
    call specpgm( p1,p2);
    return;
    end subproc;
    return;
    end main;

    We want to find out what the parameters used in the call to 'specpgm' contains and the procedure name from where the call is, in this example it is the parameters that subproc is called with, it could also have been a constant (call subproc('bbbbb ','05');).

    This is so that we can build a user defined relationship, from the compile unit to some specific routines (some general programs we have coded) also for PL/I programs (we have it running for COBOL programs).

    Thank you for the provided stmt, so I don't know if it is possible in RAA v6.0.0.8 to get the above information for PL/I programs?
  • SystemAdmin
    SystemAdmin
    849 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-05-10T05:14:07Z  
    • E12590
    • ‏2012-04-17T15:47:05Z
    What I'm after is when from a PL/I program we call some specific modules/programs, to find out from which procedure they are called and what the contents is for some of the parameters.
    We have the select stmt for COBOL programs.

    An example:
    main: PROC options (main);
    dcl specpgm ext entry;
    dcl parm01 char (08) init('aaaaa '),
    parm02 char (02) init('01');
    ...
    call subproc(parm01, parm02);

    subproc: proc(p1,p2);
    dcl p1 char (08),
    p2 char (02);
    call specpgm( p1,p2);
    return;
    end subproc;
    return;
    end main;

    We want to find out what the parameters used in the call to 'specpgm' contains and the procedure name from where the call is, in this example it is the parameters that subproc is called with, it could also have been a constant (call subproc('bbbbb ','05');).

    This is so that we can build a user defined relationship, from the compile unit to some specific routines (some general programs we have coded) also for PL/I programs (we have it running for COBOL programs).

    Thank you for the provided stmt, so I don't know if it is possible in RAA v6.0.0.8 to get the above information for PL/I programs?
    First I wanted to see details about what data RAA collects in your specific example. So I scanned your code minimally changed to call RAA sample external pgm qad03:

    
    main: PROC options (main); dcl qad03 ext entry; dcl parm01 
    
    char (08) init(
    'aaaaa '), parm02 
    
    char (02) init(
    '01'); call subproc(parm01, parm02); subproc1: proc(p1,p2); dcl p1 
    
    char (08), p2 
    
    char (02); call qad03( p1,p2); 
    
    return; end subproc1; 
    
    return; end main;
    


    Attached is the file illustrating what I can see in RAA UI. All the data you see on those screen captures should be available also from RAA REST UI, in addition REST UI gives you the list of statements (last capture).

    What I am not sure about is whether the UI should/could show modifies / modified by relationship between P1 and PARM01.

    Attachments

  • E12590
    E12590
    23 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-05-15T12:48:13Z  
    First I wanted to see details about what data RAA collects in your specific example. So I scanned your code minimally changed to call RAA sample external pgm qad03:

    <pre class="jive-pre"> main: PROC options (main); dcl qad03 ext entry; dcl parm01 char (08) init( 'aaaaa '), parm02 char (02) init( '01'); call subproc(parm01, parm02); subproc1: proc(p1,p2); dcl p1 char (08), p2 char (02); call qad03( p1,p2); return; end subproc1; return; end main; </pre>

    Attached is the file illustrating what I can see in RAA UI. All the data you see on those screen captures should be available also from RAA REST UI, in addition REST UI gives you the list of statements (last capture).

    What I am not sure about is whether the UI should/could show modifies / modified by relationship between P1 and PARM01.
    Hello Leshek

    I am not quite certain about what it is that you are not sure about.
    We would want to be able to see the parameter/variable relationship/transformation (when calling), so that we from the DB2 tables in RAA could see that
    Call subproc(parm01, parm02) and
    subproc: proc(p1, p2)
    that parm01 and p1 is the same.

    I know that in PLI that the parameter in SUBPROC can be different when used in SUBPROC depending on if it is BY VALUE or BY reference.

    Regards Kaj K. Pedersen
  • SystemAdmin
    SystemAdmin
    849 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-05-16T23:01:28Z  
    • E12590
    • ‏2012-05-15T12:48:13Z
    Hello Leshek

    I am not quite certain about what it is that you are not sure about.
    We would want to be able to see the parameter/variable relationship/transformation (when calling), so that we from the DB2 tables in RAA could see that
    Call subproc(parm01, parm02) and
    subproc: proc(p1, p2)
    that parm01 and p1 is the same.

    I know that in PLI that the parameter in SUBPROC can be different when used in SUBPROC depending on if it is BY VALUE or BY reference.

    Regards Kaj K. Pedersen
    I know we trace formal and actual parameters data flow in Impact Analysis, what I am not sure about is whether it would be 1) easy (to implement) / 2) feasible / 3) intuitively understandable when we show actual/formal parameter modifies/modified in the same place we now show regular once removed data flow relationship.
  • jcdelmo
    jcdelmo
    366 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-05-17T17:19:13Z  
    • E12590
    • ‏2012-05-15T12:48:13Z
    Hello Leshek

    I am not quite certain about what it is that you are not sure about.
    We would want to be able to see the parameter/variable relationship/transformation (when calling), so that we from the DB2 tables in RAA could see that
    Call subproc(parm01, parm02) and
    subproc: proc(p1, p2)
    that parm01 and p1 is the same.

    I know that in PLI that the parameter in SUBPROC can be different when used in SUBPROC depending on if it is BY VALUE or BY reference.

    Regards Kaj K. Pedersen
    My understanding was this thread started with finding the PROCEDURE label for a routine that contains a CALL. It now looks like one wants the linkage between the actual parameters of (one or more) calls and the formal parameters on the entry point referenced by the call(s). Is that correct?
  • E12590
    E12590
    23 Posts

    Re: SQL to find PL/I procedurename for a specific stmt.

    ‏2012-05-21T14:51:52Z  
    • jcdelmo
    • ‏2012-05-17T17:19:13Z
    My understanding was this thread started with finding the PROCEDURE label for a routine that contains a CALL. It now looks like one wants the linkage between the actual parameters of (one or more) calls and the formal parameters on the entry point referenced by the call(s). Is that correct?
    Your understanding regarding the start of this thread is correct.

    In our answer to Lesheks earlier message with code example, were we not certain of what
    it was that he was unsure about. And we answered from what we thought that he was unsure about.

    Now in a later message we can see that it was the Impact Analysis that he was unsure about some things.