International Business Machines Corporation (IBM) Earnings Call Transcript & Summary

Hello. Good morning, and good evening, wherever you are in the world. This is John Campbell, a distinguished engineer in IBM Db2 for z/OS Development. This webcast is Part 2 of my webcast on Db2 12 for z/OS migration planning and experiences, and it will be immediately followed afterwards by a live question-and-answer session. Now let's turn to the next slide. Here on Slide 2, I would like to go over the objectives of this 2-part webcast. I would like to share lessons learned, surprises and pitfalls; provide some hints and tips; address some myths that have grown up around Db2 12; provide additional planning information that you'll not find elsewhere; provide usage guidelines and positioning on the new enhancements; and most importantly, I want to help customers migrate as fast as possible, but to do it safely. Now let's turn to the next slide. Here on Slide 3, I would like to quickly review what we covered yesterday in Part 1 of this webcast. In Part 1, we covered the following topics: the Db2 11 and prerequisites for migration to Db2 12; quick hits about the Db2 12 migration itself; recommendations about preventive service planning for both Db2 12 and Db2 11; advice on risk mitigation about the Db2 12 migration; a tutorial about continuous delivery with Db2 12 with an understanding of function levels; recommended best practice for continuous delivery with Db2 12; and the JC recipe for a successful migration. Now let's turn to the next slide. Here on Slide 4, I would like to introduce the topics that I'm going to cover in Part 2 of this webcast today. I am going to talk about the features and functions which are the greatest hits of Db2 12. This is based on experience gained during the early support program and also early customer production deployment and customer motivation for an early deployment of Db2 12. In each case, I will discuss the design points of the feature function, how to use it, considerations which apply, any limitations that exist and also the pros and cons. Now let's turn to the next slide. Starting here on Slide 5, I would like to start highlighting the greatest hits of Db2 12 based on customer experience during the early support program, custom production experience and customer motivation for early production deployment of Db2 12. To start with, I would like to highlight the performance savings. Performance sells, and any performance savings and especially a reduction in CPU resource consumption is most welcome to all the customers in my worldwide social circle. The rebinding of migrated packages with APREUSE (ERROR) or APREUSE (WARN) can generate savings in CPU resource consumption. This will come from the Db2 12 runtime performance improvement and from the re-enablement of fast column processing routines. Dynamic prefetch scheduling avoidance, where there's a very high index buffer pool hit ratio and very few pages, if any, are brought into the buffer pool with dynamic prefetch. This is an autonomic function that will disable and re-enable dynamic prefetch based on the prevailing buffer pool conditions. This can generate savings in CPU resource consumption from avoiding the scheduling of dynamic prefetch engines, reduce latch class 24 contentions and the avoidance of prefetch engine unavailable condition. Fast Index Traversal or FTB for short, which is an in-memory performance optimization for indexed reversal, where there is random access to the index. This has the potential to significantly reduce the number of getpages to indexes and thereby generate significant CPU savings. Dynamic plan stability. To stabilize the performance of high-volume repetitive SQLs and avoid prepare activity during high turnover periods in the dynamic statement cache. More granular values for global commit LSN and read LSN values has the potential to improve the effectiveness of global lock avoidance and the reuse of space left by delete during insert processing. LOB compression requires zEDC hardware support. This can be very effective in compressing textual data and generate significant DASD space savings. The REORG and LOAD utilities have been enhanced to exploit statistics profiles, which can be used during in-line statistics collection. The next area of attractive feature function is in the area of application development. There are 2 new alternate SQL syntaxes for SQL pagination. One is called data-dependent syntax and the other one is numeric-based pagination. Both of these new alternative syntaxes can improve development productivity. The enhanced SQL MERGE capability is now much more powerful. There are benefits in terms of development productivity, improved performance and easier application porting to Db2 for z/OS. And lastly, native REST services, which have proved to be very popular amongst our customer base. And this feature has been retrofitted back to Db2 11. Now let's turn to the next slide. Here on Slide 6, I would like to continue highlighting the greatest hits of Db2 12. There are a number of high-value feature functions in the availability area. The ability to insert a new partition in the middle of an existing UTS partition-by-range table space. This makes it much easier to handle a partition-full condition: just add the new partition and run real against the new and the adjacent partitions, and this procedure will be easy to automate. Larger size active log datasets so you can keep at least 6 hours of recovery log data in the active log configuration at all times and possibly keep up to 24 hours or more. The introduction of the relative page numbering option for UTS partition-by-range table spaces opens up the possibility of tremendous improvements in terms of availability and usability. One example is an online immediate ALTER to increase the size of an individual partition which is running out of space, and there's no need for any REORG. Another example is lifting the size limit of an individual partition to 1 terabyte. Asynchronous systems-managed duplexing of the CF1 log structure to greatly reduce the z/OS host CPU overhead, reduce the latency in granting global lock requests without contention and an increase in the practical distance for multi-site sysplex operations. The last area of feature function that I would like to highlight is in the security area. There's a new feature called SQL TRANSFER OWNERSHIP, which enables you to transfer the ownership of data objects to a new owner and to revoke the existing privileges. There is support for databases, indexes, STOW groups, table spaces and views. Now let's turn to the next slide. Here on Slide 7, I would like to start talking about a performance improvement in Db2 12 called Fast Un-clustered INSERT. INSERT workloads are amongst the most prevalent and performance critical. The performance bottleneck in terms of INSERT throughput will vary across different INSERT workloads. It could be indexed maintenance, log write I/O, data space search, format write during dataset extend, metro mirror PPRC disk mirroring and network latency. This particular solution targets the problem of the data space search, where there's excessive contention on the space map and on the data pages, and there are false leads when the space map indicates that a particular data page has space, but then after touching the data page, there is no longer sufficient space available. But it's worth bearing in mind that the most common constraint on INSERT throughput is index maintenance and/or log write I/O. These factors tend to dominate and mask any INSERT speed bottleneck on the table space. Now let's turn to the next slide. Here on Slide 8, I would like to continue discussing the Db2 12 performance enhancement related to Fast Un-clustered INSERT. This enhancement is officially referred to as Insert Algorithm 2 or IAG2 for short. Sometimes this enhancement is referred to as smart insert or even fast insert. This enhancement potentially delivers significant improvement for unclustered inserts. For example, the application journal table pattern, where you have both the following conditions: heavy concurrent insert activity from many concurrent threads and the space search and false leads on the data is the constraint on the overall insert throughput. The enhancement applies to any universal table space defined with the member cluster attribute. And within that, it applies to both tables defined as a APPEND YES and also APPEND NO. Db2 12 implemented an advanced new insert algorithm to streamline the space search and the space utilization. It eliminates page contention and false leads because the space is pre-allocated and preassigned in order to fill up pipes which are pulled from and the set of pipes per page set partition per Db2 member. The space allocation occurs at page set open time and also real time when there is space shortage in each individual pipe. After entry to function level V12R1M500, the default is to use the new fast insert algorithm for qualifying table spaces. There are 2 controls. There's a system parameter DEFAULT_INSERT_ALGORITHM, which is a system-wide default, and there's a new attribute on the table space called insert algorithm. And the setting for insert algorithm will override the system parameter value for a DEFAULT_INSERT_ALGORITHM. Our most important point here is that Insert Algorithm 2, which helps Fast Un-clustered INSERT, is not a replacement for the existing insert algorithm, let me call it Insert Algorithm 1. Now let's turn to the next slide. Here on Slide 9, I would like to compare and contrast the 2 different types of insert space search algorithm. On the left-hand side of the slide is a picture for the general insert space search algorithm, which we can call Insert Algorithm 1. Data manager will use the space map page to identify a candidate data page, which has the promise of having sufficient free space for the subject row to be inserted. However, after accessing the data page, there may be insufficient space left for the subject data row, although data page may now be full. Data Manager will then have to go back to the space map page again to find a data page with the promise of having sufficient free space. How can this happen? With many concurrent threads in searching, each thread is competing and chasing after the same free space. This can result in page P-lock and page latch contention on the space map pages because of updates in the space class information and the modified page indicators and in combination with the free space lookups. If you are using row-level locking, this has the potential to get even worse due to page P-lock and page latch contention on the data pages. On the right-hand side is a picture of Insert Algorithm 2. With Insert Algorithm 2, at page set partition open and based on the space map information, pages are pre-distributed into different pipes based on the amount of free space: 25% free, 50% free, 75% free and empty pages. The individual threads will pull pages from the respective pipe as opposed to performing a space map lookup. This guarantees that each thread will be working on different pages. This is how the space map and data page contention problems and also the false lead problems are eliminated. Now let's turn to the next slide. Here on Slide 10, I would like to continue discussing the Db2 12 performance enhancement related to Fast Un-clustered INSERT. Your mileage for Insert Algorithm 2 will vary. Many insert workloads will see no improvement, and that is to be expected. You will probably not see much difference or improvement when there's only one insert per commit scope. However, some specific insert workloads may see significant improvement, and you'll see less benefit as more indexes are added to the respective table. Insert Algorithm 2, where it benefits, will shift the bottleneck to the next constraining factor. LOAD SHRLEVEL CHANGE can also use the Fast Un-clustered INSERT because LOAD SHRLEVEL CHANGE drives insert under the covers. Fast Un-clustered INSERT will be disabled when lock escalation occurs or there's use of SQL LOCK TABLE by the application. And Insert Algorithm 2 is available after new function activation in Db2 12; in other words, when you've activated function level V12R1M500. Now let's turn to the next slide. Here on Slide 11, I would like to continue discussing the Db2 12 performance enhancement related to Fast Un-clustered INSERT. The APAR PH02052 has now closed, and this implements automatic reenablement of Insert Algorithm 2 on an object with retry logic. Things like lock escalation and the application use of SQL LOCK TABLE can cause Insert Algorithm 2 to be disabled on an object. And prior to this APAR, the only way to re-enable Insert Algorithm 2 was to stop the object and start the object and clearly, that was not good for high availability. So this APAR is very important from an availability viewpoint. I would now like to provide a set of John Campbell point-in-time recommendations regarding the Fast Un-clustered INSERT. One size probably does not fit all table spaces in terms of using Insert Algorithm 2 for all UTS table spaces defined with member cluster. You should change the system-wide default and set the system parameter DEFAULT_INSERT_ALGORITHM to 1. In other words, use the old general insert algorithm. You should use Insert Algorithm 2 selectively at the individual table space level to override the system-wide default when it makes sense to use Insert Algorithm 2. An additional benefit can be gained with CICS and DRDA thread reuse, coupled with packages bound with RELEASE(DEALLOCATE) option. Now let's turn to the next slide. Here on Slide 12 is an example of shifting the insert throughput bottleneck. This is an example of a concurrent DRDA application insert workload, which does not use multi-row insert. The chart compares the relative performance across Db2 11 and Db2 12. You can see that there is a big drop in the number of getpages under Db2 12 when Insert Algorithm 2 is applied. You can see a drop in the accounting Class 2 CPU time and a very significant drop in the accounting Class 2 in Db2 elapsed time. However, while there is an improvement in the application response time, it is not as good as one might have expected. This is because Insert Algorithm 2 has shifted the bottleneck to the next constraining factor, which is DRDA network latency. This DRDA application is driving signal to the inserts and not using multi-row insert. So there is DRDA network latency per insert. Now let's turn to the next slide. Here on Slide 13 is an example which showcases the potential of the Fast Un-clustered INSERT when using Insert Algorithm 2. This application workload scenario was based on the re-create of a customer performance PMR, which came in under Db2 11. This scenario involves 800 concurrent threads inserting at the end of a UTS partition-by-growth table space, defined with member cluster and lock size row. This workload hits the sweet spot for the new Insert Algorithm 2. The chart compares the relative performance of the insert workload across Db2 11 and Db2 12. You can see a 24x improvement in insert throughput at 1/64 of the CPU cost on the Db2 12 when Insert Algorithm 2 is applied. A very impressive result, and it clearly showcases the benefit of Insert Algorithm 2 when you hit the sweet spot. Now let's turn to the next slide. Here on Slide 14, I would like to introduce and discuss the enhancements to real-time statistics RTS in Db2 12. There are new messages, DSNT535I, DSNT536I, when there are problems when Db2 is externalizing in-memory statistics block information to the real-time statistics tables because resources are not available. There's a new column called GETPAGES, which has been added to both SYSTABLESPACESTATS and SYSINDEXSPACESTATS, and this new metric is very valuable. It records the number of getpage requests since release migration, the last REORG, the last load replace or since object creation. But please do not rely on this value while still running in mixed release coexistence. We have also implemented the option to allow you to collect the history of RTS table information. This requires functional level V12R1M500 to be activated or later. The SQL DDL changes required were performed by CATMAINT on the initial Db2 12 migration. The history is activated by altering the respective table, adding the versioning clause to both SYSTABLESPACESTATS and SYSINDEXSPACESTATS. No indexes are provided, so you must roll your own indexes to speed up your SQL queries. MAXPART 1 is also hard wired for the history tables. And you must also develop your own procedures for the cleanup of history tables and the associated housekeeping. Now let's turn to the next slide. Here on Slide 15, I would like to switch topics and begin introducing discussing Fast Index Traversal. This is an in-memory index performance optimization and one of the most important performance features of Db2 12. It is used for fast index lookup by avoiding expensive index B-tree traversals. In order to benefit, the access must be random. SELECT can clearly benefit but also INSERT, DELETE and UPDATE. There is a Separate Fast Traversal Block memory area that's allocated outside of the buffer pools. This area uses a concatenated structure, containing a copy of the nonleaf pages only, and it uses a relative structure. This memory area does not use the buffer pool. The nonleaf pages, except the root page, are not fixed in the buffer pool. And pages are eligible for stealing and can be LRUed of the buffer pool when the nonleaf are stored in the FTB memory area. There is improved performance because the fast traverse block is an L2 cache aware B-Tree like structure. Each page is equal to 1 cache line in size, which is 256 bytes. One of the ESP customers saw a 9% CPU reduction with a 3-level index and almost a 23% CPU reduction with a 4-level index. Your mileage in terms of CPU resource consumption reduction will vary. I do strongly recommend, however, that you be aggressive in applying preventative service in this area. And in particular, you should apply the PTF for the PE resolution APAR PH07545. Now let's turn to the next slide. Here on Slide 16, I would like to continue discussing Fast Index Traversal. The amount of memory allocated for Fast Index Traversal is controlled by the Db2 system parameter INDEX_MEMORY_CONTROL. The default setting for INDEX_MEMORY_CONTROL is AUTO. With the AUTO setting, the size of the memory area allocated for Fast Index Traversal is allocated as 20% of the total allocated buffer pool sizes. It's subject to a minimum size of 10 megabytes and a maximum size of 200 gigabytes. For those customers who have very large local buffer pool sizes, 20% is probably too much. So several customers in my social circle have set the parameter INDEX_MEMORY_CONTROL to an absolute value. There's also another limit to be aware of, which is that there's a maximum limit of 10,000 FTBs, and you've got 1 FTB per index partition. Each Db2 member using a daemon will determine independently what are the good candidate index partitions. In order to qualify, the index must be unique. Unique index INCLUDE COLUMNS are supported. The index entry length in terms of the key and the additional columns has a maximum size of 64 bytes. Db2 will reevaluate every 2 minutes and adjust the priority queue of index partitions. For each index partition object, Db2 maintains a counter. For each index traversal, the counter will bump by 1. If there's index only, the counter is bumped by 2. If there's an index leaf page split, then the counter is divided by 2. And if there's an index lookaside event, then it's reduced by 1. And then an internal threshold is then applied. So clearly, from this calculation, you can see the indexes that frequently go through index leaf page splits are penalized and are unlikely to be good candidates for the Fast Index Traversal. Control is provided by a new catalog table called SYSINDEXCONTROL. You can put an entry in there to indicate preference for specific index partition or you could put entries in there as well to disable Fast Index Traversal for specific index partitions. Now let's turn to the next slide. Here on Slide 17, I would like to continue discussing Fast Index Traversal. How does an index partition come into the FTB area? The answer is Db2 has a daemon task, which is zIIP eligible and runs every 2 minutes. This daemon task has a system agent correlation identifier of 014.IFTOMK00. On this slide here is an example of the output of the Db2 display thread type system command, and this shows the daemon task. Now let's turn to the next slide. Here on Slide 18, I would like to continue discussing Fast Index Traversal. In terms of monitoring, you can use the Db2 DISPLAY STATS(INDEXMEMORYUSAGE) to identify which index partitions are using Fast Index Traversal and how much memory is used by each index partition. In terms of performance tracing, you can use IFCIDs 477 and 389. IFCID(477) is part of performance trace Class 4, and this records the allocation and deallocation of FTBs for Fast Index Traversal. IFCID(389) is part of statistics trace Class 8, and this records all index partitions whilst you're using Fast Index Traversal. Now let's turn to the next slide. Here on Slide 19, I would like to continue discussing Fast Index Traversal. The FTB area for an index will become deallocated under the following conditions: pageset close, SQL mass delete, ALTER INDEX, RECOVER INDEX, REBUILD INDEX. And also when you alter an index from COPY YES to COPY NO and vice versa, this technique of changing the copy option for an index is a very useful technique if you're evaluating performance under Db2 12, comparing with and without Fast Index Traversal. Now let's turn to the next slide. Here on Slide 20, I would like to continue discussing Fast Index Traversal. On this slide, I'm using a high-level picture to discuss the data sharing considerations. In this example, there is a 2-way data sharing group configuration with members Db2 A and Db2 B. Each member has multiple local buffer pools. The index subjects may or may not be spread over multiple local buffer pools. There is just 1 FTB memory area per Db2 member. And the FTB area is a lookaside area outside of the local buffer pools. Page registration, buffer update and page cross invalidation occurs as per normal Db2 data sharing protocols. If an index partition is allocated in the FTB area that when an index page split occurs, then the FTB area must be refreshed. This is handled by a special type of P-lock and IRLM notify messages. Now let's turn to the next slide. Here on Slide 21, I would like to conclude the discussion about Fast Index Traversal. On this slide, I want to talk about migration considerations for data sharing groups. Fast Index Traversal is available in mixed release coexistence. When you're running a mixture of Db2 11 and Db2 12 across the members of the data sharing group or all the members of the data sharing group are on Db2 12, but before new function activation; in other words, the functional level is still at V12R1M100. At this stage, FTB will only be used when the index partition object is not GBP-dependent. If an index partition object becomes GBP dependent, then the FTB content will be deleted and bypassed. After new function activation, when the function level of the data sharing group is at least V12R1M500, then FTB can now be used when the index partition object is GBP-dependent. Now let's turn to the next slide. Here on Slide 22, I would like to talk about the 2 new alternate syntaxes for SQL pagination. Existing techniques for writing efficient SQL for pagination are quite challenging. And the aim of these 2 new alternate syntaxes is to greatly improve development productivity. The first syntax is called data-dependent pagination, and there is an example on this slide. This new syntax has a performance advantage predicated on having the correct index design and the correct ORDER BY on the SQL query. This will enable Db2 to go directly to the needed rows and Db2 can exploit the range-list index scan access path that was implemented in Db2 10. The alternative syntax is called numeric-based pagination and, again, there is an example on this slide. In this case, Db2 will have to skip through the unneeded rows. And if rows are being deleted and inserted in the result set from other applications in the between time, then the application may see the same rows twice or not see the rows at all. Many existing applications which are using static scrollable cursors can now be replaced by SQL pagination. This has a number of advantages. The result set is no longer materialized. Read-only applications will no longer have to create long-running URs and performance can be greatly improved. In order to use any of the new syntaxes, this requires APPLCOMPAT(V12R1M500). And experience from customers is that both these syntaxes work very well as advertised. Now let's turn to the next slide. Here on Slide 23, I would like to discuss the increase in log record size, initially after converting the BSDS on the Db2 11 new function mode and a further increase later after entry to Db2 12. There is about a 50 byte increase after converting the BSDS on the Db2 11 new function mode, and there will be a further increase in log record size after entry to Db2 12 because of larger 7-byte RID values, which are there in support of UTS PBR relative page numbering. There is an increase of about 20 bytes for the table space and about 28 bytes for the index. The graphic on this slide shows the results for one specific data sharing customer. They saw a 23% increase in log record size after migrating from basic LRSN to extended LRSN format, and experienced a further 10% increase after entry to Db2 12. So for most customers, this means increasing the size and number of active log data set, and it also means increasing the size of a DASD pool for archive logs. Now let's turn to the next slide. Here on Slide 24, I would like to begin introducing and discussing the enhancement known as Dynamic Plan Stability. This is a welcome new feature that will bring some relief in the area of performance management of dynamic SQL. The main goal is to provide consistent, more reliable performance, and the sweet spot is short-running SQL that is executed thousands of times. And it will also help greatly with high turnover periods in the dynamic statement cache. In Db2 11, a miss in the dynamic statement cache will require a new full prepare, and this is very expensive in CPU processing terms. There are also going to be periods of high turnover, which could result in a tsunami of pool prepare activity, such as after a Db2 subsystem recycle, after a release migration or after executing RUNSTATS. In Db2 12 now, using this feature, you can stabilize prepared query statements from the dynamic statement cache and persist these in the Db2 catalog. As a result, when there is a miss in the dynamic statement cache, no new full prepare is needed. The prepared statement will be loaded into the dynamic statement cache from the persisted copy in the Db2 catalog. And these prepared statements stored in the catalog will be invalidated by SQL DDL just like a static SQL package. With this feature, you can stabilize specific dynamic SQL requests. And you can also stabilize dynamic query statements that have been executed a certain number of times. Now let's turn to the next slide. Here on Slide 25, I would like to discuss the considerations which apply and the current restrictions about the use of Dynamic Plan Stability. A change of APPLCOMPAT through the special register or changes in other special registers like DEGREE and OPTHINT will cause a miss in the dynamic statement cache. There's no REBIND capability to repair prepared statements after invalidations. You have to wait for a new stabilization. In terms of restrictions, the display command has only local scope. There's no support for literal concentration, and there's no support for query statements against temporal and transparent archive. You need to be careful about the size of stabilization groups. When you free a stabilized dynamic query statement group, this will not only delete these prepared statements out of the catalog, but it will also invalidate those statements in the dynamic statement cache, and this may result in a storm of full prepares. Also realize that stabilized dynamic query statements do consume more CPU than the equivalent static query statement, and this is because there is still a short prepare processing. Now let's turn to the next slide. Here on Slide 26, I would like to discuss the Db2 12 enhancement to provide more granular values for global commit LSN and global read LSN. Prior to Db2 12, Db2 only used a single value for global commit LSN checking or GBP-dependent objects. All it took was a single long-running UR, and that would fix the value for the global commit LSN value. And as a result, global lock avoidance and space reuse on insert would degrade. It's a similar story for global read LSN, except the problem here is long running readers and there's one value per buffer pool. So what happens in Db2 12? Db2 does not actually track a more current value for each and every individual object. Each member maintains 2 global lists of the 500 objects that have the oldest commit LSN and the oldest read LSN values. These global lists are built by a system task that wakes up every 2 seconds, and then it rebuilds its own list and then merges it with every other member's list to create the global list. When it comes time to pick an object's commit LSN or read LSN value, then Db2 will check the appropriate global list for the object. If the object is on the list, then we know exactly what the LSN value is for that object. If it is not on the list, then Db2 will use an alternate LSN value based on the newest object because that value cannot be worse than this value. Either way, Db2 will compare the LSN picked up with the old global value from the SCA and use that if it is better. So this is a very nice enhancement that has great potential to improve lock avoidance and space reuse on insert when the inevitable long-running reader and/or long-running UR is in play. Now let's turn to the next slide. Here on Slide 27, I would like to discuss LOB compression. This feature requires a zEDC hardware feature. Db2 will decompress existing compressed LOB data if the zEDC hardware feature is not available, but it will not compress a LOB if the zEDC hardware feature is not available. The in-line portion of a LOB is completely separate from LOB compression. LOB compression only applies to the out-of-line portion of the LOB that is stored in the auxiliary table space. So basically, the in-line and the outline portion of a LOB are split and are compressed independently. LOB compression is aimed at textual data. It's not aimed at video and audio that's already heavily compressed, such as MP3 or MP4. Now let's turn to the next slide. Here on Slide 28, I would like to discuss the important SQLCODE -109 issue. The problem is nondocumented and illegal use of SELECT ... INTO ... UNION ALL syntax for noncursor selects. We have received customer complaints about this. The syntax can produce wrong results. And basically, this is a defect that we needed to fix. The solution was to close the loophole in Db2 12, and we retrofitted this solution back to Db2 11 with the APAR PI67611. However, customers immediately complained because some of their existing applications were making use of this illegal SQL syntax. So we introduced a new Db2 system parameter called DISALLOW_SEL_INTO_UNION. The value for this system parameter was NO under Db2 11. And Db2 would have continued to allow usage of this illegal SQL syntax when the usage was encountered during execution of a BIND or a REBIND command. Db2 will write an incompatibility trace record when IFCID 376 is active. And customers can use these trace records to identify and correct applications that are using this illegal SQL syntax. Now most importantly, the value for the system parameter has changed to YES in Db2 12. Now Db2 will disallow usage of this illegal syntax. Statements that are including this syntax will fail with SQLCODE -109. If you run IFCID 376 under Db2 11, it will help you identify the problem applications. All customers need to deal with this potential issue before migration to Db2 12 or change the Db2 12 default for the subject system parameter to NO. Now let's turn to the next slide. Here on Slide 29, I want to start discussing the greatly enhanced SQL MERGE capability in Db2 12. Db2 12 delivers an ANSI-compliant MERGE capability. As a result, SQL MERGE is now very powerful. The source can now include TABLE, VIEW and full select. You can have additional predicates on MATCHED and NON-MATCHED. You can also do DELETE. You can do multiple UPDATE, INSERT and DELETE phrases, but not on the same row, but you can accept SIGNAL and IGNORE. There are some very nice benefits in terms of development productivity, improved performance, easier allocation porting to Db2 12 for z/OS. But it does require APPLCOMPAT(V12R1M500). Now let's turn to the next slide. Here on Slide 30, I would like to continue discussing the greatly enhanced SQL MERGE capability in Db2 12. But now the SQL MERGE capability is now so powerful. The input can be a SELECT (JOIN), returning many rows, either millions or billions, and the number of UPDATEs, INSERTs and DELETEs could explode. Therefore, there are some considerations when using the SQL merge capability. There's no intermediate commit points. You could have long rollback times. It could also trigger lock escalation and have an impact on concurrency. And there's no SQL pagination support. Now let's turn to the next slide. Here on Slide 31, I want to start introducing and discussing the new relative page numbering option for UTS partition-by-range table spaces. To begin with, I'd like to discuss the motivation. The first item is a tremendous improvement in terms of availability and usability. The DSSIZE can now vary across the different partitions. The DSSIZE can be increased for an individual partition with zero application impact. It's now an immediate ALTER and no REORG is required to increase the DSSIZE. If you do want to decrease the DSSIZE, this is still a pending ALTER, and this requires a full table space level REORG. The other motivation is scalability. The maximum partition size has now been increased to 1 terabyte. The maximum table size has been increased to 4 petabytes. And the maximum number of rows in a table has increased from 1.1 trillion to 280 trillion. Now let's turn to the next slide. Here on Slide 32, I would like to continue discussing the new relative page numbering option for UTS partition-by-range table spaces. Migration is possible from either classic partitioned or from UTS partition-by-range table spaces. In terms of the steps for conversion, if you're starting from an existing classic partition table space, then you need to alter the table space specifying SEGSIZE, and this will become a pending ALTER. Then in step 2, alter the table space specifying PAGENUM RELATIVE, and this will put the table space into advisory REORG pending. And the third step is to perform a REORG of the complete table space. The base and XML table spaces can be migrated separately and can coexist running with mixture of RELATIVE and ABSOLUTE attributes. Having migrated to relative page numbering, it's a one-way ticket. There's no ability to fall back to absolute page numbering. Extended Addressability must be used for UTS partition-by-range table spaces using relative page numbering. The DASD space requirement for such large datasets can lead to problems, for example running out of volumes. Datasets can only be spread across 59 volumes. So for example, if you've got 1 terabyte dataset, this requires 3390 Model 27 DASD or above. And in order to use this relative page numbering option, you require APPLCOMPAT(V12R1M500). Now let's turn to the next slide. Here on Slide 33, I would like to continue discussing the relative page numbering option for UTS partition-by-range table spaces. Here on this slide, I would like to talk about a couple of potential migration issues. Relative page numbering forces the use of a part-level image copies when you're running REORG. Now if these image copies are going to tape, then there may be an issue when converting an existing table space to relative page numbering or you're creating a new table space using relative page numbering because it may lead to a shortage of tape drives. To provide some relief, there's going to be a new TAPEUNITS option on REORG that will be made available with APAR PI75518. This APAR is still open. The second potential issue is the existence of pre-version 6 range partitioned table spaces with limited key values that have been truncated at 40 bytes. These table spaces cannot be converted over. Now this should only affect a small number of customers. The problem is fenced and the conversion will not succeed. When you issue the ALTER TABLESPACE PAGENUM RELATIVE, it will fail with SQLCODE -650 and reason code 39. At present, there's no target date for providing relief on this issue. Now let's turn to the next slide. Here on Slide 34, I would like to conclude my discussion about the relative page numbering option for UTS partition-by-range table spaces. There are some other considerations. After conversion to relative page numbering, indexes will increase in size because of the larger 7-byte RID values. So therefore, I recommend that you consider the use of index COPY and RECOVER for very large NPIs. And note, you can no longer identify the partition number from the page number. Now let's turn to the next slide. Here on Slide 35, I would like to introduce an attractive feature of Db2 12, which is called Insert Partition. This feature is both popular, and it also works very well based on customer feedback. Conceptually, this gives you the ability to insert a new partition in the middle of a partition-by-range table space wherever it is required. It is available for UTS partition-by-range table spaces only, but there's no requirement for the relative page numbering option. One restriction is there'll be no LOB or XML data associated with this table space. In order to insert the partition, you just simply issue an alter, adding a part and providing an ending key, and that will become a pending alter. Then the necessary REORG to deal with the pending alter can be limited to a minimum subset of partitions, including the new partition and the adjacent partition. Please be aware that the logical partition numbers have to be translated to physical partition numbers. The new physical partition is added at the end. The new logical partition is added in the middle, and the logical partitions are appropriately renumbered. So this can be an awkward consideration with utilities when you use a range of parts, as it is based on the physical partition numbers. With this feature, you do not have to take care of adjacent parts, which possibly reach their space limit. Once you determine the limit key for new inserted partition, the procedure for handling partition full conditions is much easier to automate. You simply add the new partition and run the REORG against the new and the adjacent partitions. This feature is available when you use APPLCOMPAT(V12R1M500). Now let's turn to the next slide. Starting here on Slide 36, I would like to introduce and discuss the new asynchronous flavor or systems-managed duplexing of the CF LOCK1 structure. And on this slide, I want to talk about the challenges. Systems-managed duplexing of the CF LOCK1 structure is required for the highest availability in a Db2 data sharing environment, where you've got single or multi-site Parallel Sysplex environment where there's no failure isolated CFs or external CFs. Without systems-managed duplexing, the failure of the wrong CF may result in a group-wide outage. This is because LOCK1 or SCA can only be dynamically rebuilt into an alternate CF if all the Db2 members survive the failure. The existing synchronous systems-managed duplexing of the CF LOCK1 structure can be very expensive in terms of the increased host CPU resource consumption, degraded application elapsed time performance and aggravated global lock contention. Why is this? All types of lock requests are duplexed. The duplexed requests can consume 3 to 4x the host CPU compared to a simplex structure. Synchronous lock requests are converted to asynchronous requests to limit the host CPU penalty. CF service times will increase, which will elongate transaction response times and batch processing elapsed time and possibly aggravate global lock contention. The performance impact will vary. It's very dependent on the locking intensity of the respective application workload, and it will also be aggravated by stretched distance for a multi-site data sharing group. Now let's turn to the next slide. Here on Slide 37, I would like to review how the existing synchronous flavor of system-managed duplexing of the CF LOCK1 structure works. At Time 1, the Db2 member requests a global lock on behalf of the application, and that global lock request is propagated by the associated IRLM to the associated system lock manager SLM in the XES component of z/OS. At Time 2, the global lock request is propagated by XES to both the coupling facilities, containing the respective primary and secondary LOCK1 structures. At Time 3, the pair of coupling facilities exchange messages to make sure that the global lock request is persistent in both the primary and secondary LOCK1 structures. At Time 4, both coupling facilities respond back to the originating system and XES. It is only at Time 5 that XES is able to respond back to the originating IRLM, which responds back to the originating Db2 member to indicate that the respective global lock has been granted and that processing can now continue. Hopefully, you can clearly see that with this processing model, there is delayed response times for global lock requests when the systems-manage duplexing of the CF LOCK1 structure is enabled, and that it would be seriously aggravated as the distance is stretched when there is a multi-site sysplex with a multi-site data sharing group. Now let's turn to the next slide. Here on Slide 38, I would like to begin introducing and discussing the new asynchronous flavor of CF Lock structure duplexing that is new in Db2 12. This new enhancement greatly reduces the overhead for systems-managed duplexing of the CF LOCK1 structure, and the secondary structure updates are performed asynchronously with respect to the primary updates. Db2 will periodically sync up with z/OS to ensure data integrity. To be specific, Db2 will check that all the modified locks have been hardened in the secondary lock structure before the corresponding undo/redo record for the update is written to the Db2 active log on DASD. The physical log writer task for Db2 performs the SYNC call to XES to query the second restructure, and it happens whenever log records get physically written to DASD, which can be earlier than commit. This enhancement greatly increases the practical distance for multi-site sysplex and data sharing operations whilst duplexing the CF LOCK1 structure. In terms of prerequisite requirements, you need IRLM version 2 release 3 and function level 40 with the following PTFs: you need Db2 at function level V12R1M100 with the PTF APAR PI66689, and you need the PTF APAR PI68378 applied to IRLM. The CFCC firmware support is required with CFLEVEL 21 at service level 2.16 on the z13. You need z/OS version 2 release 2 with an SPE with the PTFs for APARs OA47796 and OA49148 applied, and you need to configure CF to CF connectivity for the coupling links. Now let's turn to the next slide. Here on Slide 39, I would like to review how the new asynchronous flavor of systems-managed duplexing of the CF LOCK1 structure works under Db2 12. At Time 1, the Db2 member requests a global lock on behalf of the application, and that lock request is propagated by the associated IRLM to the associated system lock manager SLM in the XES component of z/OS. At Time 2, the global lock request is propagated by XES, but only to the coupling facility containing the primary LOCK1 structure. At Time 3, the coupling facility containing the primary LOCK1 structure responds back to the originating system and XES. At Time 4, the originating XES is able to respond back to the originating IRLM, which responds back to the originating Db2 member to indicate that the respective global lock has been granted and that processing can now continue. At Time 5 in the background, the global lock request is propagated asynchronously from the coupling facility containing the primary LOCK1 structure to the alternate coupling facility containing the secondary LOCK1 structure. At Time 6, the coupling facility containing the secondary LOCK1 structure processes all global lock requests in a strictly ordered queue. Before the Db2 physical log write task writes any undo/redo log records to the active log, Db2 through IRLM will issue a query call to make sure that the associated global lock request has been hardened into the secondary LOCK1 structure. Hopefully you can clearly see that the response times for global lock requests are greatly improved and that the practical distance for multi-site sysplex operations with multi-site data sharing is extended. Now let's turn to the next slide. Here on Slide 40, I would like to continue discussing the new asynchronous flavor our CF LOCK1 structure duplexing that becomes available with Db2 12. And specifically, on this slide, I would like to talk about the benefits. The cost of lock structure duplexing is significantly lower than before. The host CPU for lock requests decreases and the IRLMs receive responses sooner. Existing sites which are using the existing synchronous flavor of systems-managed duplexing should now see much lower host CPU cost and better elapsed times. More customer environments can now achieve higher availability in all-ICF configurations with the new systems-managed duplexing. There's reduced risk with asynchronous systems-managed duplexing and lower cost all around. There's reduced hardware maintenance cost, and you avoid the capital cost for the extra frames for the external coupling facilities. Any processor technology refresh applies to both the host GCP engines and also the ICF engines. But I want to make it clear that systems-managed duplexing of the lock structure is not free. You will have to acquire the ICF engine, and you'll also have to acquire the coupling links for CF to CF connectivity. The CF utilization is significantly higher for asynchronous systems-managed duplexing relative to the simplex case, but it's going to be much less than the synchronous flavor of systems-managed duplexing. It's naturally expected to be higher than simplex because there's simply more work for the CF to do. Now let's turn to the next slide. Here on Slide 41, I would like to share a performance summary, comparing the new asynchronous flavor of systems-managed duplexing of the CF LOCK1 structure versus a simplex lock structure. And these measurements are based on the SAP banking application benchmark workload. The internal throughput rate degraded by 7.5%. The response times and the external throughput rate are broadly comparable. The z/OS host CPU resource consumption was higher, and the CF CPU resource consumption is significantly higher. Now let's turn to the next slide. Here on Slide 42, I would like to discuss the automatic setup of statistics profiles under Db2 12. The same messages on this slide equally applies to the manual setup of statistics profiles prior to Db2 12. Statistics profiles were first introduced in Db2 10, but the customer adoption of this feature function has been limited. Now in Db2 12, there has been significant improvements around usability and consumability of statistics profiles. For example, both the REORG and the LOAD utilities have been enhanced to be able to use statistics profiles when collecting in-line statistics. So I expect a significant increase in customer use of statistics profiles. That is why stale or inconsistent statistics need to be cleaned out. These statistics can be cleaned out, either by surgically using SQL DELETE to carefully delete the rows out of SYSCOLDIST or by running RUNSTATS twice. On the first execution of RUNSTATS, use the reset access path option to destroy all the existing statistics and to replace them by default set. And on the second execution of RUNSTATS, collect a new replacement set using the default options. So it is important to clean out any statistics in SYSCOLDIST that you do not intend to regularly collect before the first BIND/REBIND, PREPARE or EXPLAIN after entry into Db2 12. These statistics could be stale or inconsistent today because they are not being regularly collected. They may have been previously collected based on the advice of a consultant or based on IBM service, in order to shoot an access path problem. But 2 things may have happened, either they failed to address the access path issue or they were too expensive to collect. And in many cases, customers have not cleaned out these advanced statistics. And as a result, these stale, inconsistent statistics may actually be compromising access path selection. So in fact, under Db2 11, you may be suffering from suboptimal access path selection. The statistics profiles will be created if they do not exist on the first BIND/REBIND, PREPARE and EXPLAIN after entry to Db2 12. After the initial create, you cannot tell from the subject STATISTICS PROFILES, what statistics are the ones that were previously old and inconsistent. Now let's turn to the next slide. Here on Slide 43, I would like to continue the discussion about STATISTICS PROFILES and how to prepare for using them under Db2 12. As previously advised, I strongly recommend that you clear out these stale, inconsistent statistics that exist in SYSCOLDIST before you leave Db2 11 new function mode for Db2 12. I recommend that use the sample query on this slide to identify the inconsistent statistics to clear out. Now let's turn to the next slide. Here on Slide 44, I would like to summarize the objectives of this 2-part webcast. I wanted to share lessons learned, surprises and pitfalls, provide some hints and tips based on experience, address some myths that have grown up around Db2 12, provide additional planning information, provide usage guidelines and positioning around the new enhancements and most importantly, help customers migrate as fast as possible to Db2 12, but to do it safely. Now let's turn to the next slide. Here on Slide 45, before going to the live question-and-answer session, I would like to advertise the top social media channels for Db2 for z/OS. You should register for the World of DB2, follow IBMDB2 on Twitter, join the DB2z group on LinkedIn, search for videos and other webcasts on YouTube and follow DB2z on Facebook. Now let's turn to the next slide. We have now completed the formal part of this webcast. Very shortly now, we will begin the live question-and-answer session. I will now hand control over temporarily to the moderator. Thank you.

