Hi

I writing a module is sqC that has a lot of reading to do from the DB. I am trying to use the FOR READ ONLY WITH UR clause. It is working for some functions and for other it gives strange errors most ly unexpected toke errors. I am not able to figure out what might be the cause. Also when I remove the FOR READ ONLY clause the compilation goes fine!!!

Attached is a sample code snippet and the error I am getting.....can anyone pls give me some insight on this....

EXEC SQL SELECT order_number,type,status,value(completion_date, 0),due_date
INTO rd_num,rd_type,rd_status,rd_completion_dat e,rd_due_date
FROM order WHERE rder_id = order_id FOR READ ONLY WITH UR;

Error is:

510 SQL0104N An unexpected token "INTO rd_nu ,rd_ty ,
rd_st ,:H0006" was found following "on_date, 0),
due_date". Expected tokens may include: "<from>".
SQLSTATE=42601

any suggestions will be a gr8 help as I am running against a deadline...
All the varaibles are declared fine. Infact the code compliles when I just remove the "FOR READ ONLY" clause.

Thanks in advance...

Shekhar.

The smileys in the code snippet are actually ': o' without the space in between.

Sorry abt that. Didnt realize it will pick up the smiley for that.....


Shekhar.

don't use the token "FOR READ ONLY" or "FOR FETCH ONLY" if you use a
SELECT ... INTO ...

that tokens may only be used in combination with an declare cursor

simply omit that and code:
EXEC SQL SELECT order_number, type, status, value(completion_date, 0), due_date
INTO :ord_num, :ord_type, :ord_status, :ord_completion_date, :ord_due_date
FROM order
WHERE :order_id = order_id WITH UR;

Thanks Umayer for the response. Thats what I did in the end. Removed the "For READ ONLY" clause where ever I was using the INTO without explicit cursor declaration.

Just to confirm, does this mean, that while reading the data from this transaction(lets call A), if anyother transaction(B) is tryng to change the row, will the tables be locked until A is completed.

If so can the locks be avoided by explicit cursor declaration along with FOR READ ONLY clause?

Thanks

Shekhar

The FOR READ ONLY clause makes sense only when you need to declare a cursor and you want it not be ambiguous. DB2 adjusts locking and fetching for non-ambiguous cursors

Ambiguous cursor is the one when DB2 cannot determine up front what the intention of the application is , i.e., will the appl be performing updates from the same cursor or only selects.

In your case, as you are not declaring a cursor, FOR READ ONLY is meaningless and hence has been excluded from the SELECT ... INTO Syntax support ...

As you are using with UR, this statement will not cause another application to wait for this statement to complete ... I'm sure you will be aware that by using UR, you may be reading un-committed data ... ie, when your SELECT stmt is executing, if application A has updated the order table with a completion date(but not committed), your SELECT statement will read the new updated values and not the old one ... Subsequently if appl A issues a ROLLBACK (ie the order is not completed), your SELECT Statement would have read the wrong value and assumed that the order is complete ...

IMHO, for your SELECT Statement, CS is the most appropriate isolation level ... Assuming you have indexed on ORDER_ID and have done a RUNSTATS, CS is unlikely to cause any undesirable locking ....

HTH

Sathyaram

Thanks Sathyaram,

your answer has cleared quite a few hazy thoughts i had about isolation levels and how to manage them in code.

Thanks once again!

Regards

Shekhar.

Thanks Sathyaram,

Your information was indeed helpful.

Would request you to solve the following too:

1. If in a declare cursor, "for fetch only" clause is not specified , then by default , is it what you called ambiguous?

2. Suppose I have to read a cursor, and after fetch, update the same read cursor. For that do I have to use " for update of " clause?

3. For cursor declare, are "for fetch only" and 'for read only", same?

Dipak, This thread should ansewer all your questions :

What is FOR FETCH ONLY parameter