Big Data: DB2 SQL Performance Is Faster Using OLD TABLE

Processing rows inside the DB2 engine is always faster than trying to do the same processing within an application program or outside of DB2.  A common program performance mistake is executing two SQL statements and then matching or merging the data inside the application.  The DB2 engine processes the data faster through a single SQL JOIN statement.  

Sometimes it’s even faster to do table row archiving or maintenance through SQL instead of unloading and then reloading the table data.  Some shops are still creating flat files and doing sort merge matches when they could be doing these processes inside the DB2 engine more quickly and still using less overall CPU than performing the task with flat files.  Improving processing time and cutting CPU can be especially important as table populations continue to grow into more terabytes of data every month.  Some DBAs still want to unload the data, do the row archiving maintenance or mass deletes and then reload the data.  This processing can be very expensive, storage intensive and cause a major lengthy data availability outage while the data reload is done.

An alternative that is faster and less CPU expensive is to use the SQL “OLD TABLE” keyword within a SQL statement processed within DB2.  By using this SQL “OLD TABLE” keyword the old data can be written to a report or file for archiving while quickly deleting the data from the table.  USING this SQL “OLD TABLE” keyword the processing only makes one pass of the table, performs the SQL DELETE while creating the report or file of the deleted data rows.  The table doesn’t get reorganized but the deleted rows make more free space within the table for more rows and will further delay the reorganization requirement for the table.

By creating a table of the keys of the rows to be deleted the processing can be done quickly inside DB2.  If the table is partitioned, the SQL “OLD TABLE” keyword can sometimes be done in parallel, consuming zIIP processors instead of general processors and reducing the CPU expenses further.  For example:

SELECT * FROM OLD TABLE (
DELETE FROM BEULKE_TABLE
WHERE KEY_COLUMN IN (
     SELECT KEY_COLUMN
      FROM DELETE_KEY_TABLE);

This “OLD TABLE” keyword SQL technique can also be used in conjunction with ALTERing the table as NOT LOGGED. Using the NOT LOGGED parameter eliminates the logging overhead making this SQL statement perform even faster.  The DB2 performance SQL “OLD TABLE” keyword processing reduces I/Os tremendously since it processes across the DB2 compressed table.  This technique also conforms to all data security and governance compliance aspects since it keeps all processing within DB2.  It also eliminates the need for the extra temporary storage of the unloaded and uncompressed terabytes of data necessary for the application unload to a flat file and reload to DB2 processing technique.  Testing the “OLD TABLE” keyword SQL technique with the NOT LOGGED parameter within a client’s environment saved them 40% of their previous CPU processing with a faster elapsed time and no outage for any other application processes.

So next time massive table maintenance processes are needed against your tables, try to do them inside DB2 with this SQL “OLD TABLE” technique and leverage the DB2 zIIP utilization and parallelism that saves CPU, terabytes of storage and database outage time.

_____________________________________________________

Have you made your plans for IDUG in Orlando this year?  Also make sure to register early and get the IDUG early bird discount. Sign up today!

I look forward to speaking at the IDUG DB2 Tech Conference 2013 North America conference.  The conference will be held in Orlando, Florida on April 29-May 2, 2013.  Get more information at www.idug.org.

I will be speaking at the conference presenting Big Data and Analytics Session F07 – “Data Warehouse Designs for Big Data Performance” Wed, May 01, 2013 (02:15 PM – 03:15 PM) in Bonaire 5&6.
_____________________________________________________
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

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>