Re: LF usage

On 19-May-2016 15:21 -0500, Steinmetz, Paul wrote:

If I confirmed that this LF is not referenced by any program, would
it be safe to delete this LF even though the object usage shows it
is used once a week.

I personally would not be so convinced about a conclusion that the "LF is not referenced by any program". Searching sources and where-used data is not going to reveal conclusively, all possible references. That is because references can be far from conspicuous; references may occur via alternative names, variables [dynamic name visible only at run-time], overrides, logically per query\view, SQL alias, non-SQL aliasing [including DDM], ¿others?. These can make quite difficult, finding an actual reference. Note: when searching sources, probably best also to search for format names, beyond just file object names.

If I really wanted to know for sure, to prevent the introduction of a problem per the missing object, I might await conclusive evidence about what directly opens the LFM or what query implements using the Access Path of that file. If not already done, I would journal the underlying PF(s) for the /used/ LF, being sure to retain Open\Close entries; ensuring the Fixed Length Data (FIXLENDTA) for the Journal (JRN) includes the program [and lib]; no matter how the data that is keyed in the access path for that LF is accessed, there will be an F-OP and F-CL for the Physical File Member (PFM) from which that data was derived.

If forcing the failure is acceptable to identify the origin, then for a direct open, the missing object problem can be forced having used instead, Rename Object (RNMOBJ) or Rename Member (RNMM); rather than an actual deletion. I explain later, considerations why that may be important, even verging on imperative for some scenarios. But for usage-origin solely per query-implementation, determining that origin could require more logging and possibly programming, along with poring through outputs, to finally accomplish; deletion in this scenario would neither directly nor definitively reveal what was the origin, although updated Access Plan(s) [as effect of the reference gone missing] might point in the right direction -- but then, to know where to look.

Note: There is the Start Database Monitor (STRDBMON) for detail results that might assist, if run over the period of the weekly-run. There is also the Open Database File Exit feature QIBM_QDB_OPEN [fmt:DBOP0100] that should be able to track any opens of the file(s) to get to the data; the Retrieve Job (QUSRJOBI) API can be used in an exit program to log the current SQL if the open is identified as an SQL open.

If the issue is per /last used/ being updated because of a Query reference vs an actual open:

Given the intention of the Query engine for having updated the last-used date of an LF [per the keyed Access Path (ACCPTH) having been used to implement a query] was to prevent /accidental/ deletion due to obsolescence\unused object validation, either a Delete File (DLTF) or Remove Member (RMVM) action is in conflict with the spirit of that intent.

However, given such /usage/ is purely an implementation detail rather than an actual functional aspect, the effect from the loss of that access path is merely the changed query-implementation for accessing the data, the next time the query runs; i.e. a new Access Plan, per re-optimization, is since required for the query.

Yet consider: If the file [or Logical File Member (LFM)] is deleted, and the effects are a poorly performing query, then the cost to rebuild the access path may be problematic; i.e. while the storage and maintenance costs are no longer, since the deletion, any increased costs for the query run-time may be found to be unacceptable. If the AccPth-rebuild is mandated as resolution, then the cost of rebuild may also be unacceptable; care[ful consideration] must be taken in the decision, so as to prevent a paradoxical situation.

Note: As I recall, Birgitta suggested reviewing the query plan cache; minimally look for the based-on file names, both long and short, from the /used/ logical file. If the cached statements are not resolved to the redirected object name from an ALIAS, then also find any aliases pointing to any of the PFs or LFs [e.g. via a catalog query] and then search for both long\short ALIAS name references there.