Re: SQL views missing after Cumulative PTF's applied

On 26 Aug 2013 09:59, Dan Kimmel wrote:

We've had the LF *FILE objects associated with views disappear when
our customers have applied PTF's on V7. RCLSTG SELECT(*DBXREF) gets
them back.

If true, there is something seriously wrong. The refresh of the DBXREF should not be necessary to recover any objects, even if that action could or might effect such a recovery. If the actions by the system had maintained the information necessary to recreate the user-created VIEW file objects that were dropped by the CASCADE effect [be it stored in a /database recovery object/ or a row in the *dbxref], then whatever effected the cascaded deletion should be performing that object recovery *inline* to the same procedure\processing; i.e. no explicit separate\later recovery by the customer should be required. Such an expectation by IBM should be recognized as nonsense; easily inferred as a defect vs working-as-designed.

The LF for a view is an odd object. There's not much in it.

But an /object/ nonetheless. While an object could be deleted, even as the effect of a cascaded DROP, a reclaim [of the DBXREF] should not magically un-delete the effect. And again, nor should there be a requirement to RCLDBXREF to effect that mysterious reincarnation, after a PTF application. While such a recovery mechanism could exist [something I actually designed in v3; and possibly something similar that exists for the restore of logical files pending restore of their based-on files] for deleted non-system [aka user] VIEW files as the side effect of a DROP CASCADE of a /system file/ by having recorded the necessary details for their automatic re-creation, the re-create effect should be automatic within the same process[ing] that performed the delete [cascade]. The onus should not be on the user to effect recovery later with some /reclaim/ request.

Just the SQL itself (repeated from the QSYS2.SYSVIEWS entry), a list
of the files it references, an access plan, some system pointers to
the files referenced and what appears to be some system pointers to
the indexes referenced in the access plan. All easily recreated from
the QSYS2.SYSTABLES and QSYS2.SYSVIEWS entries.

As a permanent /object/ there is also ownership and authority, beyond just the stored QDT and Access Plan. For the DB2 for i, unlike other systems that are not object-based and an integrated database utilizing the object concept, the database maintained catalogs are merely reflective, not definitional; i.e. what is the /object/ is recorded as a row, rather than the row being effectively the sole representation of what on IBM i would be called the object. Thus when the object is missing, then so should the corresponding row be missing [generally]. Specifically, when a VIEW is missing as a *FILE object, then having been /deleted/ [SQL DROP, directly or by CASCADE] previously, there [generally] should be no statement text within the catalogs from which to effect the re-create... because the deletion of the object should have deleted the row; there is no object to reflect as a row. In fact, the CREATE of an object that is already being tracked as a row, is grounds for a CPF32A2 [current releases as CPF32A3] for which the *DBXREF is marked as "in error" for which RCLDBXREF is thus required to correct the data that was deemed to be [conspicuously] inconsistent; i.e. a request was made to track as /new/ something that was already being tracked.