IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2019-07-06T00:36:49Z by ERF
ERF
ERF
5 Posts

Pinned topic Getting SQLSTATE 42912 after recompile on V7R2 with new PTF's

‏2019-06-14T23:03:10Z |

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?

-rf
 

  • Rocky_Marquiss
    Rocky_Marquiss
    1 Post

    Re: Getting SQLSTATE 42912 after recompile on V7R2 with new PTF's

    ‏2019-07-05T15:13:00Z  

    The documentation states:

     

    If the UPDATE clause is specified without column-name list, then the implicit
    column-name list is determined as follows:
    v If extended indicator variables are enabled, all the columns of the table or view
    identified in the first FROM clause of the fullselect.
    v 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.

  • ERF
    ERF
    5 Posts

    Re: Getting SQLSTATE 42912 after recompile on V7R2 with new PTF's

    ‏2019-07-06T00:36:49Z  

    The documentation states:

     

    If the UPDATE clause is specified without column-name list, then the implicit
    column-name list is determined as follows:
    v If extended indicator variables are enabled, all the columns of the table or view
    identified in the first FROM clause of the fullselect.
    v 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.

    Rocky, thanks for your response.  It turns out PTF's cured the problem. 

    WRT field selection, you're correct: my understanding is that SELECT * forces the SQL engine to retrieve the list of columns, which suggests the statement SQL finally executes is the list of all the update-eligible columns.  When I do program maintenance, I budget time for refactoring, and I'll modify the SQL to use specific fields the next time I work on this one.

    -rf