Thursday, April 10, 2008

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.

Thursday, March 27, 2008

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!

Wednesday, March 19, 2008

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.

Tuesday, March 18, 2008

Free Downloadable eBook from IBM Press

Pearson and IBM Press have just published a new eBook on IBM database technology that is available for free download. Just follow this link, provide your e-mail, and they’ll direct you to a PDF containing sample chapters from six recently published books.

The information in the eBook comes from the following books:

IBM Press is also offering a 35% discount on these, and other selected titles. But in some cases, the amazon discount is greater than 35%, so if you'd rather buy from amazon.com, simply follow the links in the bulleted list above.

But make sure you download that free eBook... after all, what is better than free?

Monday, March 03, 2008

Q+A: Nulls and DASD

Recently, I received the following question, which I will answer today on this blog posting.

Question: Lets say I have a table A which has 500 columns. Out of those 500 columns only 5 columns have been defined as not nullable and the rest have been defined as NULLS allowed. And out of those 500 columns I have found that 300 columns are unused(empty) totally. My business allows me to remove those 300 columns. My doubt is if I remove those 300 empty columns will I save on DASD space occupied by DB2? Will empty columns occupy DASD space?Would be really helpful if you can guide me on this.

Answer: I'm happy to try to help out. First of all, the short answer to your question is "Yes!" Those 500 columns are all consuming valuable disk space. To determine how much space is being consumed, you will need to examine the data type and length assigned to each column and add them up. And to make matters worse, you must add an additional 1 byte to each of them because the columns are nullable.

In DB2, a NULL is stored using a special one-byte null indicator that is "attached" to every nullable column. If the column is set to NULL, then the indicator field is used to record this. Using NULL will never save space in a DB2 database design - in fact, it will always add an extra byte for every column that can be NULL. The byte is used whether or not the column is actually set to NULL.

So, a column defined as CHAR(5) NOT NULL will required five bytes of storage space - but if it is defined as nullable, then it requires six bytes of storage space - five bytes for the data, and one byte for the null indicator.

Given all of this, it would seem that there is a very viable case to be made for you to remove those columns that are not being used. Of course, this means that you will likely have to make changes to any programs accessing that table. Because the table definition will change (fewer columns) you will need new DCLGENs and those will have to be included and bound into your programs. Be sure to factor this additional workload into your planning before moving forward with this change.

The better question to ask is "How the heck did all of the empty columns get put into the table to begin with and how did that design get past the DBAs?"

If you have an answer for that one, please share it by posting your answer in a comment here!