Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    1,595

    What is FOR FETCH ONLY parameter

    Hi,

    I am using DB2 v7.2 on Windows 2000.

    According to the documentation "Administration Guide Performance" the FOR FETCH ONLY parameter after select statement

    should benefit the retrive performance, because of minimal locking. Does anybody know why is locking so minimal? What is the diference betwen WITH UR parameter after select statements with also has something to do with locking.

    Thanks,
    Grofaty

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    UR stays for Uncommited Read. If you use it, you may get some pieces of information that are "out of date". DB2 will not lock the pages you're reading.
    With FOR FETCH ONLY, you're saying to DB2, that you do not intend to update the rows you are reading. DB2 will try to avoid locking the pages you are reading, but will only give you "clean" pages, i.e., pages that are allready commited.

    HTH,
    Rodney Krick

  3. #3
    Join Date
    Jan 2003
    Posts
    1,595
    Hi,

    Thank you RKrick. It helps me very much.

    Grofaty

    Originally posted by RKrick
    UR stays for Uncommited Read. If you use it, you may get some pieces of information that are "out of date". DB2 will not lock the pages you're reading.
    With FOR FETCH ONLY, you're saying to DB2, that you do not intend to update the rows you are reading. DB2 will try to avoid locking the pages you are reading, but will only give you "clean" pages, i.e., pages that are allready commited.

    HTH,

  4. #4
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37

    Re: What is FOR FETCH ONLY parameter

    Please note the FOR FETCH|READ ONLY and the FOR UPDATE OF ... clauses are ignored for dynamic statements resulting in the creation of ambiguous cursors regardless of their presence. So if you are expecting any performance increase in such environments, you may be in for a bit of surprise.

    Cheers,

    Julius

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649

    Re: What is FOR FETCH ONLY parameter

    Thanks for the info ... It is interesting ....

    Do you have a pointer to the IBM manuals which explain this bit for dynamic statements ...

    Cheers

    Sathyaram


    Originally posted by jsasvari
    Please note the FOR FETCH|READ ONLY and the FOR UPDATE OF ... clauses are ignored for dynamic statements resulting in the creation of ambiguous cursors regardless of their presence. So if you are expecting any performance increase in such environments, you may be in for a bit of surprise.

    Cheers,

    Julius

  6. #6
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37

    Re: What is FOR FETCH ONLY parameter

    The following is extracted from the redbook "Squeezing the Most Out of Dynamic SQL with DB2 for z/OS and OS/390" section 10.2 Ambiguous cursors. I hope it helps.

    Cheers,

    Julius
    ----

    A cursor is ambiguous if:
     It is not defined with the clauses:
    FOR FETCH ONLY or FOR READ ONLY. If these clauses are present, it is a read-only cursor.
    FOR UPDATE OF. This makes the cursor updatable.
     It is not defined on a read-only result table (which would make it a read-only cursor).
     It is not the target of a WHERE CURRENT clause on an SQL UPDATE or DELETE statement. (This would make the cursor updatable.)
     It is in a plan or package that contains the SQL statements PREPARE or EXECUTE IMMEDIATE:
    The presence of any dynamic SQL causes an ambiguous cursors, because DB2 cannot detect what follows in the program.
    A cursor can appear to be read-only, but a dynamic SQL statement could modify data through the cursor. Therefore the cursor is ambiguous.

  7. #7
    Join Date
    Apr 2003
    Posts
    3
    Grofaty is using DB2 for UDB. DB2 for UDB and DB2 for z/OS don't always work the same way. So here's a another opinion (which is worth what it cost).

    The DB2 UDB version 7 SQL reference has the following explanation of the FOR READ ONLY clause. It seems to indicate that it is effective in dynamic SQL.

    ---------------------------------------------
    The FOR READ ONLY clause indicates that the result table is read-only and therefore the cursor cannot be referred to in Positioned UPDATE and DELETE statements. FOR FETCH ONLY has the same meaning.

    Some result tables are read-only by nature. (For example, a table based on a read-only view.) FOR READ ONLY can still be specified for such tables, but the specification has no effect.

    For result tables in which updates and deletes are allowed, specifying FOR READ ONLY (or FOR FETCH ONLY) can possibly improve the performance of FETCH operations by allowing the database manager to do blocking and avoid exclusive locks. For example, in programs that contain dynamic SQL statements without the FOR READ ONLY or ORDER BY clause, the database manager might open cursors as if the FOR UPDATE clause was specified. It is recommended, therefore, that the FOR READ ONLY clause be used to improve performance except in cases where queries will be used in a Positioned UPDATE or DELETE statements.

    A read-only result table must not be referred to in a Positioned UPDATE or DELETE statement, whether it is read-only by nature or specified as FOR READ ONLY (FOR FETCH ONLY). See DECLARE CURSOR for more information about read-only and updatable cursors.
    ---------------------------------------------

    In the DECLARE CURSOR explanation is the following note

    ---------------------------------------------
    A cursor is ambiguous if all of the following are true:

    the select-statement is dynamically prepared
    the select-statement does not include either the FOR READ ONLY clause or the FOR UPDATE clause
    the LANGLEVEL bind option is SAA1
    the cursor otherwise satisfies the conditions of a deletable cursor.
    An ambiguous cursor is considered read-only if the BLOCKING bind option is ALL, otherwise it is considered deletable.
    ----------------------------------------------

    From these two explanations, I would say that in DB2 UDB the FOR READ ONLY clause is useful in both static and dynamic SQL. This, however, depends on one small word--"all". If DB2 meant to say "A cursor is ambiguous if ANY fo the following are true:" then jsasvari may be correct. (How's that for hedging?)

    Plumb

Posting Permissions

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