Hello. This is John Campbell here from Db2 Development. Good morning or good evening, wherever you are in the world. I've got my 2 special guests with me again today, that's Howard Paxton from HSBC U.K.; and I've also got Ute Kleyensteuber again from Commerzbank. So what we plan to do is to go through questions that came in today as well, but also, we'd like to go back to some of the questions that we didn't answer yesterday, which are particularly relevant. So to start with, I have a question for Ute, which is -- goes along the lines of, do we have to migrate the Db2 Connect client and the Db2 Connect gateway versions in order to use Db2 12 function level 100, 500 and above? You can take that question, Ute, please.

Yes. So first of all, you have to keep in mind that the SQL is limited to the APPLCOMPAT of the execution package. So if you want to use functionality that is available with function level 100, then there is no need to do anything. We run the Db2 Connect driver versions or gateways 10.5, and that's running without any issues. So once you want to use function level 500 SQL, so SQL that is limited to the rule set of function level 500, then you have to BIND your NULLID packages with the APPLCOMPAT M500. You can let them stay there as a base. So once you want to use SQL that is limited to a function level above 500, so beginning with 501, for example, then of course, again, you have to make sure that your packages have the appropriated APPLCOMPAT. And then it's important for your direct connections that you are running the Db2 Connect versions to version 11.1 Fix Pack 1. So we are running with Fix Pack 4, but that's very easy, they were already migrated. But you can run it with Fix Pack 1 or above. If you have connections via gateway, and you want to use function level above 500, so with 501, then you should make sure that your Db2 system has 1 PTF activated, that's PH08482. That's needed that you can run with APPLCOMPAT 501 or above. And if you want, and that's one new -- nearly new PTF. If you want to run with gateway 11.1, and you don't want to upgrade your clients that are using the gateway, then you should activate and apply the PH15092, then you can run your system and use the new SQL. Thanks, John.

So Howard, here's a question from me, which is what did you find of note post migration?

Okay, John. Thanks for that. Generally, it was a good experience. We've only encountered a few issues. Generally, what we do, certainly in our busier production environments, we initially have a baseline from a performance comparison perspective. So typically focusing on peak hour online day, CICS Db2 workload from equivalent days running on version 11 and comparing that with the same day on V12 after the migration so as well as going -- looking for problems on day 1 of V12. We'd also look at day 1, hour 10, on a Monday, compare that with the previous week. We'd do that across the board for all CICS workload and then we pick some specific transactions that are key critical to the business. And we do that to -- and satisfy ourselves that regardless of whether the phone isn't ringing hot or not, or whether we're getting any instance being reported, we want to go looking for any variance with regard to resource consumption, particularly CPU. And we were pleased to report that the CPU and memory usage were within bound stroke expectations, there was no degradation on response times. Clearly, at this stage, the application packages hadn't been bound on version 12. And when we go to V12, any of the packages that were invalidated because they access some of the catalog tables, for example, there are not many of them, but there's more than a handful, they need to be rebound. But we direct those rebinds to the V11 member. So we're running the CATMAINT migration from a member that's isolated, the packages that are invalidated, we route the rebind of those to a V11 member because we don't -- we want to commit as little as possible to the next version of Db2, version 12, as possible, just in case we needed to fall back. So we would use some of the comparison to satisfy ourselves that our -- for our rolling average, bill was not going to be bigger, no nasty surprises. And would like to find out before our capacity planning people come knocking on our door. From issues at the application level, there's one that I'd relate, and it was the one that was reported about 2 or 3 hours after we'd left the building with regard to the migration. And we've had this before in previous versions of Db2, and it wasn't good programming practice. But we had a report of some events taking place in one particular application. And it tied back eventually to some dynamic SQL that had been generated by a tool, which happened to have an access path on version 11 that resulted in rows being returned in a specific collating sequence. And on V12, the rows were being returned in a subtly different collating sequence. But when one of my colleagues had a look at the SQL, he said, "Well, you haven't called in an ORDER BY within the SQL. So the fact that you were getting rows back in a collating sequence that your application logic was entirely dependent on was just good fortune, and this was a classic case of an accident waiting to happen." Fortunately, the application guys held their hands up and said, "Well, yes, we'll have to change this." But -- and we've had that previously in a separate application, but it's bizarre after a 12 version of Db2 that we've still got programmers out there who don't understand some of the fundamentals of a relational database, and how you need to -- you don't need to worry yourself about the performance of it if you are just an end user, although it's handy to have an insight into access paths. But you need to tell the optimizer what you want and how you want it to be returned. So that was not a good problem to be faced with within a few hours of a migration. But through the following week, we had virtually no major issues reported. So it was a good experience generally, John.

