Re: SQL State 55019

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