A Faster Way of Joining When Applying a Distinct

I have been paying a bit of attention to cross-platform SQL optimization lately, and read this interesting post:
https://www.periscopedata.com/blog/use-subqueries-to-count-distinct-50x-faster.html?utm_content=bufferaf11f&utm_medium=social&utm_source=linkedin.com&utm_campaign=buffer

Being a bit of an experimenter, the first thing that I wondered is how DB2 would handle this scenario. Would the DB2 optimizer be smarter than others, or would the same hold true for DB2 that held true for PostgreSQL?… Read the rest

Continue reading »

Speeding up DB2 Backups

It’s a question I hear frequently – How can I speed up backups? I thought I’d share some details on doing so.

Database Layout

Any backup cannot be faster than it takes to back up the largest tablespace. Parallelism is essentially done on the tablespace level – meaning one thread per tablespace.… Read the rest

Continue reading »

Analyzing Package Cache Size

Note: updated 7/21 to reflect location of the package cache high water mark in the MON_GET* table functions

I have long been a fan of a smaller package cache size, particularly for transaction processing databases. I have seen STMM choose a very large size for the package cache, and this presents several problems:

  • Memory used for the package cache might be better used elsewhere
  • A large package cache makes statement analysis difficult
  • A large package cache may be masking statement issues – the proper use of parameter markers

Parameter Markers

Parameter markers involve telling DB2 that the same query may be executed many times with slightly different values, and that DB2 should use the same access plan, no matter what the values supplied are.… Read the rest

Continue reading »

Multiple Index Regression Analysis

I actually had a blog entry started on this topic before IDUG. I knew this was possible, but not exactly how to do it. Then I sat in Scott Hayes’ session – D04 – More Sage Advice: Invaluable DB2 LUW Performance Insights from Around the Globe, and he had all the details I needed to really get rolling on trying this out – saving me valuable time in research.… Read the rest

Continue reading »