Okay. Thanks, Howard. So the next question came in from a customer yesterday, and I felt it was so important that we need to discuss it today. And there are 3 parts to the question, although I'm hoping that the customer, by the second question, knows the answer, but let me read it out slowly. It says is it recommended to execute the CATMAINT update and the functional level activation," they're talking about 506, on the same data window when we apply the latest maintenance to raise the Db2 code level to 506? So to keep it short, what it's saying is, is it recommended that in the same window, you apply maintenance to raise the code level to 506 and run the CATMAINT for 506 and activate 506? And the answer is, absolutely not. Okay? The whole idea about this is that best practice here, and Ute and Howard can chime in when I'm finished, is that you put some new code on, you put some -- a preventative service level on and you're doing it maybe several times during the year. And incrementally, that is going to raise the maintenance level, which is the code level. And one thing you must always do is to prove -- for at least a month, if not a quarter, to prove that, that maintenance level is solid. And only after you've proven that it's solid in your production environment, should you be thinking about running a CATMAINT. And then after that, activating the function level. Now if you followed what the spirit of this question is, if you applied maintenance to take your code level from whatever it was before to 506, and you ran the CATMAINT and used the functional level activation, if there was any problem with that maintenance you just put in, you can't back it off because that maintenance level of 506 was a prerequisite to your CATMAINT and to your actual activation of the function level. So then your only way is to fix forward. So that -- for that part of the question, the answer is that when you put maintenance on progressively, it will raise the maintenance level and you would prove that maintenance level for a period of regression in production, maybe a month, maybe a quarter, and only then would you think about raising the -- running a CATMAINT in support of a future function level and also activating the function level. Ute, would you like to make some comments about that?

