× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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?


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.