Queries and DB2

[rstockton]

When queries are done against the DB2 database do they put a hold or lock on the required area of the DB2 table.  We have a situation where muiltiple queries are done for data with the same date and similiar or even identical index information, and we are experiencing backups on the queries.  The query time goes from .010 to 100.00+ seconds and spikes the CPU during the queries.  We also load about 3 million documents to the AG per day.

Thanks,

Russell

[Justin Derrick]

Queries don't lock tables.

You may need to (dramatically) increase the number of rows per table segment though.

If your table segment size is 10M rows, you're filling a table every 3 to 4 days.  That means then when you query the AG for just one month's worth of data, you're probably searching 10 tables, generating temporary tables with intermediate results, etc. etc.  If you're searching for a year, then you're searching 120 tables for a handful of results.

By increasing the table segment size to 120 million rows, searches for the previous month could hit just one table.  Whole-year searches could hit as few as 9 tables, dramatically reducing the overhead involved.

-JD.