Triggers
I am looking for a technique that would allow me to clear the physical file upon the completion of the trigger program. I attempted to do this at the end of the CL porgram only to get the error message that the file is still in use. Apparently this is because the trigger program is still executing. Any suggestions on how to handle this scenario?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 7, 2005 11:45 AM
UPDATED:
November 9, 2009 4:57 PM
Answer Wiki:
I have not used triggers but upon checking the IBM site they can call a CLP program. You could call a CLP program with the trigger that submits a second CLP into the same job queue that the inital program is running and have that program clear the file. This will work in a single threaded job queue only.
If you a worried about the job cue being stacked up you could send the job to jobq Qctl with the CLP having a dlyjob command ahead of the clrpfm.
=========================================================
Because this is a trigger function, any submission to a job queue will regularly cause more trouble than desired. And if the trigger is CL and it accesses the file, or if the CLRPFM is intended to clear the file that was triggered, then it not only isn't going to work but it also doesn't make sense.
A file accessed by a CL program is always going to be in use until after the CLP ends. And by definition, a file is obviously 'in use' if a trigger is currently executing. A CLRPFM issued by a trigger against the triggered file is going to contradict the database logic of having a trigger program executing before control is returned to whatever processed invoked the trigger.
Whatever the reasoning is for this question, it needs to be significantly clarified before any reasonable answer can be given. At the moment, the question doesn't make sense.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
If I understand correctly that you’re trying to do a CLRPFM on the same file which is causing the trigger to fire, here are a few things to consider:
1) CLRPFM requires *OBJMGT authority. That’s generally not wise to give to *PUBLIC. Since this is coded in a trigger, you don’t know who’s going to be executing your trigger — although you can deduce that it would only be people with the add, change, or delete authority to the file/table causing the trigger to fire.
2) The trigger would have to be an *AFTER trigger. The file will likely still be in use even after completion of the trigger. The function which caused the trigger to fire technically doesn’t complete until the trigger returns control and whatever other processing the original function needed to do is complete. I would set up a utility to request the file be cleared at a later time.
3) If there’s a problem with the file having records left for someone else (another function or invocation of the same program) to deal with, then you could delete the records — but you’ll have to watch out for record locking issues.
Are you certain you want to use a trigger or would a stored procedure containing all the logic be more effective?
I’m going to assume you’re talking about an *AFTER trigger.
That message is issued because the CLRPFM command, which you must be using, must allocate the file exclusivelly to execute.
On the other hand, a program, in RPG ILE for example, can read and delete all the records without the need of an exclusive allocation of the file.
SInce the file is still locked, you cannot do a CLRPFM.
As mentioned here, you can have a RPG program go through and delete all the records.
Another option is to use SQL and do a DELETE.
++
My question is if this trigger is initiated on every write to the file, you should have the trigger program delete ONLY that record. You will never be able to do an update to the file, because it should always be empty. If you do a CLRPFM or SQL DELETE to get rid of call the records, you are doing overkill since it is impossible to have more than one record in the file.