Category: Performance

hand and light 3

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? Environment...

select2 0

Administrative SQL Cookbook: BLU Buffer Pool Hit Ratios

Purpose These statements calculates the buffer pool hit ratio for both BLU and non-BLU activity. This post includes three SQLs – for calculating at the database, bufferpool, and tablespace level. The separate BLU calculations are not yet included in sysibadm.bp_hitratio as of the writing of this article. Source While I’ve modified this statement, it comes from these two sources, both...

select 2

DB2 Administrative SQL Cookbook: Column Selectivity by Table (BLU)

Purpose This statement reports how selective at the column level queries are that run against a specific table. This does not look at overall selectivity or row selectivity. It will only work in DB2 10.5. BLU performs best when not all columns are referenced by queries. Source While I’ve modified this statement, it started with statements from these two sources,...

select2 0

DB2 Administrative SQL Cookbook: BLU Compression Ratios

Purpose This statement calculates the compression ratio for BLU tables. The compression ration can be used to help identify tables where compression is not optimal and you may need to look into why. Compression is critical to optimal performance on BLU. Understanding Compression Ratios. Compression ratios across platforms and outside of databases are generally represented as: Compression Ratio = Compressed...

file000956896813 5

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. That means that if you have the majority...

Pavan 2

Automatic Bufferpools in DB2 HADR

In this blog post, I will share what I learned about DB2 automatic bufferpools when using HADR. Problem: In a DB2 10.5 (fp3a) HADR environment, during heightened database activity on the primary database, there was delayed log replay on principal standby database. The HADR_SYNCMODE was set to SYNC. Primary and Standby database servers were in the same data center. HADR_SYNCMODE...

1

Issues with STMM

I thought I’d share some issues with STMM that I’ve seen on Linux lately. I’ve mostly been a fan of STMM, and I still am for small environments that are largely transaction processing and have only one instance on a server. Here are the details of this environment. The database is a small analytics environment. It used to be a...

1

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...

file0002050295530 10

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...

IanB 6

Generating Log Archive Activity Histograms

A few weeks ago, Ember posted a tweet mentioning that being able to see a histogram showing frequency of transaction log archive activity would be helpful. I had already done some work in this area by parsing the diagnostic log file (db2diag.log) for log archive messages, but I thought I could throw together a quick example of how to do this using...