-1

Whats wrong with the below cursor. It works well with oracle, but with db2 gives error "The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified.. SQLCODE=-511, SQLSTATE=42829, "

CURSOR DED_CU(PCTYPE IN NUMBER) is
        SELECT D.something, D.something2,
               D.cid, D.used_something,D.type, crv.xrate,crv.xrate2
               FROM   xbx D, xvx  CRV
        WHERE  D.cid = CRV.CACHE_KEY
        AND    CRV.CACHE_VALUE1 = Declaredvariable1 AND CACHE_TYPE=Declaredvariable2
        AND    D.used_something  > 0
        AND    D.type   = Declaredvariable3
        ORDER BY D.something2, D.something
        FOR UPDATE OF used_something;

I am using cursor in a stored procedure. I am new to DB2.

stacky
  • 19
  • 2

1 Answers1

0

Did you look up the error message?

On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:

  • The DISTINCT keyword
  • A column function in the SELECT list
  • A GROUP BY or HAVING clause
  • A FROM clause that identifies one of the following:
    • More than one table or view
    • A read-only view
    • An OUTER clause with a typed table or typed view
    • A data change statement
  • A set operator (other than UNION ALL).

Db2 follows the SQL Standards, you can't update a joined table. You'll need something like so:

    CURSOR DED_CU(PCTYPE IN NUMBER) is
    SELECT D.something, D.something2,
           D.cid, D.used_something,D.type
           FROM   xbx D
    WHERE  D.cid in (select CRV.CACHE_KEY
                     from xvx CRV
                     where CRV.CACHE_VALUE1 = Declaredvariable1 
                       AND CACHE_TYPE=Declaredvariable2
                     )
    AND    D.used_something  > 0
    AND    D.type   = Declaredvariable3
    ORDER BY D.something2, D.something
    FOR UPDATE OF used_something;
Charles
  • 21,637
  • 1
  • 20
  • 44
  • Thanks Charles, I tried the same earlier as rather than using a join used in clause. But the problem is the statement also has few columns from the joined table which I forgot to mention. – stacky Jan 11 '22 at 21:29
  • you can't update 2 different tables in a single statement. If you're not actually trying to update two tables, you should be able to adjust the statement I've shown you. – Charles Jan 11 '22 at 21:51