Compiling V7R2 Confusion

Question

A key application program is returning SQLSTT='42912' for no apparent reason. Our V7R2 development partition was PTF'ed last weekend and I recompiled the program today after a bit of refactoring. The program works correctly in Dev and in my V7R1 (sigh) offline system; when I move the object code into the V7R2 production partition not yet PTF'ed, the program fails with this error.

I'm declaring a cursor "FOR UPDATE" and not specifying the columns to be updated (meaning all are eligible). The error message "Column HVR00001 cannot be updated" says I'm trying to update a column not defined in the DECLARE CURSOR statement. Column HVR00001 doesn't exist in any of my code and I respectfully suggest that this error message is going to send a lot of people on a snipe hunt.

Starting with this...

      EXEC SQL
      DECLARE gsr010_frp001_cursor CURSOR WITH HOLD FOR
      SELECT *
      FROM frp001
      WHERE fhpro = :fhpro
      FOR UPDATE;

After opening the cursor, this:

      EXEC SQL
      FETCH NEXT FROM gsr010_frp001_cursor INTO :frp001;

After lots of application code and SQL, this is where things fail:

         EXEC SQL
         UPDATE frp001
         SET ROW = :frp001
         WHERE CURRENT OF gsr010_frp001_cursor;

Compiling the program to V7R1M0 eliminates the error, which suggests the PTF's have at least one defect. Another solution is to define the updated columns (there are over 100) but I have a tool that can identify the updated columns (which would be better for performance).

Have I misread the SQL documentation WRT cursor'ed updates or is this a real error?

Answer 1

The documentation states:
If the UPDATE clause is specified without column-name list, then the implicit column-name list is determined as follows:

• If extended indicator variables are enabled, all the columns of the table or view identified in the first FROM clause of the fullselect.
• Otherwise, all the updatable columns of the table or view identified in the first FROM clause of the fullselect.
  
• It’s not all are eligible per se - but all that are in the list of the SELECT are. Now you and I would say * would make all of them eligible. But that may not be what the system sees it.
• I suggest listing out the fields you want to use. It’s good practice to do that anyway for a number of reasons, it is self documenting. You only retrieve the data you really need, self documenting, etc.. and it might resolve the problem you’re experiencing.