Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    11

    Unanswered: How to alow dirty read in select query in db2

    In our application we want to do the dirty read ( read_uncommited) in some of the select queries where the output of the query is not important. But at the same time we want to avoid any possible locking while read.
    For eg.
    Just to check whether table exists or not , we are using
    "select count(*) from tableName"
    Now here we can allow dirty read to avoid any db locking.

    In MSSQLServer db, this query can be written
    "select count(*) from tableName with (NOLOCK)"
    in sybase it can be written
    "select count(*) from tableName AT ISOLATION 0"

    What is the equivalent for the same in IBM-db2 database ?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to alow dirty read in select query in db2

    WITH UR

    like

    select * from table1 WITH UR

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2003
    Posts
    11

    Re: How to alow dirty read in select query in db2

    Thank you very much ,

    One more query related to lockhint ...

    What is the equivalent to "READPAST" lockhint of MSSQLServer/Sybase in IBM-DB2 ?



    Originally posted by sathyaram_s
    WITH UR

    like

    select * from table1 WITH UR

    HTH

    Sathyaram

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to alow dirty read in select query in db2

    Sorry ... I have no idea what READPAST mean ...

    Can you explain please

    Sathyaram


    Originally posted by sangramd
    Thank you very much ,

    One more query related to lockhint ...

    What is the equivalent to "READPAST" lockhint of MSSQLServer/Sybase in IBM-DB2 ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2003
    Posts
    11

    Re: How to alow dirty read in select query in db2

    Readpast lockhint guarantees to give only committed records
    but will automatically skip records that are being
    inserted or updated.

    This way we can avoid any possible locking while select and also make sure that dirty reads are not happening.

    For eg,
    I have 2 parallel Tx , in one tx i'm updating the row 5 and row 7 and in second tx i'm doing the select from row 1 to row 10.
    Without readpast , select query will be locked untill update is over, but with readpast, it will give me 8 rows skipping 2 rows being updated.

    SQLServer and Sybase has this facility in which lock is obtained while read as well.
    Is there anything similar we can use for DB2, because I have seen in DB2 as well in such scenarios locking will happen.

    Originally posted by sathyaram_s
    Sorry ... I have no idea what READPAST mean ...

    Can you explain please

    Sathyaram

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: How to alow dirty read in select query in db2

    Originally posted by sangramd
    Readpast lockhint guarantees to give only committed records
    but will automatically skip records that are being
    inserted or updated.

    This way we can avoid any possible locking while select and also make sure that dirty reads are not happening.

    For eg,
    I have 2 parallel Tx , in one tx i'm updating the row 5 and row 7 and in second tx i'm doing the select from row 1 to row 10.
    Without readpast , select query will be locked untill update is over, but with readpast, it will give me 8 rows skipping 2 rows being updated.

    No I don't think you can do it in DB2.

Posting Permissions

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