hand and light 2

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

_zlShuKV-36AkWovrKXscdxg3q6FmE8CDYOPVjs6AyQ 1

Insight 2015 Brain Dump

I call my post-conference blog posts brain dumps because that is largely what they are – information formatted in a way my brain understands but not necessarily as thoroughly organized and researched as you might be used to seeing from me. Conference Generalities Overall Themes I sure wish that DB2 was more highlighted overall, but I’m sure that’s largely a...

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

0

Improving Performance of DBCLEAN Deletes

While this post is specific to WebSphere Commerce, it covers concepts that may apply to tuning other delete statements as well. Using Functions in the Best Place The CLEANCONF table stores the delete statements that are used by DBCLEAN. Most of them use syntax like this for the date component: (days(CURRENT TIMESTAMP) – days(prevlastsession)) >= ? Every time I see...

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

Jim 5

Locking Event Monitors and Compression

UPDATE, 10/21/2015: Please see the comment from IBM at the end stating that despite the information they gave Jim via PMR, this compression does not cause a lack of license compliance even if you are not licensed for compression. Abby Rosmarin said, “Hard work, determination and talent are key for any successful venture. But sometimes you need that fourth ingredient:...

1

Looking Forward to IBM Insight 2015

IBM Insight starts in less than two weeks! You can still register and attend if you haven’t planned to already! General Tips Website If you haven’t registered already, check out the IBM Insight website for information. I’ll be linking to specific pages throughout this post. Attire This is the third year I’m going to IBM Insight (well the first year...

Screenshot_091415_065147_PM 3

Ongoing Support of DB2’s HADR

There are some things to be aware of with ongoing support of a HADR system. I thought I’d group them together to provide a primer of do’s and don’ts for support of HADR. Monitoring HADR HADR does occasionally stop all by itself. Also, system events can cause it to not be active. For these reasons, it is critical that you...

file9791234819983 1

DB2 Backups When Using Native Encryption

I’ve recently implemented native encryption for a small database on a server that is somewhat oversized on CPU and memory. One of the things I noticed after encrypting my database was both increased backup duration and increased backup size. Backup Size On this particular system, I take compressed DB2 backups to disk, which is later externalized. Immediately after enabling Native...