Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2010
    Posts
    94

    Unanswered: DB2 snapshots v/s db2top

    Hi Gurus,

    Recently, i got 'cornered' as my apps SQLs were deemed extremely resource intensive - reason: DB2 snapshots performed periodically listed my SQLs performing the following join

    Table A (with 28 million ) inner join Table B (with 10 K ) on A1 = B1

    to process 140 billion records

    A1 and B1 are not a part of any index - besides, these are full table joins, no predicate - so not sure if indexes would have done any good. I know Tables A and B are new to the environment, and the stats were updated only table A, not on table B (the smaller table).

    My question is - does DB2 perform a cartesian product when it sees stats are out dated?

    Also, while the DB2 snapshot got everyone calling me - when I ran a DB2TOP, I saw that the SQL handle was processing the same number of rows (as listed in the snapshot), but the memory , CPU and IO figures were all low!!!!!! (< 1%)

    What does that mean?

    Any insights, like always, are very much appreciated!!!

  2. #2
    Join Date
    Nov 2011
    Posts
    8
    You can use db2advis tool to get advisement on whether or not to build the index.

    DB2 use three method to perform the table join: Nested Loop join(can use index) ,Merge join and Hash join. DB2 optimizer will choose one method according to the statistics(whether or not they are out of date) .You can use db2expln tool to see which join method db2 used.
    Last edited by vesli; 11-07-11 at 21:36.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by getback0 View Post

    My question is - does DB2 perform a cartesian product when it sees stats are out dated?
    To this the answer is "no". If the query semantics don't ask for a cartesian join, no sane database management system would perform a cartesian join.

    As for your other claims, for which you don't provide any supporting evidence, I'm afraid all mind readers are out on vacation so you'll have to wait for them to come back.

  4. #4
    Join Date
    Oct 2010
    Posts
    94
    aint talking sane database management systems here funny pants! ... talking db2!

    and for supporting evidence, I'd say, based on the data, typically joins between a 28 million record table and a 10,000 record table would result in a little more than 28 million record (like 40 - 60 million) assuming a 1:1.5 cardinality - that is not in the same ballpark as 140 billion records - for detailed evidence, ask an 11-year old for his elementary math text - he should be able to guide you!
    Last edited by getback0; 11-07-11 at 22:10.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by getback0 View Post
    aint talking sane database management systems here funny pants! ... talking db2!

    and for supporting evidence, I'd say, based on the data, typically joins between a 28 million record table and a 10,000 record table would result in a little more than 28 million record (like 40 - 60 million) assuming a 1:1.2 cardinality - for detailed evidence, ask an 11-year old for his elementary math text - he should be able to guide you!
    If you join two tables and end up with a Cartesian product, then you did not join them on the correct columns (or the relationship between the two tables is not what you think it is).

    You probably should create indexes on the join columns. Specifying join columns is equivalent to putting the join condition in the predicate.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by getback0 View Post
    typically joins between a 28 million record table and a 10,000 record table would result in a little more than 28 million record (like 40 - 60 million) assuming a 1:1.5 cardinality
    Thank you. This makes it to my "quote of the week" list. And it's not even Tuesday!

  7. #7
    Join Date
    Oct 2010
    Posts
    94
    Thanks Marcus - I did verify the following -

    1. the join clause does use the right columns (of both tables) i.e. based on relationships
    2. based on the join, the join output should have been 27 million records

    With that knowledge then,

    Why are the snapshots and db2top reporting 141 billion records being processed for the join SQL? (This is where I started speculated a cartesian)

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by getback0 View Post
    Thanks Marcus - I did verify the following -

    1. the join clause does use the right columns (of both tables) i.e. based on relationships
    2. based on the join, the join output should have been 27 million records

    With that knowledge then,

    Why are the snapshots and db2top reporting 141 billion records being processed for the join SQL? (This is where I started speculated a cartesian)
    I assume that it is doing a table scan on one of the tables each time a join is done. The monitor you are looking at probably just shows the number of rows actually read based on having to do so many table scans.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Oct 2010
    Posts
    94
    So what the snapshots reporting as records read, are really records traversed in the table scan for one table - not true join output records.

    What was also interesting was - db2top reported CPU usage for this appl. handle to be 0%. What does that mean??

  10. #10
    Join Date
    Oct 2010
    Posts
    94
    Quote Originally Posted by n_i View Post
    Thank you. This makes it to my "quote of the week" list. And it's not even Tuesday!
    really - how fun!!!! dude, you really need help!

Posting Permissions

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