DB2 10.5 Row Change default date

Jim Youmans
Jim Youmans used Ask the Experts™
on
DB2 10.5 LUW on Windows Server

I added a datatime column to each table in my production database that matches this ...
DW_LASTTIME	TIMESTAMP	NOT NULL	GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Open in new window

We use this to determine when data in a row has changed so we can load it into our SQL Server data warehouse for analytics and reporting.  I knew there would be some overhead, and we tested it in QA and it seemed to be fine.  Now I have some people complaining of slowness and they are blaming this change.  How can I determine how much overhead this is causing?  This is on Windows Server so not sure how I can capture this information, if it is even possible.  Any thoughts?

Jim
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent OlsenData Warehouse / Database Architect

Commented:
Hi Jim,

That kind of update is trivial.  I can't imagine it actually making a visible impact on the update time.  


Kent
Jim YoumansSr Database Administrator

Author

Commented:
I put 500,000 rows in 2 test tables and run the following updates.  TABLE1 had the on change field and table2 had just normat datetime field.

UPDATE JYOUMANSKA.TABLE1
SET HEARTBEAT_TS = '1900-01-01';
-- 500,000 rows
-- 0:00:04.807
-- 0:00:04.996
-- 0:00:04.637



UPDATE JYOUMANSKA.TABLE2
SET HEARTBEAT_TS = '1900-01-01';
-- 500,000 rows
-- 0:00:03.119
-- 0:00:03.225
-- 0:00:03.412
Data Warehouse / Database Architect
Commented:
Hi Jim,

One interpretation says "from 3.2 seconds (average) to 4.7 is a 50% increase".

Another is that "the time added to update 500,000 row is about 1.5 seconds, or .000003 seconds per row".  I consider that trivial.  :)


Kent
Kent OlsenData Warehouse / Database Architect

Commented:
Hey, Jim....

The actual time to update that column is nearly trivial, but there may be more going on than just the update.

Does the column default to NULL and contain a NULL for all existing rows so that it's modified only on an UPDATE?  If so, the row will grow the first time the column gets a value.  This might cause block splitting, which will require overhead and index updating as half of each block will move to a new block.

If the default is NULL, the data length is 1 byte (which is the NULL/NON-NULL indicator).  When the field is populated, it will grow by up to 15 bytes, depending on default timestamp format.  (It's probably 13 bytes.)  If you've got relatively short rows in a table this extra data could be a lot of overhead.  A 50 byte row that grow by 13 bytes is now 26% longer.  A 200 byte row that grows by 13 bytes is only about 6% longer.

If you're seeing some long runtimes it could be because of the overhead of updating this field for the first time.  The good news is this is a one-time penalty for each row that is updated.  The bad news is that the block splits, if they're occurring, probably means that you'll want to do a reorg on some of your tables at some point if you're not running with auto table and index reorg enabled.  If it is enabled, some of the perceived performance issues could be the auto reorg overhead, in which case the overhead of the block splitting is being cleaned up on the fly so it shouldn't be an issue going forward.


Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial