IDUG Db2 Table Talk
IDUG Db2 Table Talk
Db2 for z/OS Housekeeping: A Panel Discussion
Use Left/Right to seek, Home/End to jump to start or end. Hold shift to jump forward or backward.
It's not exactly glamorous, but if you skip housekeeping, it will catch up to you sooner or later. Julia and Marcus sit down with three of the sharpest minds in the business – Mark Falconer, Chad Reiber, and Michal Bialecki – to discuss the latest technology insights, their own real-world experiences, and recommendations to help prevent inefficiencies and outages.
This one gets technical fast, with deep discussion on the care and feeding that keeps your system healthy long after go-live. If you manage (or depend on) a Db2 for z/OS environment, this panel is required listening.
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_05And I'm Julia Carter, and we represent the iDug Content Committee. We hope you enjoy today's edition.
SPEAKER_04Thank you for tuning in to this edition of iDug's DB2 Table Talk, our regular podcast. Today we have a special episode. I'd like to welcome Mark Falconer, who's an expert DB2 user from NatWest Bank in the UK. Hi, Mark. And Michael Biwetsky, who's a world-renowned DB2 expert from Poland, who's currently working for Broadcom in Czechia. And uh a colleague of mine, Chad Rebe, who's a DB2 utilities expert, uh, works for BMC in the UK.
SPEAKER_00USA, not UK, but that's okay. Maybe I'll be moving. Oh, great to be here, Marcus. Thanks for having me in the USA.
SPEAKER_05Great, let's get started then. So when we are talking about DB2 housekeeping, Chad, what exactly does that cover? Because it feels like a big topic.
SPEAKER_00It is a big topic, and it really uh depends on where you are in the DB2 uh infrastructure, right? If you're a system programmer, you might have different housekeeping than you know a DBA. But if we want to look at DBAs, you know, we got I'm gonna say three different responsibilities. One is recovery, one is administration, and um the last one is performance. So when we think about our DB2 databases and our housekeeping, uh it can fall into those three different categories. So recovery, right? How do we make sure that we're uh recoverable? Well, we need to take image copies. So if we didn't have to take image copies, just think how many MIPS we would save on our machine. Um but we do have to take image copies. And you know, depending on availability of the applications and you know how we plan on doing recovery, uh, that is a big ticket item for recovery. Uh so those copies. And then for performance, you know, why do we uh rely on performance, right? The optimizer uses statistics. So housekeeping is making sure those statistics are uh up to snuff and um more current than um maybe we want to run run stats. And then the other thing is reorganization of our table spaces, right? Um a lot a lot of times, and I'm sure Mark will bring up hey, we want to design our databases that we don't have to run re-orgs, right? Because reorgs can be it could be an outage, uh, it could take a lot of CPU, a lot of zip processing. Um, but eventually sometimes we need to do reorganizations. So from a DBA perspective, I I would think those are our highlights as far as DB2 housekeeping. On the system side, they're responsible for the catalog. So they have reorgs, they have backups, and they have the same things that we have to worry about as DBAs.
SPEAKER_05Yeah, good point.
SPEAKER_04So, Mark, do you think that there are activities that you would do as a DBA that you would consider to be housekeeping that Chad hasn't covered already?
SPEAKER_01Of course you've got you got mod modify in there as well as as well as copy reorg. So modify, be part of your uh housekeeping schedule to keep your catalogue in a decent state. Uh NatWest, how we have implemented housekeeping generally has has gone on an application by application basis, and over the many, many years, so we have a lot of application-specific housekeeping, although they are generally all standardized to follow the same processes. Um we do have quite strict housekeeping standards that we would implement all new housekeeping to be the same as. So we would tend to always, you know, we'd take at least daily image copies, we would occasionally some objects we would re-org on a daily basis as well. Most of the time, our re-orgs we would drive through RTS statistics. So we have some fairly complicated unload syntax that we basically unload objects that meet certain criteria for re-org, and only if they meet that criteria do they get re-orged. So we would put in a re-org typically at the database level, and underneath that we would only then re-org the objects that would meet the criteria within that database. Um, so not every object. So, like for example, reference tables, they would not they wouldn't get re-orged at all, generally speaking. However, we do also have a separate sort of suite of catch-all housekeeping that makes sure that every object in our estate will get a re-org at least once a year. Now, the reason the reason we did that is because you know, underlying disk volumes change. So sometimes, for example, our storage, our storage teams they want to migrate underlying vSAM datasets off one volume and onto another volume. So instead of us having to constantly have to do that on demand, we have this catch-all reorg schedule that runs that just chung chums through all our objects, and over the course of a year, they will generally always then get moved to the new underlying disk structure. That's just like that's just something we've we've we've put in place.
SPEAKER_05That's interesting. That's one of the kind of operational things that we might not consider, uh, but it's something that you have to deal with. And I guess your storage team would say, We're making this change, and you say, Great, we'll have those moved within a year, and they have to accept that.
SPEAKER_01Yeah, and generally speaking, that is uh that's that's acceptable to them. So that's actually happening at the moment. They've might they've they've got a whole new set of disk subsystem in, they want to move old volumes from one set of disks gradually over to another, and we said, Oh, we've already got move that's already in place, and that'll happen within a year. So we don't have to go through the whole process of raising changes and all the um and all the governance that goes along with that. So it's it's one of the things that's there in place for us. Um we do something similar um with rebinds, so we have a rebind schedule that runs as well. So basically all our production estate will get a rebind at least at the current function level of DB2. We haven't moved on our Apple Compact levels for one reason or another. They're still most of our estate is still sitting back at V10R1 for Apple Compact, but every package in our estate will get a bind at the latest function level of DB2. Now we don't um do anything with access paths, we don't allow it to um change access paths for these. These are purely in place just so we pick up the latest control structures to the latest function level on an in our package estate. I actually did a quick check before came on the the before it came on the chat, and um yeah, we don't have any packages that were bound pre-v12, so I think we're all we're all set to go for v next.
SPEAKER_04I like it, well done.
SPEAKER_01Yeah.
SPEAKER_04Also a relief, I should, I should imagine.
SPEAKER_01Oh absolutely, because the I mean when when when we went to V11 and everything had to have been bound at V10 to go to V11, that was uh that was a massive undertaking for us because that was before that's before APV use was available. So we had to we raised hundreds of changes where we pre-checked access paths before, then checked access paths afterwards. We had multiple what we'd call Cat1 change records that we had to go to various boards to get sign off on for them, and it and it was a it was two, three years worth of work. Whereas now we don't even have to don't even think about it anymore because because of of AP reuse, which means you don't access paths stay the same, so we don't have to do that. But yeah, we it saved hundreds and hundreds of hours of work.
SPEAKER_05So do you set you set your parameters to not rebound if the access path will change?
SPEAKER_01Yeah, so correct. So it's AP AP reuse error we set to.
SPEAKER_05Okay, and then what do you do? Manually figure out what to do with those afterwards?
SPEAKER_01Yeah. Yes. Yeah. To be honest, though, we don't we don't really get many. Um that's good. In fact, I d I don't remember one actually failing. We've all it's just managed to run over time.
SPEAKER_05That's really good. And what about your your object space analysis? Because with a a big bank like Nat West, you must have some massive objects, and I guess some particular tables grow quite rapidly. Uh, and maybe they weren't designed to grow rapidly when they were first put in.
SPEAKER_01Yeah, so one of we've got us, we've got what we call daily checks, but they're really a set of automated checks that run every morning at eight o'clock, and we've we check for the size uh of of all objects, and basically we alert if it gets to 85% of its maximum size. So there's some fairly complicated chain checking in there because you know you have to work out is it a is it a partition by growth table space, how many puts how many partitions can it grow to, how many are you in for range partition table spaces? You obviously you check for the DS size, and then you check how close you have to that. We alert on any table space that partition by growth that would be that would have just gone into its final partition. And for individual partitions, we would check for when it gets to be 85%, and then we know we we know we need to do something.
SPEAKER_05Right.
SPEAKER_01I think I I think only once have we ever been caught out where the it's alerted at 85% and then it's broken the same day. That's only ever happened once, I think. And that was a very and up and that was quite a small table. Um but fortunately uh yeah, it didn't cause too much of a problem. But um generally speaking, 85% we've found that's the kind of sweet spot for us to give us enough time to then plan for any mediation work we need to do.
SPEAKER_05Yeah.
SPEAKER_01So we do that. We also have automated checks for any objects that have somehow lost their image copy. So there's no current image copy for it. So if our checks would go around, go around the holy state, and if it finds one, it will automatically take an image copy and then it will raise an incident so we're aware that this has happened, and then we can investigate why it doesn't have an image copy. That usually happens where we have had some decommissioning uh activity happening where they have where a set of housekeeping's been decommissioned, but the table hasn't been decommissioned and it's just sat there.
SPEAKER_04Right.
SPEAKER_01Generally speaking, these are not um you know operational tables by that point, but we that's that's by and large the most time we see that happening.
SPEAKER_05Oh, but it will it will automatically create the the incident record when you get that alert.
SPEAKER_01Yeah, it will take the copy first and then it will raise an incident so we can then investigate why it's why it's got into that state to begin with.
SPEAKER_05That's that's good intelligence and automation.
SPEAKER_01Yeah, other things we do is so we've got we've part of those daily checks as well, we'd regularly check that uh where we have enabled uh pervasive encryption. We would do checks to make sure that the keys are still valid. Well, obviously we'd know if they weren't valid, uh we'd think we'd um have all sorts of things going on, but the alert is to when the keys are about to come up for renewal, um, so we can then plan that and schedule that for in a timely manner. Also for our DB2 certificates, and also we got checks for um Apple Compat incompatibilities as well. So we're highlighting those and gathering all that information and highlighting them as well.
SPEAKER_05It sounds like something you'll have built over years though, with probably many things going wrong along the way.
SPEAKER_01Yeah, usually when that that partition has blown. Um but that hasn't happened for that hasn't happened for a while now.
SPEAKER_00I'm currently working with a customer that had um a similar issue where they ran out of space on uh an object and it took them hours to actually bring it back. So working with them to implement something like Mark, where you know if it gets to 80 or 85 percent of that, you know, hitting the limit, you know, that you have that time to go in there and and actually fix it, whatever it means, right? Whether it's delete records, whether it's rebalance or whatever. Um, but you know, you know, Mark's automated that, but there are customers out there and probably listening to this, uh, that you know, if you put in a process, you know, when you hit the limit, you know, DV2 just can't add anything else. So it's it's very important.
SPEAKER_01I've always found it quite surprising that IBM have never supplied such a metric to say your table is as approaching its maximum size, so you need to take action. I've always been surprised that's never been a feature. It seems a bit it seems a fairly basic one.
SPEAKER_04That's where vendors come in.
SPEAKER_01Yeah.
SPEAKER_04So Michal, uh you used to work in the SWOT team for IBM in DB2. Have you seen any scary housekeeping implementations or problems in the wild?
SPEAKER_02Uh yes, I think I was exposed to every spectrum of the customer scenarios that you can imagine or you cannot imagine that exist. Starting from the guys who don't have the rebind strategy. If the rebind option never would exist, they would use it, for example, right? Just as Mark was saying, you know, access path protection, right? They don't want access path to be changed. So every forced access path change was with you know with a fear, for example. If we consider, of course, rewind as a part of the housekeeping. And same about re-org, right? I mean, I know the customers who are reorganizing everything on the weekend, every table, regardless, does it need reorganization or not? Or some of the customers who are not reorganizing enough, meaning that the table is so degraded that they will get bad access path because the index cluster ratio is less than 80, and then you don't take this index into consideration. So I would say Mark has the intelligent way to select the candidates, and I think this is the the way that housekeeping should be done. So we have some rules, you have the triggering points when you select the candidates, when you run your reorgs to fit into your maintenance windows if you have maintenance windows, or you run through the through the week. So this is the crucial part. Another crucial part is to big objects, right? How do you reorganize the big objects? How do you manage to, for example, move to the UTS? There are some customers who are having millions of objects to migrate to UTS. And based on the what Akon says in the some webcast, version 14 will be the one that will enforce you to use only UTS. And you if you have two million tables to migrate, that basically means that till version 14 is out, every day you need to reorganize about 1000 tables. 1000 every day, including weekends. So those are the challenges that customers have. I mean, either they overrun the housekeeping or they don't have the no maintenance windows, or they have too many, or they basically have too few housekeeping tests.
SPEAKER_05What can you say about that?
SPEAKER_02Well, I think that's for the reason that you know people don't want to think they want to what to do with the mass rebrands, right? I was one is a DBA where I was told every week we do the rebut. Every weekend, right? Every maintenance apply we do the rebutly. Because we didn't have time to analyze every package if this reband is really needed. So I would say this is done on purpose to save time, to save the thinking time. Maybe it's better to overdo something than you know spend time thinking, right?
SPEAKER_00I think there's a legacy of you know DBAs, right? So whether it's you know what we pick to re-org, you know, they go back to whatever, version four, version five. This is how we did it back then, and no one wants to touch that, right? Especially uh what I see with my customers, you know, maybe they're outsourced, or maybe you know, new, new in career DBAs, you know, they're still getting their feet wet or they don't want to make any changes. So uh, like Mikhail's saying, you know, what's in place, they just leave it, right? Because the fear of changing it could be, you know, that much, like, what if it breaks? What am I gonna do? You know, I made that change. But, you know, DB2 has changed, right? I mean, you know, do we care about extents like we used to care about extents? Probably not, but that might be something that triggers a re-org today, right? Versus, you know, maybe being a little bit smarter and saying, well, are these queries really getting worse? Is the performance getting worse? Right. So I I think I think DB2 has grown up and you know, uh a lot of what we did back, you know, 10, 15 years ago has changed. So um I think we need to change too. So it it doesn't hurt you to go back and look at what's in place and maybe make some changes.
SPEAKER_05Yeah, that's a very valid point. The fear of change and the I guess the headache of it, because every change that customers need to make or users need to make has to go through a long process. And if it's not broken, it's hard to justify. But I guess the the real thing is that you're you're potentially wasting CPU by running unnecessary jobs, but the the risk of changing it is maybe something that they'll they're willing to just keep going as they are. But yeah, maybe not forever. Things should change at some point.
SPEAKER_02Yeah, I think that also some of the things are coming out of misunderstanding as well. So I recall one customer who was not running image copies at all on the rear re-read-only tables. Because they thought it's a read-only table, so why should I take image copy?
SPEAKER_05Oh dear.
SPEAKER_02And what do you do when you know when the dust is broken and what? It was read-only, you have no way to recover. Yeah, it was read-only. I don't need image copies. Okay.
SPEAKER_01Good luck. I'm surprised uh an auditor hasn't objected to that one.
SPEAKER_05Yeah, yeah, I think they pick up on it now, wouldn't they?
SPEAKER_02You know, that there's no updates. We don't need image copies. Okay. Great.
SPEAKER_05Yeah, there's a lot of misconceptions, I suppose. That hopefully with more education we can we can fix. Now let's talk about the the best practices. Michal, can you can you talk to us about re-org and run stats and potentially rebind briefly? Like what should people be doing and how should they be doing it?
SPEAKER_02And the most important one is the re-org, because it organizes the data the way that it should be by the clustering index, and it gives you the possibility to get the best access path combined with the run stats. So what is important is that whenever you run the re-org, run the inline statistics. Whenever you run the re-org of the table space or the table, run the inline statistics or run it after, but then you are wasting CPU time. Uh so and I'm very opponent for the customers or the users who are running their run stats between the re-orgs, for the reason that you are losing the island of stability that you get when you're on the re-org. Your data is perfectly organized. And then if you run the run stats like a few days later, when the updates are already done, then you may not be getting the best access path. But is what is more dangerous is that you may be getting the good access path by accident. And then you know, a week later, your manager will ask you, can we go back to this access path that you had last Wednesday? And you say, Oh, well, the data has changed, I really cannot get back to it. So re-org plus Ranstat at the same time gives you this island of stability that you can always go back to, or basically try to get back to, right? Another point is about the rebinds. There is no point in Rastats when you don't rebind. So if you if your rule is not rebinded at all, or rebind every, I don't know, D B2 release, then maybe there is no point to the Rustart, except for the dynamics QLs, of course. So I'd say this all things together needs to be combined. In the in this the smart way. And the way to do it, as Mark said, they are using RTS, some queries on RTS, or maybe some vendor tools, or maybe DSNA COX provided store procedure. To get the candidates for the reorg for the run stats for the copy. Yeah. So this is the proper way. Another m good point is whenever you run the re-org on index, don't run it with the run stats. Because it may cause conflicting statistics between the table and the index. The statistics for the index. So the John Scample rule is run on the table with inline statistics. Re-org on the index without statistics. So that's my take.
unknownThanks.
SPEAKER_04You mentioned earlier, Michau, about some customers having a rebind never policy. Since version eleven, there have been uh changes to rebind with uh you know, you can rebind to a previous copy or you can run um you can test your access path without uh actually changing anything. So what's available to uh DBAs now when it comes to rebinding to protect their um precious access paths?
SPEAKER_02Yeah, Mark already mentioned the AP reuse, AP compare, AP reuse warning, AP reuse error when you don't allow any access path changes, or AP compare, the same warning and error. So whenever there is access path regression, you will be get the warning or well access path change, right? From the other side we also also have plan management, which preserves the original and previous copy. So whenever you will get access path regression, you can do the switch, rewind switch. So you you will get some good protection nowadays, I would say. Except for the fact for the dynamic SQL, which we which we don't, unless we count plan stability as well. But in plan stability, you can go back to the previous access path. Unfortunately, you can just lock the one that you have but don't go back. So nowadays definitely we have more possibilities to lock or execute with the same good access path that we did. But from the other side, if you are locking the access path, we may not be also benefiting from the new functions or new access paths that are brought through the release or maintenance PDFs. Because we are using AP reuse, tell the optimizer can you reuse this statement, access path that he it has before? But at the same time, we are not allowing those new benefits that are coming through maintenance stream to be exploited. So I would say if you want to be safe, sure, run with AP reuse. But if you want to be up to the edge of the of the newest access path, you should maybe test it somewhere on clone system. See maybe the access path has dramatically changed. So now in version 13, there is no many access path changes coming from the from the version 12. But I think that in the previous releases there were many, many enhancements that you just do the rebind, you get the new better access path, and your CPU drops by, I don't know, 20%, 10% of the execution of this particular SQL. So you can be safe and you can use more CPU, or you can test and maybe be more efficient.
SPEAKER_05Could you do AP compare with explain only so that you just find out whether if you were to rebind now it would be better or worse, and then use that to actually decide whether you rebind to make an improvement or stick with the existing access path?
SPEAKER_02Yes. You can do the explain only with any option.
SPEAKER_01I guess the problem there becomes for complex queries, what is better? How do you know how do you know a certain access path is better? And that's that's the problem because with all you know we can be very skilled at analysing access paths, but who's to say that who's to say that one type of join is better than the other until you actually see it run? And most times we just don't have representative volumes in a test environment to be absolutely certain of that. So at NAT West we tend to be risk averse, as you've no doubt um gathered by what I said earlier. So it just it just becomes a case of unless you actually unless you have a good reason to want a different access path, then we we just avoid the risk and leave things as they are, unfortunately. That's just the way it is. In terms of our in terms of our risk appetite.
SPEAKER_05Yeah.
SPEAKER_02Yeah, that's true. Mark and and you also only can prove it by by the runtime. I mean that's that's absolutely correct. And with the representative set of data. I think the rebind is also important from the maintenance perspective of applying PTFs, because there are some PDFs that are you know fixing something, for example, incorrect output. And if you don't rebind, then you have the problem still, right? So we apply it, we have that fixing something, but still you need to rebind. So rebind is also important from this perspective. So rebind never strategy is never good, I would say.
SPEAKER_04Amen. So Chad, we've heard that backups are important if you have a recovery scenario. Uh because if you don't take a backup for, for example, read-only tables and your dusty gets a hit, then you've got nothing to recover from. So what kind of best practices do you know of uh with regard to backup and recovery? For example, redirected recovery for for testing image copies, that kind of thing. Uh full image copies versus incremental merge copy, um, stuff like that.
SPEAKER_00Yeah, I I guess to that point, Marcus, I would say you know, one size doesn't fit all. So you know, even within you know, uh a company, you know, or DB2, you know, each application might have its own requirements around recovery, right? Whatever that recovery point objective or recovery time, you know, how long it's gonna take to do that recovery. Um I I always say working with a customer, but um just recently working with a customer on you know, like a cyber attack or a ransomware attack, how how am I gonna recover to that? And you know, they had a plan. We're we're gonna take these 15-minute backups of every space that we have out there. Okay, that's great, but then you know, you're as old as 15 minutes. What if you know a transaction happened uh five minutes ago and you can't recover that because you don't have the log? So um you do have to plan you know what type of recovery and what your requirements are for that recovery, and then back into your image copies and how you're gonna do that. Um, you know, pointing out some image copy capabilities that you just talked about. Do we take full copies? Do we take incremental copies? I go back uh a ways in DB2, and you know, there was a point, you know, no one took incremental copies, you know, it became very religious whether you liked incrementals or not liked incrementals. And that there are places for them, right? Um, you know, especially if you want uh fast recovery, right? Having more recovery resources and apply less log will get your your objects back that much quicker. So um I'm not anti-incremental copies, uh, but I go to people today and they just take full copies. And and even in our development environment, I mean our development teams, they might push full copies because you know Dazzy is very fast these days, right? And you can get a lot of IO done very quickly. So why the headache of incremental copies when you could just take a full? So um it all depends. And you know, to your point, how fast do we need to do that recovery? And instead of say in the past where we would just guess, well, you know, you know, it's got a million you know, data pages, you know, we can do that recovery in an hour or 30 minutes. You know, we have tools, whether from Broadcom or Rocket or BMC, that can do estimation or simulation of recovery. You know, IBM recently gave us the redirected recovery. So we can actually take that most critical table space, you know, the client uh table space, and we can actually do a recovery to a different object and see how long that would take. And if it takes two hours and two hours is too long, how do we speed that up? Do we take more image copies? Um do we take incremental copies to limit the amount of log if that's the issue, right? The log apply is taking too long. So um back to where I started, I don't think one size fits all. Um, but I think what you're trying to accomplish with your recovery SLA, um, whether it's recovery point or recovery time, can back you into what is your best um image copy uh strategy going forward.
SPEAKER_05That's such a good point that you have to go from that point backwards to to figure out how you're gonna copy it is based on how you would need to recover it in the world.
SPEAKER_00And something Mark brought up in the beginning was you know his eight o'clock health check or making sure you have image copies out there. Um and you know, you think, oh, no, we got that covered. But whether it's a new table space that just came in, you know, because you know, we hit that 85%. We said, oh, let's split this table out, and oh, we forgot to put an image copy in there. You know, making that, making you aware of that. So that preparation, that health check, I think is really important, you know, going forward.
SPEAKER_01So I found that um finding someone to make the decision about what your recovery point is will take a lot longer than your recovery time.
SPEAKER_00And that might not just be one point for the subsystem, right? I I mean, you know, the same customer that was taking 15-minute image copies, they're like, yeah, but we might want to bring this application back to current, but this one, you know, to one o'clock or you know, an hour ago because it has to sync up with some you know off-the-frame application, right? So to your point, Mark, it it could change, right? You you it all depends on where you are, right? And who's gonna make that decision? We could, as technicians, we could do the blocking and tackling, but who's gonna say, yeah, that's where we want to go back to, right?
SPEAKER_01Yeah, and also, I mean, this uh thankfully I've never had to face this, but it's like for a financial institution like ourselves, you can't go back an hour because billions have got out the door in an hour. So what point do you go back do you go back to? It's it's a real it's a real quandary that I hope we never have to face. But um yeah, because generally speaking, recoveries are un unless it's under very specific circumstances, it's recovered to current every time.
SPEAKER_05Yeah.
SPEAKER_04Well sometimes it's not the recovery that's the problem, it's the rebuild rebuild index afterwards.
SPEAKER_05Yeah, I was gonna ask about that.
SPEAKER_01For large objects, yeah.
SPEAKER_05Yeah, and im index image copies, which have been around since version six, which must be about 25 years, because I think version six was just going out as I was coming in. Um, but I haven't seen a massive uptake of of those. What about the rest of you? Are people using them? And if not, then what's what's stopping them? Is it surely it's not capacity, it must just be changing the process or something?
SPEAKER_01Yeah, well, recent in the last year, we have been gradually phasing in index copies to our critical applications. We've initially just taken a finger on the air stab, it's saying any object bigger than 10 gig will get its indexes copied. For small indexes, it's not really going to save you a huge, a huge amount of time. But for some of our large objects, um with numerous indexes, we are now indexed taking index copies. That's now part of our um housekeeping strategy. Yeah.
SPEAKER_00Because I've seen, you know, I've been in DR tests where we backed up indexes and then we go, you know, post-mortem. Okay, let's look at what was the longest running job. And sometimes it's the index recovery, um, where then we switched to a rebuild index and it it actually ran faster. So um I am a proponent of backing up indexes, don't get me wrong, but um testing I think is is key in that as well, because um, you know, how much log that we actually have to apply. Sometimes there's no log for an index, right? Because you know, even though we're you know we're updating the rows, you know, those columns aren't in that index, so there's really no log for that. So it it really depends on how the um the application is actually utilizing those indexes as well.
SPEAKER_01Was that was that a very specific scenario?
SPEAKER_00It was a very specific scenario for us. Okay. Um and and you know, there's a couple things with indexes, and you know, I was doing a log-only recovery with an index, and you know, db2 keeps the in the header page when this index was actually last updated. And again, it was a scenario where that index, there was no inserts into this table space. The indexes were only being updated, and because the table was being updated a lot, but the index was never being updated because it never updated the keys, so that um last used RBA in the header page went back a lot and brought back more log to be read than really needed to be read. So it was very specific. And you're like, why am I spinning through all these logs that are you know days old when it shouldn't? Well, because the way the recovery works, it looks at that that um header page and says, Oh, I need all these logs when it didn't need all those logs.
SPEAKER_02So yeah, and the recovery of the index used to be also kind of slow. I agree with chat, because uh DB2 is using this fast log apply for tables in the recovery, but a few years ago, I think maybe seven years ago or ten, it was not using this fast log apply processing for indexes. And that was just because of the overlook. So it was re-enabled when we discovered this for one customer that you know recovery of the index is so slow. It shouldn't be, because it should be using the fast log apply. So nowadays it's much, much quicker. And I like the mark approach for copying indexes, the big indexes, because their build can take ages and recovery can be can be quick for the large objects. Plus, you can recover table and the index in the same time, so you don't need to wait for table availability to the rebuild, which basically duplicate your time of the recovery, more or less.
SPEAKER_05That's much better for the for the larger objects, isn't it? And uh can we quickly talk about utility history, which is newish in db2 itself, um, and in principle it seems like a good idea and something that can provide some insights into what's happened in the past with with all the types of utilities. Chad, I know that the BMC have had utility history for the BMC utilities for a long time. How are people using that history data?
SPEAKER_00So um, good question. So, you know, occasionally for custom BMC customers, I'll go in and I'll do a you know BMC health check, quote unquote, where we'll look at you know how you're running our utilities, um, and I'll utilize that history, right? Because in that history for BMC, we not only know how what you're running, but how you're actually running it. So I could say, hey, you're using uh uh uh an inefficient way of a BMC utility. If you change it to this, you're gonna save a lapse time, CPU time, or whatever. Uh internally in our own code, we're also utilizing those utility histories uh for things like if you say, hey, I need these series of reorgs to run in two hours, well, I can look ahead and say, oh, you're gonna get to this table space and it takes an hour to reorg. So if you want to finish this in the two-hour window, it's not gonna finish. So we're gonna skip that one and do the other ones. So with IBM adding uh utility history in version 13, I could see more and more use for that. One, you could just easily see just how long your utilities are taking. Um, so if you want to do some performance on your utilities, so say you're doing an IBM load and you're like, well, could we do better with loads, right? A lot of times it's not in a DBA because a load is in an application. But as a DBA, you can say, you know, this load is taking you an hour and it's a share level non-load or something like that. Hey, you can change it to share level change, get higher availability back, or maybe you can look at the attributes that you're running with that load and make it run faster, right? So um I think it opens up a lot. So, like you said, um BMC had the history tables back 25 years ago, right? And you know, uh we can leverage a lot of that information, you know, in our code. And you know, we talked about figuring out what objects need to be reorged either through DSN ACOX or you know, Mark might have his own C list that he utilizes. Now you have another resource to look at. Like, okay, well, how long did this object take to re-org last time it did? Right, you got all that information, CPU, elapsed time, all that good data. So I think more and more, whether it's IBM or vendors like ours, will uh utilize some of this utility history to uh make the products that much better.
SPEAKER_05That sounds great. Some some excellent ideas there, thank you.
SPEAKER_02Yeah, and the the the point that Chad has mentioned about tuning into the options that you run, this is missing from uh from utility history now, IBM utility history. Because there is no syntax stored. You just know if it was re-org copy and that's it. But you don't get the syntax. So I would say there is room for enhancement.
SPEAKER_00Yeah, there is room for enhancement. It took us a while to add that in there, but um at first we BMC, not me, I didn't do anything. Um at first we we hid it from everyone, and then well, why are we hiding this? This is your data. So now we externalize it so you know customers can actually see and say, because it's helpful, right? If if you know IBM comes out with a great new parameter for copy, and you want to know what other copies you're running without that parameter, right? And so you could say, hey, let's address all these jobs that are using you know attribute A when we should change it to aperture dupe B, right? And and you know, you're just gonna save resources or time or whatever it is. So um, yeah, to your point, yeah, we need we need to see what the attributes are that you run.
SPEAKER_01Broadcom actually does does do that for you a little bit. Um it's not perfect, but the audit utility table in the insight history database, that does capture some of the parameters that you've used and some of your utilities. Doesn't capture them all, but it um it definitely does capture some of them.
SPEAKER_05Yeah. Insight now known as history for DBT.
SPEAKER_01Yes, yes, yes.
SPEAKER_05Insight for many people for forever, probably. Yeah, thank you.
SPEAKER_04So this has been a really interesting conversation. We hope our listeners find it useful. Let me just go around the table before we close and ask each one of you if people could do one thing to improve their housekeeping posture. What would you recommend?
SPEAKER_02I fully support what Mark says about AP reuse, AP compare. So this would be the one thing that I would change for the safety. Let's say. Some customers are not using it still, some customers not using plant management at all. And this is your safety net. If you get into troubles, get back to the good access pod that you have been to.
SPEAKER_04Good one. Excellent. Thank you. Chad.
SPEAKER_00Now we have a real life DBA here, and you know, that's what I do for a living, right? I talk to DBAs um all the time. I do believe there's a lot of information in the DB2 catalog, and the IBM keeps on adding more and more like the utility history out there. So you know, something that's I think important, and I I actually talk to our development people uh to put this in our products uh continuously, is some sort of uh health check that you can run every day, that you can come in in the morning, look at what that health check produced, and that can set you what you need to do this week, today, you know, whether it's something hit an 85%, you know, something's not being backed up, or we had utilities that failed and was restarted or terminated, you know, because stuff like that might not have a problem today, but it can develop into a problem. So I think there's a lot of information, especially if you use third-party vendor products, say like BMC or Broadcom, there are internal tables there uh that can actually show you what ran last night and then you know what ran well, what ran poorly, or what didn't run at all. So I would say it behooves you to do some sort of health check that can run periodically. Um that would be my my suggestion to people. And finally, Mark.
SPEAKER_01I think my main suggestion would be automate as much as you can. It'll save you a lot of a lot of trouble further down the line if you have automated procedure, if you have ways of automating all these things we've talked about, beyond any part of a batch schedule. If you can actually automate it, it will save you a lot of pain further down the line.
SPEAKER_05Absolutely.
SPEAKER_04I would have to agree with Mark, because I'm lazy. And if there's anything that I could automate and bake in that's doable, that it works once if you can do it again and again and again and automate it, uh, that is worth its weight in gold. So reports, um moving DevOps, yeah, your database changes using established DevOps principles, stuff like that. But automation, it would be but my buzzword.
SPEAKER_05Yeah, and for me, I think I would just say refresh yourself on what the utilities can do. Things may have changed since you last really looked at your housekeeping setup. So check out what's changed in the last couple of releases and what you might be able to do that will save you time, stress, energy, uh, and maybe some sleepless nights.
SPEAKER_04And on that note, I'd like to thank our guests, Chad, Mark, Michal, uh, for a very interesting discussion. And thank you all for listening. And tune in next time for iDug DB2 Table Talk.
SPEAKER_05Visit iDug.org for the latest DB Two 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.