Hi,

We havent used any commitment control to the Table. Now we are going to Add some more fields to the Table.We have used constraints.
Might be the programs using this Table,would have been using Commitment control.(because this is a Master Table).
Please could you suggest how to overcome the above error and Add new field to the Table.
We are also trying our best from our end.

Thanks
Amutha

One minor tip — when you report an error message, you need to include the the error message identifier. The text is often almost useless to us, but we can look up the message identifier to find possible recoveries. (We can also look up the message text which is another reason it is less important.)

Tom

Hi ,

Error message Id is SQL0910.
Cause : Object file has a Outstanding change made under commitment control that is preventing this operation.One of the following may occured…………………

We have used constraints.

Remove the constraints.

Then ALTER the table, and add the constraints back after the ALTER finishes. The process of ALTERing the table is most likely causing a constraint to be violated while changes are progressing through the table.

Tom

Hi,

We had run this Alter table in all the servers, without removing the constraints.
In only one server, we are facing this problem.
If the commitment control is the Issues means, Alter Script will not run in all other servers.
So here the question arises why only in one server we are unable to do Alter table.
Even in this server also, we run Alter table Script lot of times earlier a month back.
We dont know why this problem occurs this time.
Could you suggest any other way or how to resolve this.

Thanks
Amutha

Could you suggest any other way or how to resolve this.

No, not that I’m aware of. You will almost certainly need to remove the offending constraint — once you find it. If you have eliminated general commitment control issues, there isn’t much else for you to look at. It doesn’t matter if other systems have the same symptom or not.

Without seeing the definitions of all constraints along with the definitions of all tables referenced in the constraints, plus all data that would affect the resolved states of all of the constraints, there is no way to guess why this system is different from any other.

Perhaps a row in one of your tables would cause a foreign-key dependency violation on this system and no similar row exists on another system. Perhaps another table has a dependency relationship with this table on this system but not on other systems.

There is no way to know from here.

You might do some simple investigation with DSPDBR to see what tables are dependent. It might take deeper investigation to see if other tables have constraints that point back to this table — that might mean you will need to remove constraints from that other table.

Of course, it might also simply be that this system is not up to date on DB2 PTFs, but that ought to be eliminated as a likelihood before looking for other answers anyway. (A secondary possibility is that this system has more recent DB2 PTFs than other systems, and one of those newer PTFs has broken something.)

Tom

After re-reading this thread, I see that you might not have eliminated commitment control yet. I had gotten a mistaken impression that it wasn’t a consideration.

The preferred method of investigating commitment control definitions is through iSeries Navigator. Drill down into the Connection-> Databases-> {databasename}-> Transaction-> Database Transactions.

A list of open commitment control definitions should appear. You can drill into those to see which jobs are involved and what those jobs are doing.

However, you might be able to use the WRKCMTDFN (Work with Commitment Def) command if you have green-screen access. The command is fairly easy to use, though it’s easy to abuse as well.

Neither of those is especially helpful on an active system when you have little to go on. You should look first for any object or member locks (WRKOBJLCK command) against the table to see if any obvious jobs can be found. You might also use DSPRCDLCK (Display Record Locks) to look for jobs.

If you can narrow down the jobs that are referencing the table, you can use WRKJOB jobname OPTION(*CMTCTL) to look into details for that job and even have some influence over what happens.

My apologies for not seeing that the issue of commitment control had not yet been closed. I misread the early comment about “We havent used any commitment control to the Table.”

Tom