Yes, John, I would like. So I can totally agree to that what you said. So our best practice is definitely that we run maintenance, and we increase the maintenance level in the development system much more frequently than in the other systems. So in the development systems we sometimes increase the maintenance level every week to make sure that our applications in the development system can run with the newest and the latest maintenance activated. And then we are going step-by-step in the other environments. So in production, when we think we have a stable maintenance in our pre-live system, and we know that our applications running there are going fine, then we start applying that maintenance on the first side of the LPARs -- are you still there?

Yes. I'm -- you're still there, I can hear you.

Okay. Sorry, I couldn't hear myself. And in the first LPARs. And then, of course, you will never or we will never activate maintenance on all of the LPARs in assist labs. Because if you get into trouble with an application, you have the chance to run that application on another LPAR, for example. And for activating or running the CATMAINT, you have to make sure that all of your applications are running fine in the regression with the latest and the highest maintenance that you need. And as John said, when this runs for several weeks or more 2 or 3 months and they are stable, then we think about running the CATMAINT. And another thing is you -- there is no need to activate the function level in the same window where you ran the CATMAINT. As you know, activating the function level, that can be done concurrently. You can do it whenever you like, so you have no impact. But you have to run the CATMAINT, of course, in a window where you have not so much DDL, where you have not lots of utilities. But as John said, running the CATMAINT means that the maintenance is -- yes, is frozen and you can't go back with the maintenance once you run the CATMAINT. That's the reason why you definitely have to make sure that your applications are running fine with that maintenance level you need. And then it's up to you. So from our experiences, we have some systems where we activate the function level after we have run the CATMAINT successfully. And for test purposes, we have a lot of pre-let systems and development systems, where we sometimes activate the CATMAINT or run the CATMAINT. But we wait for some weeks to activate the function level or to set the function level on the test systems back to make sure that in case of problems in production, we can run with our applications with the stable maintenance level, with the CATMAINT and playing around a little bit with the function level. That's our experience.

