Bookmark and Share
RSS

Recent Posts

Comparing DB2 12 Function Levels M100 and M500

January 03, 2017

I was recently reading the DB2 12 for z/OS What’s New guide. It's interesting comparing the features available in the initial Function Level M100 with what you can get when you activate Function Level M500.

While most new capabilities in the initial DB2 12 release are enabled only after activation of M500, of course there are benefits with M100:
  • Virtual storage enhancements – All enhancements are available in M100. No new enhancements are delivered with M500.
  • Subsystem parameters – All SQL optimization enhancements are available in M100 as long as the statement goes through a full prepare. No new enhancements are delivered in M500. 
  • SQL and application compatibility – New SQL capabilities become available after the activation of a function level. No new SQL capabilities are delivered with M100. (I’ll list separately the SQL capabilities delivered with M500.) You can also continue to execute SQL with DB2 10 or DB2 11 new-function mode by using application compatibility values “V10R1” or “V11R1.”
Now for the M500 enablement enhancements. I'll write plenty more about specific features in 2017, but here are some highlights:
  • Advance triggers – DB2 12 introduces support for advanced triggers. Any trigger created before activation of M500 is considered a basic trigger.
  • Additional array support – DB2 11 introduced array data types. DB2 12 adds the capability to define a global variable as an array data type. In addition, the ARRAY_AGG function can now be invoked without a GROUP BY clause, and can be used with an associative array.
  • Additional support for global variables – BLOB, CLOB or DBCLOB data types are added.
  • Additional support for pureXML – There are performance improvements with multi-document updates using XMLMODIFY.
  • Additional support for JSON – A quick list: the JSON_VAL function isn't required to be BLOB. In addition, view column, CASE expression, table expression with UNION ALL, a trigger transition variable and a SQL PL variable or parameter are now supported.
  • MERGE statement enhancements – There's greater functionality and improved compatibility with the DB2 family including: table-reference for source data, multiple MATCHED clause, additional predicates with MATCHED or NOT MATCHED, DELETE operation, IGNORE and SIGNAL actions.
  • SQL pagination support – This allows mobile devices to access the next set of data using an OFFSET clause or a row value expression.
  • Unicode column in EBCDIC table.
  • Piece-wise deletion of data – This is designed to help avoid locking contention when a large number of rows are being deleted in a single SQL statement.
  • Support for temporal referential constraint – You can use the PERIOD BUSINESS_TIME when creating a referential constraint for an application-period referential constraint.
  • More flexibility in defining application periods for temporal tables – You can now define an application period to be inclusive-inclusive. The end date would be included in the period.
  • Support for temporal logical transactions – The application can set a built-in global variable with a system time period and all data, regardless of commit processing, will contain the same logical time period.
  • PERCENTILE function support.
  • DRDA fast load – Enables quick and easy loading of data from files on a distributed client.
  • ODBC enhancements – Performance and portability of the DB2 ODBC driver are improved.
  • Obfuscated source code for SQL routines and triggers -- The SQL logic is rendered unreadable, enabling the delivery of SQL routines and triggers without the need to share the intellectual property of the SQL PL logic.
  • Data sharing support for global transactions.
  • Support for maintaining session data on target server.
  • Resource limits for static SQL statements.

Posted January 03, 2017 | Permalink

comments powered by Disqus