Re: Table locked - pending commit

[CRPence <CRPbottle@xxxxxxxxx>]

  The ALTER runs implicitly under CmtCtl irrespective of a chosen 
isolation level of *NONE.  In that case there is no availability of 
the COMMIT or ROLLBACK.  The operation functions more like a 
standard [non I\O] database request under /recovery processing/ 
whereby the database needs the operation to function with effective 
atomicity.  This /recovery/ spans jobs, so ending the job is of no 
assist, except to release the lock.  Once the work is effectively 
complete except for locking, the commit definition information is no 
longer available; the recovery suggests however, that the object 
remains registered.  There were some PTFs which were preventive for 
some instances of CHGPF SRCFILE() and\or ALTER leaving the altered 
table in such a pending recovery state.

  If the file is still locked, then collect the spooled joblog and 
dspjob of the job which [presumably experienced a failure while it] 
altered the table.  If another job than the one performing the 
alter, then that job plus the job that performed the alter.  Then 
also collect DMPSYSOBJ *ALL The_Lib 19 D4 spooled output for the 
library of the file named TBL_PM which was being altered; the 
creation timestamp of the QDBDBDROBJTBL_PM* object in The_Lib should 
denote the instant the alter started, and if the job is still active 
the job pointer may appear in the .POINTERS section of the dump.  It 
is that /recovery object/ which tells the database to prevent the 
I/O operation; i.e. the failing open by DSPPFM, and similarly 
prevent any DDL-like activity against the file.  Note: There may be 
some messages in the history log regarding "failed recovery" or some 
other strange errors involving database file(s) in the DBF network 
for the TBL_PM *FILE object.

  Although there is a /patch/ to get past that condition, the table 
and its network should be created again if the patch is used.  That 
is because the operation did not complete under /recovery/ and so 
its state should not be trusted.  An IPL will process all of the 
/recovery objects/ as does a RCLSTG; the latter can not break any 
locks however, so if the locks are there due to and held by the IPL 
job, that problem must be resolved first.

Regards, Chuck

Åke Olsson wrote:
> The problem is that the job that performed the action leading to
> the need to do the commit or rollback is no longer active!
>
> The system has not figured that out and performed the automatic
> rollback.
>
> What I am trying to figure out is if there is a way to tell the
> system what it has to do in this situation.
>
> A pwrdwnsys will obviously work. But aside from that?
>
> I have a hunch that some important ptf:s may be missing on this
> system as well.
>
> Birgitta Hauser wrote:
> > If the user is working under commitment control, commit or
> > rollback must always be executed after the change. If the user
> > will end the job without executing commit a rollback will be 
> > performed and the changes will be reset.
> >
> > Åke Olsson wrote:
> > > We experienced the following situation on one system:
> > >
> > > A user logged in and did an alter table.
> > >
> > > After this the table was locked - completely. Could not be
> > > accessed by sql, dsppfm or any other command.
> > >
> > > For a DSPPFM I get a CPF4326 "Commitment definition" {blank
> > > space} "not valid for open of TBL_PM"
> > >
> > > The commitment identifier is listed as X'00000000000000000000'
> > >
> > > I was unable to find any pending operations using the
> > > WRKCMTDFN ("Work with commitment definitions") command.
> > > Otherwise I would have tried a forced commit using that one.
> > >
> > > Any ideas on how to solve a situation like this?