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.

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?

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.

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?

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

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?

We are using UDBDB2 7.2.9

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.

MQT - Materialized query tables

you can use, select .... with UR

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