1

I'm hoping to catch the eye of someone with experience in both SQL Server and DB2. I thought I'd ask to see if anyone could comment on these from the top of their head. The following is a list of features with SQL Server, that I'd like to do with DB2 as well.

  1. Configuration option "optimize for ad hoc workloads", which saves first-time query plans as stubs, to avoid memory pressure from heavy-duty one-time queries (especially helpful with an extreme number of parameterized queries). What - if any - is the equivalent for this with DB2?

    On a similar note, what would be the equivalents for SQL Server configuration options auto create statistics, auto update statistics and auto update statistics async. Which all are fundamental for creating and maintaining proper statistics without causing too much overhead during business hours?

  2. Indexes. MSSQL standard for index maintenance is REORGANIZE when fragmentation is between 5 - 35%, REBUILD (technically identical to DROP & RECREATE) when over 35%. As importantly, MSSQL supports ONLINE index rebuilds which keeps the associated data accessible by read / write operations. Anything similar with DB2?

  3. Statistics. In SQL Server the standard statistics update procedure is all but useless in larger DB's, as the sample ratio is far too low. Is there an equivalent to UPDATE STATISTICS X WITH FULLSCAN in DB2, or a similarly functioning consideration?

    In MSSQL, REBUILD index operations also fully recreate the underlying statistics, which is important to consider with maintenance operations in order to avoid overlapping statistics maintenance. The best method for statistics updates in larger DB's also involves targeting them on a per-statistic basis, since full table statistics maintenance can be extremely heavy when for example only a few of the dozens of statistics on a table actually need to be updated. How would this relate to DB2?

  4. Show execution plan is an invaluable tool for analyzing specific queries and potential index / statistic issues with SQL Server. What would be the best similar method to use with DB2 (Explain tools? Or something else)?

  5. Finding the bottlenecks: SQL Server has system views such as sys.dm_exec_query_stats and sys.dm_exec_sql_text, which make it extremely easy to see the most run, and most resource-intensive (number of logical reads, for instance) queries that need tuning, or proper indexing. Is there an equivalent query in DB2 you can use to instantly recognize problems in a clear and easy to understand manner?

All these questions represent a big chunk of where many of the problems are with SQL Server databases. I'd like to take that know-how, and translate it to DB2.

Improve this question
2
  • What platform and version of DB2? That will make a difference. Commented Aug 19, 2015 at 14:53
  • Linux. And apologies, didn't even realize this was relevant as well. I'm just that used to SQL Server. Commented Aug 20, 2015 at 6:15

1 Answer 1

Reset to default
3

I'm assuming this is about DB2 for Linux, Unix and Windows.

Configuration option "optimize for ad hoc workloads", which saves first-time query plans as stubs, to avoid memory pressure from heavy-duty one-time queries (especially helpful with an extreme number of parameterized queries). What - if any - is the equivalent for this with DB2?

There is no equivalent; DB2 will evict least recently used plans from the package cache. One can enable automatic memory management for the package cache, where DB2 will grow and shrink it on demand (taking into account other memory consumers of course).

what would be the equivalents for SQL Server configuration options auto create statistics, auto update statistics and auto update statistics async.

Database configuration parameters auto_runstats and auto_stmt_stats

MSSQL standard for index maintenance is REORGANIZE when fragmentation is between 5 - 35%, REBUILD (technically identical to DROP & RECREATE) when over 35%. As importantly, MSSQL supports ONLINE index rebuilds

You have an option of automatic table reorganization (which includes indexes); the trigger threshold is not documented. Additionally you have a REORGCHK utility that calculates and prints a number of statistics that allow you to decide what tables/indexes you want to reorganize manually. Both table and index reorganization can be performed online with read-only or full access.

Is there an equivalent to UPDATE STATISTICS X WITH FULLSCAN in DB2, or a similarly functioning consideration? ... The best method for statistics updates in larger DB's also involves targeting them on a per-statistic basis, since full table statistics maintenance can be extremely heavy when for example only a few of the dozens of statistics on a table actually need to be updated.

You can configure automatic statistics collection to use sampling or not (configuration parameter auto_sampling). When updating statistics manually using the RUNSTATS utility you have full control over the sample size and what statistics to collect.

Show execution plan is an invaluable tool for analyzing specific queries and potential index / statistic issues with SQL Server. What would be the best similar method to use with DB2

You have both GUI (Data Studio, Data Server Manager) and command-line (db2expln, db2exfmt) tools to generate query plans, including plans for statements that are in the package cache or are currently executing.

Finding the bottlenecks: SQL Server has system views such as sys.dm_exec_query_stats and sys.dm_exec_sql_text, which make it extremely easy to see the most run, and most resource-intensive (number of logical reads, for instance) queries that need tuning

There is an extensive set of monitor procedures, views and table functions, e.g. MONREPORT.DBSUMMARY(), TOP_DYNAMIC_SQL, SNAP_GET_DYN_SQL, MON_CURRENT_SQL, MON_CONNECTION_SUMMARY etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

"Optimize for ad-hoc workloads" sounds like the DB2 statement concentrator where a certain of query is only compiled once and then the query plan taken for similar queries. As a side effect the package cache will hold a lot less individual query plans.
This is great stuff. Will certainly help me get started. If you have any other things in mind regarding general optimization of larger DB's, specific to DB2, please do share them as well!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.