Why you want DB2 12 — Just the highlights
– – Version 12 of DB2 for z/OS
Okay, would somebody remind you again why you would want to upgrade to DB2 12? you might ask. Probably mainly because:
2) Some tasks are easier; you can use ALTER to increase partition size, and
3) Version 12 has significant performance enhancements that can make some online transactions run a lot faster; for example, as long as the object isn’t too big you can keep an object in an in-memory buffer pool, which is potentially a major turbo boost for some transactions. Also the new fast index traversal feature can speed up random index access when reading data, and.the new fast insert algorithm can speed up inserts (but only for unclustered data on universal table spaces).
Yes, those things that look like links are in fact links to IBM doc on the topic, click them if you want to find more information.
So: DB2 12 lets you have bigger data tables and partitions, some tasks are easier to accomplish, and some online transactions will probably run faster.
More data, Easier, Faster . . . Sounds great.
But wait, there’s more:
Obfuscated data definition statements: If you’re into code secrecy, for example if you sell a software product that uses DB2 tables and you don’t want competitors disguised as customers to copy your code, or you just don’t want customers to copy the code, modify it, and then open a problem report when their mods don’t work, then you might like this feature: For you, DB2 12 lets you disguise the source for SQL procedures, SQL functions, and triggers, using obfuscated data definition statements.
Triggers can be written in SQL PL (SQL Programming Language)
In Spanish, you can have fun pronouncing the name DB2 12 “Day-bay-dos-Dosay”, which sounds a bit like dosey-doe (a square dance move).
Okay, skip that as a reason. J
Availability: Advisory-REORG pending (AREOR) status during alteration of index compression for universal table space indexes means those indexes can continue to be used during the alteration. Also you can increase the size of a partition using ALTER to change the DSSIZE rather than doing a REORG.
Extended support for the array data type and for global variables. A global variable can now be defined as having the array data type. As a global variable, an array can be used both within and outside of PL SQL code.
Extended support for Unicode columns defined within ordinary non-Unicode (EBCDIC) DB2 tables.
Performance improvements for XML as well as for index list prefetch-based plans, outer joins, UNION ALL, and some other areas, including a number of performance improvements for online transaction processing – That is, some online transactions ought to run faster. Click here to get a list of online transaction performance enhancements.
Okay, How big can a DB2 table be now? 256 trillion rows if you use the new table space structure (PBR: partition-by-range).
How did they do it? They use RID Record IDentifiers 7 bytes long (2-byte partition number plus 5-byte page number) to locate data based on RPN Relative Page Number.
How big can a partition be? 1024 GB, which is 1 Terabyte (TB), or about a trillion bytes
How fast are those improved inserts? potentially over 11 million inserts per second for unclustered data, according to IBM. Sounds pretty good. Match that on your cell phone app if you can. Note that IBM also tells us “The fast insert algorithm is enabled for only universal table spaces using MEMBER CLUSTER. The algorithm can be enabled system-wide or for individual table spaces."
Vocabulary (These vocabulary definitions are straight from IBM references)
RPN: Relative Page Number(s)
PBR RPN: Partition-By-Range Relative-Page-Number
UTS: Universal Table Space
RID: Record Identifier(s) (note: Expanded to 7 bytes in DB2 12, to allow bigger sizes)
SQL PL : SQL Programming Language.
OLTP: Online Transaction Processing
AREOR: advisory-REORG pending status
RBDP: Restrictive REBUILD-Pending status
DSSIZE: Maximum size for each partition.—or, in a LOB, maximum size for each data set.
MEMBER CLUSTER specifies that inserted data is not clustered either by the implicit clustering index (the first index), or the explicit clustering index
FTB : “Fast Traversal Block(s) for indexes are in-memory optimized index structures that allow DB2 to traverse the (index) much faster than traditional page-oriented page traversal for indexes cached in buffer pools.”
References, Bibliography, Further Reading
Okay, you don’t need all of these, but it can save time when you want to find something if you have a list for reference. Look at whichever ones happen to interest you. You can find a lot more good stuff in Version 12 besides the little bit that was mentioned in this post. Also you should be able to find more details on exactly how to use the features (syntax, examples, caveats and restrictions).
IBM DB2 12 for z/OS Technical Overview
What's new in the DB2 12 base release?
IBM Systems Magazine – DB2 12 for z/OS Tools: What's Improved
Administrator function in DB2 12
Changing the boundary between partitions
Increasing the partition size of a partitioned table space
Increased partition sizes and simplified partition management
for range-partitioned table spaces with relative page numbering
Improved availability when altering index compression
New and changed catalog tables
DB2 12 for z/OS Product Documentation
Administrator information roadmap
New-user information roadmap
Application Developer information roadmap
DB2 12 – What's new – Improved EDM pool management
DB2 12 – What's new – OLTP performance in DB2 12
DB2 12 – Performance – Designing EDM storage space for performance
DB2 12 – Installation and migration – Calculating EDM pool size
DB2 12 – Performance – Calculating the EDM statement cache hit ratio
Creating and modifying DB2 objects from application programs