DB2 Temporal Tables Performance: Three Critical Null in DB2 Column Considerations

When designing your DB2 temporal table make sure the column null-ability definitions are properly analyzed for your application. Defining a DB2 column as null able can be a tricky issue when dealing with computing, totaling and understanding the value of your data.

Even though I personally prefer to have data values and avoid nulls within my DB2 table designs, sometimes there are many reasons to have a column with a null in DB2. When evaluating your table column designs make sure to consider these three critical issues for NULL columns in your table design.

First, remember the basic rules for using a NULL in DB2: any table column can be NULLable, NULL in DB2 means the absence of any value, and not all SQL functions or columns can return a NULL value in DB2. Even though the column is defined as NULLable, a SQL statement may not return a NULL value to the application in some use cases.

The best example I heard for explaining NULLs involves two NULLable columns within a life insurance application: BIRTH_DATE and DEATH_DATE. If an active person has an active life insurance policy the BIRTH_DATE column is filled in and the DEATH_DATE is NULL in DB2. No one knows when they are going to die so it is NULL, no value. Also BIRTH_DATE could be NULL in DB2 also if a couple bought a new policy for their pregnant mother who’s baby isn’t quite born yet. These use cases show how the columns can be NULL and how the NULL indicator in DB2 adds valuable information to the business.

Second, if your applications are involved in calculations, understand how NULL values affect the computed results. Since NULL values are not factored in the calculations when using the AVG, COUNT, SUM and other DB2 SQL functions, the NULL values will definitely have an impact. For example, if the SALARY is NULLable and the simple SQL statement below is executed you can see that there are 4 EMPLOYEE rows that have NULL salary values because the number of rows counted in the average function has four fewer rows than the number of rows counted in the sum function. We can also see that the SQL calculations of average, sums and counts ignore the NULL values when the SALARY column is specified. So analyze and understand when the NULL column value impacts your SQL results.

SELECT

AVG(SALARY)        AS SALARY_AVG,

COUNT(SALARY)      AS SALARY_COUNT,

SUM(SALARY)        AS SALARY_SUM,

COUNT(*)           AS NBR_OF_ROWS_COUNT

    FROM BEULKE.EMPLOYEE;

SALARY _AVGSALARY_COUNTSALARY_SUMNBR_OF_ROWS_COUNT
58155.357142857142857142857142422442525.0046

Third, make sure to remember that NULLs on a column add an extra indicator byte to the length of your table column. The extra byte that a NULL in DB2 requires is usually not a big deal unless your table is going to have millions or billions of rows. A number of NULLS in a table can quickly add 10 bytes to a table and further limit the number of rows per 4k DB2 data page. Also remember the extra NULL indicator byte in DB2 is included when the NULLable column is used within an index definition, so it makes your index bigger also.

When your DB2 temporal table design is being finalized take a second look to analyze the NULLable columns again. Realize their importance in calculations and determine their impact on the temporal nature of your table and whether they should or should not be included in the calculations that are being done. Understand the NULL ability of the column and determine whether it being NULL within the HISTORY portion of your DB2 temporal table configuration impacts your calculations and results appropriately. Remember analyze twice and define it into production only once.


Coming up Sept. 29th: DB2 10 for z/OS Performance Training and 30th SQL Performance Training. Two great DB2 classes are being offered in Washington DC: Get more information here (http://davebeulke.com/db2-performance-tuning-and-sql-training/)

 I will be presenting at the International DB2 User Group (IDUG) European Conference in Prague. I will be presenting my “DB2 10 Temporal Database Designs for Performance” on Mon, November 14, 1:30 PM – 2:30 PM. For more details on my presentation click here. (http://www.idug.org/e/in/eid=2&req=info&s=97&all=1I will also be involved in the Data Warehousing Special Interest Group (SIG) and the final discussion panel, so please join all the presentations, networking and discussions on DB2. Sign up now at www.idug.org.

Come see me in any of these venues and ask me your data warehouse performance questions.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>