File operations performed during a call

[kpandian]

Hello,

I have the following need:
"To list the file names (with library) to which an Insert or a Update operation is performed, in a program call."
May be the called program performs these operations or it's consecutive calls does these operations (could be thru a OPCODES or Embedded SQLs).

The idea is not to modify the existing programs and still identify such DB operations.

Tried to use the command STRDBMON, but could identify only the listing of DB operations done thru SQLs.

Any suggestions ?

[tomliotta]

Re: File operations performed during a call

How many files does this need to cover? Certainly not system database catalog files...?

What about source files? I.e., if you implement the process and find that it needs a minor bug fix or two, will it need to catch inserts/updates to source members that are changed? What about source PFs that are used by applications, e.g., FTP scripts? What about system commands such as CPYF or outfiles from various commands? Do you need to track outfiles from queries?

Do you need to know if actual inserts/updates are done, or is it only necessary to know files that are opened in an update mode? (You possibly have to wait until a program ends before you can know if any inserts/updates are actually done, but the open mode can often be known as soon as the file is opened.)

The concept is extremely broad. Can you narrow it down at all?

[kpandian]

Re: File operations performed during a call

Hello Tom,

Thanks for the reply.
I will put it more specific as per my need.

I have a list of existing programs Pgm-1, Pgm-2, ...Pgm-x that has some business logics and perform Select, Insert, Update, Delete operations on DB files. These DB operations might be performed thru SQL or thru native opcodes like CHAIN, WRITE,etc., and not always. That is to say, only on some conditions, these DB are performed.

And i have a case where a maintenance screen program Pgm-A, would make calls to any of these programs.
The thing is that i just need to say, the list of files on which the Insert/Update operations were performed during the call.

The number of files are not too many. I would expect that to be maximum of 10 to 15. I was wondering, if there is a way to identify that without modifying the existing programs to indicate me the list of such files.

And i need to identify the files which are modified and not the ones which might be opened and not modified.

Thanks again...

[GLS400]

Re: File operations performed during a call

Hi kpandian:

You might try using dsppgmref to an output file.
Use the following "file usage" codes on object type "F" to determine how the file was used:

The program use of the file (1=input, 2=output, 4=update, 8=unspecified, or a number representing a combination of any of these four; for example, a code of 11 is a combination of 1, 2, and 8, which is input, output, and unspecified)

The above was taken from here:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fcl%2Fdsppgmref.htm

Best of Luck
GLS

(you can use *all for the program name)

[Scott M]

Re: File operations performed during a call

If you have the files journaled you would be able to tell what was modifed during the call.

[kpandian]

Re: File operations performed during a call

Hello Gls,

Thanks.
Yes, DSPPGMREF would serve me if i want to get the files referenced/utilized in a program and filter the files as per their specific mode usage.

But, in my case, the files might be affected conditionally and not always. So, i can be sure of these only based on the execution, Right ?


@Scott: Regarding the journalling option, i need to add journalling to each of such files referenced. But this could help me to identify correctly the affected files.
Is there any other way than adding journal to each file?
Sorry for asking on different solutions, just trying for an optimal solution.

[GLS400]

Re: File operations performed during a call

But, in my case, the files might be affected conditionally and not always.

If you need to identify what conditions will do an update/insert etc......you will need to get into the program logic.

Perhaps I am not understanding the end goal.....please explain more.
{are you expecting to see something like option_a/program_a ..... updates file_b field_b program_b?}

Best of Luck
GLS

[tomliotta]

Re: File operations performed during a call

It's possible that journaling should already be done for those files. Regardless, it's probably the easiest thing to set up. With only 15 files, it could be done with at most three commands. If a database journal already exists that you'd want to use, then a single command (STRJRNPF) would get it all started doing the logging.

That could track opens and closes, users, program names, job names, timestamps, inserts, deletions and updates. If needed, both before- and after-images of the records can be logged. Reporting could be done that allows levels of drill-down, and it could be made about as general or detailed as you want to get.

At least one alternative that some use is to create triggers for each file. You'd need to decide (and program) what actions each trigger performed. Reporting would depend on what each trigger did. You'd need to create some kind of logging database.

