On 26 Aug 2013 08:15, Robert Clay wrote:
Followup:
IBM could offer no assistance, advising that they “never” delete
views when applying PTFs.
A categorically false claim. One should never say never ;-) If the
system had not been IPLed since the loss, and system logs and joblogs
were left active or spooled, available for review, then anyone with the
time [and necessary experience] could assist. Without some specific
evidence that /IBM was to blame/ for the incident, I can understand
their reluctance to review something that seems to them more likely to
be a usage issue. They would want some evidence presented that
implicates an IBM program rather than they take on the expense of
possibly finding a usage issue *or* a problem; at least not without
first having a paid consulting contract, in case it was the former vs
the latter.
The effects of one particular series of PTFs, activity involving
objects in the library SYSIBM, surely includes deleting some VIEW files.
IIRC the PTFs are associated with the *PGM object QSQSYSIBM in QSYS
would likely be involved; i.e. that program would be co-requisite or
pre-requisite. Many PTFs over incremental /enhancements/ to the IBM
software have called that program to [re]create some objects in SYSIBM.
Further, they advised that there is no audit trail to begin an
investigation because we do not journal deletions for these
particular database objects.
Best described as to "audit deletions" vs to "journal deletions";
i.e. as a distinction, objects vs rows. While object deletions are at
least somewhat logged in a [database\commit] journal, the "deletions"
with regard to /journals/ are more notably in reference to data rather
than objects. Because a logical file is not explicitly journaled,
neither would its creation or deletion, as an object, necessarily be
logged to the journal of the underlying data; it is object /auditing/
that would log such an action, object deletion, to the auditing journal
QAUDJRN. Note: an LF will be implicitly journaled, but I do not recall
under what circumstances... and as I recall that effect was not manifest
visibly, but IIRC that effect is now visibly manifest; i.e. the DSPOBJD
and DSPFD, I seem to recall reading, will since some change actually
shows /journaled=Yes/ for non-keyed logical files after they have been
implicitly journaled.
Perhaps no /audit trail/ per lack of general or specific object
auditing being in place, or for [effectively] a lack of journaled
logical files [vs access paths], but that does not mean there is nothing
that could be found [as an effective audit trail; or perhaps just the
proverbial "smoking gun"]. IIRC I hadd replied earlier to this thread
indicating some joblogs could have been available for review, to
determine the culprit; there is a specific message for "dependent file
deleted" when a DROP TABLE ... CASCADE is performed.
I thought (hoped) that it was just a fluke but this past weekend we
installed the current batch of group PTFs and it happened AGAIN:
missing views! More this time than last time.
Last time, I was able to tell which views were missing by querying
QSYS2.SYSTABLES...WHERE table_type = 'V' AND system_table <> 'Y'.
I think that it is strange that the views show up in SYSTABLES and
yet don't really exist as objects.
That should not happen; if indeed that is the case. While the row
tracking a /deleted/ VIEW [one pending deletion under isolation] might
validly persist in some cases, the selection of the WHERE clause in the
SYSTABLES VIEW should prevent its inclusion. There is one other case
the row would persist and be visible, but then the VIEW objects would
need to be under non-commit /database recovery/ for which a CREATE
request should conflict, and that request to create should fail with a
specific error.
We were able to re-create the views but it's just a hassle.
Are the VIEWs by any chance created in and used by only one job, such
that perhaps they were created under isolation but never committed; and
that job persists until a pwrdwn/IPL? And are the requests to create
the files coded as just CREATE or possibly instead coded with the CREATE
OR REPLACE syntax?
The one thing that these five views have in common is that they each
utilize UNION with values based on SYSIBM.SYSDUMMY1 (don't ask).
While unexpected generally, one must understand that a CREATE VIEW
over any *system-supplied database file* is subject to deletion upon any
system maintenance as well in some situations of self-corrective
actions; in the latter case, CPF32D1. The /normal/ expectation would be
that the effect should be experienced only during any OS or LPP [option]
upgrade; i.e. atypically with any PTF activity, and one would hope that
PTF special instructions were quite clear on the effects if indeed PTF
activity should effect that deletion.
Was the creation date of the SYSDUMMY1 reviewed to see if that
database *FILE had been dropped and re-created since the missing VIEW
*FILE objects had been re-created previously?
Unless something has changed, the SQL TABLE SYSDUMMY1 in SYSIBM is
not explicitly journaled. That could easily be effected with STRJRNPF,
and then a DROP action against the file will be recorded; the job and
program included in the logged information.
The reference to SYSDUMMY1 should be able to be replaced easily
enough with the row VALUES-clause.? Thus eliminating the dependency on
the TABLE, and thus irrespective of what the OS database\SQL feature
does with that file during PTF application or upgrades, the user-created
VIEW files would be unaffected.
If a TABLE is desirable or for some unexpected reason is considered a
requirement, there is *no reason* to utilize the IBM-supplied SYSDUMMY1
for user-created VIEWs. The CREATE TABLE [or CRTPF] to effect similar
[and optionally CREATE TRIGGER to protect the integrity of the file]
should be easy enough to avoid any dependency on the IBM-supplied
object. Even CRTDUPOBJ SYSDUMMY1 SYSIBM *FILE MyLib MyDummy DATA(*YES)
would enable the CREATE VIEW to reference the MyDummy instead of
SYSDUMMY1 on the FROM-clause.
Of course what the prior two paragraphs offer are merely
circumventions. And those actions would simply bypass whatever is the
origin for the problem; i.e. whatever is the problem would no longer
impact the user-created VIEWs, yet whatever was happening to cause the
original problem will presumably persist, but remain unknown.
I see the need to start auditing on these objects, at least for
deletions. Then, maybe I'll have something for IBM to begin an
investigation.
General object auditing should be sufficient, but the explicit object
auditing would be best.
I'm really surprised that no one else has experienced this issue.
Perhaps others have, but they realized that either their System
Change Management in post-maintenance or their applications at run-time
must re-create such VIEWs if\when they have gone missing.? Or they [had
since changed to] use the row values-clause when that became available?
midrange.com
