As I talked about in the previous weeks, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these features can really benefit your standard DBA operations, improve application performance and overall availability of your systems and data.
Skip Uncommitted Inserts zParm
Another one of the enhancements with DB2 Version 9 for z/OS that helps with your very robust tables and their locking is the new zParm, Skip Uncommitted Inserts – SKIPUNCI. If your applications are still deadlocking after going to row level locking you might want to look into this new zParm, Skip Uncommitted Inserts, because it works as its name indicates.
By enabling (YES) for the new Skip Uncommitted Inserts zParm within your system, your applications can avoid deadlocks.The zParm enables DB2 applications bound with a read stability (RS) level that are referencing a row-level locking table to skip locks of uncommitted inserts of other applications. This allows the applications to avoid waiting for those other applications to commit their rows and avoid deadlocking.
By skipping the other application’s uncommitted rows and not waiting for these other applications, these uncommitted rows are totally bypassed. These uncommitted rows would not be available or known in your application’s SQL result set or any of their key values. This can be a downside if these uncommitted rows could affect further processing or the key values are needed within your application. This is the only downside, because if your application needs to know about these other application’s uncommitted rows, this zParm is not for your system.
The Skip Uncommitted Inserts zPARM along with the proper tablespace partitioning, PCTFREE, FREEPAGE and MAXROWS parameters provide the diversity of settings to handle the most robust transaction volume in the world and avoid deadlocks.Check out all the new zParms in DB2 to help many different aspects of your system and application performance within DB2 Version 9 for z/OS.
Leave a Reply