And just to add to that, John and Ute. In the U.K., we're typically running with the maintenance and code level, well ahead of the function level that were currently activated or last activated because we haven't changed our preventative maintenance approach just because of continuous delivery. We always were aggressive on preventative maintenance within the bank. And therefore, we see our code level, either from the DISPLAY GROUP command or from the message issued at subsystem start-up, that our code level is incrementing, has been going through our maintenance drops each year. And then we are formulating our plans to activate function levels, 500 and above, with quite a delay, no correlation with the point at which maintenance moved the code level formally. I mean, sometimes you might put maintenance on and the code level won't move. And quite frankly, I don't need to know what maintenance resulted in that code level incrementing. Maybe it tells me what the code level is at start-up, I can issue a DISPLAY GROUP command to tell me if I've got any discrepancies across the members of the group. And the way we do it is that the code level will always be well ahead of the highest activated function level. And -- because there is no risk within the bank to get to activate function levels high as 506, for example, we're looking at a managed approach and separation of preventative maintenance, hypers PA and just standard maintenance from they move to the next function level or even a jump in function level. The only other thing I'd add, John and Ute, is that yet with the CATMAINT. CATMAINTs, we always expect that to be potentially disruptive because we're always still online maintenance, we don't shut all services down. And we don't want -- we want to have the minimum involve whenever we're doing a CATMAINT in production. Obviously, a CATMAINT at a function level in version 12 isn't anywhere near as significant as the CATMAINT to get to version 12. So in function level 502, there's a column added to SYSTABLE. So -- and I think might be utility change as well from memory. But it's a bite sized CATMAINT rather than a significant CATMAINT. But we still -- so for example, a bit of a tangent. We will always plan to shut down CDC Replication whenever we're running CATMAINT. So we'd want that to be a separate activity. Certainly, we do that whenever we upgrade. And I think as a good practice with our safety, that we'll look to shut down CDC whenever we're doing a CATMAINT as part of getting to a new functional level or positioning to get into a new function level. So hopefully, that added some value, John.

