Category: SQL

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

file0001696162701 0

Static SQL vs Dynamic SQL in Stored Procedures

Updated 13 September 2016 to use more correct wording around how and when the access plan is generated and reused. As a new DB2 for LUW DBA or developer it can take a while to understand the difference between static and dynamic SQL. I’d like to use an example in a stored procedure to explain. Example The same SQL can...

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

DSC03515 0

Funny SQL I Have Seen Lately

I’ve run across a few strange SQL statements lately, and while the mistakes they make are not the most impactful, I thought I’d share how I think they could be better written. Unnecessary use of a Common Table Expression This query isn’t really wrong, it just doesn’t make much sense. Here it is as I discovered it: with sortsnap as...

DSC06398 0

Calculating Row Size for a Table

There are several scenarios with DB2 where we need to know what the “worst-case” row length is. That is, if all varying fields are completely full and any LOBs are maximally inlined, how much space could a single row actually take up? SYSCAT.TABLES gives us the average row size, which is useful for some use cases, but not all of...

file0001207444674 1

Using DB2’s ADMIN_MOVE_TABLE to Move Tables from One Tablespace to Another

What is ADMIN_MOVE_TABLE ADMIN_MOVE_TABLE is an administrative stored procedure introduced in DB2 9.7. The intent is to provide a tool that can be used to perform an online move of a table, while transactions are still occurring against the table. Moves can be used to change what tablespace a table is in, convert a table to MDC, change the table...

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

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