Last time we looked at AI and Db2; today let’s look more closely at Db2v13 improvements in Application Support.
There are always changes and features delivered with each new version of Db2 that impact application developers, and Db2 13 for z/OS is no different. The first item for developers to digest is the SQL changes delivered in the new release.
We already covered the new built-in AI functions (delivered as part of SQL Data Insights) in the previous section, but developers looking to use them need to be knowledgeable in their usage and functionality.
Another impact may be longer column names. As of Db2 13 FL100 the maximum length of a column name grows from 30 bytes of EBCDIC, up to 128 bytes. However, even though you can use longer column names, most shops probably will not. This is because there is only limited support for using these longer column names. First of all, there is a ZPARM setting that must be turned on (TABLE_COL_NAME_EXPANSION) before longer column names can be used. And even then, although you can now define column names greater than 30 bytes (up to 128), the column name might be truncated. Furthermore, column names returned in the SQLDA contain 30 bytes at most.
As of FL502, it becomes possible for the OPTIMIZE clause to be specified in SELECT INTO statements. So, it is now legal to code something like OPTIMIZE FOR 10 ROWS on your SELECT INTO SQL statements.
One of the most interesting, and perhaps one of the most dangerous, new capabilities in Db2 13 for z/OS is application-level lock control. This new feature enables applications to take more control over Db2 locking. If you have applications that could benefit from different lock parameters than the system-wide settings used by Db2, then this new capability could be useful for at least some of your applications and tasks.
The first thing to note is that you must be at FL500 before you can use application-level lock control. Using application-level lock control then requires setting a special register using the SET CURRENT LOCK TIMEOUT statement. This statement can be included in application programs to control the lock wait duration in seconds. The data type is INTEGER with a range of acceptable values from -1 to 32,767. Setting the CURRENT LOCK TIMEOUT to -1 indicates an indefinite wait, setting it to 0 indicates no waiting.
Most DBAs reading that last sentence will shudder at the possible implications of waiting forever! Fortunately, there is another new DSNZPARM called SPREG_LOCKTIMEOUT_MAX that can limit the upper bound that an application can use for CURRENT LOCK TIMEOUT.
Nevertheless, in order to implement application-level lock control you will need to modify your application code. So, if you want to wait for locks for up to 50 seconds, you would issue the following before the SQL that should wait for that duration:
SET CURRENT LOCK TIMEOUT = 50
Of course, any applications using application-level lock control should be monitored for lock contention. This can be done using Db2 monitoring tools, such as IBM’s Omegamon or BMC’s Mainview. The trace record IFCID 437 can also be monitored to discover the specific applications and authorization IDs that use this special register.
Similar to controlling the lock timeout duration, Db2 13 also introduced the ability to manage deadlock resolution control at the application level. This can be achieved using a new system built-in global variable. Instead of just relying on the system setting to control deadlock detection, applications can choose to set the new global variable named SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY.
Valid values range from 0 to 255. The higher the value, the less likely that locks requested by the application will deadlock when the application is involved in a deadlock situation. Applications and users require the WRITE privilege on this global variable to be able to issue it successfully.
So, if you want to set this to the max, you would issue:
SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = 200
And then issue the statement(s) you are concerned about deadlocking.
Of course, using this global variable does not guarantee that the application won’t experience a deadlock because there are other considerations involved that Db2 still must negotiate and consider. It is also important to note that you can use Profile tables to set CURRENT LOCK TIMEOUT and SYSIBM.DEADLOCK_RESOLUTION_PRIORITY.
One additional precaution for the new application-level locking control capabilities: you should always talk to your DBAs before attempting to modify the system-wide lock behavior that is set for Db2. Improperly adjusting lock behavior can cause system availability and performance issues if not enacted carefully.
And finally, application developers should also regularly consult the documentation for new, changed, and deleted Db2 messages and codes, as well as for deprecated functionality and incompatible changes.
Next time we’ll look at Db2v13 improvements in the areas of Availability, Resiliency, and Scalability and Performance.
Previously:
Regular Planet Mainframe Blog Contributor
Craig Mullins is President & Principal Consultant of Mullins Consulting, Inc., and the publisher/editor of The Database Site. Craig also writes for many popular IT and database journals and web sites, and is a frequent speaker on database issues at IT conferences. He has been named by IBM as a Gold Consultant and an Information Champion. He was recently named one of the Top 200 Thought Leaders in Big Data & Analytics by AnalyticsWeek magazine.