Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2009
    Posts
    13

    Unanswered: Is this DB2 bug? Using "OR" or "IN"

    I created table following way.
    TABLE - TAB1(ID is primary key)
    ID DEPT
    1 1
    2 2
    3 3
    4 4
    5 5

    In Session1, I executed following cmd with auto commit off.
    db2 +c "Delete from TAB1 where ID = 3"
    so it holds X lock on 3rd row.

    In session2, I try to execute db2 "select * from TAB1 where ID IN(1,2)"
    it goes into wait state and requesting S lock on row3. eventhough my intension is read only 1 and 2 rows.

    Is this DB2 bug? It looks like DB2 defect. Can somebody please confirm?
    If it is DB2 defect what is the workaround for this? If it's not a DB2 defect why DB2 is behaving like this?

    Thanks,

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, it is not a bug. If DB2 did a table scan on the table (instead of using an index to go straight to rows 1 and 3) because the table was small and a table scan was more efficient, then it would wait on a lock on row 2 while doing the scan.

    There are some ways to get around this:

    1. Alter the table to volatile, and DB2 will strongly encourage use the PK index to retrieve the row isntead of a table scan. Of course, with Index "OR'ing" because of the IN clause, the threshold of when DB2 will use the index (instead of table scan) is usually set higher.

    2. Set the following environment variables and restart the instance:

    db2set DB2_EVALUNCOMMITTED=ON

    While you are at it, do the following also:

    db2set DB2_SKIPDELETED=ON
    db2set DB2_SKIPINSERTED=ON

    3. Switch to DB2 V9.7 which has a new default setting for Currently Committed semantics that modifies how CS isolation level works and would help your situation. This is set by the DB CFG parm called "cur_commit", which is set ON by default for new databases in 9.7 and can be turned on for databases upgraded to 9.7. You can also set cur_commit to AVAILABLE in which case you need to explicitly request for currently committed behavior to see the results that are currently committed.

    4. If you still are encountering problems with Option 1 above due to index OR'ing with the IN clause, and you don't want to change any DB2 environment variables or switch to 9.7, then the following SQL statement might help encourage index usage in the second query:

    select * from TAB1 where ID = 1
    UNION ALL
    select * from TAB1 where ID = 2

    This is particularly useful if you have two different predicate variable that you checking with an OR, such as "WHERE ID = 1 or NAME = 'JONES' ".
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2009
    Posts
    13
    Hi Fedman,
    Thank you very much for your reply. Now I understood why DB2 is scanning table as oppose to use index.

    As you suggested, I tried options.

    1. Alter table volatile:
    DB2 is taking more time to return data as you mentioned.
    We can't use this option at this time because there are 3000 tables in our product.

    2. Setting registry varialbes:
    We don't want to set DB2EVALUncomitted because we don't wnat to wrok on uncomiited data.

    3. DB2 9.7: We can't move to DB9.7 at this time because we released product and thousands of customers are using our product.

    4. UNION ALL: We can't change queries at this time because there are thousands of queries.

    I think first option is the only reasonble option at this time.

    You said DB2 goes for table scan because my table is tiny. Do you know when it goes for Index scan? How big data needs to be insert into the table to see index scan?
    We are desparate to find some good solution because our customers are suffering with lock waits, and we thought by default DB2 goes for index rather than table scan.

    Thanks,

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    1. DB2 would not usually be taking more time to retrieve data from tables set to volatile. Certainly not for the queries you provided in the example. It "could" affect some other queries, but even that is doubtful unless the tables are large. How do you know this is a problem? Setting a table to volatile encourages index usuage, so it should not slow anything down.

    2. You would not be "working on" uncommitted data. What this registry variable would do would allow queries to exclude rows for consideration by evaluationg them even though they were uncommitted, but if the row qualifies then it would wait for the lock to be released. It is very unlikely that this would cause any problem with your application, unless the application has implemented its own locking scheme using DB2 tables (instead of using DB2 locking). You should probably spend some more time understanding this, and which very limited situations it might cause a problem.

    3. DB2 9.7 was designed to be compatible with Oracle applications, which are often not written for high concurrency. If the other options don't work, then consider it.

    4. Another option is to use WITH UR on the queries where uncommitted read is acceptable. This is usually 99% of the time for most applications. Keep in mind that you will never be able to read partially updated rows, just uncommitted rows where the integrity of the row is intact, but not necessarily the entire UOW.

    DB2 chooses the access path that is fastest. It is sometimes faster to read all the rows from the table pages and ignore the index even when an index is present. When it is faster depends on a number of factors that DB2 uses, such as a guess on whether the data will be in the buffer pool, how fast your disk is, and how fast your CPU is (notice that these last two parms are stored in the tablespace definition and DB2 will calculate them for you if they are omitted when the tablespace is created).

    If Option 1 is not working (which would be unusual), then do the following:

    1. run the following runstats on all tables

    runstats on table <table-name> with distribution on key columns and indexes all

    2. update the syscat.tables column for all your tables as follows:

    update syscat.tables set card = 1000000 where tabschema = 'XXXXX' (where XXXXX is your schema name). You will have to repeat this update after every time you do a runstats (or don't do any more runstats after steps 1 & 2.)

    If the above doesn't work, you have some unusual problems that requires an experience DB2 expert to come in and take a closer look at your system.

    One other thing: If you don't know where the lockwaits are happening, then you should set LOCKTIMEOUT to 20 seconds (or less) on the db cfg, and then do the following:

    • db2set DB2_CAPTURE_LOCKTIMEOUT=ON and restart DB2.
    • create event monitor xxxxxxx for deadlocks with details history write to file path /xxxxxx (if you already have an existing deadlock event monitor, delete it first).

    When you get a locktimeout, the participant locktimeout information will be written to the path above. It is possible you only have locking issues in one or a few limited areas that could be fixed easily with better coding techniques.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2009
    Posts
    13
    Thank you very much for detailed explanation.
    I used DB2 explain to meassure cost on sql with and without volatile.
    Report shows Estimated Cost as 30.215302 with volatile and 7.583225 without volatile.

    I used following commands.
    1. alter table T3 volatile;
    2. runstats on table T3 with distribution on key columns and indexes all
    3. update syscat.tables set card = 1000000 where tabschema = 'SCHEMA1' .
    4. db2expln -database db -stmtfile 1.sql -terminator @ -terminal
    1.sql = select * from T3 where id in (1,2)@

    DB2expln Report with Volatile:

    DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool

    DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007

    Licensed Material - Program Property of IBM

    IBM DB2 Universal Database SQL and XQUERY Explain Tool



    ******************** DYNAMIC ***************************************



    ==================== STATEMENT ==========================================



    Isolation Level = Cursor Stability

    Blocking = Block Unambiguous Cursors

    Query Optimization Class = 5



    Partition Parallel = No

    Intra-Partition Parallel = No



    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",

    "SCHEMA1"





    Statement:



    select *

    from T3

    where id in(1, 2)





    Section Code Page = 1208



    Estimated Cost = 30.215302

    Estimated Cardinality = 2.000000



    Access Table Name = SCHEMA1.T3 ID = 2,8722

    | Index Scan: Name = SYSIBM.SQL090818150832840 ID = 1

    | | Regular Index (Not Clustered)

    | | Index Columns:

    | | | 1: ID (Ascending)

    | #Columns = 0

    | Volatile Cardinality

    | Fully Qualified Unique Key

    | #Key Columns = 1

    | | Start Key: Inclusive Value

    | | | | 1: 1

    | | Stop Key: Inclusive Value

    | | | | 1: 1

    | Index-Only Access

    | Index Prefetch: None

    | Isolation Level: Uncommitted Read

    | Lock Intents

    | | Table: Intent None

    | | Row : None

    | Sargable Index Predicate(s)

    | | Insert Into Sorted Temp Table ID = t1

    | | | #Columns = 1

    | | | #Sort Key Columns = 1

    | | | | Key 1: (Ascending)

    | | | Sortheap Allocation Parameters:

    | | | | #Rows = 2.000000

    | | | | Row Width = 20

    | | | Piped

    | | | Duplicate Elimination

    Sorted Temp Table Completion ID = t1

    Access Table Name = SCHEMA1.T3 ID = 2,8722

    | Index Scan: Name = SYSIBM.SQL090818150832840 ID = 1

    | | Regular Index (Not Clustered)

    | | Index Columns:

    | | | 1: ID (Ascending)

    | #Columns = 0

    | Volatile Cardinality

    | Fully Qualified Unique Key

    | #Key Columns = 1

    | | Start Key: Inclusive Value

    | | | | 1: 2

    | | Stop Key: Inclusive Value

    | | | | 1: 2

    | Index-Only Access

    | Index Prefetch: None

    | Isolation Level: Uncommitted Read

    | Lock Intents

    | | Table: Intent None

    | | Row : None

    | Sargable Index Predicate(s)

    | | Insert Into Sorted Temp Table ID = t1

    | | | #Columns = 1

    Sorted Temp Table Completion ID = t1

    Index ORing Preparation

    | Access Table Name = SCHEMA1.T3 ID = 2,8722

    | | #Columns = 2

    | | Volatile Cardinality

    | | Avoid Locking Committed Data

    | | Evaluate Block/Data Predicates Before Locking Committed Row

    | | Fetch Using Prefetched List

    | | | Prefetch: 1 Pages

    | | Lock Intents

    | | | Table: Intent Share

    | | | Row : Next Key Share

    | | Sargable Predicate(s)

    | | | #Predicates = 1

    | | | Return Data to Application

    | | | | #Columns = 2

    Return Data Completion



    End of section







    Report as follows without Volatile:

    DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL and XQUERY Explain Tool

    DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007

    Licensed Material - Program Property of IBM

    IBM DB2 Universal Database SQL and XQUERY Explain Tool



    ******************** DYNAMIC ***************************************



    ==================== STATEMENT ==========================================



    Isolation Level = Cursor Stability

    Blocking = Block Unambiguous Cursors

    Query Optimization Class = 5



    Partition Parallel = No

    Intra-Partition Parallel = No



    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",

    "SCHEMA1"





    Statement:



    select *

    from T3

    where id in (1, 2)





    Section Code Page = 1208



    Estimated Cost = 7.583225

    Estimated Cardinality = 2.000000



    Table Constructor

    | 2-Row(s)

    Nested Loop Join

    | Access Table Name = SCHEMA1.T5 ID = 2,8724

    | | Index Scan: Name = SYSIBM.SQL090822150204150 ID = 1

    | | | Regular Index (Not Clustered)

    | | | Index Columns:

    | | | | 1: ID (Ascending)

    | | #Columns = 2

    | | Single Record

    | | Fully Qualified Unique Key

    | | #Key Columns = 1

    | | | Start Key: Inclusive Value

    | | | | | 1: ?

    | | | Stop Key: Inclusive Value

    | | | | | 1: ?

    | | Data Prefetch: Eligible 0

    | | Index Prefetch: None

    | | Lock Intents

    | | | Table: Intent Share

    | | | Row : Next Key Share

    Return Data to Application

    | #Columns = 2



    End of section

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The difference between relative cost of 7 and 30 is probably not even measurable. Anything less than 50 is extremely fast. You should run some snapshots for dynamic SQL if you want to see the actual elapsed time of these SQL statements.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mowry_889
    I used following commands.
    1. alter table T3 volatile;
    2. runstats on table T3 with distribution on key columns and indexes all
    3. update syscat.tables set card = 1000000 where tabschema = 'SCHEMA1' .
    Keep in mind that with step 3 you updated the card for every table in your application (I presume) with a schema name of 'SCHEMA1'.

    But with step 2 you apparently only ran runstats on the one table.

    You should do steps 2 and 3 for specific tables or all tables in your applicaiton.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Aug 2009
    Posts
    13
    I changed all my tables to volatile, and I restarted DB2 instance also, but I still see the same problem. It does table scan as oppose to Index scan.

    My table has 270 rows, and also I checked Volatile column on my table from syscat.tables. It shows 'C'.

    I am not sure why volatile is not working. I am confused now.

    Thanks,

  9. #9
    Join Date
    Aug 2009
    Posts
    13
    I haven't run runstats and set card = 1000000. I altered table as Volatile.
    I thought this will fix my problem. I can't rely on runstats and update set cars=1000000 because Customers can run runstats any time. As you said whenever we run runstats, we need to update card also, but once product with customers, they may not run set card.

    Thanks,

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Do the runstats and the alter table to volatile. There is no problem about what order you do them in. You always should run runstats (the version of the command I suggested above, not just any runstats) at least once for a table or after any index changes.

    You can skip the update of the card column.

    Just to verify the access plan with volatile, you might want to try the UNION ALL version I gave you. Using an IN (which is the same as OR) can be tricky on tables with small number of rows and if DB2 thinks there are a small number of unique values in the indexed column.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2009
    Posts
    13
    Based on my understanding If I change my tables to volatile and run runstats with the version you gave me should be good enough for all cases. Is it right?
    I tried above, and it looks ok with IN clause.

    After doing above steps, do you think in any case DB2 fails to go for index scan if there is already an index on the table whether it's a samll or large table with IN clause?

    Thank you for your valubale suggestions!

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The DB2 optimizer chooses the fastest access path (at least it tries to, and usually does a very good job). In your case, you want to fool the optimizer to choose a different path for better concurrency, and it is hard to say with 100% certainty what DB2 will do in every case when you try and fool it. But usually altering the table to volatile will encourage index use, even on a small table, which can improve concurrency.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Aug 2009
    Posts
    13
    I alteted all my tables to volatile in my application and runstats on all tables.
    It started working fine, but parameters in my IN clause goes certain limit, DB2 goes for table scan again.
    So I tried on my sample tab(ID int primary key, dept int). I inserted 10,000 rows. When I used 47 ids in my IN clause, it's returning data, but if it goes beyond 47 ids(select * from id where id in(1,2....,48), it's waiting for other transaction to be completed.

    I am very disappointed with this behavior. Since my apllication transactions are very long, other transcation needs to wait for many minutes. This one proves DB2 is giving 0 concurrency. One user can work on DB2 database at one time.

    I tesed DB2 9.7, and this is the behavior we are expecting though I haven't run all my scenarios.
    Is there any patch we can install on DB2 9.5 to behave same way as DB2 9.7?

    or is there any other method we can follow to achieve DB2 9.7 behavior on DB2 9.5?

    Thank you very much for your all responses. Your responses help me to move fwd.

    Thanks
    Last edited by mowry_889; 08-28-09 at 21:24.

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Having more than 47 values in the IN clause and expecting DB2 to use an index is asking too much IMO. It sounds to me this may be a theoretical objection, as opposed to a real world problem. How many times does this really happen?

    Maybe you should think about changing the application program to use something other than the IN clause if their are really more than 47 values. That could include putting the values in a table (possibly a temp table) and then DB2 would probably do a join on the two tables.

    I don't believe there are any DB2 level changes that can be made to duplicate the cur_commit parm behavior in 9.7 over and above what has been discussed above. What 9.7 does with cur_commit on (it is "on" by default for newly created databases in 9.7) is to have an application waiting on a lock go to the transaction log to get the value of the row before it was updated or deleted. There is extra overhead with this (just like there is a lot of extra overhead in Oracle for read consistency), especially if the data is no longer in the Log Buffer and disk access is required. Also, the data from the log is older than would be the case if you waited for the transaction to finish (same with Oracle read consistency), so in some applications it might be better to just wait on the locks (assuming the wait time is reasonable).

    The better solution is to hire some decent programmers who know how to close transactions and issue commits on a timely basis. In all likelihood there are only a few offending situations where the developers have completely screwed up. You can trap these in DB2 by doing a locktimeout analysis as described here:
    DB2 Database for Linux, UNIX, and Windows
    New options for analyzing lock timeouts in DB2 9.5

    It is true that databases like Oracle have better concurrency (prior to DB2 9.7) when you have brain dead developers, which seem to be the norm these days. Your application will run much faster if your company cleans up the application code, regardless of what database you use.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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