Learning to Code Efficient Db2 SQL

存储架构 2018-02-20

February 20, 2018

Craig Mullins just came out with this post on writing SQL to maximize performance

This got me thinking about a point in my career when most of the work I performed was SQL tuning. While there's an axiom about the best performing SQL statement being one that doesn't execute, if you're actually going to write functional SQL, you should try your best to understand the DBMS SQL engine. Unfortunately, many programmers/developers don't take the time to do this, so the SQL they write is generic and may not perform as well as it could.

Again, writing efficient SQL takes effort and understanding. So study the Db2 optimizer rules and focus on these tips:

  • Whenever possible, use stage 1 rather than stage 2. Stage 1 predicates are better because they disqualify rows earlier, reducing the amount of processing that's needed at stage 2.
  • Write queries to prioritize the evaluation of the most restrictive predicates. When predicates with a high filter factor are processed first, unnecessary rows are screened early on; this can reduce processing costs down the line. However, a predicate's restrictiveness is only effective among predicates of the same type and at the same evaluation stage.

I encourage you to read Craig's work, and catch him at the upcoming IDUG conference.
Here are additional resources that can help you write efficient SQL running on Db2 for z/OS:

  • "DB2 12 for z Optimizer."
    This IBM Redbook is authored by by Terry Purcell, the lead designer responsible for the Db2 for z/OS optimizer.
  • Db2 12 for z/OS Managing Performance.
    This IBM Knowledge Center document explains how to use predicates efficiently.


Shortcut: Using line numbers and Go To in SQL Serv... If you ever have long scripts in SQL Server Management Studio and need to refer to a particular line, it can be helpful to have line numbers shown. Th...
SQL Graph Objects in SQL Server 2017: the Good and... SQL Server 2017 will be bringing us support for some of the functionality of graph databases. Graph databases are unlike relational databases in that ...
T-SQL Enhancement in SQL Server 2005 较之前一版本, SQL Server 2005 可以说是作出了根本性的革新。对于一般的编程人员来说,最具吸引力的一大特性就是实现了对 CLR 的寄宿,使我们可以使用任意一种 .NET Programming Language 来编写 Stored Procedure 、 Function 、 Tr...
ORACLE Index Lookup索引访问路径总结 在 ORACLE 中,索引访问 / 查找( Index Lookup )路径有五种方式,分别为 INDEX UNIQUE SCAN 、 INDEX RANGE SCAN 、 INDEX FULL SCAN 、 INDEX FAST FULL SCAN 、 INDEX SKIP SCAN 。下面通...
SQL Server Failed to Open Loopback Connection – Wh... SQL Server Failed to Open Loopback Connection – What? BLOB EATER Posted on 12 December 2017 Comments I could not r...