Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Question Unanswered: For Read Only With Ur

    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_date,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.

  2. #2
    Join Date
    Feb 2006
    Posts
    4

    Red face ignore the smileys pls.

    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.

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    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;

  4. #4
    Join Date
    Feb 2006
    Posts
    4

    Arrow Thanks Umayer

    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2006
    Posts
    4

    Thumbs up Thanks 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.

  7. #7
    Join Date
    Mar 2010
    Location
    Kolkata
    Posts
    1
    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?

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Dipak, This thread should ansewer all your questions :

    http://www.dbforums.com/db2/725190-w...parameter.html
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •