DSPPGMREF and a file's fields in update or add mode

[John192]

Hi,


I have a list of source programs where a specific file is being used in another mode (2,3,...11 etc.) except input mode ('1') then based on such a long list( received using DSPPGMREF pgm(*all) output (*outfile) objtype(*all) outfile(Lib1/F1) of programs, how can I quickly identify where exactly that file's particular set of fields are getting updated from rather than going inside in each and every program to find that out?


Also how to use the same in Hawkeye ?


Thanks.

[MFisher]

Do you want to see every reference to any of the fields for Usage # 2, 3, 4, 5, 6, 7 that might update a field ?
How many fields are you looking at ?

WHFUSG - Referenced File Usage:

1 - Input
2 - Output
3 - Input/Output
4 - Update
5 - Input/Update
6 - Output/Update
7 - Input/Output/Update
8 - Not specified
0 - Not available

Does Hawkeye have Field X-Ref option ? If so, it should have "Help" to explain how to use it.

[John192]

I have around 6-7 such fields in a specific file for this file I need to know that where all those fields are either getting updated or from where these fields are receiving inputs.


Thanks.

[MFisher]

I wrote a query that I use to search RPG & CL for character strings. You could run this for each field name. It displays each line of code where the character string is found.
I'm sure it's not fool proof, but might help.

You will need to first put in your source library, and then change the search string. (I tried to highlight, but doesn't work in Code block)
You could add code to only include program names in your DSPPGMREF outfile.

----==================================================
----===================   RPG   SEARCH  ====================
----==================================================

--- Create TEMP File for Source member names to search , &  File of Results

-----  RPG Source Member Names that will be searched
CREATE OR REPLACE TABLE QTEMP.RPG_SOURCE (
    ROW# BIGINT NOT NULL ,
    TABLE_SCHEMA FOR COLUMN TABSCHEMA  VARCHAR(128) ALLOCATE(10) CCSID 37 NOT NULL ,
    TABLE_NAME FOR COLUMN TABNAME    VARCHAR(128) ALLOCATE(18) CCSID 37 NOT NULL ,
    TABLE_PARTITION FOR COLUMN TABPART    VARCHAR(128) ALLOCATE(100) CCSID 37 NOT NULL ,
    COMPLETED VARCHAR(1) ALLOCATE(1) CCSID 37 NOT NULL,
    PARTITION_TEXT FOR COLUMN TABTEXT  VARCHAR(100)  ALLOCATE(100) CCSID 37 )          ON REPLACE DELETE ROWS    RCDFMT RPG_SOURCE ;

CREATE OR REPLACE  TABLE QTEMP.SRC_SEARCH (
    SRCSEQ NUMERIC(6, 2) NOT NULL ,
    SRCDTA CHAR(100) CCSID 37 NOT NULL ,
    SRCHSTRING VARCHAR(30) CCSID 37 NOT NULL ,
    SRC_MEMBER VARCHAR(12) CCSID 37 NOT NULL ,
    SRC_TEXT VARCHAR(100) CCSID 37,
    SRCDAT NUMERIC(6,0)   ,
    SRCYYYYMMDD numeric(8, 0) ,
    SRCLIB VARCHAR(20)  )            ON REPLACE DELETE ROWS     RCDFMT SRC_SEARCH ;

INSERT INTO QTEMP.RPG_SOURCE       -----  Load RPG Source Member Names into File  RPG_SOURCE
select *
from (select     ROW_NUMBER() OVER () row#,  TABLE_SCHEMA , TABLE_NAME , TABLE_PARTITION , ' ' as  Completed , PARTITION_TEXT
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_NAME LIKE 'QRPG%' AND TABLE_SCHEMA in ( 'MYSOURCELIBRARY' )--- <<----  SEARCH  SOURCE LIBRARY    & FILE  QDDS, QCL, QRPG.....        
)
ORDER by TABLE_PARTITION  ;

---==================

begin

   declare  v_Lib    char(50);
   declare  v_Table    char(50);
   declare v_member char(50);
   declare  SQLState  char(5);
   declare v_Cmd    varchar(256);
   declare Srch_String    varchar(25);
declare Srch_Wildcard    varchar(27);
declare v_Text varchar(100);

   declare SourceCode cursor for
      select Table_Schema , Table_Name , Table_Partition , PARTITION_TEXT    ---   Library ,  Src File Name , Member Name
        FROM QTEMP.RPG_SOURCE  ;  

   open SourceCode ;

SET Srch_String =  'MYSTRING' ;            ---    <<<<=========   SEARCH STRING          Include quotes in search if looking for '97':  SET Srch_String =  '''97'''
SET Srch_Wildcard = '%' concat UPPER(Srch_String) concat '%' ;    --- Puts % before and after for LIKE statement.

FetchLoop:
   loop
      fetch SourceCode into v_Lib , v_Table,  v_member  , v_Text ;
      if SQLState >= '02000' then
         leave FetchLoop;
      end if;

set v_Cmd = 'CREATE ALIAS QTEMP.MYMBRB FOR ' concat  v_Lib concat '.' concat v_Table concat ' (' concat  v_member concat ')';

execute immediate v_Cmd;

  INSERT INTO QTEMP.SRC_SEARCH  SELECT SRCSEQ , SRCDTA , Srch_String , v_member , v_Text, SRCDAT,
CASE WHEN SRCDAT < 700000 THEN (20000000 + SRCDAT)  ELSE (19000000 + SRCDAT) END,
 v_lib
FROM QTEMP.MYMBRB where UPPER(SRCDTA) like   Srch_Wildcard ;          --- '%' concat Srch_String concat '%'        ------'%SUBSTR%'  ;
DROP ALIAS QTEMP.MYMBRB ;
   end loop;

   close SourceCode;

end;

;
SELECT  SRCSEQ , SRCDTA ,
SUBSTR(SRCYYYYMMDD,1,4) || '-' || SUBSTR(SRCYYYYMMDD,5,2) || '-' || SUBSTR(SRCYYYYMMDD,7,2) as SRC_DATE ,
SRCHSTRING , SRC_MEMBER ,SRCLIB , SRC_TEXT --*
FROM QTEMP.SRC_SEARCH    ---- Results.  . Can be run manually with modified WHERE to fine-tune results.
WHERE SUBSTR(SRCDTA,7,1) <> '*'    --- Omit Comment lines with * in pos 7
ORDER BY SRCLIB , SRC_MEMBER , SRCSEQ​

[MFisher]

I wrote a query that I use to search RPG & CL for character strings. You could run this for each field name. It displays each line of code where the character string is found.
I'm sure it's not fool proof, but might help.

You will need to first put in your source library, and then change the search string.
You could add code to the last SELECT to only include program names in your DSPPGMREF outfile.

----==================================================
----=================== RPG SEARCH ====================
----==================================================

--- Create TEMP File for Source member names to search , & File of Results

----- RPG Source Member Names that will be searched
CREATE OR REPLACE TABLE QTEMP.RPG_SOURCE (
ROW# BIGINT NOT NULL ,
TABLE_SCHEMA FOR COLUMN TABSCHEMA VARCHAR(128) ALLOCATE(10) CCSID 37 NOT NULL ,
TABLE_NAME FOR COLUMN TABNAME VARCHAR(128) ALLOCATE(18) CCSID 37 NOT NULL ,
TABLE_PARTITION FOR COLUMN TABPART VARCHAR(128) ALLOCATE(100) CCSID 37 NOT NULL ,
COMPLETED VARCHAR(1) ALLOCATE(1) CCSID 37 NOT NULL,
PARTITION_TEXT FOR COLUMN TABTEXT VARCHAR(100) ALLOCATE(100) CCSID 37 ) ON REPLACE DELETE ROWS RCDFMT RPG_SOURCE ;

CREATE OR REPLACE TABLE QTEMP.SRC_SEARCH (
SRCSEQ NUMERIC(6, 2) NOT NULL ,
SRCDTA CHAR(100) CCSID 37 NOT NULL ,
SRCHSTRING VARCHAR(30) CCSID 37 NOT NULL ,
SRC_MEMBER VARCHAR(12) CCSID 37 NOT NULL ,
SRC_TEXT VARCHAR(100) CCSID 37,
SRCDAT NUMERIC(6,0) ,
SRCYYYYMMDD numeric(8, 0) ,
SRCLIB VARCHAR(20) ) ON REPLACE DELETE ROWS RCDFMT SRC_SEARCH ;

INSERT INTO QTEMP.RPG_SOURCE ----- Load RPG Source Member Names into File RPG_SOURCE
select *
from (select ROW_NUMBER() OVER () row#, TABLE_SCHEMA , TABLE_NAME , TABLE_PARTITION , ' ' as Completed , PARTITION_TEXT
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_NAME LIKE 'QRPG%' AND TABLE_SCHEMA in ( 'MYSOURCELIBRARY' )--- <<---- SEARCH SOURCE LIBRARY & FILE QDDS, QCL, QRPG.....
)
ORDER by TABLE_PARTITION ;

---==================

begin

declare v_Lib char(50);
declare v_Table char(50);
declare v_member char(50);
declare SQLState char(5);
declare v_Cmd varchar(256);
declare Srch_String varchar(25);
declare Srch_Wildcard varchar(27);
declare v_Text varchar(100);

declare SourceCode cursor for
select Table_Schema , Table_Name , Table_Partition , PARTITION_TEXT --- Library , Src File Name , Member Name
FROM QTEMP.RPG_SOURCE ;

open SourceCode ;

SET Srch_String = 'MYSTRING' ; --- <<<<========= SEARCH STRING Include quotes in search if looking for '97': SET Srch_String = '''97'''
SET Srch_Wildcard = '%' concat UPPER(Srch_String) concat '%' ; --- Puts % before and after for LIKE statement.

FetchLoop:
loop
fetch SourceCode into v_Lib , v_Table, v_member , v_Text ;
if SQLState >= '02000' then
leave FetchLoop;
end if;

set v_Cmd = 'CREATE ALIAS QTEMP.MYMBRB FOR ' concat v_Lib concat '.' concat v_Table concat ' (' concat v_member concat ')';

execute immediate v_Cmd;

INSERT INTO QTEMP.SRC_SEARCH SELECT SRCSEQ , SRCDTA , Srch_String , v_member , v_Text, SRCDAT,
CASE WHEN SRCDAT < 700000 THEN (20000000 + SRCDAT) ELSE (19000000 + SRCDAT) END,
v_lib
FROM QTEMP.MYMBRB where UPPER(SRCDTA) like Srch_Wildcard ; --- '%' concat Srch_String concat '%' ------'%SUBSTR%' ;
DROP ALIAS QTEMP.MYMBRB ;
end loop;

close SourceCode;

end;

;
SELECT SRCSEQ , SRCDTA ,
SUBSTR(SRCYYYYMMDD,1,4) || '-' || SUBSTR(SRCYYYYMMDD,5,2) || '-' || SUBSTR(SRCYYYYMMDD,7,2) as SRC_DATE ,
SRCHSTRING , SRC_MEMBER ,SRCLIB , SRC_TEXT --*
FROM QTEMP.SRC_SEARCH ---- Results. . Can be run manually with modified WHERE to fine-tune results.
WHERE SUBSTR(SRCDTA,7,1) <> '*' --- Omit Comment lines with * in pos 7
ORDER BY SRCLIB , SRC_MEMBER , SRCSEQ

[John192]

if someone does not have ACS to run this long sql script then will it be executable on SEU ?

also if i have such 6 fields then should i give it in SET Srch_String = 'field1','field2','field3','field4','field5','field 6' ;

like this ?


Thanks

[MFisher]

I would run the script 6 times for each field by itself, and save the results.

ACS is very useful. Can you get it ?

[John192]

no i have ACS but with limited functionality running sql scripts feature is not present in it.

as i just have SEU so do i need to run all these sql queries one by one? or could you please number them here to be run in SEU one by one?

and these 5-6 queries for each field i need to execute so total 6 *6 =36 times i need to run these sql queries to get desired result here?

[MFisher]

I don't know if it would work in SEU. I don't use SEU Query
In ACS, I highlight the entire code and click "run" CTL-R . Then change the variable, and run again. Total of 6 times. 1 for each search string.
Why can you not run ACS queries if you have ACS ? Get a newer version ?

[John192]

Not allowed to install any other version so stuck with the SEU and not sure how can I execute these long sql queries in SEU now.

[MFisher]

Maybe you could use the SQL in an RPG program and pass in parameter of the search string, and put the results to a report.

Make a case to management that ACS is a useful tool and the full version should be available.

You mentioned Hawkeye. Did you find a "Field Where Used" option ?

[MFisher]

Features of the Pathfinder iSeries Development Tools (AS/400 Development Tools) (hawkinfo.com)

• Object and field cross reference documentation built using your source

[John192]

Yes i found that but it generated a spool file where i need to look it's usage for a specific file that too either in update or add mode.

so now searching one by one all these program in a specific library that too i need to search if a specific value let' say '1' whether it's being assigned it to or not.

so manually searching this seems a tough task.

[MFisher]

Can Hawkeye output results to a physical file,? Then you could query the result file with WHERE and ORDER BY to tune your results.

[MFisher]

PATHFiNDER analyzes files, programs, and fields, as well as other system or user objects; PATHFINDER analyzes how they are used, where they are used, and what they do. This information may then be reviewed on a workstation, in printed format, or placed in an outfile.