Wednesday, November 27, 2019

Happy Thanksgiving 2019

Just a quick post today to wish all of my readers in the US (and everywhere, really) a very Happy Thanksgiving.



Thanksgiving is a day we celebrate in the USA by spending time with family, eating well (traditionally turkey), and giving thanks for all that we have and hold dear.

Oh... and also for watching football!

May all of you reading this have a warm and happy Thanksgiving holiday surrounded by your family and loved one.

Happy Thanksgiving!

Thursday, November 07, 2019

Db2 12 for z/OS Function Level 506

Late last month, October 2019, IBM introduced a new function level, FL506, for Db2 12 for z/OS.  There are two significant impacts of this new function level:
  • Alternative function names support
  • Support for implicitly dropping explicitly created table spaces
The first impact, support for additional, alternative names for some existing Db2 built-in functions, was added is to improve compatibility across the Db2 product line. It is basically just a new way to refer to existing functionality, an alternative syntax, if you will. The following chart outlines the existing and new FL506 alternative syntax.

Table 1. Alternative Syntax for Function Names in FL506        
Existing Function Name
New Alternative Syntax Name
CHARACTER_LENGTH
CHAR_LENGTH
COVARIANCE or COVAR
COVAR_POP
HASH_MD5 or HASH-SHA1 or HASH_SHA256
HASH
POWER
POW
RAND
RANDOM
LEFT
STRLEFT
POSSTR
STRPOS
RIGHT
STRRIGHT
CLOB
TO_CLOB
TIMESTAMP_FORMAT
TO_TIMESTAMP


Support for these alternative spelling of built-in function names should make it easier to support applications across multiple members of the Db2 family where support already exists for these spellings. Of course, you may run into issues if you used any of the new spellings in your existing applications, for example as variable names.

The other significant feature of FL506 is support for implicitly dropping explicitly created universal table spaces when a DROP TABLE statement is executed. Prior to FL506 dropping a table that resides in an explicitly created table space does not drop the table space.

If you use vendor tools that manage and generate scripts for DDL changes, they need to be modified to support FL506. If not, they could produce -204 SQL codes when the generated DDL is executed if the DDL contains a DROP TABLESPACE statement. The table space will have already been implicitly dropped and trying to drop a table space that does not exist will throw an error. Be sure to discuss this with your tools vendor before migrating to FL506 to understand the cendor’s support timeline or if they have a workaround.

Summary

IBM is using function levels to deliver significant new capabilities for Db2 12 for z/OS. It is important for you and your organization to keep up-to-date on this new functionality and to determine where and when it makes sense to introduce it into your Db2 databases and applications.

Also, be aware that if you are not currently running at FL505, moving to FL506 activates all earlier function levels. You can find a list of all the current function levels here.