So maybe we can add here. So if I may add this. So on the maintenance, of course, you can -- if you're not sure which function level of CATMAINT you can run or if you have the maintenance active, then please don't forget there is an activate function level test command. That will tell you, if you are okay with maintenance or not. And so sometimes, of course, we are also activating the maintenance. And if you apply maintenance and you restart the Db2 member, then it's active. And you have some function levels where you have no CATMAINT, you have no need for the CATMAINT. And there's really good documentation as of today in the knowledge center, that you also know which CATMAINT, which function level is adding what in the catalog. And then you should have a look into that because it took us a long time to make sure that all of the data are there. And then you have an idea if the CATMAINT might impact your applications or not. Thanks, John.

Okay. So I know there's a long discussion there, but I just want to draw out some key points to try and summarize this, okay? So first thing is these CATMAINTs that are in support of these new function levels. So far today, these are what I call micro-CATMAINTs, they're very small updates to the catalog and directory schema. So that's one thing. And therefore, if when -- even if it's a micro-CATMAINT, people are going to take some special care to make sure there's no contention on the catalog. So Ute or Howard mentioned, CDC, but most people will turn off replication. They'll stop utilities running at that time. They'll stop binds and rebinds and grants and revokes during that period. Advancing the function level and taking the functional level back, that's a group-wide command and it's very unobtrusive, right? It's a very simple command and very easy to do, a lot better than just shut members down into back maintenance out, okay? Now what are my expectations of customers? My expectations are probably that customers will only advance the function level once a year, at most. And they'll do it simply because they want some new application function or some new DBA function. Or they may decide once every 2 years to advance the functional level, just to keep evergreening the system. And all times, to pick up a point that Howard made, I'm always going to make sure my actual maintenance level, my code level is advanced or well advanced of the actual maximum functional level I'm going to do during that period. So I just wanted to draw out those key points. Now next topic, a question that came in which says with function level M504 under Db2 12, what have been some challenges with regarding of the universal tables by default, particularly multiple tables within that segmented table space? Well, multiple things I'd like to say about this is, first of all, we -- as part of the advertising and marketing for functional level 504, we've announced that -- or reinforced the deprecation of objects like simple linear, segmented and classic partitioned and things like synonyms. But it's not the activation of function level 504 that you have to be aware of, it's actually running your DDL through a package that's got an APPLCOMPAT of 504 or later feed. It's actually the fact that you run your DDL, even if you're SPUFI or TEP2 or your favorite DDL tool from your vendor. It's when the APPLCOMPAT is raised to 504 or later that you will not be able to run any DDL against those deprecated objects. So I think Ute will comment later that you spoke. I mean she's running at function level 504 in production. But in actual fact, the function level for the packages is much lower than that. So I just want to be very clear here, it's about running the DDL with an APPLCOMPAT of 504, where this DDL is prevented. Now to get around it, if you want to continue maintaining and running DDL against all those existing objects, all you have to do is run that DDL through a package or a plan that points to a package, which has got an APPLCOMPAT of 503 or later. But typically, what you do in the job stream is you use a special register and adjust the current APPLCOMPAT -- application compatibility special register and toggle it up and down between 503. And all of the vendors have all been disclosed on that. And so if you are concerned about this or you're going in that direction, you should talk to your respective vendors and get very clear answers from them as to what releases or patch levels you need where this toggling of the APPLCOMPAT special register will be done automatically by the tool. That's one thing. I'll stop there because there's a second part in a minute. Ute, you go, if you want to carry on now, please do.

