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

Hello there. This is John Campbell, a Distinguished Engineer in IBM Db2 for z/OS development. Good morning or good evening, wherever you are in the world. The subject of today's mini cast is about saving CPU, which is about using thread reuse and the BIND option on a package of RELEASE(DEALLOCATE). These particular techniques are not new. They've been around a long time in Db2, but I think in the run-up to Version 10 when people were constrained on 31-bit memory in the DBM1 system address space, a lot of people had to go on what was called the John Campbell diet plan where they had to trade removing the thread reuse and switching from RELEASE(DEALLOCATE) to RELEASE(COMMIT) in order to save virtual storage. So they were actually trading reduced virtual storage but at the same time, increasing CPU. But since the advent of Version 10 with 64-bit memory and also cheaper, larger real memory sizes, these are very important tuning options here to reduce CPU resource consumption. And in far too often, when I do detailed studies with customers, I find customers who are just simply not using this technique of thread reuse and coupling it with the BIND option RELEASE(DEALLOCATE). Or if they are using it, they're not using it enough, and there's a lot more potential for reducing CPU. For those people who are expecting me to be on camera today, I do apologize. The problem is I don't have enough bandwidth out on my Internet. So therefore, I'm actually talking down at landline. But never mind, I've spared you having to look at me. [Operator Instructions] So here on Slide 2, I want to talk about, first of all, introduce the subject of thread reuse. The whole idea of thread reuse is to avoid the repetitive cost of creating and terminating a thread for each transaction. So without thread reuse, you're in a situation where you're creating and terminating a thread for each transaction. And if you have a high-volume transaction that's relatively simple, this repetitive cost of creating and terminating a thread per transaction is very expensive. Now what are the benefits of thread reuse, in other words, avoiding this repetitive cost of thread create and terminate? The benefits are lower CPU resource consumption; faster transaction throughput; fewer Db2 threads for the same throughput; and if you're a data-sharing customer, it would delay the need for additional -- adding in additional Db2 members. But it does require a small amount -- a relatively small amount of additional real memory, so it will increase your real memory consumption. So when I'm talking here about thread reuse, I'm talking here about selectively targeting. It's not a blanket statement of one-size-fits-all. You want to selectively target your high-volume transactions. So one of the things you want to do, whether it be CICS or IMS or DRDA, you want to create your scoring -- create yourself a scoring table, like the table in the top right-hand corner here, where here, the actual plans, which are, in fact, the transactions, are sorted into a scoring table with commits per second descending. And what you can see is there's 2 high-volume transactions at the top. So when starting this approach of designing for thread reuse using the appropriate attach, you should go after the heavy hitters at the top of this scoring table first. Now how can you achieve thread reuse? Well, if you're using the CICS attachment to Db2, there's 2 ways of doing it. The first one is to define Db2ENTRY threads and define some of them as being protected. So what you need to do is have a specific Db2ENTRY for the plan with one or more transactions pointing to that plan. And you want to have a THREADLIMIT greater than 0. And let's say, you say that the THREADLIMIT was 10, for example, you can then specify another option called PROTECTNUM, and PROTECTNUM identifies the number of those threads which are protected. So PROTECTNUM must be less than or equal to the THREADLIMIT. And so what happens when you specify a protected thread is that when a transaction runs on the thread, rather than actually purchase the thread automatically at the end of the transaction, if you have -- if you specified thread protection and you've not reached PROTECTNUM, then the CICS Db2 attachment will protect the thread and leave it around for reuse. And in fact, there's a parameter in CICS attachment called the purge cycle, and it will leave it around for an average of 45 seconds before it will purge it. So that's the first method of using thread protection. The second approach is what I call the organic queuing method. And again, it resolves around having specific Db2 entries for a plan where one or more transactions point to the plan. And what you do here, as you say, the THREADLIMIT is a certain value greater than 0, you don't specify any thread protection, so PROTECTNUM is 0. But what you do is you specify another parameter called THREADWAIT, which rather than saying THREADWAIT pull, you say THREADWAIT(YES). And what happens is that when you reach the THREADLIMIT, then transactions will start to queue because they will not spill to use pool threads. And once you start hitting the THREADLIMIT, then transactions will start to serially reuse these normally unprotected ENTRY threads. So when the transaction from currency rate is very low, there will be no thread reuse. But when the transaction rate increasingly gets higher and higher and all the threads are -- the unprotected ENTRY threads are in use, then once they start to queue rather than spill to the pool, they will start serially reusing those existing threads. One thing I want to point out about the conditions for reuse is that when you have one or more transactions reusing a Db2ENTRY, they must be using the same Db2 plan. So some CICS Db2 customers are using the dynamic plan selection exit, which is part of the CICS attachment. Now if you're using the CICS dynamic plan selection exit and that exit is not changing the plan, it's always the same plan name, then you're eligible for thread reuse. But if you're using that dynamic plan selection exit and it is changing the plan name constantly, then that would kill off the thread reuse. So that covers the CICS attachment. One thing I'd like to show you in the bottom right-hand corner here is an extract from the Db2 accounting for -- this is for the correlation name US15, which is the transaction code. And I've actually highlighted some fields on what would be an OMP report. So the one that's red tells you the number of deallocations and the average. And then it also tells you how many times there was a new user and when there was a resignon. So in the case of both the new user and the resignon, that is reusing the thread. The new user is -- when it says new user, what it means is based on your definition in the CICS RDO definitions, it means the author ID is changing. And the resignon happens, which is like a simple signon, is when you've specified account rec unit of work or unit of work -- sorry, account rec task, and it's causing a simple signon. So in this particular case here, we're getting reasonably good thread reuse. So 73% of the time, we're actually getting thread reuse, and only 27% of the time is the thread being deallocated. So this is just one example to show you looking at the accounting report. And if, for example, you're finding that you're getting deallocation all the time and it's a high-volume transaction, that would indicate you've got potential to start using thread reuse and harvest the CPU savings; or alternatively, you may see some degree of deallocation, but unfortunately, it's not 27%. Let's say it's 50% or more, in which case, there's potential to use more protected threads or to do some tuning of the organic queuing method to try and get better thread reuse. In the case of IMS transaction manager, you can get thread reuse if using Fast Path IFP regions, Wait-For-Input regions or Pseudo Wait-For-Input regions. All 3 of those types of regions would allow thread reuse. You can also get it with WebSphere Type 2 local connections where WebSphere and Db2 are co-located on the same LPAR and it's using the Type 2 connection into the target Db2 subsystem. And you can also get it starting with Version 10 where we implemented high-performance DBATs. And that's going to be a separate topic, which I'll talk about in the final section of this presentation. Now one thing I want to point out is that when it comes to the cost of thread create and terminate, you will not see that cost in the accounting class -- accounting report in the Class 1 and Class 2 CPU time. Similarly, if you achieve thread reuse and you avoid this post of thread create and terminate, again, you will not see that in the accounting Class 1 and Class 2 CPU time. So what you have to do if it was a CICS transaction is look in the CICS type -- the SMF Type 110 record, and you would see the CPU savings there. Okay. And the thing I want to point out is that if you do use the BIND option RELEASE(DEALLOCATE) on your packages, then you won't see any benefit from RELEASE(DEALLOCATE) unless you're already getting thread reuse. So thread -- so getting effective thread reuse is the prerequisite to getting the run time performance benefit of using RELEASE(DEALLOCATE) for the packages. So now I want to switch to using the option RELEASE(DEALLOCATE) on your packaged BINDs. So if you use the BIND option RELEASE(DEALLOCATE), that avoids the repetitive CPU cost of freeing and reacquiring the same resources for each transaction reusing the thread. So if you use RELEASE(COMMIT) and you're reusing the thread, then you're going to incur still the CPU cost of reacquiring statement blocks and other assets that are owned by the transactions. So RELEASE(DEALLOCATE) will avoid that repetitive CPU cost of freeing and reacquiring the same resources. Now what are those resources? If it's static SQL or it's dynamic SQL with the BIND option KEEPDYNAMIC(YES), we're talking about avoiding the repetitive cost of allocating and freeing the packages and the statement information; avoiding the cost of allocating and freeing control blocks associated with the statements; avoiding the repetitive cost of acquiring and releasing the parent locks; keeping the index lookaside buffer going, if the data is sequentially accessed; and to keep the dynamic prefetch tracking and sustain dynamic prefetch, if the data is being sequentially accessed. On the other hand, if you're talking about dynamic SQL without the KEEPDYNAMIC(YES) option, then it just simply avoids the allocation and freeing of the packages. One thing I also want to point out, although primarily during this mini cast I'm talking about the CICS and the IMS transactions, RELEASE(DEALLOCATE) is also beneficial when you have long-running batch processes that commit frequently. So if you have long-running batch processes that commit frequently, you should also consider binding those packages as RELEASE(DEALLOCATE) to get the benefits, as articulated on the top half of this chart. Now when it comes to using RELEASE(DEALLOCATE), again, you want to use it selectively, and you want to target the high-use packages associated with the high-volume transactions. These are the ones -- these are the packages that are the ideal candidates for using RELEASE(DEALLOCATE). So again, what you'd like to do is to create yourself a scoring table of the packages based on allocations per second. And here, you can see the first package here is allocating over 100 -- almost 110 times per second. And it's contributing to 6.5% of the total Class 7 CPU. So use this scoring table to -- you start at the top and work your way down the list until the law of diminishing return sets in. Okay. So again, by use -- incrementally using RELEASE(DEALLOCATE), it will use additional real memory. And what you should try to do is use it selectively as opposed to setting RELEASE(DEALLOCATE) on every single package. So my approach here is to set it selectively based on the scoring table. Okay. Now the benefit of coupling packages with RELEASE(DEALLOCATE) with thread reuse, that benefit will show up in the Db2 accounting trace. And you should see that benefit in reduced Class 2 CPU time. And I'm being quite conservative here because customers who have coupled RELEASE(DEALLOCATE) with thread reuse have seen 10% or more CPU reduction per transaction, which is really worth having. And in some cases, it's been much higher than 10%. So now what I want to do on Slide 6 here is talk about high-performance DBATs, which our threads used for DDF traffic. High-performance DBATs also have the potential for significant opportunity for CPU reduction for the following reasons: first of all, avoiding the connection going inactive and switching back to active later; avoiding the database access thread, the DBAT being pulled at transaction end to avoid the DBAT going back into the DDF thread pool for reuse probably by a different connection; and it supports true RELEASE(DEALLOCATE) execution for static SQL packages to avoid the repetitive cost of package and statement block allocation and deallocation. When you're not using high-performance DBATs, Db2 does not respect the difference between RELEASE(DEALLOCATE) versus RELEASE(COMMIT). So ever since we introduced DDF for, it certainly, in the Version 5, Version 6 time frame, if ever you specified RELEASE(DEALLOCATE) on a package, we just forced it to be RELEASE(COMMIT). So use of RELEASE(DEALLOCATE) was a moot point. So now with high-performance DBATs, you can get true RELEASE(DEALLOCATE) execution for static SQL packages. Now a warning here. High-performance DBAT is a very powerful performance option, but it can be dangerous if it's not used intelligently. And I will proceed in the next chart here to explain why. So the first thing is identifying good candidates for high-performance DBATs. And what you want to do here is look at the Db2 accounting report, and what you want to do is take the sum of the number of aborts and commits and divide through that by the number of conversations received. And the field names in the IFCID are listed on the chart here. And what you're looking for here is number of -- is that calculation of the commits received plus the aborts received divided by the conversations received. As a result of that calculation, you'd like to be at least 100 and probably greater than 200 to justify the use of using a high-performance DBAT. And here's an example from an OMPE of an accounting short report. And again, if you do this calculation, you'll see that the top row in this accounting short report is not a good candidate, but the one at the bottom is a very good candidate. Now for some installations, they're already using the option called KEEPDYNAMIC(YES). All the SAP installations, for example, are using packages that have the option KEEPDYNAMIC(YES). So when you're using KEEPDYNAMIC(YES), the actual connection does not go inactive already, and the thread does not get pulled. So there's no additional benefit for users of KEEPDYNAMIC(YES). Now what's in red here is some bad candidates. If you've got short-lived connections, which are not going to reuse the thread, and no connection pooling, then there's little or no performance benefit from trying to use a high-performance DBAT. And all you'll end up doing is incurring CPU overhead because of frequent DBAT termination. And that overhead will show up in the disk system address space as additional SRB CPU time. So here on Slide 8, I'd like to talk about the high-performance DBAT behavior. So first of all, you must be using the ZPARM CMT stat set to inactive, so that DBATs can be pooled and reused. Secondly, to activate high-performance DBATs, you need to use the MODIFY DDF command -- Db2 command and specify a package rail value of BNDOPT or BNDPOOL. Now what's the difference between BNDOPT and BNDPOOL? Well, BNDOPT was there originally starting in Version 10. And this means that when the connection goes away, the DBAT is immediately terminated. However, later on, we introduced another option called BNDPOOL, and what happens here is that the DBAT is returned to the pool for reuse by a different connection. So when -- so the way this works is when a DBAT is about to be pooled, if there is at least one package -- all it takes is one package with RELEASE(DEALLOCATE) allocated against the thread, and that DBAT will stay active with the connection. In other words, it becomes a high-performance DBATs. So let me stress again, all it takes is just a single package to be bound with RELEASE(DEALLOCATE), and that's all it takes then to get high-performance DBATs. So you need to be careful. If you've got those dynamic SQL packages, which are start SYS, et cetera, or a stored procedure package or UDF or trigger package, any one of those is defined with RELEASE(DEALLOCATE), that is enough to create a high-performance DBAT. And the connections, even when you're using a high-performance DBAT, the connections will turn inactive after 200x. There's no external control on that, and that will free up the DBAT to be rejuvenated. The ZPARM POOLINAC also plays a role. This controls the duration of inactivity. So if a high-performance DBAT, which has not been pulled, has been idling for a certain period of time, then the DBAT will be killed off. And the default for POOLINAC is 120 seconds. Now an open question here is, so what happens if you've got BIND activity REBIND, SQL DDL or you've got an online REORG that's materializing a DDL change because those sort of online REORGs that materialize DDL changes often result in packaging validation? So if you want to run those types of activities, you need to turn off the high-performance DBATs. So you must issue the Db2 command MODIFY DDF as specified PKGREL(COMMIT) to overlay the previous BNDOPT or BNDPOOL option. And that switch over to PKGREL(COMMIT) will occur gradually. And once completed, that will allow those various activities, BIND/REBIND, SQL DDL or online REORG materializing DDL changes that causes packaging validation. It would cause those to break in. Now there are some important operational considerations. You do not want to use RELEASE(DEALLOCATE) on common packages that are widely shared across distributed workloads as this will drive up the overall requirement for MAXDBAT. So if you do that, you risk -- you have the risk of not having enough DBATs available for all workloads, not just those that are using high-performance DBATs. And in the very worst case, you could run out of DBATs altogether or you could end up with escalating thread management costs. Because everything is using a high-performance DBAT, the DBATs are not being reused, and therefore, they're being frequently terminated. Now important thing I want to stress here is that you need to check what is the BIND option, the RELEASE BIND option on the ODBC and JDBC driver packages that are in that standard collection NULLID. They used to be bound as RELEASE(COMMIT). But in Version 9.7 Fix Pack 3a, the default BIND option for these client driver packages was switched to RELEASE(DEALLOCATE). So what you want to do here is make sure that those driver packages in the NULLID collection are all set to RELEASE(COMMIT). Now also need to be careful if those DDF workloads are also reusing common static SQL packages used by CICS and IMS transactions or batch workloads with RELEASE(DEALLOCATE). Because again, if these are common packages, they can trigger a huge number of high-performance DBATs. Another lesson I've learned is get the -- when it comes to the application server connection pool definitions, do not overinflate the size of those connection pool definitions. Otherwise, this will considerably drive up the demand for high-performance DBATs. If I share a war story with you, I worked with a China in customer who basically turned on high-performance DBATs everywhere, but they couple that by also inflating the size of the connection pools. Because sometimes when you're dealing with people with web-based transaction processing, the solution to every problem for them is to always supersize the size of the connection pool. Anyway, in this customer in China, they ended up with a phenomenal number of high-performance DBATs, but these DBATs were being constantly purged. And the end result is that when they shrunk the size of the connection pool definitions, they required far less high-performance DBATs. And in fact, then, they got super use of high-performance DBATs, and these high-performance DBATs were never terminated. So overall, they had a much better performance experience by shrinking the size of the connection pool definitions. What I would also say is that if you activate high-performance DBATs, be prepared to use the Db2 command MODIFY DDF PKGREL(COMMIT) to switch off high-performance DBATs at the first sign of DBAT congestion. In other words, overuse of DBATs and -- if you're overusing DBATs and worse still, you're hitting max DBAT ZPARM frequently. So general recommendation and yet you spotted it already is that high-performance DBATs should be used selectively. So here, I've got a series of steps in terms of implementation for you. First of all, you want to prime those driver packages into an alternate collection. And I'm calling it here NULLID2. So in the standard NULLID collection, those driver packages will be bound to RELEASE(COMMIT). But what you also want to do is to repeat the BIND of those driver packages into something like a NULLID2 collection and specify RELEASE(DEALLOCATE) on those BIND operations. Secondly, for those applications that you want to use high-performance DBATs, you want to route them to the NULLID2 collection. And there are various ways of doing this. First of all, you could use a system monitor profile or profiles and set the current package path special register to point those specific applications to the NULLID2 collection; or for JDBC applications, you could set the current package set property in the respective data source; or for.NET and ODBC/CLI applications, you could set the current package set parameter in the data server driver configuration file. Then you can activate high-performance DBATs by issuing the MODIFY DDF with the PKGREL of BNDOPT or BNDPOOL. And just to repeat here, when you say BNDOPT, then when the connection goes away, the DBAT is purged, terminated. As you say BNDPOOL, then the DBAT is returned to the pool for reuse. And five, you should monitor and adjust MAXDBAT based on the demand -- overall demand for DBATs and the demand for DBATs coming from high-performance DBATs. And you're likely going to have to increase these -- the value for MAXDBAT to support this demand for high-performance DBATs. And if there are any problems, congestion or you're hitting MAXDBAT, use the MODIFY DDF PKGREL(COMMIT) to overlay the BNDOPT/BNDPOOL option. And over time, then, over a few -- good few seconds or a small number of minutes, then the use of high-performance DBATs will go away. So now on Slide 11 here, I want to talk about limitations and considerations which apply when designing for effective thread reuse and using the BIND option RELEASE(DEALLOCATE). Well, the first danger is that you could be accumulating a lot of objects, including thread memory -- large thread memory footprint and CPU resource consumption. Now in CICS, there is a defensive mechanism called REUSELIMIT. That was introduced in CICS transaction server Version 4, Release 2, and the default is 1,000. So after 1,000 reuses of the CICS Db2 thread, then it would purge and have to go through reallocation. So as a result of the purging process, then this accumulation will be -- of resources will go away. And you can play around and tune that frame, if you want to, to keep down the thread memory footprint. Also, when it comes to WebSphere Type 2 connections, you've also got the age timeout setting here. And it's -- the interval in seconds before a physical connection is discarded. So what you could consider doing is setting this parameter below 5 minutes, and I generally recommend 120 seconds to reduce the exposure of long-lived threads accumulating a lot of resources. Now the good news as well is that Db2 11 autonomically tracks the object-related resources and lock accumulation when you've got thread reuse and will release them as needed to achieve a smaller memory and CPU footprint for you. So that makes life an awful lot easier. Now on Slide 12, I want to talk about concurrency. First of all, you want to ensure that those packages that are bound to RELEASE(DEALLOCATE) don't issue an SQL LOCK TABLE or they're not touching a TABLESPACE with LOCKSIZE TABLESPACE or TABLE. Because when you use RELEASE(DEALLOCATE), in some respects, it's the same as RELEASE(COMMIT). We have RELEASE(DEALLOCATE), the page and the row locks are always RELEASE(COMMIT). So there's no difference between RELEASE(DEALLOCATE) and RELEASE(COMMIT) when it comes to those child locks, whether they be page or row. But there's a big difference about the parent locks. So when you say an SQL LOCK TABLE or it so happens that the TABLE you're touching and processing has LOCKSIZE TABLESPACE or TABLE, those are parent locks. And once they're acquired, they will stay for the duration of that thread as it's being serially reused. So again, let me share a war story with you. I had a customer in Australia, in fact -- or at least, Australia or New Zealand, who implemented thread reuse with RELEASE(DEALLOCATE) on some select CICS transactions. And for several hours, this man was a hero because he saved 17% CPU. But unfortunately, one of the underlying tables, the LOCKSIZE was TABLESPACE. And that TABLESPACE, although it was infrequently updated, it eventually did get updated, and it took an exclusive TABLESPACE lock, and that obviously caused devastation. So that's my first warning about concurrency. The second warning is not about the packages that you use RELEASE(DEALLOCATE) on. It's about the other transactions or the other batch processes because some of those may be using SQL LOCK TABLE or they may be triggering lock escalation. So if you've got another process that's not using thread reuse, not using RELEASE(DEALLOCATE), if they try to do an SQL LOCK TABLE or they try to trigger lock escalation, they will fail. So you don't want other applications using SQL LOCK TABLE or triggering a lock escalation. They just -- they won't be able to break in and take the lock -- the table-level lock or achieve the lock escalation. And then we come back to this subject of BIND/REBIND, SQL DDL and online REORG activity. How can it break in when you've got thread reuse with RELEASE(DEALLOCATE)? Well, there's most definitely a trade-off here between using thread reuse with RELEASE(DEALLOCATE) with concurrency of these particular features of Db2. So the first thing to say is on Version 10 and 11, when you've got DDF workload, you can do the MODIFY DDF PKGREL(COMMIT) option, and that will turn off the high-performance DBATs. And then this activity can run. And then in Db2 11, we've got an autonomic capability to allow BIND and REBIND and the other activities to break in. Not always 100 -- working 100%. But in Version 12, when you get to Function Level M505, there's a new BIND in break-in capability, which will allow you to create a new package copy, and any new threads being allocated will pick up the new package copy. And when the existing threads reach deallocation, when those threads are reallocated, they will pick up the new package copy. So the BIND break-in mechanism that's available in Function Level 505 is a very big and very important enhancement for our Db2 customers. So with that said, very shortly now, we're going to join the live Q&A. What I've got on this slide here is a link that will take you to my other webcast, which include, for example, the Version 12 technical overview. That's a 2-part webcast, and there's also another 2-part webcast about Version 12 migration planning and experiences. Secondly, I want to announce the Db2 advanced class that used to be called the Db2 master class. This is held twice a year, in June and September. Unfortunately, that cannot be held face-to-face this year because of COVID-19, but they are available in virtual classes. And we will be running -- sorry, this advanced class in September. And it will run on the U.S. West Coast time zone. And lastly here, to look at for webcasts in general and also other useful materials, you should enroll as a member of the World of Db2. And as a member, you'll be able to see information about those materials and webcasts and so on. Okay. So this mini cast is part of an 8-part series here. Today's one is about thread reuse and RELEASE(DEALLOCATE). All -- several of these mini casts, the first 3 have already been run and recorded. All of these webcasts are -- every single one of them are available on replay. You can download the slides, and you can also download the -- or not download, but you can basically listen to the webcast on replay. You'll be able to watch the slides and listen through the audio. And finally, I'd just like to advertise next week's mini cast, which is about point-in-time data recovery in Db2 12 using the RECOVERY utility in conjunction with the new SCOPE UPDATED option as a new option on the RECOVERY utility in Version 12 called SCOPE and the default is UPDATED. And I would like to share with you some -- the value of this enhancement but also share with you some consideration which apply and some must-dos when using this feature. So with that said, that's the end of the formal part of this webcast. And what we're now going to do is go to the live question-and-answer session. So I've got some questions ready and waiting for me there.

