File usage in AS/400

[John192]

Hi,

How can we findout that a particular file is being used by these many Programs in AS/400 If we don't have an option of FNDSTRPDM and there is no tools like Turnover or Hawkeye avaialble with us to do impact analysis ?



Thanks much...

[andreas.prouza]

With DSPPGMREF you can get a list of all references of a programm.
You can use it to list all references from all programms of all libs. But I would strongly recommend to use only user libs (*ALLUSR)

[John192]

Thanks, but could you please specify this command so that i could filter out all the programs which are using a particular file?


Thanks much...

[John192]

also executing this command in production environment like this DSPPGMREF PGM(*ALL/*ALL) OUTPUT(*OUTFILE) OBJTYPE(*ALL) OUTFILE(lib1/tf1) will be fine enough to get the information with respect to a specific file to which i want to see how many programs in the system is using this file ?

Also would executing above command cause any locks or any probable MSGW in production environment or it is safe to execute this command to generate out file for this purpose and then later on investigate this file which may have probable program names in it who might be using that specific file?



Thanks much...

[John192]

With DSPPGMREF you can get a list of all references of a programm.
You can use it to list all references from all programms of all libs. But I would strongly recommend to use only user libs (*ALLUSR)

why do you recommend 'to use only user libs (*ALLUSR)' ?


Thanks much...

[andreas.prouza]

If you use *ALL than you will get a lot of data and it will take long time.
Usually you don't want to analyse system objects.

[andreas.prouza]

With DSPPGMREF you get a list of all references (programms and tables) which are referenced with a programm.
So you first have to generate the list and then you can make a query to find out where exactly your file is used.
You should also check the IBM documentation if you want to know what it is about in detail: https://letmegooglethat.com/?q=dsppgmref

[John192]

When I am doing this to know file usage in all the programs in the system myjob is going in 'LCKW' status, what to correct in this command to get desired information in the outfile as well as to avoid this command(job) going into 'LCKW' ?


DSPPGMREF PGM(*ALL) OUTPUT(*OUTFILE) OBJTYPE(*ALL) OUTFILE(lib1/tf1)

Note :- in above command I just used library as ' *libl' here based on specific environment here.




Thanks...

[Scott M]

what is the LCKW on, look at the job locks and determine that first.

[John192]

ok, thanks but similarly if we need to know a particular program is being used in these many programs in the system then how to know that?



Thanks..

[MFisher]

Why can't you use FNDSTRPDM ?

You can use this instead of FNDSTRPDM

https://code400.com/forum/forum/iseries-programming-languages/sql/155493-source-code-search-string-utility

[John192]

1) In FNDSTRPDM I need to mention file name and library names so when i am not sure about this because particular program could be used in different source file and different libraries and also in particular source file it could again be used like a call statement inside this souce member then it is very difficult in such case.

2) i tried the example in the link but got below error:-

Type SQL statement, press Enter.
Numeric constant 37TABLE_NAME not valid.
===> CREATE OR REPLACE TABLE QTEMP.RPG_SOURCE (
ROW# BIGINT NOT NULL ,
TABLE_SCHEMA FOR COLUMN TABSCHEMA VARCHAR(128) ALLOCATE(10) CCSID 37
TABLE_NAME FOR COLUMN TABNAME VARCHAR(128) ALLOCATE(18) CCSID 37 NOT
TABLE_PARTITION FOR COLUMN TABPART VARCHAR(128) ALLOCATE(100) CCSID
COMPLETED VARCHAR(1) ALLOCATE(1) CCSID 37 NOT NULL,
PARTITION_TEXT FOR COLUMN TABTEXT VARCHAR(100) ALLOCATE(100) CCSID 3
7 ) ON REPLACE DELETE ROWS RCDFMT RPG_SOURCE

also what does that begin and end between the SQL queries ? is it any SQLRPGLE program in between we need to code and compile in the system to use the SQL queries mentioned there ? if yes, what should be it's name and first we should write this SQL rpgle program then should we try executing these SQL queries step by step ? it would be much better if you could please mention steps properly for that link as in between SQL query there seems to be program code for SQLRPGLE program whereas on top it's written "
Run the whole block at once" ?


Thanks...


Thanks..

[MFisher]

You got an error because you cut off part of the code. Don't run it in green screen. Run it in Navigator or Client Solutions query window.

I wrote that script to work with 1 source library that is hardcoded. You could run it multiple times for each source library.

EDIT: Modified to allow multiple source libraries.

[John192]

that is the main issue here if there are multiple such libraries ( and we are not sure in which library and source file) that particular source member could exist then to avoid it running again and again is there not any easier way to get this information?

Thanks..

[MFisher]

that is the main issue here if there are multiple such libraries ( and we are not sure in which library and source file) that particular source member could exist then to avoid it running again and again is there not any easier way to get this information?

Thanks..

I just Modified it to allow multiple RPG or CL Source File searches.

WHERE ........... TABLE_SCHEMA in ( 'SRCLIB_1' , 'SRCLIB_2' )

[MFisher]

Run in a window:

[John192]

But in this also we need to be sure of source file and library names what I meant to say that instead of putting their names manually because we can not be sure of their locations in the entire IBM i system so to scan into all such possible source files and library names wherever that source member could be present what could be done in a automatic way?


Thanks..