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
FROM order WHERE rder_id = order_id FOR READ ONLY WITH UR;
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>".
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.
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
WHERE :order_id = order_id WITH UR;
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 ....
Visit the new-look IDUG Website , register to gain access to the excellent content.