November 01, 2016
When a high number of inserts arrive in a non-clustered way at the end of a table space or table space partition, it's known as a "journaling pattern." While I personally haven't worked on this type of application, Dan Luksetich of DanL Database Consulting has, and he wrote this white paper about it
As a former colleague, I know Dan is a very smart guy who's spent untold hours coming up with the best design to provide support for high performance insert processing. In the white paper, Dan says he's achieved as many as 17,000 inserts per second, and he believes he can top this number. Meanwhile, the DB2 lab has achieved over 8.6 million inserts per second.
To support this new technique, you must be using a Universal Table Space (UTS) with either partition by growth (PBG) or partition by range (BPR). Then specify MEMBER CLUSTER, either with or without append. These numbers, by the way, are based on not having an index. With an index, you'd be looking at around 4.3 million inserts per second.
This new DB2 12 algorithm streamlines the space search, and is the default when using UTS with MEMBER CLUSTER. You can override this change at either the system level, by using the INSERT ALGORITHM parameter, or with a table space, by using the INSERT ALGORITHM parameter, which will override the system parameter.
DB2 for z/OS 12 has taken journal pattern inserts from the thousands to the millions per second. That's great for everyone. In Dan's case, it means he won't have to work so hard to improve the application journal performance. I look forward to more updates from Dan on how fast this application is inserting into DB2.
Posted November 01, 2016 | Permalink