Re: Enforcing Read Only Access on files open for maintenance

If resource level security/access is properly implemented, such that no applications adopt all authorities, even if application or group authority is enabled, and if *ALLOBJ special authority is not an issue [directly, or from a group], then the following is sufficient to prevent any insert, update, or delete activity against the copy of the file in the archival library:
crtusrprf PURGE /* no groups, no owned/adopting applications */
signon or run job as user PURGE, to perform the following:
crtlib PURGE aut(*use) crtaut(*use) crtobjaud(*change)
create all files into library PURGE with AUT(*LIBCRTAUT)
By isolating the ownership to a separate profile and only *PUBLIC authority of *USE, only users with *ALLOBJ special authority should be able to access for insert/update/delete activity. If only certain users should be authorized to the library and files of archival data [either now, or if possibly in the future], then an option is to use a *AUTL name for AUT() and CRTAUT(), and assign *PUBLIC *EXCLUDE to the *AUTL, and assign each user added to the list to have *USE. Note: A file can be opened for OPTION(*ALL) [opened for all I/O options], but the actual non-read I/O requests will be denied when the authority is *USE.

Inhibit Write is good for testing applications that are known to be compatible with such testing. However some update programs could loop for use of InhWrt(*YES), due to programmed assumptions. Since the program will infer the operation completes even though nothing happened, an assumption about that completed I/O request could lead the programmed logic astray. For example, the following logic is problematic for a delete request that does not actually effect the delete:
dowhile not_found;
call a program to delete a record with a specific key
if key not found, set not_found
end
The above sequence of repeated calls, depending on actual coding, could result in an endless loop trying to delete the same record.

A good way to prevent insert, update, and delete activity against a database file is to defer to the database for enforcement. This ensures that irrespective of any [special, private, or adopted/application] authorities or [properly] implemented inhibit write capabilities enforced by the data management, the database is the arbiter via the registered trigger. Unfortunately excessive authority [e.g. *ALLOBJ] would enable CHGPFTRG STATE(*DISABLED) or RMVPFTRG.
That can be accomplished by creating one program to be used for a *BEFORE trigger for each event, then assigning every file that same trigger program for all three of those events. The trigger need do nothing more than issue a diagnostic message that says "This is the archival data library. No updates allowed!", and then issue an escape message so the requested I/O fails. Thus any programs that were not inquiry only would fail if they accidentally directed against those files.

With DDS created files, the ALWUPD(*NO) and ALWDLT(*NO) can be used, such that effectively only full refreshes would be allowed for any situation when only Insert activity is insufficient to establish the data sets. The given scenario suggests insert would be all that is necessary, since the archival copy of the data is only pruned records; i.e. a pruned record can not be changed, because it no longer exists, thus an inability to update is a non-issue. Using this approach, only writes would need to be prevented, from all but the prune/archival routine -- for which deferral to other [e.g. of those above] options is required.

If locking is attempted as [an additional] preventive to any access other than read, be sure to modify the archival files to have the WAITFILE(*IMMED) so if/when an application does attempt more than OPTION(*INP) [more than read-only] then there will not be a long wait to find out the application is just going to fail -- it fails immediately when trying to open the archival copy of the file for other than read.

Of course any of these approaches could work in conjunction with any of the others.

Almost all other approaches I can think of would have to see modifications to the application. The only exception, being one that I recommend against using, even though it is an inviting option. That is the Database File Open Exit Program. Input to the exit program includes both the library name and the open options, and thus the ability exists easily to determine if the program should, and then the ability to set a feedback via its /return code/ to deny the open when the file is in the archival library. I do not recommend that exit because it will be called for effectively every database full open performed on the system. It would be best if there was instead, a Database-Open-event [effective] trigger defined at either the file or member level; optionally an unchangeable attribute [so as not to be defeated], like the ALWUPD & ALWDLT.

Since SQL TABLE does not support ALWUPD & ALWDLT, it might be worthwhile to submit a design change request asking for the ability to perform something like the imaginary request:
CHGPFALW ALWUPD(*NO|*SAME) ALWDLT(*NO|*SAME) ALWWRT(*NO|*SAME).
Admittedly not really a valid CHGxxx command since the command would allow only unidirectional change, but such a command would enable SQL TABLE to get the same protection available to CRTPF, and it would add the additional ability to make a truly read-only file. The obvious concern would be for lack of ability to continue insert from future pruning, so that would have to be resolved by the sequence of actions like either of:
rnm-alwNot-> create-> copy-from-rnm-> prune/add-> ChgAlw-> dlt-rnm
or
create-like-> copy-to-like-> prune+add-to-like-> save-like->
rst-like-over-alwNot-> dlt-like
The obvious complaint to come with such an implementation would be, "But why not enable resetting to have ALWxxx(*YES)," and the response is... "Because the attributes must be permanent, to ensure they are not defeated since being set." FWiW the ALWWRT [or ALWINS] was presumably never externalized because when originated, having been manifest only for CRTPF interface, the obvious catch-22 is that there would be no ability to populate, given an inability to write. However that can be /circumvented/ by either a data-only restore [which is not a write], or if the ability to turn off the allowed capabilities was extended to some point in time after the create -- by for example, CHGPFALW.

Another design change request idea: add a new /return code/ to the database file open exit which tells the DB Open to force the open to be 'input only' irrespective of what open options were specified; presumably resulting in a message logged to suggest that the open options were overridden -- like conditions CPF4015, CPF4028, etc.

Regards, Chuck