Why You Want DB2 12 Now

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:

1) It lets you have bigger DB2 tables  and bigger partition sizes,

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.

SoDB2 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

Utilities REORG, LOAD, RUNSTATS (Runstats), Backup and Recovery, and REPAIR CATALOG have enhancements.

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.

­Questions

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)

LOB : Large Object (Large Object table space)

PBG:  partition-by-growth

PBR: partition-by-range

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
http://www.redbooks.ibm.com/redbooks/pdfs/sg248383.pdf
http://www.redbooks.ibm.com/abstracts/sg248383.html

What's new in the DB2 12 base release?
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_wnew.html

IBM Systems Magazine – DB2 12 for z/OS Tools: What's Improved
http://www.ibmsystemsmag.com/Blogs/DB2utor/October-2016/DB2-12-for-z-OS-Tools–What-s-Improved/

DB2Tutor blog
http://www.ibmsystemsmag.com/Blogs/DB2utor/January-2017/DB2-12-Table-Space-with-Relative-Page-Numbers/
http://www.ibmsystemsmag.com/Blogs/DB2utor/January-2017/DB2-12-REORG-Enhancements/
http://www.ibmsystemsmag.com/Blogs/DB2utor/January-2017/Comparing-DB2-12-Function-Levels-M100-and-M500/

Administrator function in DB2 12
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_dba.html

Altering DB2 tables
http://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/admin/src/tpc/db2z_altertables.html

Altering partitions
http://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/admin/src/tpc/db2z_alterpartitions.html

Changing the boundary between partitions
http://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/admin/src/tpc/db2z_changepartitionboundary.html

Increasing the partition size of a partitioned table space
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/admin/src/tpc/db2z_increasingpartsize.html

Increased partition sizes and simplified partition management
for range-partitioned table spaces with relative page numbering

http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_increasedpartitionsize.html

Improved availability when altering index compression
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_onlinealtercomp.html

New and changed catalog tables
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/cattab/src/tpc/db2z_newandchangedcatalogtables.html

DB2 12 for z/OS Product Documentation
http://www-01.ibm.com/support/docview.wss?uid=swg27047206

Administrator information roadmap
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_inforoadmap.html

New-user information roadmap
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_newuserroadmap.html

Application Developer information roadmap
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/intro/src/tpc/db2z_appdevroadmap.html

DB2 12 – What's new – Improved EDM pool management
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_edmpoolimprovements.html

DB2 12 – What's new – OLTP performance in DB2 12
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_oltpperf.html

DB2 12 – Performance – Designing EDM storage space for performance
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/perf/src/tpc/db2z_designedmstoragespace.html

DB2 12 – Installation and migration – Calculating EDM pool size
http://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/inst/src/tpc/db2z_calcedmpoolsize.html

DB2 12 – Performance – Calculating the EDM statement cache hit ratio
http://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/perf/src/tpc/db2z_calculateedmstmtcachehit.html

Creating and modifying DB2 objects from application programs
http://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/apsg/src/tpc/db2z_createmodifydb2objects.html