So there's a question from [ Russell ], which said, "You mentioned something about KEEPDYNAMIC, but I didn't catch it." So I apologize, [ Russell ], I may have said it too quickly as well. So when you've got a package with RELEASE(DEALLOCATE) -- sorry, when you got a package with KEEPDYNAMIC(YES), that stops the connection going inactive. So I think I need to step back and just explain to people generally what normally happens. Normally, what happens is that people have the ZPARM CMT stat set to inactive. And what happens when a connection wants to do work, what Db2 will do is take a DBAT out of the DBAT pool, which is sized based on MAXDBAT, and the connection will run on that DBAT. And when the commit happens, the connection and the DBAT will be disassociated. The connection will go inactive, and the DBAT will go in the pool. So that's thread pooling in DDF. However, when you actually use the BIND option KEEPDYNAMIC(YES), that stops the connection going inactive. And it stops the connection going active, and it still stops the DBAT going back into the pool. So when you're using KEEPDYNAMIC(YES), you're already getting the benefit of stopping the connection going inactive and you're avoiding the cost of the DBAT going back into the pool. So if you're using KEEPDYNAMIC(YES), you will get no additional benefit out of high-performance DBATs. Okay. So there's another question from [ Azim ], which says, "When we enable RELEASE(DEALLOCATE) for Type 2 RSF..." Unfortunately, you just left the question hanging. You said when we enable. Well, you can. So we've had success for a number of customers using WebSphere Db2 co-located on the LPAR, using the Type 2 connection. And they've been able to use RELEASE(DEALLOCATE). And in the examples I've got, they were, in particular, using SQLJ, which is static SQL packages. So that's static SQL for job using SQLJ, and they got significant improvements. On the other hand, if you're using Type 2 connection and you're using dynamic SQL, you will get some benefits, okay, but not as much benefits as if you were using static SQL packages. Excuse me. "We're using 10.5 and 11.1 on Red Hat Linux." Okay. But what I've been talking about in today's webcast is about the behavior of Db2 for z/OS. It's not about Db2 for LUW. That's one way of interpreting it. But on the other hand, if you've got applications that are out using -- using Z Linux, Red Hat Linux, x86 Linux, then they could benefit from high-performance DBATs. But I just want to stress -- I just want to make sure I'm interpreting the question correctly that this does not apply -- I'm not talking about Db2 LUW here. I'm talking about Db2 for z/OS. [ Sabahat ] is asking, "Which packages can be used with RELEASE(DEALLOCATE) in a production hour?" Well, what you need to do there is look through the accounting, if you're collecting the package-level accounting in Db2, and you extract the information using OMP or your favorite monitoring tool, you can find out which packages are used during that peak production hour and create yourself a scoring table and use that to drive your decision. So [ Chris ] has asked me a question. He says, "The high-performance DBATs, are those eligible for batch programs or just online CICS distributed?" So [ Chris ], high-performance DBATs are only related to DDF activity. So high-performance DBATs are nothing to do with CICS or IMS transaction manager or true batch programs, but they would apply to either transactions or batch processes that come through DRDA into Db2. There's a question here from [ Ramu ]. It says, "Regarding the BIND break-in mechanism, in our high-volume POS environment, we still see lock waits and transactions delayed on share-level change REORGs. If we change the protected limit to 0, would this avoid transaction delays?" So Ramu, there's 2 things in this, right? The first thing is about online real share-level change REORGs. So yes, that's nothing to do with thread reuse. I will cover that in a second. But yes, an online REORG has to drain. And of course, transactions that are running are holding claims on the object. So the drain will end up queuing behind those claimers. And if those claimers declaim, because they've got to a commit or a rollback, then the online real can break in. So it's common to see with online REORGs that there will be some lock waits and transaction delays when the online REORG tries to drain ahead of doing the switch phase. And one of my long-standing recommendations is the max RO setting, plus the drain wait setting on the REORG. The sum of those 2 numbers should be less than the IRLM resource timeout value minus 5 seconds. And that way, you're allowing the online REORG to try and break in as best it can, but you're trying to avoid transaction timeouts. Now going back to the CICS-protected entry threads. When we decided drains and claims back -- way back in Version 3, our design point, the main design point, was thread reuse with CICS-protected entry threads and IMS Wait-For-Input regions, for example. So the whole -- so I can tell you that, generally speaking, and I'll -- generally speaking, online REORG will still be able to break in when you're achieving thread reuse with RELEASE(DEALLOCATE), unless, for example, you use withhold cursors. Because if you use an open withhold cursor, then the claim count will be elevated at plus 1 across the commit. So provided you're not using open withheld cursors, the online REORG can behave the same way as it is before and break into protected thread, CICS or IMS, in your case, it's CICS, that's using RELEASE(DEALLOCATE) packages. So [ Prasad ] is telling me he missed the fields' names in the accounting report. So [ Prasad ], after this particular mini cast is finished, the slides will be available for download. And in the downloaded slides, the field in the slides, you will get the field names. [ Laura ] is asking me about, "How can I measure the CPU reduction of high-performance DBATs?" So Laura, you can -- for -- again, it's just distributed workload. You can see that in the accounting Class 2 and 7 CPU time and also in the statistics trace, hopefully in reduced CPU in the disk system address space under SRB. [ Sabahat ] is asking me, "Please take some session on PGSTEAL(NONE). I would like to implement it with minor casualty." So [ Sabahat ], that's not a subject of today's webcast. I am going to talk about this in a future webcast. So I would ask you to tune in for that future mini cast. [ Maria ] has asked me, "Then for CICS transaction, you need thread reuse to get the advantage from RELEASE(DEALLOCATE)?" Most certainly true, [ Maria ]. Using RELEASE(DEALLOCATE) give you no benefit unless you achieve thread reuse first. You've got to get transactions, serially reusing the thread to get the benefit from RELEASE(DEALLOCATE). If the thread -- if you don't get any thread reuse and the thread is being terminated at the end of the transaction, there's no benefit. And then she says, "Is there a way to get the benefit from RELEASE(DEALLOCATE) without making thread reuse?" And the answer is no. So you actually have to get the thread reuse first. Although I said for batch program, you've got long-running batch programs that commit frequently, then for that -- effectively, that batch program is reusing the same thread over successive commits, we'd get the benefit there. But to recap, for IMS and CICS, you need to get the thread reuse first before you're able to get any real benefit out of RELEASE(DEALLOCATE). So [ Stephen ] is asking the question, "In Function Level 500, online REORG can break into RELEASE(DEALLOCATE)?" Now that's true, [ Stephen ]. I mean that's true since Version 3, okay? But the second half of your question is more interesting, which says, "Can DDL, such as ALTER TABLE add column break in with a thread reuse RELEASE(DEALLOCATE)?" And the answer, to be perfectly honest with you, Stephen, is most of the time or some of the time, okay? I have seen cases where customers are not able -- this sort of DDL, just simple DDL, ALTER TABLE add column is not able to break in. So one particular customer in my social circle, they have thread reuse of RELEASE(DEALLOCATE). When they want to do a BIND break, a REBIND break-in, it worked, consistently worked. But when they came to do ALTER TABLE add column, it didn't consistently work. So I suggest, [ Stephen ], if you've got situations like that is that you open a case with IBM, and they will give you a particular job to run. And what it will do is it will trap the minus 904 for the ALTER TABLE, and it will take a dump. And that will allow them to investigate the problem for you. And in fact, I have one customer in my social circle who's already tracking that already. [ Paul ] is telling me that he's using CICS-protected anti-thread at release 1,000. That's good, [ Paul ]. That's the default. And I don't mean to brag here, but I was the person that actually put the REUSELIMIT into CICS and campaign for it because I wanted a safety mechanism in case people misuse protected entry threads and overused it and accumulated a lot of resources. So REUSELIMIT is only 1,000. So every 1,000, we're going to free off the thread. It's a -- any cost of that one create terminate and recreate a thread every 1,000 is simply going to get amortized away. [ Laura ] is telling me I answered the concurrency questions. Thanks, [ Laura ]. And [ Maria ] has just said, "Ooh." So I don't know what that means, [ Maria ], but if there's a question, let me know. So as [ Azim ] has said, "We faced high number of deadlocks and timeouts when we try to enable high-performance DBATs for Type 4, okay? These tables are accessed concurrently with foreign keys." What I would say, [ Azim ], I made out some points earlier about concurrency. Maybe the reason for the timeouts and deadlocks is that other applications -- I've already covered 2 types of concurrency issue. It could be that there are applications outside of these that are trying to do an SQL LOCK TABLE or trying to trigger lock escalation, and they will not succeed. Similarly, there may be some of these high-performance -- these transactions using high-performance DBATs that are triggering high escalation whatever, and they may be colliding with those transactions. So you should investigate that first. So [ Dennis ] is asking me to rereview the implications of binding a package with RELEASE(DEALLOCATE). So -- and he says, "e.g., a batch package." So the implication here, [ Dennis ], if you compare RELEASE(COMMIT) versus RELEASE(DEALLOCATE), when it comes to the child locks, which are the page or the row locks, so if your lock size is page, row or any, there's no difference. Those child locks are always RELEASE(COMMIT). However, the parent locks are maintained across commit. So one of the advantages of RELEASE(DEALLOCATE) is that the parent locks are held across commit. So if you're using chart page, row or lock size any, then the parent locks are what's called IS or IX. And they're kept throughout that thread, right? The problem is, if you have some other process that triggers lock escalation or uses SQL LOCK TABLE, they're not going to succeed because they'll be blocked by the IS and IX locks. Similarly, if those threads that are using RELEASE(DEALLOCATE), if they themselves touch a table with LOCKSIZE TABLESPACE or TABLE or they do an SQL LOCK TABLE, they may be prevented from doing it because of the other processes. So to keep this simple here, okay, you want to check on the objects for those packages that you want to use RELEASE(DEALLOCATE) on, you should make sure that the lock size in each of those tables is either page, row or any. The second thing is you want to make sure that those packages don't use SQL LOCK TABLE. It should be hopefully very infrequent because it serializes everything, although these packages don't trigger lock escalation. So hopefully, that helps. So [ Mark ] is asking a question about, "Is there a sweet spot for a number of tables touched by a package over which RELEASE(DEALLOCATE) becomes an overhead?" So [ Mark ], there is a sweet, sweet spot. And that's why in Version 11, we put -- it's internal, so I can't share the internals with you. But basically, if we accumulate too many -- touch too many tables or take too many parent locks, we will progressively delete them, okay? So that's why we put that defensive mechanism into Version 11. We had some extreme situations, [ Mark ], where customers used thread reuse RELEASE(DEALLOCATE) everywhere and particularly with CICS. They had one -- the extreme case was one customer had one plan for CICS used by every transaction, and they bind all of those package RELEASE(DEALLOCATE). And they just acquired a gazillion amount of resources. So it's very extreme. And what happened then is was that RELEASE(DEALLOCATE) started performing less than commit. So that's why we put that code in Version 11 to autonomically free off the resources as a defensive measure. Okay. [ Paul ] has asked me a question here. I can't see it as clearly as I would like to, [ Paul ], but, "We have a package bound RELEASE(DEALLOCATE) with just an insert statement. We have issues with REORG with discard share-level change, which fails to get a drain due to multiple transactions. Things seem to have got worse since we got to Db2 Version 12 Function Level 100." Oh, okay. So I just want -- I think I know what's happening here, okay? So what I've seen is that some customers went -- after they went to Version 12, they saw online REORG taking more timeouts. And what's happening here is that there's a performance enhancement in Version 12 automatically. You don't do anything for it. It happens automatically. You have no control over it, whatsoever. And what it does is it tries to avoid what's called -- I mentioned earlier about claims. So it tries to avoid the repetitive cost of claiming and declaiming on a per-transaction basis. And what's happening in Version 12 is this feature is there. And when the online REORG comes in, what happens is the online REORG is going to be blocked by this retained claim, and that then causes Db2 to drive a dummy commit on the thread. And that dummy commit will switch from RELEASE(DEALLOCATE) to commit and remove the claim. And so that may be why you're seeing more timeouts since you went to Version 12. And so what you might want to do is to reduce the value of the drain weight or the max RO setting on your online REORGs to reduce the number of deadlocks -- sorry, timeouts and deadlocks. [ Mark ] has asked me a question which says, "We found that to have -- we have to rebind the online packages of RELEASE(COMMIT) to be able to perform DDL." And I think actually that -- [ Mark ], that is the option really of last resort here. I mean the design intent in Db2 is that you shouldn't have to kill off thread reuse, and you shouldn't have to switch to RELEASE(COMMIT) to break in for DDL. But a number of my customers have had to do that in order to break in. Normally, what they've done, [ Mark ], is they didn't change the packages from deallocate to commit. They just temporarily killed off the thread protection. And one of my earlier webcast this year, I think it was on the 17th of June, I discussed in there some techniques for temporary killing off the thread protection. So [ Barry ] has asked me. It's a theme that's coming through here. He says, "RELEASE(COMMIT) provides the greater concurrency." Agreed. "But will switching to RELEASE(DEALLOCATE) cause increased locking?" So in fact, RELEASE(DEALLOCATE) is going to reduce locking because it's going to avoid the repetitive cost of acquiring and releasing the parent locks. But I think you need to go back to the answer I gave earlier is you got to worry about concurrency when you've got applications using SQL LOCK TABLE, tables that have got LOCKSIZE TABLE or TABLESPACE or triggering lock escalation. And [ Ashish ] has asked me the question, I'm very grateful this question. He says, "For frequently used NULLID packages, is it advisable to use RELEASE(DEALLOCATE) option?" And the answer is most definitely not. So here, we're talking about NULLID packages that are used by DDF traffic. And if you take those packages that are in the NULLID collection, the standard ones, it's one package for UR, one for CS, one for RS and one for RR, if you bind those -- one or more of those packages for RELEASE(DEALLOCATE), then any dynamic SQL will touch one of those packages and immediately become eligible for using a high-performance DBAT. So to repeat the recommendation in the presentation, the packages in the NULLID collection should be bound with RELEASE(COMMIT). And what you should do, if you want to use the high-performance DBATs, is introduce a new collection, and let me call it NULLID2. And it's the packages in those collection that you should bind as RELEASE(DEALLOCATE). And typically, people might decide to use system profile monitoring throughout the applications to use the NULLID2 collection. Okay. [ Mike ] has asked me a question about, "Using RELEASE(DEALLOCATE) for OLTP transactions, when do the data locks get released?" So to repeat, [ Mike ], and I think it's a very important point is that when you use the RELEASE(DEALLOCATE), the page and the row locks are released at the commit of the transaction. It is only the parent L locks, the IX and IS locks, that persist. So typically using row- and page-level locking, so those row and page locks go away at commit. It's just the parent locks that persist. [ Gale ] has asked me, which says, "If those high-volume packages need to be reorganized, what if they need to be reorganized?" Well, that's one of our design points here was the online REORG was designed to be out since Version 3 to break into long-running threads that were using RELEASE(DEALLOCATE). Now when it comes to the alter and the exchange, I think I said earlier that the design point in Version 11 was for DDL and things like exchange for the clone to break in. But a number of customers have reported not. So I think that we need -- in development need to do some more work here to make sure that DDL can break in. But if you are using them, and I'm assuming the alters are going in, in a relatively quiet period, you should temporarily turn off the CICS thread protection. And the how to do that was in my webcast I did on the 16th and 17th of June. [ Mark ] is then saying, "Why can't all packages use RELEASE(DEALLOCATE)? Where is the disadvantage in this?" Well, the only disadvantage of this is all those things I said earlier, about lock escalation, tables having LOCKSIZE TABLE and so on. So I mean, if you bind all your packages with RELEASE(DEALLOCATE) and you don't get thread reuse, then deallocate can do no damage. It's one you -- once you get the thread reuse with RELEASE(DEALLOCATE) that you have to consider those concurrency issues or if you have a batch program that frequently commits and you use RELEASE(DEALLOCATE) on those packages. [ Laura ] is telling me they use a dynamic plan selection exit to set the plan name. And she says, "We currently do not have anything using getting thread reuse." Yes. So [ Laura ], the key to this is, if this -- for this Db2 entry, if -- basically, the -- if the exit is constantly switching the plan name to the CICS transaction name, then that will kill off thread reuse. So [ Laura ], what I'm thinking here is that you might have a few or many transactions linked to the same entry, Db2 entry in the CICS definitions, and that the dynamic plan selection exit when the transaction runs is setting the plan name to the respective transaction. So in that case, the CICS dynamic plan transaction exit, as it's being invoked as the thread is being reused, is constantly changing the plan name. And if that's the case, that will kick off thread reuse. So [ Laura ], I think maybe I have a solution to this. Why do you still use the CICS dynamic plan selection exit? Why don't you put a separate entry in the CICS RDO definitions for each transaction where that transaction points to its own transaction name -- own plan name. So get rid of the dynamic plan selection exit and have multiple entries in the CICS RDO definitions that point the transaction to the respective plan, and then you can use protected entry threads on the high-volume transactions. Thank you for that, [ Laura ]. Yes. Another good question from [ Laura ], "Is there a way to control, monitor to number -- to control a monitor number of application server connections from z/OS? It's difficult to control what applications are installing on their app servers." So from controlling it from the center, the answer is no. But what you could do is use the display location command and see how many connections are coming through from the app servers and see if they've grossly overallocated them, [ Laura ]. [ MK ] is asking me. He assumes that the type -- the WebSphere-type connection would also apply to the CICS Liberty Type 2 connection as well. And the answer is yes. Anyway, all good things come to an end here. There was a tiny number of -- we've got -- all good things come to an end here. We've run out of time in here. So I'd like to thank everybody for hanging in here and for -- especially for those who asked the questions. I hope you found it valuable, and I hope you tune in next week as well. Thank you very much.