My Most Recent DB2 Articles

Today I'm posting a quick blog entry to let my readers know about a couple of recent DB2-related articles/columns that I've had published. You can always keep up-to-date on my writings by visiting my web site at http://www.craigsmullins.com/articles.htm.

Any way, the following three articles might be of interest to DB2 for z/OS folks:

Use Real Time Statistics to Automate Your Database Maintenance was published in the April/May 2008 issue of zJournal. This article examines Real Time Statistics (RTS) and the benefits that can be accrued by using RTS. If you aren't using RTS yet, be sure to read this article to learn why you should!

Collecting Histogram Statistics With RUNSTATS was published in the March 2008 issue of DB2 Update. This article discusses one of the many new enhancements that have found their way into DB2 9 for z/OS -- the ability to gather histogram statistics with the IBM RUNSTATS utility.

And finally, the February/March 2008 issue of zJournal contains Much Ado About DB2 Locking. This installation of the z/Data Perspectives column takes a look at the most recent, new locking-related features of DB2 for z/OS.

Happy reading!

The Mainframe Still Rocks!

Mainframe Executive, a new publication for CIOs and IT managers in enterprises with IBM mainframe systems, just published a list of 15 reasons to stay on, or move to, a mainframe. The list is a good one, covering reliability, availability, security, resource utilization, scalability, power consumption, staffing concerns, quality, and on and on.

Here is the list for those not inclined to click on the link:

1. Lowest outage costs from highest platform reliability, availability, and serviceability.
2. Lowest security breach risks/costs via most secure design, encryption, etc.
3. Highest resource use efficiency/utilization for mixed commercial workloads.
4. Widest platform scalability supports any workload size, mix, growth.
5. Consolidates many new workloads, extends traditional workload strengths.
6. Top data-serving capacity, performance, value—best Information on Demand host.
7. Highest QoS, best performance with fastest response times.
8. Best enterprise SOA platform; enables fullest reuse of mainframe application assets.
9. Much-improved cost model transformed mainframe economics.
10. Lowest power consumption, cooling, and data center floor space needs.
11. Lowest staffing and support costs for enterprise workloads.
12. Lowest total cost of ownership, total cost per user, and total cost per transaction.
13. Best customer investment protection for any enterprise platform.
14. Lowest business risk platform with best world class support.
15. Healthy, expanding mainframe ecosystem is supporting the platform.

If you are a mainframer this list won't come as any surprise to you... but it can be handy to keep it readily available for the next time someone attempts to convince you that mainframes are already obsolete, or should be.

In fact, maybe you can come up with additional reasons. After reading the list (http://www.mainframe-exec.com/articles/?p=12) feel free to submit comments here with any additional reasons you might come up with!


Also, for those who don't know, Mainframe Executive is published by Thomas Communications, the same folks who publish the excellent bi-monthly z/Journal.

Consider Table Expressions to Improve Performance

Table expressions are frequently overlooked as a potential solution for resolving problems using only SQL. Table expressions can be used to force the optimizer to choose a specific processing order.

For example, consider the following query:

SELECT D.DEPTNO,
       MIN(D.DEPTNAME) AS DEPT_NAME,
       MIN(D.LOCATION) AS DEPT_LOCATION,
       SUM(E.SALARY) AS TOTAL_SALARY
FROM   DEPT D,
       EMP E
WHERE  D.DEPTNO = E.WORKDEPT
AND    E.BONUS BETWEEN 0.00 AND 1000.00
GROUP BY D.DEPTNO;

In this query, the detail rows that qualify from each table are joined prior to the GROUP BY processing. In general, there will be more EMP rows than DEPT rows because a department comprises multiple employees. Suppose there were 200 DEPT rows joined to 75,000 EMP rows. The join is done and then the GROUP BY is processed.

Instead, you can use table expressions to force the optimizer to process the aggregations on a table-by-table basis:

SELECT D.DEPTNO,
       D.DEPTNAME,
       D.LOCATION,
       E.TOTAL_SALARY
FROM   DEPT D,
       (SELECT WORKDEPT, SUM(SALARY) AS TOTAL_SALARY
        FROM EMP E
        WHERE E.BONUS BETWEEN 0.00 and 1000.00
        GROUP BY E.WORKDEPT) AS E
WHERE  D.DEPTNO = E.WORKDEPT;

This will produce the same results but it should perform better.

In general, consider using table expressions to pre-filter FULL JOIN tables, to pre-filter null supplying tables of LEFT/RIGHT joins, to separate GROUP BY work, and to generate or derive data.

Updated Stored Procedure RedBook for DB2 V9

If you are a stored procedure developer, or a DBA who manages a system with stored procedures - - and you are running DB2 9 for z/OS - - there is an updated RedBook you should download immediately. It is titled DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond. You may have the previous edition, DB2 for z/OS Stored Procedures: Through the CALL and Beyond (SG24-7083)...

This new edition of an all-time favorite RedBook is newly updated to show the changes that have happened to DB2 stored procedures and related tools from V8 to V9. It offers examples and guidelines for developing stored procedures in several languages. You will also find many useful recommendations for setting up and tuning your environment for stored procedures in this free-to-download manual.

And if you are looking for some "stuff" on using Data Studio with stored procedures, this is the place to go... so, it is time to update by downloading this new edition today!

DB2 Developer's Guide Tops Book Poll

I learned of some good news today. Evidently my DB2 book, DB2 Developer’s Guide, recently won the favorite book poll conducted by IBM Database Magazine (formerly DB2 Magazine) a couple of months ago… and it won by a substantial margin.



Thanks to everyone who voted for my book… I appreciate your support.