SQL TOKEN -510 : Ambiguous & Unambiguous

I perform quite a lot of BIND's in my environments. Lately, I received an email with an error message pointing out to a BIND operation  -

BIND error

DSNX200I -TEST BIND SQL ERROR
USING TAUTH00 AUTHORITY
PLAN=PLAN0001
DBRM=PACK0001
STATEMENT=278
SQLCODE=-510
SQLSTATE=42828
TOKENS=
CSECT NAME=DSNXODML
RDS CODE=-530

Before we research into the error message, lets look at one of the BIND parameter - CURRENTDATA and we will also see the difference between Ambiguous & Unambiguous cursors

Assuming two applications try to access a set of tables at the same time -

If the cursor is read only ( FOR FETCH ONLY ) and the program resides on isolation level CS, this isolation level does not guarantee that the data retrieved will be stable.
If the cursor if updatable ( FOR UPDATE OF ) and the program resides on isolation level CS, this isolation level guarantees that the data retrieved is stable.

I guess I have found ways to guarantee stable data for read-only cursors -

CURRENTDATA(YES|NO) with Isolation level CS -

If you have a read-only cursor and you want to fetch stable data, use CURRENTDATA(YES) in the bind card. CURRENTDATA(YES) ensures data stability for read-only cursors. If you specify CURRENTDATA(NO) then, there is no guarantee that data retrieved is stable as another application would have updated the data before your application performs the commit.

Ambiguous and Unambiguous Cursors -

The cursor is ambiguous if DB2 is not able to determine whether it is used for update or read-only purpose. Lets look at one example :

STEP1 : DECLARE P01-CUR FOR SELECT * FROM PRDNSCH.VIEW001 WHERE P01_EMPNO = :HV-01
STEP2 : OPEN P01-CUR
STEP3 : FETCH P01-CUR INTO :FV-01, :FV-02, :FV-03
STEP4: CLOSE P01-CUR

DB2 will never know what's happening with P01-CUR. In STEP 3, FETCH operation is performed from the view, but the view is not set as read-only with help of FOR FETCH ONLY clause in STEP1.

Unambiguous cursors are the one's where DB2 clearly understands what the cursor is meant for. As an example :

STEP1 : DECLARE P02-CUR FOR SELECT * FROM PRDNSCH.VIEW001 WHERE P02_EMPNO =:HV-01 FOR UPDATE OF P02_EMPNO, P02_SALARY
STEP2 : OPEN P02-CUR
STEP3 : UPDATE PRDNSCH.VIEW001 SET P02_EMP =:FV-01, P02_SALARY =:FV-02 WHERE CURRENT OF P02-CUR
STEP4 : CLOSE P02-CUR

Now, DB2 knows that P02-CUR is a UPDATABLE cursor because of the FOR UPDATE OF clause specified in STEP1.

We now know the differences between ambiguous and unambiguous cursors along with CURRENTDATA, lets look at solving the -510 error code. Looking at the source code I discovered:

DECLARE clause for the cursor was defined as -

DELCARE P01-CUR FOR SELECT * FROM PRDNSCH.VIEW001 WHERE P01_EMPNO =:HV-01

The processing being performed by the cursor was -

UPDATE PRDNSCH.VIEW001 SET P01_EMPNO = :FV-01, P01_SALARY =:FV-02 WHERE CURRENT OF P01_CUR

It's a typical case of -510, DB2 could not make out that this cursor was updatable, since FOR UPDATE OF clause is not defined in the DECLARE CURSOR statement and CURRENTDATA is set as NO.

The developer then changed the DECLARE clause of the cursor and introduced the FOR UPDATE OF clause which resolved the -510 error code. Uff !! I am trying to improve this post on a constant basis and I admit this is a confusing topic !