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
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 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?