Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: Different data on same row..

    I use DB2 Connect on Solaris to access a DB2 database on a AS/400.
    The version of DB2 on the AS/400 is: OS/400 5.1.1

    I have a long running query (20-35min) and once in a while I get 2 rows back from the same table from the same row.

    Example:

    Table: A Row: 50

    Brings back:
    1. Schedule Date: Oct 15th Comment: Customer has dog
    2. Schedule Date: Oct 25th Comment: Customer has attack dog

    While my query is running, someone is changing the data and my query
    brings back 2 of these rows.

    After doing some research (I'm pretty new to DB2) I think it has something to do with Isolation Levels but I don't know much more. I'm connecting and accessing data using IBM's DB2 Connect Product via JDBC.

    Any help would be appreciated.

    Thanks,
    Eric

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you use isolation level RR (repeatable read) it should prevent anyone from changing a row that your query has already read (until your query is finished or does a commit). Obviously, this limits the concurrency (ability of others to update while you are running your query).

  3. #3
    Join Date
    Sep 2003
    Posts
    5

    Read level consistancy..

    So, nobody can change this record while I'm querying it? I come from the Oracle world where they have read level consistancy. Meaning when I query the table, I get what the data is at the point in time then move on...

    I guess I'm at a loss why my query would read this table twice if I'm just going there once to get the data. Thanks for the feedback.

    Originally posted by Marcus_A
    If you use isolation level RR (repeatable read) it should prevent anyone from changing a row that your query has already read (until your query is finished or does a commit). Obviously, this limits the concurrency (ability of others to update while you are running your query).

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 doesn't read the table twice, but if it reads the data "in place" (if it does not have to materialize the answer set into a temporary table) then it might find the row in a new location (if changed) and end up reading that row twice. This would not happen with RR (which I assume is same as Oracle's read consistency), which would prevent the row from being updated until the query was finished or committed.

    Note that if the SQLwas complex enough that DB2 had to create a temporary table to satisfy the query, this probably would not have happened.

  5. #5
    Join Date
    Sep 2003
    Posts
    5
    The only thing I can't find in my research is if I run RR, does the rows get locked just while I'm reading them, or are they locked for the entire time of the query?

    Originally posted by Marcus_A
    DB2 doesn't read the table twice, but if it reads the data "in place" (if it does not have to materialize the answer set into a temporary table) then it might find the row in a new location (if changed) and end up reading that row twice. This would not happen with RR (which I assume is same as Oracle's read consistency), which would prevent the row from being updated until the query was finished or committed.

    Note that if the SQLwas complex enough that DB2 had to create a temporary table to satisfy the query, this probably would not have happened.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    My guess is it will be locked for the entire time of the query ...

    Because, you tell db2 to make the query RR and not any individual table ..

    There could be different thoughts

    Cheers
    Sathyaram

    Originally posted by natter
    The only thing I can't find in my research is if I run RR, does the rows get locked just while I'm reading them, or are they locked for the entire time of the query?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For RR, the rows are locked (preventing updates, but allowing other reads) for the length of the query.

Posting Permissions

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