DB2 Administrative SQL Cookbook: Finding Statements in the Package Cache using a lot of Temporary Space
To
Tag SQL
SQL That Meant Well: Where Functions are Applied
Not only was this delete running unreasonably slow – it was also causing lock timeouts on the MEMBER table while running because of the slowness. This post contains application specific details that you don’t need to understand the underlying concept here, which applies across many SQL statements in the real world.
SQL That Meant Well: Applying Conditions in the Subquery vs the Outer Query
This is a new series that has been percolating in my head for a while. When I help clients with SQL, we’re often adding (or removing) indexes to help SQL performance. It has been said (though not by me) that…
Db2 SQL Basics: The Most Basic Select Statement
Learning SQL can feel intimidating, even to some experienced DB2 DBAs. For years, you could administer Db2 without using SQL. There are a lot of developers, too who want to learn SQL. The set-thinking of SQL can be confusing. The…
DB2 Administrative SQL Cookbook: Listing Database Authorities that an ID/Group Holds
Purpose To list the database authorities held by an ID or group – authorities, not privileges. This information is not available in sysibmadm.privileges. The format of syscat.dbauth has columns with ‘Y’ or ‘N’ in them, and reading that quickly to…
Query Performance Analysis
Some of the more complicated work a DBA does is often analyzing a query. Whether it is proactive or in response to a performance problem, there are so many factors that go into query performance. Even when looking at a…
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…
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…
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…
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…
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…
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.…