A few notes about file usage in popular utilities LMI, XA and Hawkeye

To review and revised when we have more time



Some LMI files

 
PRTINS     - checked sources by project with username
PRTXASM    - checkout info with task, current and past
APPDEF     - Application Master File
RELDEF     - Release Master File
OBJENV     - All objects 
PRTDEP     - sources by date checked out with all files used and calls made
USR        - List of registered users by IBM i User ID
ENHDEF     - Tasks Master File
DPLSETOBJ  - ?
ATVLOG     - activity log file
MERGESRC   - merged source?
DPLLOG     - Compile log 
DPLCNVOPT  - Log
DPLYSPLFS  - Deployment log file - Header

ARCIINS    - checkout information?
ARCPINS    - checkout information?
PRTDEF     - checkout information?
ITMDEF     - checkout information?
PGMMODSRV  - list of objects?
DPLSET     - list of objects
PINSXASM   - list of objects
ARCPDEF    - list of objects
OBJINFO    - list of objects 


List checked out objects by Project/Task/Object

SELECT PRTINS.RELNMK AS RELEASE,  ENHDEF.ENH ,                    
substr(PRTINS.PRTNMK,1,15) AS OBJECT,                             
trim(PRTINS.PRTFMLYNM) CONCAT '/' CONCAT 
trim(PRTATTRNM) CONCAT '/' CONCAT  
trim(PRTEXTANM) AS TYPE_ATTRIB, 
PRTINS.DATAOBJ AS DATA, 
PRTINS.ENVTYPAB As ENV, 
substr(PRTINS.DVPNAM ,1,12) AS USERID,                                                                          
PRTINS.PRTDSC, PRTINS.EFFSTRTS                                    
FROM PRTINS                                                       
LEFT OUTER JOIN PRTXASM ON (PRTXASM.PRTID = PRTINS.PRTID )        
LEFT OUTER JOIN USR ON (PRTINS.CKOUSRID = USR.USRID)              
LEFT OUTER JOIN ENHDEF  ON (PRTXASM.TSKKEY  = ENHDEF.ENH###)      
WHERE PRTINS.RELNMK = 'MYPROJ'                                    
ORDER BY PRTINS.RELNMK,  ENHDEF.ENH , PRTINS.PRTNMK



Query on Log file

SELECT substr(USRPRFS ,1,12) USERNAME , substr( PRTNMK ,1,12)     
SOURCE , PRTFMLYNM ,PRTTYPNM ,FRMENV , ACTCDE , ATVLOG.* FROM     
ATVLOG WHERE USRPRFS != 'USERNAME'
ORDER BY USRPRFS                                                  


Some XA files

XOBJECT   - List of all objects, one record for each object in application libraries
XPFSRC    - List of source file members 
XMEMBER   – List of Members for each source file member in source file
XAPGMREF  - Program references
X@XPGRF   - objects referenced by programs, commands, and menus 
X@XDBR    - File dependencies between tables in the database (physical/logicals)
XDPGREF   - All references
XLFFD     - List of fields in all files
XAFFWU    - File Field Where used
XPFSRC    – List of Source Files
XLSRCMBR  – Source Member Data

Find all Programs used by file SOMEFILE

SELECT WHFNAM, WHFUSG , WHOBJT, WHOTYP, WHLIB, WHPNAM, WHTEXT,        
WHFNAM FROM XAPGMREF WHERE WHFNAM <> 'SOMEFILE' and WHOBJT ='F' and   
WHFNAM = 'SOMEFILE' ORDER BY WHFUSG                                     

List most used files

SELECT MostUsed.NAME, MostUsed.COUNT FROM 
	( SELECT WHSNAM as NAME, count(*) AS COUNT 
	  FROM XAPGMREF 
	  WHERE WHOBJT = 'F' 
	  GROUP BY WHSNAM 
	  ORDER BY WHSNAM                                                 
	) 
AS MostUsed ORDER BY COUNT DESC

List most used Programs

SELECT MostUsed.NAME, MostUsed.COUNT FROM 
     ( SELECT WHFNAM AS NAME, 
	   COUNT(*) AS COUNT 
       FROM XAPGMREF 
       GROUP BY WHFNAM 
       ORDER BY WHFNAM 
     ) 
AS MostUsed ORDER BY COUNT DESC

List all files and fields

SELECT L#LIB, L#OBJ, L#TYPE, 
       L#ATTR, L#TEXT, 
       WHFLDI, WHFTXT,
       WHFIOB 
FROM XOBJECT LEFT JOIN XLFFD       
ON (L#LIB = WHLIB AND
    L#OBJ = WHFILE)
WHERE L#TYPE = '*FILE' and L#ATTR LIKE 'PF%'

Find all files/objects used by a program or programs

SELECT WHPNAM, WHTEXT, WHFNAM, WHSNAM, WHFUSG, WHOBJT 
FROM XAPGMREF    
WHERE WHPNAM = 'AR0001'                                                
ORDER BY WHPNAM


Iterate through call stack recursively (hierarchically) to find programs and files used by program MYPGM. Use NOCYCLE to prevent eliminates cyclic redundances. Assume X-Analysis repository found in library PROD13109.

SELECT CONNECT_BY_ROOT WHONAM AS TOP_LEVEL, WHONAM AS PARENT , WHRNAM AS CHILD, 
   WHRTYP AS Obj_Type, WHRATR OBJ_ATTTR, WHUSG AS USAGE, 
LEVEL WHONAM 
FROM PROD13109/XDPGREF START WITH WHONAM = 'MYPGM'
CONNECT BY NOCYCLE PRIOR WHRNAM = WHONAM                               



Iterate through call stack recursively to find all objects used by program MYPGM and show files that perform Add, Update or Both.

SELECT * FROM 
( 
 SELECT CONNECT_BY_ROOT WHONAM AS TOP_LEVEL, WHONAM AS REF_A, 
 WHRNAM AS REF_B, 
 WHRTYP AS OBJ_TYPE, WHRATR OBJ_ATTTR, WHUSG AS USAGE, 
 LEVEL WHONAM 
 FROM PROD13109/XDPGREF START WITH WHONAM =            
 'MYPGM' 
 CONNECT BY NOCYCLE PRIOR WHRNAM = WHONAM                               
 ) 
 WHERE OBJ_TYPE ='*FILE' AND
       OBJ_ATTTR IN ('PF', 'LF', 'PF38', 'LF38')  AND
	   USAGE IN ('U','A','B')


Some more files

XAPGMREF    Contains all DSPPGMREF references plus service program references, trigger program references, etc...
XOBJECT     List of Objects loaded into X-Analysis
XPGMLOGIC	Program Logic 
XAGWU		X-Analysis: Where Used Data	
XDBFLDDTL	External and internal file and field Details 
XPGMDS		Details of all DS in the program
XVARFLDKWD	Program Variable Keyword Identifiers
XCALLPMS	Called Parameters 
XPGMVARS	Program Variables 
XFRELOGIC	Xrev: Extracted /Free source code 
XABIGSTMT	File Field where used Source Statement - SQL
XFNFLDS		Function Fields	
XSRCVARDTL	Variable Details
XPGFLDKWD	Program Variable Keyword Identifiers
XDMMBRDTL	Member names in PF and LF	15781888
XFDBRLOGIC	File Field BR Logic	10567680
XAXPROTO	X-Analysis: Prototypes by Member 
XDBFEXT		DB File Extended information 
XAFFWU		File Field where used Field information	 
XDBHSTFLD	File Field Details In Buffer Order 
XEXBRLKEYS	X-Extract: B/Rule Generic Logic, Keys
XFUNDEF		Function Definitions
XMETLOG		Metrics: To Contain Report Log 
XDBHSTFDBK	File Field Details In Buffer Order 
XCALLS		External Program Calls 
XDSPF		Screen fields 
XDVKWDS		Dspf keywords 
XLFFD		X-Analysis: Basic Field Data 
XEXPGMLSTS	X-Extract: Program Lists 
XEXBIZRLES	X-Extract: Business Rules 
XMETSUBH	Metrics Subroutine Data 
XFNDEFH		Function Headers
XPNAMES		Proper Names File
XPGMPRMDTL	Program And The Corresponding Parameter Details
XPRCPARM	Program Procedure's parameter dtls
XDVFLDS		Dspf fields
XLODAUD		X-Analysis: Load Audit
XRFPREFX	X-Analysis: Program File Names
XFNCONS		Function Constants	
XLODAUDBK	X-Analysis: Load Audit
XPGMENTPRM	Program Entry Parameters
XMETSUB		Metrics Subroutine Data
XDPGREF		Object References File 
XDD			Field Data Dictionary by field
XFDBRMATCH	File/Field BR Logic matches 
XFDBIZRLES	File Field Business Rules
XAXSCD		X-Analysis: Program SCD Data
XWKEYMAP	X-Rev: Key Mapping Data 
XFNENPMS	Fun.Entry Parameters 
XFDBROCUR	File/Field Business Rules Occurrences 
XCBLDBXRF	X-Rev: Cobol Database X-Ref DBCS 
XS2EDBXRF	X-Rev: Synon Database X-Ref 
XAXPMX		Program Module References
XFNDSCNS	Function Display Conditions 
X@PMX		ILE program module cross references 
XEXECTIME	X-Analysis: Execution Time File 
XOBJECT		Object list data 
XPGMPROC	Program Procedures & their parameter dtls 
XPRLIDS		X-Analysis: Program References Level IDs 
XAXPGRFE	Program References From Source	 
XMSGD		Message descriptions 
XAALIST		Application area programs 
XFNPRCTL	Printer Function Control Data
XAFALIAS	X-Analysis: Field Aliases 
XFNJNRLS	Fun.Join Rules
XAALISTBK	Application area programs 
XMETOBJ		Metrics Object Data
XMEMBER		X-Analysis: Member List 
XDVIND		Dspf fields indicators 
XAXMFF		X-Analysis: Member Files & Formats
ZREVFFD		Output file for DSPFFD
XKEYMAP		X-Browse: Key Mapping Data 
XSRCMBRID	Source Member Identifiers 
XEXPGMMGSS	X-Extract: Program Message Statements 
XFNPSKYS	Fun.Position To Keys 
XSTANDARDS	X-Analysis: Program Standards 
X4MEMBER	X-Analysis: Member Index Control 
XMBEXCTIME	Track The Member Execution Time 
XOBJMBRDTL	object details
XSTANDARBK	X-Analysis: Program Standards 
XAFPXDTA	X-Analysis: File Prefix Data 
XDBHSTFLBK	DataBase History Maintenance 
XEXBIZCON	X-Extract: Business Rule Consolidation
XDBHSTFIL	DataBase History Maintenance
QCLSRC		Schedule Entries CL Source File 
XPGMFILES	Files declared in program 
XEXRULMSG	X-Analysis: Business Rule Message Data 
XOWNXREF	X-Browse: Owner X-Reference Data by owner 
X@EMOD		Program Entry Module Information 
XINILOG		X-Analysis: Initialisation Log 
XFNACTS		Function Actions 
XPGMFFLD	Actual File Fields information 
XAXEXTDSS	X-Analysis: EDS X-Ref
XFNJOINS	Fun.Secondary Files 
X@OBJRFDTL	Object Refrence Details 
XDMGENPRBK	'General Migration Settings 
XLBASATR	Output file for DSPFD TYPE(*BASATR) 
XRSCRTNS	X-Extract: Re-engineer Routine Stack 

Turnover

 

Turnover Libraries:
SOFTTURND – Recology Turnover data
SOFTTURN – program objects
SOFTTESTD  - ?

TTYPEF  - defines object types
TDFTSRCF – defines object types available for each application
TSRCOUT      Checkout – list of all checked out objects (missing run date)

SQL: SELECT COOBJ, COTYPE, COLIB, COUSER, COODT, COOTM FROM SOFTTURND/   
TSRCOUT WHERE COOBJ ='ARUT5000' ORDER BY COLIB, COODT DESC , COOTM  
DESC                                                                                                                   

TPRJTSK    Project/Task/SubTask
TPRJDSC   Task Details - Project/Task/Subtask with descriptions

SQL: Task + Description: SELECT TPROJ Project , TTASK Task , TDESC Description , TRQSTR       
Requester, TSTS Project_Status_ID, TRSRC Resource_ID_Details 
FROM SOFTTURND/TPRJTSKF Task, SOFTTURND/TPRJDSCF Descr 
WHERE Task.TPROJ = Descr.PDPROJ AND Task.TTASK = Descr.PDTASK 
AND Task.TSUBT = Descr.PDSUBT              


TDFTL       Application Defaults.By Object Library    - contains libraries used for each phase           
TNAMFLTF  - filters?
                                                                     

TOPERF   - users/authority 
TPRJCTS      Wisedesk Category Matching Task.Physical table 
TPRJADT      Project Audit.Physical table                   
TLINE        Form Line.Physical table                       
TPRJESC     Escalation Queue.Physical table                
TCREATE     Form Line Creation Command.Physical table      
TLOGF       SoftTurn Error Message file                    
TARCAUDF    Archive Audit File                             
TOBJRPT     Audit Object.Physical table                    
TPRJLNK     Task Link.Physical table                       
TWLXOPT     TWLXOPTF.Physical table                        
TAUDHSTF    Turnover Audit History Report File             
TECMITM     eCM Worklist Item.Physical table               
TPRJTIM     Timesheet.Physical table                       
TFORLOG     Form Log.Physical table                        
TIFSOUT     IFS Checkout.Physical table                    
TPRJUDFF    Project User Defined Fields
TPRJPRJF    Project Master
TAPPLF      Application.Real attributes 
TPRJVSTF    Projects/Status
TPRJVRSF    Project/Resources
TPRJVAPF    Project/Application
TPRJREQF    Requesters w/ Name
TPRJRSRF    Resource?Name/Category
TPRJTYPF    Task Types
TPRJSTSF    Project Statuses
TPRJAUTF    Project/User

TARCAUDF  Archive Audit File                               
TARCTMPF  Archive Temporary File                           
TPRJTCT      Wisedesk Category 
TDFTSRC        Application Type Code 
TTYPE             Type Code 
TDFT              Application Defaults 
TQNTC           QNTC User                                        
TNETOVR      Network Overrides                                
TIFSDFT         IFS Application Defaults          
TPRJADT        Project Audit                     
TUSROPTF     Turnover User Options File                       
TWEBHID       Web UI Group Hidden               
TPRJPRJ           Project 
TPRJSTI           Status Transition Item 
TPRJEQM           Escalation Table Message          
TAPPDFN           Application Definition            
TPRJMSG           Project Message                        
TAPPL             Application                            
TSETXRE           Application X-Ref Method               
TSEQLINF          TSEQFORMS Work file                                   
TPRJREQ           Requester                              
TPRJSTS           Project Status Code                    
TPRJEMA           EMail Address                          
TPRJCTS           Wisedesk Category Matching Task        
TDIST             Application Distribution Default       
TDIMTSKF     Dimensions Change Doc to TaskID Mapping               
TAUDEVTF     Audit Events                                          
TAPPINFF     Application additional information                    
TAPPAUT      Application Authority                  
TXRFSYM      X-Ref ILE Symbols                      
TSRCOUT      Checkout                               
TPRJRMK      Project Report Mask                    
TNAMFLTF     Named Filters                                         
TAPPPGM      Application Notify Pgmr                
DLIBLSTF    Library List Object.Update                          
TCHKAPPA    Logical view by *APPLLIBL                           
TCHKITML    Worklist Item.By Library                            
TEXPLODFR   Remote system application explode libraries         
TLIBL       Form Library List.Physical table                    

TAPPLF      
TAPPROVF 
TPGMRF     
TAPPPGMF 
TAPPGRPF   Application Group Approval
TAPPRELF    - Application Relationships
TAPPLSTF    - Application Approval List.R
TPRJREQF   - Requester.Real attributes  
TPRJPRJF    - Project.Real attributes     
TPRJEMAF   - EMail Address.Real attribute
TAPPROV     Form Approval List.Physical table   - was specific form number approved?          

Files used by Hawkeye/Pathfinder

H$DCALL     *FILE     HAWKEYE     PF          PATHFINDER CALLING List    - empty 
H$DCLIST    *FILE     HAWKEYE     PF          PATHFINDER Compile List     
H$DCPP      *FILE     HAWKEYE     PF          PATHFINDER USER COMMANDS    
H$DDOCL     *FILE     HAWKEYE     PF          PATHFINDER DOCLIBL          
H$DFILES    *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref - file/format/member      
H$DFLDKS    *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref      
H$DFLDS     *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref      
H$DFREE     *FILE     HAWKEYE     PF                                      
H$DHISTD    *FILE     HAWKEYE     PF          Field X-ref history         
H$DHISTO    *FILE     HAWKEYE     PF          Object X-ref history     
H$DLGS      *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref   
H$DOBJS     *FILE     HAWKEYE     PF          PATHFINDER-Object X-ref  - very big  - program to program calls
H$DOBJS3    *FILE     HAWKEYE     PF          PATHFINDER-Object X-ref  - 40K
H$DOMIT     *FILE     HAWKEYE     PF          PATHFINDER Omit List     
H$DREFS     *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref   
H$DSRCFL    *FILE     HAWKEYE     PF          PATHFINDER SOURCE LIST   
H$DSRCS     *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref   
H$DSRCSO    *FILE     HAWKEYE     PF          PATHFINDER-Object X-ref  11K program -> source 
H$DTBLA     *FILE     HAWKEYE     PF          TABOP1                   
H$DTBL1     *FILE     HAWKEYE     PF          TABSK                    
H$DCALL     *FILE     HAWKEYE     PF          PATHFINDER CALLING List     
H$DCLIST    *FILE     HAWKEYE     PF          PATHFINDER Compile List     
H$DCPP      *FILE     HAWKEYE     PF          PATHFINDER USER COMMANDS    
H$DDOCL     *FILE     HAWKEYE     PF          PATHFINDER DOCLIBL          
H$DFILES    *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref      
H$DFLDKS    *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref      
H$DFLDS     *FILE     HAWKEYE     PF          PATHFINDER-Field X-ref      
H$DFREE     *FILE     HAWKEYE     PF                                      
H$DHISTD    *FILE     HAWKEYE     PF          Field X-ref history         
H$DHISTO    *FILE     HAWKEYE     PF          Object X-ref history        
H$DTBL2     *FILE     HAWKEYE     PF          TABSP                   
H$DTEXT     *FILE     HAWKEYE     PF          PATHFINDER object text  
In Hawkeye: Program to Program calls SELECT OXNAME, OXLIB, OXCMD , OXPGM FROM H$DOBJS WHERE OXNAME = 'PGM_NAME' AND OXLIB = 'MYLIB'