The goal of this article is to to take one query and use the explain facilities of Db2 to walk through it, and then do the same with MySQL, and then show some of the optimizations I might make for the query and why.… Read the rest
Continue reading »
Tag: Explains
Comparing Db2 and MySQL: NULL in Indexes
I’ve been spending some time working with indexes as I learn MySQL, and there were a few things I thought were were worth mentioning. As always, I’m writing from my own knowledge with deep experience in IBM DB2, and as a learner when it comes to MySQL.… Read the rest
Continue reading »
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 SQL re-write is obsolete because the Db2 Optimizer will take care of that.… Read the rest
Continue reading »
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 query that has a performance problem, there is only occasionally a single, obvious cause for all of the problems.… Read the rest
Continue reading »
DB2 Upgrade Detail: Upgrading Explain Tables
Explain tables change in structure from version to version of DB2. If you want to continue to use the same set of explain tables across a DB2 upgrade, you must take special action to upgrade them.
Explain Tables
Explain tables are used when generating a human-readable version of the access plan for a query.… Read the rest
Continue reading »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)) = ?… Read the rest Continue reading »
Adding a GENERATED ALWAYS Column to a Table
GENERATED ALWAYS can be a blessing or a curse. I’m not talking about identity columns here, but about creating a column that is actually a duplicate of some part of the data to boost performance. Sure, in 10.5 we can do indexes on expressions, but for some clients I have trouble just getting them to go to a supported level, much less the latest and greatest.… Read the rest
Continue reading »
Keeping EXPLAIN under control
Have you ever looked at a list of tables in your database, only to see the set of EXPLAIN tables present in two, five or even 10 different developers’ schemas?
I don’t know about you, but this drives me crazy.… Read the rest
Continue reading »
DB2 Basics: Administrative Commands
I had been in conversation with a respected DBA who is a well known speaker and author. I had mentioned how difficult it is to come up with advanced topics to write and speak about. That is when he responded “Advanced topics only narrow the audience”.… Read the rest
Continue reading »LBAC – Label Based Access Control
To go with my recent article on RCAC/FGAC, I thought I would do some similar work using LBAC and see what I could learn about it and the differences between the two.
What is LBAC
Label Based Access Control essentially adds a column to a table that labels each row (think confidential, secret, top secret), and then grants uses of those labels to users to allow them to access the data.… Read the rest
Continue reading »DB2 Fine-Grained and Row Access Control (FGAC/RCAC)
DB2 10.1 introduced a new feature commonly called RCAC (Row and Column Access Control) or FGAC(Fine-Grained Access Control). This is a bit less labor intensive to support than LBAC (Label Based Access Control), and solves some of the problems with LBAC.… Read the rest
Continue reading »DB2 Explain Output in Another Format
I’m always keeping an eye on Twitter to catch interesting bits of news, and that includes watching cross platform information. I find it fascinating to compare DB2 to other RDMBSes, and saw a great article from Use the Index, Luke (fabulous name!)… Read the rest
Continue reading »