Re: View / PF Conundrum

Jerry Adams wrote:

We have two System i's: Production and Backup. I have a process
(CL) that can be used to restore the previous night's Production
libraries to the Backup (the libraries are in SaveFiles FTPed
around 0100). The process does the restore by running a CLRLIB
on the target library and, then, doing a RSTOBJ from the
SaveFile.

During testing something else, I found out that my boss created
an SQL view in LibraryA over a table in LibraryB. LibraryA is
CLRLIB/RSTOBJ first, which means the View is erased. After the
RSTOBJ the View/LF picks up on the table (PF) in LibraryB. I
have no problem understanding that.

The SQL VIEW in LibraryA has its "based on" PF in LibraryB. Given PF named P, and VIEW named V created as SELECT * FROM AnyLib/P, then during a RSTOBJ OBJ(V) LIB(LibraryA) RSTLIB(LibraryA) where LibraryA has a PF named P, be aware that the relationship may change to have the SQL VIEW in LibraryA have its based-on PF in LibraryA. Care must be taken when using save\restore on database files with any cross-library dependencies; i.e. the outcome seen, may not continue to be the outcome seen.

I am, though, confused as to
why I have not gotten an error when the process clears LibraryB,
which contains the table on which the View is hung.

If the VIEW is deleted before the PF, in this case by the CLRLIB, there remains no restriction [given only the VIEW and the PF in the database file network] to prevent the delete of the PF; i.e. CLRLIB LibraryB functions without error [CPF3219 being logged].

A little more: I found the View when I was testing some year-end
changes. I tried to CLRLIB on LibraryB manually (i.e., not as
part of the aforementioned process) and got an error that not all
objects could be deleted. Tracking down the reason was how I
found the View hanging off in another library.

In this case the "dependent" LF over the PF in LibraryB prevents the delete of the PF; i.e. CLRLIB LibraryB leaves the PF and logs the error CPF3219 indicating that the dependent logical(s) must be deleted before the PF can be deleted; or the DROP TABLE with the CASCADE clause [the default; versus RESTRICT] can be used instead of the [effective] DLTF that the CLRLIB uses.

I checked the PF, and it is current as of 0100 on Production
(it's a historical table so all I have to do is verify that it
has transactions dated yesterday, which it does).

In the given environment, the best results will likely be had, by removing any cross-library dependencies for the database files.

Regards, Chuck