On 16-Dec-2011 13:36 , Dan Kimmel wrote:
SQL0007 Reason code 9 - Index is currently held or is not valid
Following
CPF5090 The unique keyed access path of member WORKP00004 file
... cannot be maintained.
What does that mean?
SQL7008 rc9 or sqlcode -7008 I presume? Anyhow...
The CPF5090 indicates that a unique access path [keyed UNIQUE logical
file <¿or primary or unique key?>] was previously invalidated; then that
the rebuild activity was either placed on hold [held via iNav or the
cmdline version of EDTRBDAP "Edit Rebuild of Access Paths"] or that the
AccPth still has not been rebuilt. I do not recall if there might also
be an EDTRBDCST or some such if the unique key were a constraint instead.?
There is a [presumably still, and possibly problematic] feature
which, for each incident, resignals the "rebuild event" to the QDBSRV01
job to have that job forward the index rebuild work to one of the
runpty-52 QDBSRV## jobs. That should ensure the entry [re]appears on
the EdtRbdAP screen.
In newer releases there is more tracking [beyond what was previously
only available in the VLIC logs; those can be saved to be sure details
about the origin remain] which diagnose the origin of the invalidate
request. The typical case is restore of the dataspace, without the
owning logical file being restored on the same RSTxxx request. I do not
recall what the OS manifests visibly in that regard, but the iNav
interface and the underlying support [some database file in library
QRECOVERY] has some more details recorded. There may also be
information in the history log. I do not recall the message range to
search. There may also be information in the QDBSRV01 joblog and\or any
QDBSRV## joblog which attempted to rebuild; the messages sent to the
QSYSOPR and\or QHST would come from those jobs.
Reviewing the logging for the file activity and reviewing any errors
in the joblog [or errors sent to the history by\]of a job that "did
something" to that file might give insight as to the origin of the
invalidation. The review of the history for "access path" related
messaging and information in the joblogs of the jobs that rebuild the
access paths might also give insight.
The most direct resolution to the CPF5090 is to OPNDBF ACCPTH(*FILE)
of the file member which owns the unique access path; the open performed
in a job of the desire Work Management parameters, which may be more
appropriate that the default runpty-52 if the OS DB2 takes on the work;
i.e. lower priority than batch.
FWiW: The problem can persist in the unfortunate [and AFaIK should
never transpire, excepting cases of defects] situation whereby duplicate
key values correctly cause the rebuild of the unique key to fail. In
that case the file might best be "moved" to allow for further
investigation, and a new copy made without the duplicates, and the
application restarted on the new version of the file; of course enabling
investigation is a "would be nice for the thought of investigating the
origin and possible matches to existing defects to allow finding
preventives or to assist finding a new defect, but not reasonable" so
just deleting the bad rows that are causing duplicates would be deleted
and life goes on... but still reporting the errors.
Regards, Chuck
midrange.com
