Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    47

    Unanswered: How to avoid contention on the database server

    I have a table called Balance with the following columns.
    452 CHARACTER 2 FIRM_NBR 8
    452 CHARACTER 8 ACCT_NBR 8
    452 CHARACTER 1 ACCT_TYPE 9
    485 DECIMAL 15, 2 TRADE_BALANCE 13
    & some more colums

    Each Customer(ACCT_NBR) may have 1 to 5 records for example
    firm nbr acct nbr acct type trade balance
    10 11111111 1 100000
    10 11111111 2 200000
    10 11111111 3 200000
    10 11111111 c 200000
    10 11111111 M 200000

    This table contains 4 to 5 Million Records.

    I have a requirement to pull all the accounts having sum(TRADE_BALANCE) > ?(may be $100)

    SQL:

    select acct_nbr from balance group by acct_nbr having sum(TRADE_BALANCE) > 100

    SQLSome times)

    select acct_nbr from balance group by acct_nbr having sum(TRADE_BALANCE) > 100
    and acct_nbr(list of acct_nbrs from some other table)


    There will be 15000 users trying to get list of accounts with different sum(TRADE_BALANCE) value on the web.

    This approach is causing contention on the database because of huge amount of data. I came to know that in db2 even select makes shared locks on the table.

    I am thinking of implementing a table or view or some other mechanism having account number, sum(TRADE_BALANCE) of the account number as a batch process.


    Any suggestions are wellcome.
    p.srinivasarao

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Share locks are used by DB2 for select statements, but they do not cause contention with eachother (other share locks). Are you doing any inserts, updates, or deletes on the table while the selects are being done?
    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
    Jan 2003
    Posts
    47
    I was told by some DBA's, If select criteria fetches large size of data then db2 uses exclusive locks instead of shared locks on the table.

    And sql also uses group by and having which leads to
    table scan instead of index scan. Thats why I am thinking
    of creating a table having account number,balance and populating the data from balance table. Then I can use simple sql to fetch data from the new table.
    p.srinivasarao

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Share locks may escalate from row level to table level, but they are still share locks and do not block other share locks (even at the table level).

    Please answer my original question. Are there any inserts, updates, or deletes happening when the select SQL statements are submitted?
    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 2001
    Location
    UK
    Posts
    4,650
    I guess you are thinking of something like MQT.

    But Marcus_A's question on IUDs need an answer for MQTs as well

    HTH

    Sathyaram

    I am thinking of implementing a table or view or some other mechanism having account number, sum(TRADE_BALANCE) of the account number as a batch process.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2003
    Posts
    47
    There won't be any inserts/delete/updates during intraday.
    At night we load the table with new data. Because I am a java developer I am not familiar with db2 buzz words. What do you mean by MQT?
    p.srinivasarao

  7. #7
    Join Date
    Jan 2003
    Posts
    47
    We are using UDBDB2 7.2.9
    p.srinivasarao

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since there are no inserts, updates, or deletes intraday, then there is no lock contention. Share locks do not block other share locks, even if table scans are used.

    If you are talking about disk contention, memory contention, or other resource contention that happens when a lot of queries are doing table scans, that is a totally different issue than lock contention.
    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
    Dec 2005
    Posts
    18
    MQT - Materialized query tables

    you can use, select .... with UR

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ON 7.2, there were no MQTs ... Search for AST (Automatic Summary Tables).
    in the docs.


    Define ASTs with REFRESH DEFERRED so that you can refresh after your entire overnight load of the table is completed.

    You do NOT need to change your appl. DB2 Optimizer is intelligent enough to pick up the AST even if you refer to the base table.

    In the above discussion, I'm assuming you are using dynamic SQL.



    HTH

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

Posting Permissions

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