IDUG Db2 Table Talk
IDUG Db2 Table Talk
Db2! Disaster! Recovery!
Use Left/Right to seek, Home/End to jump to start or end. Hold shift to jump forward or backward.
A fireside chat about Db2 disasters, and recovery best practice, starring John Campbell, Emil Kotrc and Bill Pothoff.
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_01And I'm Julia Carter, and we represent the iDug Content Committee. We hope you enjoy today's edition.
SPEAKER_03And welcome to another exciting episode of the iDug DB2 Table Talk podcast. And with us, we have a host of famous names. We have John Campbell, once a distinguished engineer for IBM, Mr. DB2, now as a consultant for Triton. We have Bill Pottoff, who's basically Mr. Recovery for BMC Software, and Emil Kotuch, who started off as a developer for the recovery solutions for CA Tools, now Broadcom, and now he's an architect for Broadcom. And of course, you've got Julia Carter and myself, Marcus Davich, your wonderful hosts. So before we get started, let me tell you why I wanted this fireside chat to take place. So it was in GSE conference in the UK last year, and I give a presentation on uh is your DB2 recoverable from a ransomware attack? And Mr. Campbell comes up to me afterwards and says, I don't want to burst your happiness bubble, but have you considered and started to regale me with a list of uh circumstances under which DB2 is not recoverable? Uh and I thought this would be a really good podcast. And this is it. So thanks for entertaining me, and I hope you will entertain our users. So welcome everyone.
SPEAKER_01Yeah, thank you for joining us. And uh John, you've got some stories, haven't you, from uh from your days in the field.
SPEAKER_02Yeah, there's no short there's no shortage of these war stories, but to get some perspective here, though, that a system-wide point-in-time recovery and the key emphasis point-in-time recovery with data loss is a very rare event. Maybe one every couple of years, let's say, or something like that, but it's much more common than a real DR event. So the first story I'm going to tell you about illustrates a number of things, right? It'll clearly illustrate to you by the end of the story that if you haven't got a plan, you haven't got a design, and you haven't got a set of canned jobs, life can become very difficult. And what could take a few hours might end up being many hours and several days. Um, it will also illustrate to you that logical data corruption, whether it be by ransomware or whatever, is typically unique in nature. And also that it may start small and then escalate. So it may take a long time before the true nature of the problem becomes exposed, and you actually know what the root cause is, because you need to need to know the root cause to stop the bleeding or to staunch the bleeding, if I use that expression. So let me start with the first story then. This goes back to version six of DB2, and this is the worst thing I've ever seen. But the background to it is that there was an enhancement in version six, which required an enhancement to the CF microcode to enhance the pre-existing instruction called delete name. And it was done specifically for DB2. And when it was out in the field in production, I happened to be involved with the first customer that hit the problem and obviously IBM then worked on a fix for the coupling facility microcode. But what DB2 did, or we did in time at DB2 development, was we put out a hyper PTF to disable DB2's use of that feature. And that way we were protecting our customers. So once that PTF disabled the use of that feature, then all the customers were protected. So now let's begin the war story. So what happens is that the CF microcode gets fixed, it's readily available for some time, and we in DB2 put out a PTF which wasn't marked hyper, which re-enables this function, but it has a plus plus hold action on it, which says, please check you've got the microcode level on before you put this PTF on. So anyway, the customer didn't read the hold hold action or forgot to do it.
SPEAKER_01That must have been the first time that's ever happened.
SPEAKER_02Well, it just goes to show how something can start quite innocently and escalate. So basically, then the maintenance package that included this PTF went in at 6 30 p.m. on a Saturday night. Now on Sunday, the customer started to think it was a referential integrity problem and opened a case with IBM. And then what happened on Monday and Tuesday, this problem started to escalate because the volumes went up and so on. And the nature of this CF microcode bug was related to DB2 and the group buffer pool. And basically, this the purpose of this enhancement was to efficiently delete those pages that have been cast out to DASD. Well, in fact, what it was actually doing indiscriminately was deleting live pages, pages that haven't been cast out to DASD. So remember the timeline here. It starts 6.30 on a Saturday night. Nobody really knows what the problem is. They suspect it's uh it's a CEV2 with IBM about referential integrity, and this escalates badly into Tuesday. In fact, late on Tuesday night, when I happen to go on site, and people still don't know what the problem is, but unfortunately, um having seen the first problem, I um basically had a look and asked a few questions, and then we decide jointly, I with my direction, this is what the problem is, isn't it? And everybody agrees, and I then say, We're going back to 6.30 on Saturday night, and we're going to have to recover the whole system. And by the way, anything that happens after 6.30 on Saturday night, you're going to lose. Now think about if you have a situation like this, it's called page regression. So you in put you inserted rows that that never got to the DASD, you updated rows that didn't get to the DASD, you've got deleted rows that don't get to the DASD. But yes, you had the log records, right? But now you try to insert the row, a row, a new row, into the same slot as the one that should have been there. You try to update a row that's not there, and all of this starts to happen. Now, before I gave them this direction, they already had direction from the lab to recover to currency. And I said, Well, this won't work. Once you get beyond 6.30 on Saturday night on the log apply, this is then going to fail. And sure enough, that's what happened. So, anyway, they have to do a point-in-time recovery back to 6.30 on Saturday night. So they're going to lose three days worth of updates, insert updates, and deletes. So I could ask you the question, but I know the answer obviously. How long does it take to recover this data? And and or should I say, to get the services back? And the answer is five days to recover the data and a further two days to make sure it was done correctly. Wow. So you think, how can it take so long? Well, there's no there's no design, there's no plan, they know how to generate single object recoveries. So what first of all, you notice nobody's got the job to recover the catalogue and directory. Because it's in somebody's private JCL library, and he happens to be away. So um then you have to build the job to recover the catalogue and directory. And then, you know, there's always an expert who says what we need to do for 50,000 objects is put 50,000 recovers in the system, 50,000 single object recovers, and all you end up doing is overloading the CPU and overloading the I.O. So the brute force method of 50,000 or whatever the magic number was of recoveries is not going to work because you you can't sustain it, it'll take weeks, right? So anyway, then you run into further problems. You find you find out, for example, they've used tape stacking on a virtual tape server. So as soon as you try to run the jobs in parallel, you end up with deadlocks between the jobs. And then you find out to save money, they write the archive logs directly to VTS. Now, VTS, if you do a test in a bunch of people, you ask them, is it a disk or a tape device? And some it'll be 50-50, right? So I know it's got I know some of these VTSs are tapeless and there's a disk cache inside, but to the operating system, they are tape. So you've you've immediately screwed up completely any degree of parallelism in there, and you're having to undo all of this. And then because it's everybody hands-on deck submitting these jobs, what happens if the jobs fail? Is anybody checking that they're working? Is anybody looking for the ones that failed? So I could go on and on here in terms of the reverse of best practices, but that's how it can lead to seven days, okay? And loss of data.
SPEAKER_01It it sounds like it was a perfect storm that everything that, you know, all these tiny little things that could go wrong or that weren't quite right just added up to a complete disaster for this recovery exercise.
SPEAKER_02It does, but having done many of those uh DB2360 studies for IBM and led those studies, this is the biggest area of weakness in our customers today. And the thing is, if I now move on to the second case study, this is another innocent one where a customer gets hit with a problem on the DASD storage subsystem, and they're missing what should be a patch onto the onto the IBM disk subsystem. So, anyway, through a bit of confused language on a on a conference call with the IBM lab, they decide to turn off a live DASD storage subsystem that's got tens of thousands of pages in the cache. And then they apply the microcode upgrade to the DASD box and they start up and they find a few problems very quickly. And somebody says, Oh, just issue a recoverer against that object, and then the days and the weeks and the months go on, and all they end up doing is incrementally finding data corruption. Now, thankfully, this didn't end up with lost data, but it what this event illustrates is that when you think you've got logical data corruption, what you must do is find out the scale of the damage, how many objects and how much damage is to the object, because that would either drive you into a fixed forward strategy, which might invite some form of advanced data recovery, or it's going to lead to the point-in-time recovery. And that needs to be a very well-informed decision. And luckily, 99 times out of 100, probably at least, fixed forward is the way to go, especially if it's an application-induced problem. But you see, in this case, nobody decided to use the check index utility on the production system because they don't have data set flash copy. So therefore, running the check indexes on production would have been disruptive. And equally, nobody thought, why don't we why don't we invoke the DR environment and spin off a tertiary copy and run the check index over there? So the m the stuff. So this illustrates the need to quickly identify the scale of the damage to drive the decision. And also a bit of best practices in there as well. So I could go on with numerous examples here, except the topic we're having today is a very worthwhile topic. And uh a lot of people have a rather simplistic view, especially if we go on to talk about you know cybercrime, etc. But I'll stop at that point and allow the other people here, my colleagues, to chime in.
SPEAKER_03So, Bill, Emil, any comments? Pretty pretty horrific horror stories.
SPEAKER_04Yes, interesting stories from John. So yeah.
SPEAKER_00That is horrific. And that that wraps together almost all of the craziness I've seen in you know various recovery stories where we've seen uh you know customers have have uh you know these scenarios where just everything goes wrong all at once, but but that took all of those different pieces all into the same same story. That that was uh uh really incredible. Um I mean I I think you know the issue really is is um there's a lack of preparation in a lot of places. I look back to you know when I started in DB2, um we would do disaster recovery exercises, right? Um and they're painful. You have to a lot of times get an off-site location temporarily, you take everything over there, and you you have a limited amount of time to get everything back. We're nothing if we're not adaptable. We we adapt to this the situation and we we like to sleep at night. So we show up at DR and we have all of our copies, and well, gosh, it it's really painful if we have a lot of log to apply. So let's let's time the disaster so it happens right after our copies are made, right? Because that's that's really the best time for a disaster. We'll we'll lay down the copies, we'll apply just a few log records, and you know, boom, you're up and running and you can go home, which is great. But then something like this happens, right? And and you're you're not prepared because when you're applying you know a day, a full day's worth of log, um, it's it's completely different than if you're recovering you know a handful of objects uh to a point right after the copies are made. And so you know what you find is is folks just a lot of times aren't prepared for the real real disaster. Um you know, one of my favorite stories to tell is uh you know, I used to work in support before I was in development, and uh I got a call from a customer who said, you know, hey, my copies they're running so long that they're bumping into the next one, uh, you know, and and the next one's trying to start, the prior one isn't finished. You know, can you help me? And so we helped him tune his copies a little bit, and when we were done doing that, and we got things running quite a bit better, but I said, you know, hey, you're you're copying these things, you're you're stacking your copies on tape, uh, you've got a ton of data here. You know, what's your plan for you know getting this all back? And he said, Well, our plan is we we hope we never have to. You know, and and hope is not a great strategy, right? And so uh and eventually two or three years later, we did get a call and they did have one of those scenarios just like what John was talking about. There were uh you know lots of spaces going into LPL and GRECP, and they they needed to you know do recoveries, and and a big problem was well, how do you identify them, right? Well, we're identifying them by what comes out in the master, the db2 master log. And so, you know, here's a handful of spaces, here's you know, okay, we've got 500 of them, and they grab all their DBAs, circle them up, and say, you take you know, these 50, you take these 50, and everybody gets 50, and they go each submit 50 recovery jobs, all concurrently, all competing for the same tape stacks, the same blog resources, and the system just comes to its knees and everything gets tied up in a big knot, and it takes days to get back. So uh what John you know went through is not unique. We see this uh you know over and over.
SPEAKER_04Yeah, and if I may add something, what I think is important and we'll mention it a few times, it's like practice and preparation, right? How often do you practice recoveries and so on? For example, recently we had a customer case, and it was not that that big as those that John mentioned, but customer was doing an UTS migration, right? So from multi-table segments to universal table spaces to PVGs, and of course the process failed. And the process was using traditional unload and load. So it unloaded the data, created a new one, dropped the original objects, created the new ones, and started to load them. But the load failed, of course. So far, so good. But by accident, because they were like in panic and they had this short window of maintenance, so accidentally they deleted these unload data sets. So the unloaded data were all gone. So now what do you do? So you have the new table spaces, the tables in it they are empty. So and you have old image copies that are for the segmented. So what do you do? So we were doing this DSN1 copy recovery. How often do you do that? So we have to do this this mapping. So, how do you know these original IDs? Fortunately, that unload process captured the internal IDs of the tables, so we don't we didn't need to look into the DSN1 prints and so on. So, but it was really like DSN1 copy recovery, and we had to to work with the customer with our level one to show them and generate the jobs for them with the internal IDs, how to convert, because what we did was we recreated the original environment, but now when we do it, the internal IDs are different, right? So that's why we need to do this this translation. And again, this this is about the practice. Uh, how do you do these things? And of course, if we have these short windows, the panic also doesn't help with you're absolutely right.
SPEAKER_01Yeah, the panic can be overwhelming. And yeah, you make more bad decisions, don't you, when when you've got that pressure. And probably a manager behind you as well saying, Is it fixed yet?
SPEAKER_04Oh yeah.
SPEAKER_01But then what are the the most basic things that people should be doing? If they've got I mean, most companies seem to have a good DR test. Well, not a good one, but they have they do a DR test. So they know the basic things that they're supposed to do with that. But from a DB2 perspective, what are the basic things that people should be doing in order to ensure that they don't have these moments of panic and you know, looking for a job that can do a recover of multiple objects at the same time? And what what should people be doing?
SPEAKER_02Well, this is John here. I mean, there has to be a plan, right? I mean, even if you haven't got all the procedures built, you've got to have a plan and an outline design for what you want to do. And the best practice, of course, would be to have done the whole cycle. So I have worked with customers who in the financial services industry who got into such serious reputa got them their reputation damaged and might even have lost their banking license and went through a project for several years whereby they started off the answer to everything was flash copy, but forget about that for the moment because it wasn't. But basically, by going through and doing a whole list of optimizations, they were able to dramatically cut down the elapsed time. So if I go back to the seven-day recovery or the five days to do it, and then nor the two days to check it, there's so much dead time in there. People are thinking. And the danger if you don't have a plan is that when you have a very large subsystem, somebody's going to say, under pressure, oh, we could save time by doing this. And often you find in some of the situations where there's not a clear cut plan and where relationships between applications are not clearly defined, people come up with these optimizations, and then what is a really bad problem really then becomes super bad. And so that's what one thing to have that plan. And the second thing that's aggravating a lot of these situations is that new applications are being put online and people are not thinking about the archiving strategy, and these operational databases are getting bigger and bigger all the time. Now, clearly, that won't aggravate the log apply part of recovery, but it will inflate this the restore phase of restoring the objects, as well as aggravating all your housekeeping in terms of re-org and so on. And then the other thing is application teams don't think about designing for the non-functional requirements. And part of that non-functional requirement is archiving, but also thinking about well, what data sets are absolutely required to bring the service back? And let me introduce this concept of bringing a service back somewhat crippled. In other words, you bring it back in such a way as to process new business and appear to be in business to your internal and external users, and this other data, which might be historical, can be recovered in the background.
SPEAKER_03So basically a minimal viable service.
SPEAKER_02Yeah. That you need to be very clear about, that you know, I mean, a more polite way of it, would be creating rings of fire, and at least one ring of fire between the the the near-term operational data that you need, history data that you need to appear to be in business to the institutions and your customers, and then this history data, which you'd like to be maybe in a different set of tables. When it's all bundled into the same set of tables, you've got to recover everything to bring it back. And if you do decide that some applications are critical and some are less critical, you've got to be absolutely cast iron clear that these applications can be brought back on their own. So any attempt at bringing back a subset needs to be very clear because otherwise, if you recover and then start, you will lead to other types of corruption. But then we get to some basic things, which I think we've Bill mentioned already and Emil as well. I mean, you need to write both copies of the archive log to real DASD, and then you can migrate them away at different times after VTS there. Don't think about using tape stacking on VTS volume serial numbers. Think about you know intelligent use of flash copy. I mean, one customer said to me, John, after his experience, he said, where's the flash left in flash copy? Because he thought, well, I'm going to modernize my system and I'm just going to replace everything with data set flash copy. Well, what did that do for you? What it meant was that the elapsed time of the restore phase goes down to zero, but it does nothing for the fast log apply. And so another thing to consider is not having a one-size-fits-all backup cycle. Some objects are very heavily updated, and you need to take more frequent image copies, maybe even consider doing incremental. So, in fact, you know, flash copy has its purpose and it should be used selectively where it helps to reduce the restore phase of a large partition table space so the log apply can start earlier. Think about it from an index viewpoint. Some of these tables are so huge. Do I really want to go through rebuilding all my indexes? Couldn't I do index recover on some of these very large ones and use dataset flash copy to reduce the store phase down? But I think if you've done the test, if you if you've gone through the plan, the design, and you've tested something, and you actually lay out where the time went, then you know, well, what could I do to solve or optimize that area? Could I change the recovery order so I recover these big ones first or these heavily updated ones first, and that way I can reduce the dead time out of the cycle. So there's nothing better than a good test to prove it. But then I think the challenge for a customer is where do you test this stuff?
SPEAKER_01I was just gonna ask that, because in in my past we didn't have a a real production like environment, and it was only when we had to do it for real that we find out how long it's gonna take. And so when people talk about recovery time objectives, you're kind of just you're clueless until you can actually do it. Unless, I mean, how how do you test when you don't have the same size objects and the same sort of activity going on in your non-production environments?
SPEAKER_02Then what you've got to do is create yourself an environment in which to test. And the challenge here is where can you find that environment where you can use the hardware and you can use the software without incurring heavy charges? So, for example, with DR, people negotiate with IBM, for example, and the vendors about doing so many DR tests a year. Well, that doesn't include mass data recovery. So I would encourage to the vendors as well as the customers here, that's the sort of thing you need to do is create an environment where you can do a large-scale test and from a vendor perspective, allow the customers the same way of testing mass data recovery as they can test disaster recovery. And that way they're going to be encouraged to test it because they don't have to pay out a lot of money in terms of software charges.
SPEAKER_01Yeah, and I think one of the things you've mentioned a few times is about the the length of that log apply phase and how it can just go on and on. And I think that's one of the things that's really hard to recreate in any non-production environment. Um, you need you need to do a lot of setup in order to try and recreate anything like what goes on in a production environment to recreate the the logging, or is there a is there a trick?
SPEAKER_02Uh not so much a trick, but you have I think um for BMC, they've got a simulation capability, and now now IBM is recommending using redirected recovery to estimate the elapsed time. But the question is when you have to do it at scale, that's the challenge. It's easy to to benchmark an individual recover, but when you want to scale this up, if you have a full-scale mass data recovery, it's actually going to put more pressure on the CPU and the I.O. subsystem than it is really going to be the main production system.
SPEAKER_01Yeah.
SPEAKER_02So I mean the the testing is the obvious thing to do, but there's some simple things that are obvious things to do, like avoid it, you know, avoiding the serialization to do it stacked image copies on a VTS volume and and avoid serialization on archive log copy log copy one. But there's lots of other things you can do, like you know, looking for you know, selective use of data set flash copy, where for a certain object the recovery time is dominated by the restore phase and not the log apply. And when it comes to log apply, there are a lot of things you can do. I and I'm making an assumption here using the IBM supplied DB2 utilities. With their fast log apply, there's only so much memory on each on each particular DB2 member. And also, if you if you even go to that limit, then it becomes more less and less efficient as you do more and more concurrent jobs. So I always say to people that you probably want to spread the jobs around the members of the data sharing group and don't run more than about 50 or 60 at a time on each of the members here and and and try to improve the throughput that way. But again, you can if you are able to test and demonstrate the capability, then you can make some really well-informed decisions that could make a really big difference. And how about separating the near the near-term history and the long-term history out into separate objects so you can bring the system back to process new business and allow this historical data be to be in a second tier and recovered later?
SPEAKER_03And archiving, as you said earlier, John, uh archiving data that's um old age um as as part of your application design is important that would speed up recovery of the objects that are crucial.
SPEAKER_02It would make in some cases, it would make an absolutely huge difference. And also, if you were able to identify a ring of fire between those that you could bring back, the most critical for the business, and bring those back, then you might only have to recover a relatively small, at least it would at least be a subset, and it might be a quite small subset of the overall data. If you were in a confident position to do that, that would make a radic a radical difference.
SPEAKER_03Bill, Emil, any additional uh hints and tips?
SPEAKER_04Yeah, I I was taking some notes and I I wanted to add something. So, first thing is to have a plan. John mentioned that you need to have a plan, and that's a like that's that's a good start. You need to have some plan, but also you need to to review and revisit your plans after some time. Recently, we we had some customer case, and they were using the old snapshot technique to build the the image copies, and then they they had a process to create the sequential image copies so that they they can recover from them eventually. So it was like a process that did many things, and one of the pieces failed in the middle, so they were not able to create image copies. So, and it was like it was a high priority case, so we met with them every day, and the clock was ticking because they had some policy to have image copies like for some time, right? So uh so and the problem was so so at the end we fixed that problem, but after the uh the case was closed, I was thinking their process is not optimal, right? Because they could replace this entire thing by taking flash copies, which can do the sequential copy at the same time. So, but how do you like like don't touch it, right? It's working for years, so this is the plan you have. Don't touch it. But I was thinking, well, I I think there is a potential to optimize the existing process and make it easier and faster also. So that's one thing. So review the plans.
SPEAKER_01The other thing sorry, just to add to that, Emil, that um you might have some static um static schedules built to to recover a group of objects or a particular application. If you're adding to that application, maybe not very often, do you remember always to add to your your recovery um schedule as well to include those new objects or do they get forgotten about? Carry on, sorry.
SPEAKER_04Uh so my other point was about applications, because one of the things you were recommending to our customers and to the community was start with your applications and make sure that you like group them based on their like priorities. Like this set of applications needs to be recovered as soon as possible, and at the bottom you have some applications that are not that important. But apparently, this is a very complex topic because DVAs don't often even know what the applications are because they just see some tables, they don't even know what the tables contain. So I know there are some tools that can bring draw you topologies and so on, but this is really complex thing how to how to build these groups of the applications. So, but if you start writing them, you already know. But if if you don't, if you just write and write, then it's too late because it's difficult to like see all the connections and so on. So that that's so but if you start from beginning and you start designing and and see, okay, this is my high priority application, and this is something small, but it needs to go with with the design basically later. I spoke with one DBA, and because we have some component that can help you to group these objects based on the applications, but they he told us, like, okay, this is nice, but how do I know? Because I see tables, it's like table 256. I have no idea what it is. I don't know what the data are in just some some numbers, I have no idea, so I don't know if this is important or not. So and that's basically also the communication between DBAs and uh developers, right? If they are disconnected completely, then it's very challenging.
SPEAKER_01Yeah, that's that is a real problem. And I think it's probably more common than we'd like it to be to have that disconnect. So essentially we need a plan, but in order to get that plan, we've got to do some research into what's going on, how it's happening, what's happening, and then we start to figure out things about our image copy strategy as well. So it's not just about recovery, we've also got to take care of the image copy because without that, the recovery well, it'd be different, won't it, depending on what we've got. Uh there are there any recommendations for what people should be looking at for their for their image copy strategy?
SPEAKER_02Well, so uh you to just pick up on that on that point there, I mean the image copy cycle is important, but not all objects are updated at the same sort of rate. So, for example, you know, volume level flash copy, I have to say, from an operational viewpoint, is so simple. People some people may disagree with you, but once it's set up, you know, volume level flash copy is operationally simple and robust. But the problem is it's a it's a one-speed highway. And so what I've actually found out in practice, it's not free and it puts a huge load on the I.O. subsystem, for example, because you know, today with customer, you've got a satisfy fast synchronous I.O. response times, you've got to have Metro Mirror and um and global mirror to replicate the data, and now you've got the flash copy on there as well. So whilst flash copy is free from a software charge viewpoint, it's it's not free from the DASD controller viewpoint. So I think one of the things you need to look at is look at the, you know, in terms of the image copies and how you do them, I think one thing you need to do is look at the update rates per object. Because driven by those update rates, that may r drive you down the road of having more frequent image copies for those objects and using incremental image copy. On the other hand, if you've got the if you've got another sort of scoring table based on the size of the objects, these are the ones you might decide to use data set level flash copy. Keep the same frequency, but use a data set level flash copy so that when you come to do the recover, you've driven the the cot the elapsed time of the restore phase down to zero, and the log apply can start sooner. So that's in terms of the frequency, that's the two variables. One is the size of the objects, and the other one is the update frequency.
SPEAKER_00I'll just jump in and say I agree with all that. I think a lot of that is driven by just just the the testing and and testing at scale, right? And you'd mentioned that, John. Uh the the key in in my mind is not just testing at scale, but being able to repeat it on a more frequent basis, right? If you can you know run those tests and run those tests, but you only run them once a year, it's very difficult to find those things that you're talking about, make those tuning changes, and then come back six months or a year later and figure out how much difference have I really made? Have I made any progress? Is the data grown and you know we're about the same place, but but you know what's what's next? And being able to iterate on that uh on an ongoing basis in in weeks rather than months or years makes that so much easier to to find those those bottlenecks. And it's it's you know, your processors get saturated from index builds, so you need index copies. It's it's log apply is slow, or or you know, flash copy, uh like you said, it's not free. We've seen folks who've actually measured, you know, at some point you get so many flash copies going in the system that that the the IO subsystem just you know uh it it it doesn't park, but it slows down significantly. So so it's this a balancing act, right? And you've got to be able to run those tests repeatedly and and make small tuning changes and see you know uh what difference it makes and then move on to the next thing to be able to really get to where you want to be uh in any reasonable amount of time.
SPEAKER_02Yeah, I agree. I agree, Bill. You've got to iterate around it because every time somebody does it, they're frightened by how long it takes. And so the first thing to do is do you need to build out a timeline here by object and by phases of the utility. Where is my time going? I mean, just reordering the recovery, the the way you recover the objects can make a really big difference to the schedule. Don't just do it in alphabetical order, for example. I mean, there's that. Then there's replacing some, you know, with a with a large partition table space, you can't rebuild the index until you've actually recovered the partitions. But with an index recover, you could recover an MPI in parallel, even in fact in the same job as the partitions.
SPEAKER_00Yeah. And it's not always obvious where those bottlenecks are. You know, we've we've seen where, okay, we've got flash copy, image copies, we'll snap the thing back. But then you go to apply log, and the log apply is inexplicably slow. Well, it's because now we've got to wait for all that replication to physically occur in the hardware backend before we can actually apply the log records. So um things like that, they're not obvious until you're in there and actually running through the process and going, oh, well, that's really interesting. Why is that happening?
SPEAKER_02Another thing, actually, Julia, on your business on the point about the image copy cycles, is how long are people going to keep these image copies for? Now I'm working with a client at the moment who should remain nameless, but they're only keeping image copies for three days. Well, if that's the case, it doesn't really matter, it could be f you know, others might only have five or ten days. But the point is, my first war story I shared with you is where it took three days to get to the root of the problem and to trigger the right sum of recovery. That customer, if they had their retention period was that time, would get into trouble. So I tend to say to people, think about 30 days. Why? It's unlikely, it's most unlikely you're ever going to do a point-in-time recovery starting that far back. But some types of recoveries are fixed-forward where you want to go back and identify the missing data, and you need the objects from that far back to drive your log analysis tools to generate the redo SQL, for example. So, and also you may decide to take those image copies and create a forensic environment to lay those image copies down as well. So a lot of people are bit by bit driven by money, reducing on the reducing down on the retention period for the recovery assets, both the image copies and the logs. And then imagine you open an instant with IBM about data corruption, and if you don't make it a CEV one, the clock will drift, won't it? Yeah. And then there's a danger then that the recovery assets will have cycled away, and then you can't do that. So that's another thing that is to make sure you keep those recovery assets for a long period of time.
SPEAKER_03Is it sensible, therefore, to keep your archive logs at the same expiration date or the same going as far back as your image copies? So that in case you have to do a point-in-time recovery and fix forward, maybe even redirected recovery and fix forward to find the where the uh the error occurred, or even, God forbid, a ransomware attack occurred, then at least your logs, your archive logs and your image copies are consistent.
SPEAKER_02They should be consistent, Marcus. I mean, if you were trying to save money, what you could decide to do is that archive log copy two after a certain period of time, you may decide to not keep them as long. I mean, when we're when the IBM recover runs, then it's it's always going to go to archive log copy one unless log copy one is unavailable. But given the reliability these days of VTS and also the um the DASD subsystem, you could make one compromise there, which is not to keep log copy archive log copy two for so long, but certainly archive log copy one needs to be kept for the same duration as the image copies.
SPEAKER_01And what about securing these things to the cloud, the archive logs and the image copies? Is is that a is that a good solution if you're trying to save money?
SPEAKER_02It might be a good solution for saving money, but the problem is if you have one of these events and you have to do a mass recovery at scale, you might have to get a lot of this data back very quickly. And getting data back from the cloud, it's not going to be at the speed of light, is it? So if you've got if you take some of this older data, older image copies and older logs, you may decide to push them out there. But you don't want to be too aggressive about it because then if you are faced with a recovery scenario like the first case study I described and that Bill talked about, you don't want to be pulling that back from the cloud.
SPEAKER_01That's true. Yeah. So I guess if if you want to keep it all your image copies for 30 days and the archive logs as well, maybe after two weeks they could be migrated to the cloud.
SPEAKER_02Yes, because that way you at that stage, you know, mass data recovery, it's probably most unlikely. You'll you'll have known about it by this stage. But you but but but where you would want them for is you know for audit purposes or to do to do with a case you've opened with IBM, but around potential data integrity, or if you want to do some form of advanced data recovery, you're not going after a bit massive recovery at that stage to do it.
SPEAKER_01No, it'd be game over, wouldn't it?
unknownYeah.
SPEAKER_02I'm actually itching here for Marcus to start talking about a cyber attack. Yeah.
SPEAKER_04But I wanted to mention maybe you want to keep your image copies off-site somewhere else, right? So that if something happens, like this cyber attack. I can give you two examples of the cyber attack actually recently, because uh I don't know if you have heard, but in in Slovakia, the Slovak uh land registry, there was a ransomware attack on this. So currently it's like two, two, three weeks, it's not available, they are not able to access, so you cannot sell the house or whatever because it's not working. So these cyber attacks are really very very hard. So, how do you are you prepared for that? How do you know if the data are corrupted? And that's a problem because it's a logical problem. It's not like that you have a disk crash or something, it's really some data were corrupted at some point, and how do you know when?
SPEAKER_01And so how do you how do you start to start to unpick that?
SPEAKER_03Yeah, well, you have to go back to the point where the ransomware attack occurred, and to do that, you have to analyze behavioral data and data outside db2. So you have to you have probably have to use some kind of AI tool that analyzes traffic, analyzes behavior, and works out okay, something unusual happened at this point in time, and that's where your investigations uh would have to begin. But the first thing you must do is just uh do a clean shutdown of db2, flush your log buffers, flush your buffer pools, externalise your pages, and that's it. Lock it down, and then bring DB2 up in a a different LPA that's not connected to the uh the rest of the world, and then start your recoveries once you've worked out where you're recovering from.
SPEAKER_02Well, Marcus, I I just want to say here, I have for the record that I haven't got a criminal background. But if I really but if but if I wanted a cause for the nightmare scenario, right? Well, I all I need to do is to get control of the system, like APF authorized or whatever to do this, right? And then all I need to do is incrementally encrypt the data that I took that's touched in the system with my key. And that's it's just like that first scenario I described today. It's not instant. It would have progressively gone on for many hours or days before the scale of the problem happened. Or I sent you the email saying, please send so much Bitcoin to my account. So, you know, the thing is the solution to Joure now being offered, you know, fully by IBM and the other DASD vendors is using uh Um, I think from an IBM viewpoint, it's called safeguarded copy services. And you know what this is doing, I mean, for those of you who may not know what safeguarded type solution is, it's where it's a it's a thin provisioning DASD solution that basically takes a point in time copy of of your data. It's policy-driven, right? So you might do it every hour, every few hours. You would take this point-in-time copy, and it's just the changes, so it's thin provisioning, right? And you'd keep them for so long. And so the question is how how long do you have to keep them for? You know, for for the sort of tack I'm talking about. And then if you don't know what's caused it, right, it's not you know it's not for ransom, it's simply because somebody's trying to cause serious trouble here, which one of those copies is good? So then if you've got all these safeguarded copies, you're gonna have to spend time bringing these back, thinking what it might be, and bringing these things back, and then seeing whether the corruption's there. And remember that copy, each of those copies, point-in-time copies, includes the logs. So if this wherever this happens to be, whether it be near term or longer term, there, can I go beyond that point with the log apply? So a lot of people right now are feeling too way too comfortable about the safeguarded type solutions. I have no doubt they work, but the thing is, when the incident happens, which one of these is good? And can I use logs on the follow-on copies to use? Because if not, I'm going to lose a lot of data, aren't I? And then from a business perspective, forget about a technical perspective, from a business perspective, how are you going to compensate for the lost transaction and batch workload? So I think a lot of people are still assuming that it's like a DR event, it'll be a bomb, a fire, whatever. It happens immediately, and the amount of data loss is going to be tiny. And it doesn't have to be that way. It could be the slow, the thing that starts small and then escalates until you find the full scale of the damage. And you may have to go back a long point in time to a copy that is actually good. So I mean that's a bit depressing, really, but I mean it's a dose of reality here. It's not just about buying a technical solution, which is obviously the best thing any of our customers would like to do. Can I buy a solution from a vendor that will do this? And the answer is it's not just as simple as that. Again, it comes down to processes and procedures. So imagine if you haven't prepared a recovery LPA where you can take a tertiary copy of this safeguarded copy and IPL off it and check it. If you have to do this without a plan again, it's lost time and dead time, isn't it? To get the recovery LPA or to steal an LPAR from somebody else. And you know the clock is just going to keep ticking on this. And so the scale of the impact from a business perspective will be much bigger.
SPEAKER_00Well, very difficult to choose to push that button, too, isn't it? Because it is an all-or-nothing thing and it's it's data loss. So I think folks, you know, your inclination is, well, I'm just going to recover the stuff that's encrypted or that's that's been attacked, right? And you'll start out with this this you know, forward recovery of of just the bits that I think that I really need. And eventually it'll grow and grow to the point where either that's not an option or you've you've you know the the pain has gotten to the point where you have no other choice. But it's it tends to be a last choice, last ditch effort to go, okay, throw it all away and go back a day or two days or whatever it is, because that is such a such a big data loss, right? Nobody wants to start there.
SPEAKER_02And so you're absolutely right, but that's why I said 99% of cases the right solution is to um fix forward. And that's where it's important to know what the scale of the damage is. So, for example, I've seen uh it's another war story, but this is really a one about availability is that um it was all the trigger point was in fact the the DV2 ran out of active log space and pages went on the LPL and G Rep. And the operator or the the systems team decided to do the the start database commands to recover the LPLs and the GREPPs. They also failed. Why? Because they didn't fix the core problem of get providing some more active log space, because they would those recoveries have to be logged. So then they reach for the rebuild index and they do it on an SAP system in a banking system, and they hit the payment item table, and seven hours later the system comes back because nothing can get done if you can access the payment item. So that scale of that damage could have been really tiny. You could have postponed the rebuild index for a quieter period to avoid the business impact. So it's really important here to know what checks you can have in place, such as check index in the case of DV2 or the vendor equivalent, or if there are application checks that can be done from an application perspective that can be canned and ready to go to understand the scale of the damage. Because then you'll quickly make a well-informed decision that's fixed forward, which is typically going to be the case, but certainly in a financial institution, there's so much reputational damage caused by a point in time recovery, people really don't want to do it. But at least make it a well-informed decision, and it may determine the timing of your recovery action that you don't need to do it immediately. You can postpone it later in away from peak periods where it's still a problem to be fixed, but you can live you can limp along with it the way it is until that quiet period.
SPEAKER_01Emma, have you got something you want to add?
SPEAKER_04Yeah, yeah, I wanted to add something because the ransomware is really how do you recover from that? So I think the most important thing in ransomware case is the prevention. Make sure it never happens actually. So I know it's hard, but uh but there are like some ways. So we have some articles at IDAC also on the websites, like what like a checklist, what should you do, like what to check, how to how to secure your DB2. Uh so there are some known techniques, ways, how eventually someone can hack into your system. So make sure this is all closed, maybe do some pen testing, things like that. So this is really very important. Uh because then as John mentioned, even if you have some vendor solution, how do you know? Like you need to a lot of time, you need to go to this copy, validate, it's all good, go to the older copy, and so on. And I wanted to give you a quick example what happened to my Alma Matter. So it's not DB2 related, but they were also under ransomware attack uh last year. And what happened? So they had some shared server, some shared disk, and uh ransomware software. What was what it was doing? So it went from top to bottom, from A to Z. So it started with all users which started with A. So then it was encrypting all their data, then it went to B. But fortunately, there was someone uh with the name started with with P who had so so many data that the ransomware crashed actually. So the user with A and B lost the data, but the rest was safe. So maybe if you have someone who has a lot of data at the beginning of the alphabet, maybe you are safe.
SPEAKER_03But you know it's just a stroke of luck.
SPEAKER_04Uh you know, but they were lucky in this unlucky situation that it affected just some some some users, not all.
SPEAKER_03One of the important points of recovery from ransomware is an immutable copy stored somewhere, preferably off-site and possibly in the cloud. And that would be your your first recovery point. Um, I know some companies have strategies of doing disk level recoveries, so uh disk level copies, sorry, and putting those up into the cloud uh and make them read-only immutable. And in the event of a ransomware, they just um recover from the disk image and then use a tool to roll forward through the logs and apply log updates up to the point of um recovery. Uh, and it's also important to make sure that your archive logs um have been copied uh to uh off-site or up to the cloud as soon as they're released. Um, just to make your your window of unrecoverability as small as possible. But there's always that the the important thing is prevention, as you said, Emil, and uh identification. So have I been attacked, or have I just been uh the victim of uh an accidental drop table space by the storage administrator at 10 o'clock in the morning on a Monday, which has actually happened. But I think the key points that we we can uh uh take from this would be to plan, uh to practice uh and prepare, get everything ready, get your jobs ready, your catalog and directory image copies uh ready, so that you don't have to wake somebody up to find out where they are kept, but they're accessible and you've got schedules ready and waiting to run, and to repeat. I mean, it's okay going to the gym once, uh, and it's not very nice going to the gym again. But if you go to the gym regularly, then you become more confident in your use of the gym. And the same thing with uh with recoveries. Uh the more confident you are is only is is a function of how much how often you prepare and how often you actually perform a recovery.
SPEAKER_01I've got a question about the planning. I know we've talked about it a lot, but if we've got people listening that are relatively new but would like to help make a difference and an improvement in their in their team, where is that is there any kind of template or examples of a recovery plan? Is that is there any document or checklist that they can look at to give them some ideas of of the things that they need to be considering and the things they need to find out and the things they need to plan for?
SPEAKER_02Well, I think the two best references I've seen in the public domain are two presentations that were given at iDug. One was given by Thomas Baumann from Swiss Mobile Air, and that was just a general discussion about speed of recovery, and that includes everything that Marcus has just replayed from this talk. And the second one was a talk from uh Bjarni Nielsen from UBAT in Switzerland, who actually had a real incident where they were in a lot of reputational damage and risk of uh losing banking license, and he tells the story about how they did the planning, the testing, and the iterated around it optimizing it and managed to get the time down absolutely dramatically. And as part of that, he talks about just about everything we've mentioned, plus more, of optimizations that can be done.
SPEAKER_01Great. Okay, I'll try and include those links when we publish this then, and that I think that'll be very helpful to people.
SPEAKER_02Yeah. I mean, a lot of people think if they buy a recovery wizard type tool, and by the way, I may try to make that vendor neutral.
SPEAKER_01Very good.
SPEAKER_02A recovery wizard tool, that that that's it. I can build my JCL and I can build the JCL for that object, and it'll be the most efficient way of recovering that object, guaranteed. But then the tricky part is well, you can also build a profile to do the mass data recovery and store it in there. Well, somebody has to build the plan, which goes back to your question. Yeah, and then you have to start thinking about which applic can I can do I have to bring everything back or can I just bring a critical subset back and to be confident about that and then then evolve the plan bit by bit, building the plan as that with that as the starting point.
SPEAKER_01Yeah, that's a really good point. And lots of people will have tools that will be able to help them, but you still need to do that that pre-rec work um in order to get the best out of those tools.
SPEAKER_02That's right, yeah.
SPEAKER_03Yeah, when I was a systems programmer back in the day, I think with DB2 version 4, I was so paranoid of recovery failing that I tagged a DSM1 copy check step after the image copy step of every image copy, just to make sure that the image copy that we were taking was recoverable from. And we did we actually did production recovery exercises once a year. Uh, logged on to production, started a low impact transaction, and then cancelled the RLM and then recovered from image copy and rolled forward. And the more often we did it, the more confident we were that we would be able to recover in uh an actual disaster.
SPEAKER_02So, another point I think I'd like to make is getting your recovery assets also over to your DR site. I have come across a number of customers, for example, who may not ship the image copies over there, may not ship both copies to the archive log over there. And a lot of people are cheating on their disaster recovery test because what they're doing, I mean, that there's two parts of a DR test. One is doing a site swap, which is a controlled shutdown. That's all it is. It's testing a shutdown and start, clean start. Or the other one is they're going to basically split the mirror or something and do the test then. But again, they're doing it during a quiet period. Well, again, if you have a real crash in a busy period, there are going to be a lot of exception conditions generated that the restart will not cover. And some of those, for example, require recovering the object. So you need to have those image copies and those archive logs over at the DR site. I mean, an example is can be, and it's unique to a 32k size page, that you can end up with what's called a torn page. Because in fact, it's two with a 32k page, it's the one page size in DV2, which can straddle a track the boundary. And therefore it's possible that half the half it gets there and half it doesn't to the DR in a real event. So the recovery action is to recover the object using image copy and roll forward. So that's just one just one trivial example. So there are these exception conditions, and because of the way that people test their DR, they're not testing to find problems, they're testing to get a successful test and a check mark. And in reality, a number of customers are exposed because they don't get the recovery assets over to the DR side. And therefore, if they really do exception conditions, then they could find themselves with someone some unrecoverable data, not all of it, just some of it being unrecoverable.
SPEAKER_04Yeah, the verification part is important. It's also often overlooked because I remember there were some cases where the copy job was running fine, the copy was created, but at the point of recovery, the recovery was complaining this is not a usable copy. And then how you publish it with the copy.
SPEAKER_01So yeah, you need to have a step for verifying in there as well, don't you?
SPEAKER_04But by the way, the DSN1 copy check wouldn't help in this case because it checks just some parity. In this case, I remember we had some image copy that was that had a broken chain in the space map page. So some pages were just left out, so they were not like used in in the recovery. So we just recovered a fraction of the data.
SPEAKER_01So any uh any final thoughts from you, Bill?
SPEAKER_00No, I mean practice, practice, practice. Right. That's that's what we like to see folks do, and that's uh, you know, we we we worked a lot with the folks at UBS uh mentioning Bjarna. I mean, they that's that's exactly what they did, repeat the test over and over again until they got it. I think their their time frame was somewhere in the two to four hour range to get just massive amounts of data back. And the only way you do that is keep, you know, repeat, lather, rinse, repeat, right? Do it, do it again, do it again, and keep tuning it until you get it where you need it to be, so that when it actually happens, you've got the stuff sitting there that you need to run uh to get back, and you don't have to you know think about it for for you know half a day and and then fight fires along the way.
SPEAKER_01Yeah. Yeah, absolutely.
SPEAKER_03Well, this has been a really interesting and fun fireside chat.
SPEAKER_01Horrifying.
SPEAKER_03Quite horrifying. I'm just gonna sit down and in a quiet dark corner and rock back and forth. But uh I think all remains for me to do is to thank our guests, John Campbell, Bill Potroff, and Emil Kottich, and wish you all uh a great day and thanks very much for listening.
SPEAKER_01Yep, thank you all.
SPEAKER_02Thank you, Marcus and Julia. Thank you.
SPEAKER_04Thank you all.
SPEAKER_01Visit iDog.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.