IDUG Db2 Table Talk
IDUG Db2 Table Talk
De-Mystifying the High Performance DBAT with John Campbell
Use Left/Right to seek, Home/End to jump to start or end. Hold shift to jump forward or backward.
After hearing John Campbell speak at GSE Nordic 2025, Marcus had questions...What is a high-performance DBAT? How do you make a thread "high performance"?
John Campbell – Db2 guru, consultant, and former IBM Distinguished Engineer – starts at the beginning of the distributed journey within Db2 for z/OS. Tune in to learn about cheap connections vs expensive threads, whether a large or small connection pool is better, and the really stupid default in an early Db2 Connect fix pack. Truly an enjoyable conversation (that even our non-technical marketing team could follow).
Learn more about IDUG, the International Db2 User Group, at www.idug.org.
Hi everyone. Thanks for joining us for this series of iDug Podcasts, where we speak to some great people in the DB2 community. I'm Marcus Gavage.
SPEAKER_03And I'm Julia Carter, and we represent the iDug Content Committee. We hope you enjoy today's edition.
SPEAKER_00And it gives me great pleasure to welcome John Campbell to a special edition of the iDug DB2 Table Talk podcast. The reason we are chatting with John today is because I had the pleasure of listening to him a couple of weeks ago in the GSE Nordic conference, and he was talking about high performance DBATS. And after his presentation, I asked, What is a high performance DBAT and how do you make a thread high performance in the first place? That led to a very interesting conversation, and I thought this should be great fodder for a podcast. And so it gives me great pleasure to welcome John Campbell, late distinguished engineer of IBM, global world uh globe trotter for uh DB2 Guru, and now is a Triton consultant for Triton Consultant in the UK. Welcome. Thank you, Marcus. Thank you.
SPEAKER_03Yeah, it's great to have you back again, John. And this is a topic that I've also been very interested in over the years. High performance DBATs were introduced in DB210, but DBATS or DDF threads have been around for a lot longer. So can you give us a quick history lesson and explain how we came to high performance DBATS?
SPEAKER_01All right then. So let me go back to the beginning of the distributed journey within DB2 for ZOS. So this started in 2.2 of DB2, where DB2 development implemented this private protocol to allow inter DB2 for ZOS communication. The really big shift came in version 2.3 when IBM implemented the DRDA protocol, which enabled then that was designed so that all the members of the DB2 family, DB2 for ZOS, SQL 400, and DB2 LUW called all inter interconnect. So that was the really the start of what we now see today. And um in those days, if you can catch your mind back, I mean IBM was really heavily focused on on DB2 on OS2 as the way of connecting to the back mainframe. But in those days, um when you opened a connection, a connection resulted always in using a thread. So this correlation between a connection being used to do some work or even idling with a DBAT in DB2. So one of the important things on this journey of distributing in DB2 was called type 2 inactive connection support. And the whole idea behind this is that connections, once they're created, are very cheap. On the other hand, DBATs are expensive in terms of the real memory to back them, and particularly the CPU required, if the if the connections are being can broken and reconnected frequently, then you've got the CPU overhead of creating and destroying the DBAT. So to repeat myself, connections, although they're expensive to create, once they're created are very cheap. On the other hand, threads which are DBATS are expensive because the real memory to back them and also the CPU required if those DBATs are being frequently created and and destroyed. So the whole idea of this type 2 inactive connection support is that it could have a a gazillion connect a gazillion connections where you stay can potentially could stay connected all day, but when that connection becomes active to do work, what then happens is that DB2 will reach into what's called a DBAT pool, which is defined by the ZPAM max dbat, which defines a pool of DBATs which can be serially reused. So when a connection switches from being inactive to actually wanting to do some work, DB2 reaches into the DBAT pool, and assuming one is available, it will assign that connection to the DBAT. The transaction will then run, and when we get to the commit point in that transaction, what then happens is the DBAT is pooled, the term is DBAT pooling. Those DBATs are put back, the DBAT is being put back into the pool for reuse, probably by a completely different connection, and the connection is parked. So in this way, you have a relatively small pool of DBATs which are serially being used, and on the other hand, you can have many, many connections out there because they're very cheap. So that was a very significant thing in the history of DB2. We often refer to this as DBAT pooling or thread pooling. And uh there has been a lot of misinformation over the years in the publications, which is now being corrected, because people used to talk about type 2 inactive threads, and in actual fact, there's no such thing as an inactive thread, it's in fact an inactive connection. So that that terminology has been corrected, and I've used it for a long time for the purposes of explaining what goes on. So to repeat again, max DBAT defines a pool of DBATs that can be serially used by different connections, and you then got a large number of connections which are expensive to create, and you want to keep them there for as long as possible. And every time one of these inactive connections wakes up to do some work, it is assigned to a DBAT out of the pool, which has been idling in the pool, to then run the work. And when you get to the commit point, then at this point, the DBAT is pulled for reuse by a different connection. It doesn't have to be, but probably will be a different connection. And the other hand, then the connection becomes inactive again. So that leads us then into high performance DBATs. So if you think if those of you who've got a KIX background, think of the concept of protected threads and using the bind option release deallocate. This is a high performance option used by many KIX customers to avoid the repetitive cost of thread create and terminate and to get true release deallocate execution. And without having the thread reuse happening, then there's no value from release deallocate. So let's just look at prior to high performance debat, what goes on? First of all, you've got this overhead of the DBAT being pulled and the connection going inactive. And the second thing is that even if you bound the packages with release deallocate, prior to high performance debates, release deallocate is is not used at all. It's a no-op in there. So when you look at what high performance DBAT is trying to do, it's trying to give you a performance improvement in two places. One, avoid the DBAT pooling, and secondly, to give you true release deallocate execution. And it's really trying to give you the same sort of benefits you would get with KICS thread reuse with release deallocate, or you would get with IMS TM thread reuse and release deallocate. So simplistically, that's what we're trying to achieve. And if repeat again, there are two benefits which you should consider separately. One is to avoid the DBAP pooling, and the second thing is to get true release deallocate execution.
SPEAKER_00Right.
SPEAKER_01Does that make sense to you both?
SPEAKER_00Yeah.
SPEAKER_03So it's increasing performance and it's decreasing the memory?
SPEAKER_01No, in fact, yes, it's it's pure aim is about performance. But one of the dangers of high performance DBATS is you're talking about using a high performance DBAT is in fact a dedicated thread. So, like anything like this, if you overuse high performance DBATS, you run the risk of using an excessive amount of memory, an excessive amount of threads, and blowing the limit off the top of max DBAT. And when we maybe later we'll talk about war stories. But a number of customers have actually just jumped into this with both feet and both hands and hit max DBAT and blowing the limit off it and not really knowing what's happened.
SPEAKER_03Yeah. Uh yes, I'm nodding along because uh that that may have been me back in version 10.
SPEAKER_01So, I mean, so the basic way of triggering high performance DBATs is first of all, when a transaction runs, it needs to touch at least one package with release deallocate. So that's the first ingredient to get this going. At least one package that's marked with release deallocate. And also you put in the modify DDF command with a package rel in there of either bind opt or bind pull. So that modify DDF command is a lever, a big lever here about turning high performance dbats on and off. So once you pull that lever to say modify DF package rel, bind opt or bind pull, and then what happens is a package comes along that's got release deallocate, then the DBAT that's being used is then marked as a high performance DBAT. And that immediately stops the DBat being pulled and the connection going inactive. So now this connection is hardwired to this DBAT. In the same way you would have a protected entry thread with kicks. So it's so high performance DBAT is going to drive up the demand for DBATs. And you've got to make sure that you have enough DBATs left over for the workload that's not using high performance DBATS.
SPEAKER_03Right. And when we're talking about binding or rebinding packages, it has to have release the allocate.
SPEAKER_00Correct.
SPEAKER_03So that's the bind option. Um and we're talking about the null ID packages.
SPEAKER_01So it is the null ID packages. So here we get we end up in our first war story, okay? So by default, most people are using dynamic SQL, therefore, the packages that are in the null ID collection. The last thing you want to do is to bind those packages in the null that the default null ID collection as release deallocate. Because if you do that, everything becomes a high performance DBAT and you completely blow away max dbat. And there was a period of time, if I remember correctly, people forgive me if I don't get this wrong. I think it was DB2 Connect 9.7 fixed pack 3. They changed the default to release deallocate, which is a really stupid default. So that's my first war story here, which is you don't want to take the null ID packages and bind them as release deallocate. And similarly, if you're using things like store procedures or UDFs, you don't want to use a commonly heavily used store procedure or UDF and mark that as release deallocate because again it has the same effect of everything becoming a high performance DBAP.
SPEAKER_03Right. So we need to have a copy of the collection. And so for these remote threads, what what do they need to change in order to use this different collection?
SPEAKER_01Okay. So I'm going to echo back something that you said. So what you want to do to start with as a safe starting position is to make sure before you start anything that the bind option on the null ID collection is release commit. Get yourself into a safe starting position.
SPEAKER_02Right.
SPEAKER_01Then what you need to do is clone those packages into a different collection. We could call it HPDBAT or whatever it happens to be. And in those you mark you bind those as release deallocate. So now what you have to do is find a way of identifying the workload that's eligible and routing them to that collection called HPDBATS. And that sounds so so simple, okay? But in fact, turns out to be quite difficult. Because if you do it coarse-grained, where you haven't actually found good candidates, again you run into this problem of consuming a large amount of DBATs and blowing the lid off max DBAT. So you need to find good candidates. So just like if I use the Kix example again, you're looking for high volume transactions that occur frequently, like once every second. And you're also looking for long-lived connections because the whole idea here is the connection is tied to the DBAT. And along that connection, you've got lots of transactions serially reusing that DBAT. So you want there to be the volume of transactions running on that connection. Otherwise, you end up having a max DBAT, sorry, a high performance DBAT, where in fact most of the time it's spent idly. So you want to have high utilized EBATs for this and not low utilised EBATS. So the common way of identifying good candidates is to look, for example, in the DB2 accounting trace, and from an OMP perspective, it's would be the short accounting report trace. Sorry, the short report. And what you're looking for is how many commits per conversation? Conversation being a connection. And the general accepted rule of thumb is 200 commits per conversation. Right. So that means you had a long-lived connection that at least 200 times is reusing that um as having commits. Now, the other dimension, which is usually not talked about, is over what time period are you getting those reuses? You want to you've you've got to factor in how frequently those transactions are arriving. We've seen a number of people use high performance DBATs using the simple rule of thumb of greater than 200 commits per conversation, and they end up in situations where there's a large number of DBATs, but they're actually lightly utilized. And that's because there's a big time gap between those successive transactions running. So you're looking for a long-lived connection, ideally greater than 200 commits per conversation, and you want the transactions to frequently arrive. So that's what you're trying to do in terms of selecting the candidates.
SPEAKER_03Right. So you absolutely have to do some analysis of your accounting.
SPEAKER_01Absolutely, you need to do that.
SPEAKER_00So it's more than just turning a switch on. You've there's a lot of analysis that needs to be done. Yeah, this is uh this is really interesting.
SPEAKER_01One of the earlier customers did exactly that, Marcus, pulled the big red switch on this, right? Big green switch, I should say, on this. And basically, they also ended up situations where there weren't long-lived connections. And therefore, when the when if you don't have a long-lived connection, which means you've got a short-running connection, which means you're continually connecting and disconnecting from DB2 from the app server, then that would kill off the high performance dBats. And so your whole idea was to avoid the overhead of pooling, but now you've got yourself in a worse situation that with short with short-running uh connections, then you're continually creating and destroying the DBAT. And that's why that second option on the modify DDF package roll command was coming in. The original option was just simply modify DDF package roll bind up, which says respect the bind option. But then later on, an APAR came out to give you the other option, which is called bind pool, which says don't destroy the DBAT, just put it back in the pool to be reused. So again, that's reacting to some you know um bad practice in terms of uh using high performance dbatts.
SPEAKER_02Yeah, okay.
SPEAKER_01And now we get to the killer thing, which is having identified your application, how are you going to route it to the to the high performance DBATS collection?
unknownYeah.
SPEAKER_01Now in a perfect change. In a perfect world, you've got an app server that all it's doing is this pure high performance workload. And maybe that app server has got a technical user ID on it, and then you can put up a system monitor profile based on that technical user ID and route it over to the high performance DBATS collection. But now what happens if the application server is using a mixture of high performance workload and low performance workload? In other words, workload that doesn't run very often. What happens if it's if that app server is running multiple applications? And some of them are actually good candidates for high performance DBATs, and others are not. So then you need some identifiers to identify the workload. So the the nice case is where on that app server everything is a good candidate for high performance DBATS, and you can simply put a system monitor profile based on that technical user ID to use the high performance DBAT collection. But after that, if you've in a worse case, we've got multiple applications, some are good candidates, some of those applications or some of the transactions inside those applications are good, and the rest of them are not. So therefore, you need some other identifiers. So now you have to think, well, what can I play with? And the answer is not a lot. And so this is so if the customer has used those APIs where you can I you can annotate the thread, where you actually say you can have your accounting string, your user ID, um, workstation, if those are populated, then you can use those identifiers in a system monitor profile to have fine granularity of routing that workload to the high performance debat collection. But a lot of customers haven't invested their time in it. So then you say, Maybe I can use the IP address. Let's assume the best case that all the workload is suitable for high performance DBAT, then you say, well, maybe I can use the IP address. And that rolled off my tongue really easily as well. But then in a situation where, for example, you know, Kubernetes is being used, let's say OpenShift with Kubernetes as an example, is being used to dynamically create application servers and take them away, then you don't know what those IP addresses may be. So it's very important to identify the candidate workload, but then your next challenge is how can I identify that workload that's the unique that's the best candidate for high performance DBATS and route it to that particular high performance DBAT collection.
SPEAKER_00So your application developers need to invest a lot of time and effort into populating these uh these fields, the client info fields on the application side, in order to specify this these are good candidates for high performance DBAT so that the DBAs can uh write can can code the correct parameters in the system monitoring profile tables.
SPEAKER_01I mean, um just echo back your assertion. I think in some cases it will be required to get the fine granularity you need. You may be in a blue sky case where all the workload on that app server is all good quality workload for high performance DBATs. But if not, you've got to look for another way. And I think using these APIs for those applications is is the way forward. And in fact, I've always been a big fan of using one or more of these identifiers, not just for high performance DBATs, but to do with monitor performance monitoring and to get that more value out of the accounting and out of the display thread command to do that.
SPEAKER_03Absolutely. It's so frustrating as a DBA when you're looking in your monitor or you're looking in the reports and you just see the generic strings and you're like, oh, help us, please, just help us a little bit with some sort of identifier of what this thing is. And it when you've got an incident, it just makes it so much easier if you have managed to get people to fill in those those blanks.
SPEAKER_01Yeah, at least at least fill in one of them to help you with the monitoring. So so again, going back to Marcus's assertion, I totally agree with you, Marcus. I would encourage all customers to use those APIs with a you know with an accounting string or a workstation ID or Apple name there as a way of being able to improve your monitoring and problem determination as well as using high performance DBATs.
SPEAKER_03It's not an easy win, is it, this?
SPEAKER_01No, it m in a lot of cases it's not an easy win at all. And there's great and if it's used intelligently, there can be significant performance benefits from this. But if used in a wild fashion, can I use the word wild, the danger is that you starve the DBAT pool for the workload not using high performance DBATs, and in the worst case you blow the lid off max DBAT, and then in between then, there are other issues maybe that you've got these DBATs, but there's high performance DBATs, but they're so low the utilization is so low that then after so many reuses or being inactive for a period of time, should say being inactive for a period of time, defined by pooling act, they end up um getting purged. And I can give you a war story from that as well. This was a customer in China who really had a very high volume workload, so they were well suited to using high performance DBATs. But unfortunately, the connection pools and the size of those connection pools on their app servers were were huge. So we end up with this strange situation that they did a full-scale stress test at about 30 transactions per second, and they came to me saying, John, why am I getting DBATs created? Why am I seeing DBATS being terminated? And the fact is the connection pools were so huge, completely way oversized there, that they were using high performance DBATs, but at the same time, some of them were then idling for the 120 seconds, which is the default for pooling AC and being killed. Now the solution for that customer was really easy. Just shrink progressively the size of the connection pools, and then we end up progressively with the DBAT being highly utilized, and we don't see any DBAT termination going on at all. And and they got the benefits they they they expected. So, yes, it rolls off the tongue very easily, and when you look at KICS, for example, you've got the KIX RDO definition, resource definitions online, the old RCT, and you have very precise controls in KICS where you can root one or more transactions to a particular plan and you can specify exactly how many um uh protected threads you want and how many, then you can overflow for the pool. You don't have that level of precise control with DDF. Your your your your tool of choice is in fact system monitor profiles, but at the same time, you can't say I want that application using high performance DBATs to use 10 DBATS and I want this one to use five. So there's no way of limiting what these individual applications will do. What you can do with system monitor profiles, of course, is limit the absolute number, but you want to try and carve it out in the same way you had with CICS. So you can't do that. So the big message here is about finding good candidates based on 200 commits per conversation with a high transaction rate per second, and also then being able to identify that workload at a granular level so you can route that particular workload to use the high performance dbatz collection.
SPEAKER_03Right. And just thinking about max dbatks, um is the recommendation then that if you're going to implement high performance dbatts, that you would need to increase your max dbat a bit anyway.
SPEAKER_01I think the answer is probably yes. I mean, some people, you know, have I'm just making this up. I saw a customer recently uh max dbat are set to a thousand. But in actual fact, the number of DBATs they're actually using during peak period is only about 200, 250. So they've got a lot of gas in there.
unknownYeah.
SPEAKER_01But for some customers, it will naturally lead to them having to increase max DBAT. And that then leads us to a thing that if you as and when you implement high performance DBATS, the first thing you want to do is to monitor what's happening and be prepared to put the big RID switch in, which is obviously the modify DDF package roll commit to turn it off. If you if you get surprising results, bad results, you've got to be prepared to pull that red switch to turn it off. Yeah.
SPEAKER_03Max DBATS that's has that increased in the last couple of versions of DB2? Has it gone up?
SPEAKER_01So again, it gets a bit confusing based on some of the parameters. So max DBAT is the number of threads, and typically I think the default is probably a thousand. I can't remember exactly what it is. But then you've got another parameter called con dbat. Now, con dbat is absolutely nothing to do with threads, dbat. It's actually the number of connections.
SPEAKER_03Connections.
SPEAKER_01So you can afford to have con dbat at much higher values, 30,000, 50,000, 100,000, even. But you but you but you end up with max dbat being reasonably small. And since the advent of version 10 of DB2 with the 64-bit use of 64-bit shared memory, private memory for the threads, then obviously you can have many more DBATs than you had before.
SPEAKER_03Oh, okay. Right.
SPEAKER_00Our listening demographic ranges from newbies to seasoned professionals. So for the for the newbies, could you elaborate on the difference between a connection and a thread?
SPEAKER_01Right. So the connection is simply an IP socket. That's all it is. It's an IP connection to the target database server. And so when they're being created, a connection's being created, it does require a DBAT in DB2 to do that connection, to do the authorization checking. But once it's created, it is and it's idling, not doing any work, then it's got a very small storage memory footprint. So, but so as I repeat what I said in the introduction, connections are expensive to create and destroy, but once they're created, they're very cheap. So the whole idea is you can have many, many of these around there, and once you're connected, you want to stay connected for as long as possible. So, as I said, a connection is nothing more than an IP address, a communication pipe from the application server down to the database server. A DBAT, a database access thread, is a classic thread in DB2. So that it has a significant memory footprint, and you don't want to create and destroy them all the time. So that's why in DB2 we have thread pooling, as I described, that when you get to a commit point in a transaction, the connection goes inactive and the DBAT goes back in the pool to be reused. Note the key word to be reused. So we're not creating and destroying threads on a frequent basis. The DBAT is being put back in the pool to be reused almost certainly by a completely different connection. And what DB2 does is after it was in version up to version um 13, up to, but not including version 13, after 200 reuses, a DBAT would be purged and recreated. In version 13, it's gone to 500. And the reason why DB2 purges it after so many reuses is because it's running all this workload from different connections, it's accumulating, can I use a lot of garbage or collecting a lot of information on the thread? So it's basically rejuvenating the thread after so many re-reuses.
SPEAKER_00Right.
SPEAKER_01So so the whole idea, uh hopefully I've addressed the question, Mark, is about what they actually are and have that very clear in your own mind. And so, yeah, connections are an IP address, a DBAT is a thread in DV2, and in both cases, you want to avoid the repetitive cost of creating and destroying connections and creating and destroying threads.
SPEAKER_00So we've talked about what a DBAT is, um database access thread, and how a regular DBAT differs from a HP DBAT. We've talked about the big red or green switch and the release the allocate and how to how to rebind the copy of uh null ID packages just for high performance dBATs. We've talked about uh how you identify a DDF workload for high performance. So, what are the operational considerations for high performance DBATs? What are the overheads? Is it worth switching them on or off at different times of the day? Or what can you tell us about the operational considerations?
SPEAKER_01So I think if you've chosen good candidates for the workload, then there's no overheads as per se as such. You are making a trade-off of using more memory for the threads because you're likely to use more DBATs. So there is that overhead, but then you get the benefit of avoiding the thread pooling, and you get true release deallocate execution for the same time.
SPEAKER_00Right.
SPEAKER_01Okay, so that's it. The problem is when you make a bad choice, then you end up using too many threads, too many DBATs, and or you end up with a situation where DBATS, despite what you thought was going to happen, you're gonna end up with DBATs being purged and recreated. But I guess the nightmare scenario is that you do it in a very blunt way without doing your research, and you actually hit max DBAT, which then becomes a denial uh denial of service.
SPEAKER_03Yeah, yeah.
SPEAKER_01I don't think it's scary, I think it's a performance opportunity. Um, it comes across as being quite simple to do, which it is, but it is you need to use it intelligently, or else there are these to these serious drawbacks to to actually consider.
SPEAKER_03Yeah, I remember one place that I worked, it was version 10 quite early on, and we liked the benefits of high performance DBATS. I don't think we'd really explored the the potential downsides of using them. And we implemented them, looked at the stats after implementing them, and then had to switch them off. And I think they stayed switched off because we couldn't get all of the pieces of the puzzle to work so that we could get the situation that you've that you've been describing, that that that good situation where it will be really advantageous to have them.
SPEAKER_01Yeah. So I think I'll say something else about good candidates. So with both JDBC and ODBC.net, the default is auto-commit, and we still could see customers implement applications with or without using framework application frameworks who still leave autocommit turned on. So with autocommit turned on, that means every single SQL is followed by a commit. Well, you sadly to say, that is the one of the best use cases for high performance DBATs because if you've got a commit after every single SQL, after every commit, the connection's going inactive and the DBAT's being pulled. So in fact, strangely enough, this situation um is actually of uh is a good use case for it. When you look at the benefit from the least you allocate execution, what you're looking for is a unit of work that does many SQLs per unit of work. And when I say many, I don't mean a single SQL section with a loop around it. I'm on about lots of different SQLs touching different tables. So the more complex the unit of work, the more tables and indexes you touch, that will magnify the benefit of the release deallocate execution benefit and so on. And one thing we haven't discussed yet, which apologize for not mentioning it earlier, there are also some WLM workload manager considerations here. So with or so with or without high performance DBATS, then you've got every time a unit of work runs, then an enclave gets created, and when you get to the commit point, the enclave gets deleted. So an enclave is an anchor point from a WLM perspective to run a DDF transaction and to be able to control it. So as part of the implementation, way back in in 2.2 and 2.3 of DB2, when a transaction runs, it needs a thread, it needs an anchor point. So what happens is that DB2, with the help of ZOS, uses what's called independent enclaves. And roughly speaking, until somewhat recently, then it was a very simple model that basically every time you had a transaction running in DB2 distributed, an enclave was created at the start, and when you got to the commit point, the enclave was deleted. Well, if I look across my user base, my social worldwide social circle over many years, the biggest growth area in DB2 is in distributed.
SPEAKER_02Yeah.
SPEAKER_01And I mean, I've seen several customers now where the usage of KICs and IMS is declining dramatically and the workload is moving over to distributed. So over time, what you're seeing is the rate of enclave creation and deletion get higher and higher. And enclave creation and deletion is not free, it's going to cost CPU. And unfortunately, there is an underlying issue in ZOS. Well, ZOS will sometimes take what's called a spin lock, a ZOS spin lock. So IBM put out an APA, I think, a couple of years ago now, and that APAR only applies to high performance DBATS. I repeat, only applies to high performance DBATs. And what it did was it changed the duration of the enclave. When you've got a high performance DBAT now, the enclave creation is exactly the same at the start, but the enclave stays until the connection or the thread dies. So before an enclave duration was the same as the commit scope roughly in DB2. But with high performance DBATs, once that PTF of the APA went on, then the duration of the enclave went from being a commit scope to being a connection scope.
SPEAKER_00Right.
SPEAKER_01So why does that matter? The reason why it matters is that in the in your WLM policy, you would classify your workload, put it in a service class, and typically you don't have to do it. You would give it a response time goal. You might say, I want, you know, I don't know, 0.2 of a second um for 90% of my transactions. Well, that's assuming an enclave duration is the same as the commit duration. And by the way, sometimes people will put um what what's called a velocity goal on there. But many of many of the customers I work with actually have response time goals. They may be using multiple periods, but they have a response time goal. Well, this change that DB2 put out is a huge change from a WLM perspective. Because if you are using a response time goal, put it politely, you're now screwed. Because WLM can no longer manage the workload. You gave it a response time goal for an individual transaction, but now the duration of the enclave is the connection. So if you didn't change your WLM policy to change it to a velocity goal, then WLM is no longer able to effectively manage the workload. So very important operational consideration is that when you do go to using high performance DBATS, you want to make sure that workload is isolated into its own service class with a velocity goal.
SPEAKER_03Ah, okay. That's quite that's quite important information, I think, isn't it?
SPEAKER_01It is very important information, and remiss of me not to have mentioned it earlier when Marcus asked me about operational considerations. So I understand in the future ZOS will provide a fix for this RSM spin lock, but nevertheless, you've still got the situation with or without high performance DBATs, when the workloads are growing in terms of the rate per second, you've got this overhead of creating and deleting enclaves. So this was a, if you like, a tactical solution implemented by DV2 to get to eliminate this overhead of enclave create and delete for high performance DBATs, uh, and to avoid any exposure to the ZOS spin lock problem.
SPEAKER_02Wow, okay.
SPEAKER_01More than you thought, eh?
SPEAKER_02Yeah.
SPEAKER_00I think that's uh WLM management for DP2 is uh the topic for another presentation or podcast, I'm sure. Probably, probably, yeah. So we've talked about the history and operational workloads and considerations. Uh we've talked about the benefits. Are there any more war stories you'd like to share?
SPEAKER_01No, I think I've exhausted my uh my stack of uh of war stories.
SPEAKER_00I do remember one little war story, it's not necessarily related to HPD bats, um, but it starts, it did start back in DB2 2.2, uh where the systems programmer, the MVS systems programmer installed it, just read the manual and installed db2 as it was without giving any consideration to sizes or naming conventions. And so the the batch plan name was called plan name, and the kicks plan name was also called plan name. Oh uh, and so it wasn't until DB2 2.3 when packages came along and uh DRDA, which replaced private protocol, uh, that we actually thought let's rename these plans and uh have some sense if we'll have a KIX plan per transaction, uh, we'll have uh batch plan per whatever and sensible naming conventions for collection IDs. So that was a very good move. One of the most important, I think, of uh of DB2 early on was uh packages in 2.3.
SPEAKER_01I agree.
SPEAKER_00Yeah, it's really awful or and difficult to find out where your performance hit is is happening when all of your plans are called planning from all of your kickes talking to DB2.
SPEAKER_01Well, in fact, we talk about the same thing here in distributed. If you haven't used those uh APIs to classify your workload, then everything is running under a plan called Disk Serve, right? Yeah. Yeah, yeah. And then if you've got technical user IDs for each of the app servers, then that may help you tie it to an individual app server. But you don't know if there's multiple applications or quite disparate workloads in terms of what's running on the app server, you can't identify what the outliers are, what are the problem areas.
SPEAKER_03No. And in terms of the in terms of the monitoring and the reports that you need to be checking after you've implemented um high performance dbatks, what where would you recommend people looking?
SPEAKER_01Well, I think first of all, um you'd want you can look in you first of all, I would say look in the statistics trace and see the impact on the CPU being being burnt by the GDF address space.
SPEAKER_03Okay.
SPEAKER_01That's one thing I would look at there. So um when you look at the the SRB time for the disk address space and statistics trace, about 60 to 70 percent of that it represents the SQL running on the threads. The rest of it is the system overhead to do with you know thread pooling and connections going inactive.
SPEAKER_02Right.
SPEAKER_01Um obviously, if you're getting benefit from the release deallocate portion, you'd obviously see a reduction there. But if you want to see elsewhere, want to see the benefit of release deallocate execution, you can look in the accounting trace.
SPEAKER_03Right. So for our before and after check of whether it's actually improved things, it's the accounting trace.
SPEAKER_01Stats trace and also the accounting trace.
SPEAKER_03Oh, both. Okay, right.
SPEAKER_01So the release deallocate benefit. So just for for for people listening to this podcast, there's an element of double accounting between the accounting trace and the stats trace. So obviously accounting, you know what that means. But in the stats trace, it's not just the overhead of the disk address space, it also includes the enclave CPU. So when you look at distributed, it's the SRB time can be huge. And the reason is it's not just the overhead of the disk address space in terms of classifying and routing workload and doing security checking, it all 60 to 70 percent of that cost is in fact the same CPU cost for the SQL that you see in the accounting trace. So you mustn't double you mustn't double count it. And that's nothing to do with high performance DBATS, that has always been the case. On the other hand, from a ZOS capacity planning viewpoint, if you if you look in the type 30 records, you know exactly what DIST is using versus exactly what the application is doing. But from a D V2 side, there is this double account. Counting in there.
SPEAKER_03Yeah, okay. Something to bear in mind.
SPEAKER_01And then the other thing to do is to see what happens. Are you seeing, you know, how many DBAT obviously, how many DBATs are you using? By by looking in the stats trace, I mean you can see, for example, how many DBAT you're using. You can see how many times max DBAT was reached. You can see how many times connections that became active had to wait for a DBAT. So those are indications of starvation and the need to increase max DBAT.
SPEAKER_00And these are accounting traces?
SPEAKER_01No, those are in the stats trace.
SPEAKER_00Stats traces.
SPEAKER_03And is this quite a difficult thing for people to test? Or is it quite easy to implement high performance DBATs on a lower volume of transactions and then extrapolate up for your performance workload? Or is it do you need to be running a test with what's going to be happening in production?
SPEAKER_01I think it's very difficult, uh Julia, to do this in tests, because very few customers have a workload that represents the stress level of production.
SPEAKER_02Yeah.
SPEAKER_01And therefore, if you do it in a test system where you don't have the workload stress, then you can see situations where you know the threat the DBATs hit pool and ac and are being destroyed.
SPEAKER_03Right.
SPEAKER_01So often in most customers, very few exceptions, there's a big difference in the stress level in test to production. And I think that leads me to a recommendation which says you really don't want to go after your biggest, heaviest application, most important application to start with. You need to find an application which is a good candidate, but not as important as your favorite children here. And to safely implement that first, and once you've done that, then move on to your your high priority, most important business transactions.
SPEAKER_03Yeah, that's a really good piece of advice. And have you seen customers maybe implementing high performance DBATS for um for maybe a short period of time, six hours perhaps or a day, and switching it off to then do the analysis and work out whether they can really switch it on or if they were better before without the high performance DBATS?
SPEAKER_01Um generally speaking, um I've seen people just turn it on, and if they're happy with it, they're just leaving it on they're going to monitor. But I do think that if you uh it's good practice anyway, whenever you do a change in production, to know what the implications are of what the change would be, what you expect to see in the stats and accounting race, and also to have a plan to bail it out, you know, fall back out of high performance if it's not good. So I think people should be ready, always with the the plan to know what they expect to see, and if not what could be causing it, and to make sure they have a backout plan.
SPEAKER_03The backup is super simple, it's just that modify DDF um package rel commit. Package rel commit. Okay, simple.
SPEAKER_01Yeah.
SPEAKER_03That's the backup.
SPEAKER_01Unfortunately, it is the big red switch, you switch it off completely when you do that.
unknownYeah.
SPEAKER_01That's your that's your safety valve if you're over overusing it. Package rel bind opt and or bind pool or when when you actually enable it, you so if you want to switch it off, you use can package rel commit. If you want to turn it on, you're saying bind pool or bind opt. Right. Bind opt was the original parameter option, which said simply respect the bind option. But then DB2 development introduced another option called bind pool, which says when the connection goes away, for example, rather than actually blow up the DBAT and have to have the DBAT to be created, put it back in the pool to be reused. And that was done into as a reaction to people misusing high performance DBATs with short-running connections, and therefore to avoid the repetitive cost of creating and destroying and then recreating DBATs. Most people will go with bind opt. Just respect the bind option if they've done their homework properly.
SPEAKER_03Yeah, yeah.
SPEAKER_00So, as Iron Man said, with great power comes great responsibility. So there's there's great power in high performance DBATs, but you need to be responsible in how you uh switch them on, do your analysis, work out which uh threads are eligible for high performance, and of course, on your development side, uh make sure you're populating all of the uh APIs and uh client info just so that the you the DBA can have a fighting chance of uh uh isolating which threads uh eligible for high performance.
SPEAKER_01Yeah, that's a good recap, Marcus.
SPEAKER_03Excellent. Is there anything else that you wanted to add, John?
SPEAKER_01No, I think I've exhausted my my knowledge at the moment. I might have regret analysis later because I forgot to mention something. But um no, I think that's it. I I remembered the WM consideration. I'd almost forgotten that, but I I remember that just in time.
SPEAKER_00So if you have forgotten anything, uh write it down and then you can deliver it in the GSE or the GSUK conference in November.
SPEAKER_03We'll do that. Yeah, so Gareth Cobbleston Jones, your colleague, is going to be doing a presentation on high performance DBATS in a couple of weeks in the north of England in the United Kingdom. So if anyone around is gonna be is gonna be there, you'll you can you can look forward to seeing Gareth. Uh John, we're gonna see you a few times later this year as well. Can you tell us about your your upcoming conferences?
SPEAKER_01Okay, so um the one I I think at the L Dog in September in London, where the theme of that day is going to be about data recovery. And so I'm building a presentation, it's well built now, almost finished, where I'm gonna talk about considerations for data recovery, what the issues are, um, and basically how to set yourself up so you can recover quickly from logical data corruption. So that's it, that'll be good. And then I've got the iDug coming up in Dusseldorf, uh, where I'm gonna talk about uh features in DB230, new features in D B213 related to performance and availability resilience. Oh and I've yet I've yet to have it confirmed. Hopefully, I will be selected that at GSUK I've got this presentation about high performance DBATs, which uh Marcus and also uh Martin Packer asked me would I do. So I haven't started that yet, but um today's been good practice to build all the key points into a presentation.
SPEAKER_00Well, we've got to be of service, John.
SPEAKER_03Thank you. Well no, thank thank you very much. This has been really, really interesting, and I'm sure that our listeners will um will be very, very happy to have had this this very technical but enlightening podcast today.
SPEAKER_00Yeah, thank you very much, John. We really appreciated your time and your uh expertise and your delivery. Very, very clear, very easy to listen to, and hopefully our listeners will uh have appreciated and will learn a lot more about high performance DBATS as a result. Thank you.
SPEAKER_01Thank you, Marcus. Thank you, Julia.
SPEAKER_03Visit iDoc.org for the latest DB2 community news, information about events, and all of our technical content. Tune in again soon for the next podcast at idog.org forward slash podcast.