Either way, no changes to existing programs would be needed. It's possible that you'd want to make (hopefully) minor changes in the case of triggers, to handle any conditions that each of your trigger programs could run into.

Once either method is implemented, it's possible that all reporting could be done by running common commands and basic queries. (Hard to be sure about triggers without knowing how you'd define any logging database.) Maintenance of the "logs" would likely also be done with standard commands. (Again, triggers are essentially custom, so it depends on what you create.)

Journaling can also provide significant added advantages for various kinds of problem handling, but that gets into the general topic rather than your basic purpose. Additional questions can be posted for details of whatever method you choose.

[DAG0000]

Re: File operations performed during a call

Journaling will be useless until the files are actually used for a write, delete or update. Depending on what the program is doing you may have to wait a bit to get an accurate picture of file usage. Your better off using DSPPGMREF for something like this.

Also you would need to journal every file for it to work and in trying to locate every file to journal you will end up answering your original question anyway.

[tomliotta]

Re: File operations performed during a call

Your better off using DSPPGMREF for something like this.

Since he said he already has his list of programs, what will DSPPGMREF tell him except that the program belongs on the list?

[DAG0000]

Re: File operations performed during a call

Since he said he already has his list of programs, what will DSPPGMREF tell him except that the program belongs on the list?

It will tell him which files and how they are being used in those programs, which is what he was looking for.

[tomliotta]

Re: File operations performed during a call

It's possible that that would be all he needs, except it doesn't answer this question he asked:

But, in my case, the files might be affected conditionally and not always. So, i can be sure of these only based on the execution, Right ?

And it's true. Only by execution can he tell if a WRITE or INSERT or other actually happened. Files are opened in update or output mode all the time without ever writing anything. The output often happens only under certain conditions.

Personally, though, that seems to be a grey area in the problem. The actual need isn't quite clear.

[GLS400]

Re: File operations performed during a call

And it's true. Only by execution can he tell if a WRITE or INSERT or other actually happened.......................................... ... The output often happens only under certain conditions

That is the issue I was trying to raise in my post #7. Weather you use dsppgmref or journaling...
I think the OP wants to determine which options in program_a cause the insert/update/delete in program_b. The only way to make that determination is to dig into the program logic.
Although admittedly I am probably reading a lot into this.

GLS

[tomliotta]

Re: File operations performed during a call

Extra note... dynamic SQL programs often won't show any file references at all through DSPPGMREF. And native I/O can be misleading where overrides are used or in other coding cases.

[kpandian]

Re: File operations performed during a call

Hello Tom, Gls and Dag !

Thanks a lot for your ideas.
I am more inclined towards choosing the Triggers as an option although finding Journalling could be a nice option as well, considering the following reasons:
1) In my system, don't have sufficient authorities for journalling related commands and it's kinda hard to negotiate for the authorities.
2) It would store details about so many events on the desired files, requiring relatively more storage, etc.

Just imagining the solution in the following manner, with Trigger:
Briefing again my problem area: "The maintenance program (Pgm-A) invokes one of the transactional programs (Pgm-1, Pgm-2, ... Pgm-X), depends on the transaction/operation and it is required to list the files inserted during the transactional program calls."
1) Identify the files used in the transactional programs using DSPPGMREF and add triggers with a RPG program.
As of now, don't have any Dynamic SQL programs in my list.
In case of future need, planning to manually identify the file, because the information is available only during runtime.
2) Generate a sequence value and store it in the local data area or a specific data area, from the maintenance program before invoking the transactional programs.
3) In the trigger program, check the Data area value and write the details of file name, Library name, member, etc., into a DB file or Data queue.
4) Read the file DB file or DQ with the same sequence value and display to the used at the end of execution of the selected transaction/operation.

Thought of the option of a "Sequence number" for two reasons:
a) To avoid the data confusion because concurrent execution of the same operation from Maintenance screen
b) To avoid getting the data because of execution of unwanted inserts, performed outside the scope of maintenance screen.

Any observations or suggestions, would be greatly welcomed.