Category: Performance

file000863593965 0

DB2 Memory Area In-Depth: The Package Cache

The package cache is just one memory area that DB2 offers to tune memory usage for a DB2 database. This article is a deep dive into this memory area. What is the Package Cache The package cache is an area of memory that DB2 uses to store access plans. Access plans are detailed strategies for how DB2 will get to...

file0001332675785 0

A Physical DBA Reviewing DDL

My background comes largely from the physical DBA world, though I’ve gained logical DBA skills over the years. Any reasonably mature IT organization will have a DBA at some level or another review SQL and DDL before it is implemented in production. Why Reviewing SQL and DDL is Important Reviewing SQL and DDL is important because there are some very...

IsolationLevels 0

DB2 Basics: Isolation Levels and Concurrency Phenomena

Isolation levels are such a critical concept to maximizing concurrency for transaction processing databases where ACID is so critical. What is ACID? ACID stands for Atomicity, Consistency, Isolation, Durability. This is a set of concepts that applies to any transaction processing system, whether it is a Relational Database Management System (like DB2) or not. ACID is one of the big...

4

Looking at Sort Memory Performance on BLU

Performance tuning of BLU databases is an evolving topic at this time. For BLU databases, sort memory cannot be tuned by STMM, and so must be manually tuned. BLU also makes extensive use of sort memory. It is a bit of a misnomer at this point. It is more of a working memory area that is used for hashes, grouping,...

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

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 3

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