Yes. So what we have done is exactly what you said. So we are running the admin packages from BMC at function level 503. There is already a 501, there is already a PTF in BMC that you can apply, and I'm sure that's the same for the other vendors. That you can run in one administration package SQL that -- or DDL that is using new functionality as well as lower functionality. And that, John, is absolutely important that the customer talk to the vendor when they plan to increase the function level or the application convertibility of the execution.

So I want to just reassure everybody, all that code for all those classic table space types and all those objects is still in the Db2 code, in Db2 12 today. There's no plans to remove that code. But what we're trying to do is to encourage you to migrate to universal table space, in particular, because that's where all our investment is. And at some point, which has not even been determined yet, we will eventually get rid of all the support for all those objects. So it's a very strong signal to you to start converting your objects, for example, over to UTS. Now the next part of the question was about particularly multiple tables with a segmented table space. So building on the first answer from me, augmented by Ute here. I mean, the strategy for UTS and particularly, UTS PBG, because UTS PBG is like a one-to-one relationship with segmented. We have no intention to support multi-table PBG UTS table spaces. So what we are working on is a solution, hopefully that will be delivered this year, which will allow you using the Db2 REORG utility as a vehicle to break out the individual tables out of a multi-table segmented or simple linear table space and break those in tables out into individual PBG UTSs. Now there are some ramifications that come from that, okay, that you need to be aware of. Clearly, if you've got a segmented table space, got 20 tables in it, this will then become 20 tables. So you need to think about the additional 31-bit virtual storage requirement below the bar in DBM1, you need to reevaluate your DSMAX setting, you also need to think about the housekeeping because now you had 1 table space before, now you've got 20. So you have to develop the housekeeping for it in terms of COPY, RECOVER, REORG, et cetera. And you also think -- have to think about the implications of actually opening those data sets during Db2 restart. So I'll just give you a simple example of one table space that had 20 tables, that then becomes 20. So what I suggest people do is think about how many multi-table segmented and simple linear table spaces you've got and think about the average number of tables in each one of those table -- across those table spaces, and do some of the math there in terms of the 31-bit virtual storage requirement in the DBM1, it's 5,000 per data set and think about how many data sets there are going to be and what adjustment you might have to make to DSMAX as well. Any comments from Ute or Howard about this?

Actually -- yes.

Well, it's -- yes, I'll go first, Ute. Yes, I mean much to my embarrassment in some respects, we still do have some multi-table spaces. Although, they are often as DDL as provided by a vendor. But there's nothing that stops me in amending that DDL. But for vendor DDL, our approach is that we don't -- we're not as aggressive with adherence to standards with vendor DDL because it can make subsequent upgrades of vendor products more difficult, more customization to be done. But we -- on the in-house side, we -- again, much to my shame, we've got some -- a number of simple table spaces. We've had Db2 at the bank for many, many years and we've got more than a handful, that's for sure. And the enhancement that John talked about would be very interesting to us because we don't want to take outages unnecessarily for the conversion of these objects, so that would be very useful to us to finally bite the bullet and get this done. I know that I've always been comforted by the fact like John Campbell's word, saying that there's nothing anywhere, that there's a certainty that these will stop being supported; function level 504, of course, was mentioned earlier. But you still got a get-out-jail-free card there with using the APPLCOMPAT parameter for the packages, so you can still work with those objects. But the die is cast and the move to UTS, that die is cast. And anything that IBM can do to make it easier for customers to transition across to UTS with REORG, for example, will be gratefully received. Thanks, John.

Okay. So maybe what I can -- yes, sorry?