[00:00:00] – Amanda Hendley
Well, welcome. My name is Amanda Hendley. I’m so pleased to have you here today for our virtual user group for Db2. Got a great session for you. And let’s go through my agenda for today. So our introduction to the session. Hi, you’re here for the Db2 Virtual User Group. Glad to have you. We do six of these a year. So thank you for joining. What’s fun for me right now is at Planet Mainframe, we’re doing Db2 month. And this month we also have a Db2 user group session. And last week I was at IDUG. So I may have seen a few of you there.
[00:00:40] – Amanda Hendley
For today, we’re going to have our presentation. There’ll be time for Q&A. I’ve got then just some updates in the community. Before we move too much further, I want to let you know that if you are a company that would get value out of sponsoring a virtual user group session, we do have a partnership opportunity for this series. And then I mentioned that Planet Mainframe, it is Db2 month. And what that means is this month we are dedicating content around Db2 specifically. So I want to thank Log-On Software, our sponsor of Db2 month, and then call out some of these great pieces of content.
[00:01:21] – Amanda Hendley
This is just a snapshot of some of the things that we’ve published this month. Just this week, we did Caching Query Results with Craig Mullin’s, and a new piece, I believe it posted today, called “Trust Me, You Want an IBM Db2 Analytics Accelerator for Christmas”. Those are at planetmainframe.com. And if you get our newsletter, you would have gotten our newsletter this morning with links to some of these. Happy to also drop them in chat, but it is just planetmainframe.com.
[00:02:01] – Amanda Hendley
After today’s session, there’s a quick exit survey. It’s two questions, what did you think and what are some future topics we should cover? So if you take a moment, that’d be fantastic.
[00:02:11] – Amanda Hendley
And then for today’s Q&A, we decided, I believe, that we’ll just drop them in chat throughout the presentation. I’ll be monitoring the chat. We’ll try to get them as we go through, especially if it’s something that’s going to hold up your learning. But at the end, Is it anything that is more appropriate to tackle a bigger question or a very specific use case, we’ll do that at the end. And now, I’m ready to stop my share. Got it, Erin.
[00:02:46] – Amanda Hendley
And figure out how to exit my… So I can read, introduce our speaker. Forgive me if I’m pronouncing this incorrectly, Sarb Oh, you’re muted.
[00:03:04] – Saurabh Pandey
Yes, it’s Saurabh. Yeah.
[00:03:05] – Amanda Hendley
Saurabh Pandey works as a lead product manager at BMC Software. He is an IBM champion, two years running, and has over 18 years experience in product management, people management, managing product development, quality assurance, and IBM Z and database administration. Frequent speaker, I connected with him because he did a at GS UK last year. With that, I’m going to turn it over to you and let you take over the screen share.
[00:03:37] – Saurabh Pandey
Yeah. Thanks, Amanda. Thanks for the introduction. And thanks to everyone who has joined today. Good morning to all. I think most of you are from US, but good evening to my friends from India as well. Glad to have you all here. Let me share first. Just a Can you see that sharing? Amanda, can you see that? Yes.
[00:04:21] – Amanda Hendley
It has loaded for me. You might need to hide the Zoom The screens at the top of your monitor. They’re showing black right now. For some reason, they’re still showing. Okay. Might be a button that says hide. Let me see.
[00:05:01] – Saurabh Pandey
Is it okay?
[00:05:03] – Amanda Hendley
I still see them. Does anyone else still see them?
[00:05:11] – Saurabh Pandey
Is it still visible? Should I reshare it? Yeah, it’s still visible.
[00:05:21] – Amanda Hendley
Yeah. Let me see if I can…
[00:05:25] – Saurabh Pandey
Okay. Because I removed those.
[00:05:29] – Amanda Hendley
Yeah, Hold on. I’m going to replace the current share. You all just bear with me one second. I’m just going to share a point desktop to see if I can find me. Okay, you’re going to press Control+Alt+Shift+H, or when you’ve got your control bar, you’ll click on more, and then it says, high floating meeting controls. So you can take… I’m going to stop my share. You take it back over. Yeah.
[00:05:58] – Saurabh Pandey
Okay. One second. Is it still there?
[00:06:28] – Amanda Hendley
Well, we don’t have your presentation.
[00:06:32] – Saurabh Pandey
Okay, one second. Is it there now?
[00:06:35] – Amanda Hendley
Oh, perfect. We are seeing Presenter View. So if you want to- You are seeing the Presenter View? Yes. You’ll just want to flip that.
[00:06:46] – Saurabh Pandey
Okay.
[00:06:47] – Amanda Hendley
Yeah, that or display settings might do it. There. Perfect.
[00:06:53] – Saurabh Pandey
Okay. Thank you. Let’s get started. I think we have a great gathering over here, a lot of Db2 experts. Welcome, everybody, once more. I’d like to say this is not one hour which I’m going to spend to train you on something, but discuss about a certain concept about pay splitting, why it is a problem, and give you some glimpses about how the pay splitting happens, how you can monitor it, and what are the upgrades or updates which has happened in Db2 13, a few months back or the last year, I guess. Definitely, I’m not going to train on certain topics, I’m assuming. This is more from basic concepts to the intermediate concepts about indexes and paste splitting and how to monitor, how to make sure it doesn’t happen, all that stuff. I’d love to have your inputs as well as we go ahead or at the end. Please come up with your inputs. It could be a question, it could be just a simple input from you to add on whatever we are going to talk about today. Let’s move ahead. This is a basic from IBM Redbooks.
[00:08:23] – Saurabh Pandey
Just to start with, I didn’t know where to start with, so I thought I’ll put this here. This is the basic high the hierarchy of objects. We all know where the index space goes on. So index space is the physical space which we create to make the traversing of data faster to our table, basically the table space is the physical form where we actually fetch the data from. You create all these spaces under the storage groups which has the set of volumes on that. This is the basic hierarchy, very introductory level just to get started. A bit on index.
[00:09:07] – Saurabh Pandey
We have a lot of things in index, but just to give you a glimpse, just to scratch the refresh things, something called as Unique Index. Unique Index basically makes sure that the rows in your table are unique. That’s the whole purpose. Although when you fetch some data from Unique Index, it It could be jumbled. When you look for a row in a page and you use an index row, it can take some time. Basically, it is not ordered, but The one purpose here is it makes sure the uniqueness of the rows in a table. Another very important index is the Clustering Index, which makes sure the ordering of your rows is there. Basically, it’s a form. It makes sure the data is sorted in terms of the index key values. What is the purpose behind it? Patching is very easy as you see the rows, the arrows are so aligned and they look pretty good here. It’s easier to pitch or insert or delete a row by using this cluster string index. Just to give some basic information just to get started. I know we have a group of experts who are here, so we don’t have to go through all these basics, but just to get started.
[00:10:41] – Saurabh Pandey
Why it is so important to talk about page splitting and why it happens and what is the story behind it. When you add or insert some rows in a table, it’s easier, it’s very flexible to a row in a table space. Why it is so? Because the ordering of row is a burden for an index, not for the table. When you insert a row, it knows where it has to go, it goes directly to that page. If that page is full or if the page is locked by something else, then it will look for another space, another page where it will go and be inserted. Whereas in index, the entries must always go to a very specific page based on the key value. Why it is important? Because index has to maintain the order. That is the accountability of index. It has to make sure the rows are in order in the physical format. That is the constraint which index has. Whenever you send a value, insert a value, a key into an index, it has to go there. What if the page where it has to get inserted is full? If it is full, in that case, the splitting happens. Most of the time, half of the rows goes to a different page and it creates a space for that key value and it inserts there. That’s the flexibility which tablespace has, but not the index has.
[00:12:24] – Saurabh Pandey
Index follows the Balanced tree architecture. We all know it is It is very efficient way to search rows or insert or delete rows, basically the index keys. The architecture is it starts from a root page which is at the top, then at the bottom level, you have leaf pages which are basically sorted and they contain the sorted index keys and the pointer. So the keys, basically every page will have a key and a pointer, which in Db2 terms, we call RIDs or row IDs to the actual data. And all the leaf pages are linked. And in between root page and leaf pages, you have non-leaf pages, intermediate pages. Basically, those pages direct you from the root to the actual leaf page to fetch the exact row. All leaf pages are the same depth. Yes, so the root page is at the top, leaf page is at the bottom, so the distance from root to leaf is same. You might have multiple layers of non-leaf pages in between. And keys within each page are always kept in sorted order. Every page will have keys in sorted order, either ascending or downwards order. That’s basic B3.
[00:13:54] – Saurabh Pandey
Today, I have a very small exercise here where I want to show you how the B tree gets created. I have a very trivial, small, easier example to just show you how it happens. This is not related to right now, the Db2 standards, what I’m just going through with a simplicity of a simple example here. Let’s say we have certain rules. Let’s say each leaf page can hold maximum three index entries. That is the constraint. The leaf page can have only three index entries, and each entry has a key value and the RID. Each entry has a key value and RID. For such entries, you can have three set of these. That is the maximum it can hold. All non-leaf pages can hold maximum two keys, but three RIDs or pointers. In terms of key value, the non-leaf page can have only two, but it can have an additional pointer to point to the next page to the next level. Let’s say we have this first page. It has a value 50 and the R ID or the pointer is R1. This is basically at the beginning, that is the seed. It will grow to a root as well as multiple leaf pages and multiple non-leaf pages. Now, let’s say you insert a key, 90, which has a RID R2. This will insert that row in an order. First 50 will come and then 90 will come because that’s in the ascending order. That’s how the page will because and it can hold up to three values, so we still got one more key value to go inside. Let’s insert 30. It will again put the 30 there with the RIDR 3. In sorted orders, if you see the ordering is maintained 30, 50, 90.
[00:16:09] – Saurabh Pandey
Now the page is full. Now the things gets interesting. You insert another row. When you insert another row, basically… Let me… There are option. I don’t When you insert another row, what happens? This is the original page, leaf page 1, where you see the leaf page 1. It has now got the fourth row inserted, which is 70. Now, what happens? It gets splited. Now, two pages will be created. The original page, leaf page 1, and the second new page, which is leaf page 2. The split will happen and the rows will be distributed in all the ordered way. The leaf page one will have 30 and 50, and leaf page two will have 70 and 90. If you see, the ordering is maintained. Now, another page, a new root page will be created. When a new root page will be created. It will contain a key value which is 70 and a pointer to both the pages. How it will assess, how it will point? If the values are more than 70, 70 or more than 70, it will go to the new page, which is that leaf page 2. Less than 70, it will go to the leaf page 1.
[00:17:41] – Saurabh Pandey
Pretty much sorted here. But you see how the layering got inserted in the index structure. Now you add 95. The 95 goes to leaf page 2 because it always maintains the order here. But the leaf page 2 is now full. You insert again another row, another key value which is 80. As soon as you add, the similar splitting will happen. It will redistribute the data in three pages, and two key values will be added. You see The ordering is always maintained. So 30, 50, 70, 80, 90, 95. Now the root page, a new root page, no, the same root page is there. The root page has now two pointers. One is an implicit pointer. If If the values are less than 70, it will go to leave page 2. If the values are more than 90, it will go to leave page 3. If the values are more than 70, it will go to the page 2. Others will go to page 1. Now we have two pointers and three leave pages and one root page. Again, you insert one more row. This gets complicated created. Again, the redistribution happens. Now, first time, you see a non-leaf page created in between and a new root page gets created. I’ll not go into explaining that, but you can understand. This keeps on happening and the B tree grows.
[00:19:18] – Saurabh Pandey
As you keep on adding values, you see something. This is a very popular picture I got from somewhere. This is the way you… You keep on adding rows It grows and the new non-leaf pages gets created, new leaf pages get created, then a new root page gets created. It grows. It grows like a tree. You started with a seed and then it grows like a tree, but the only thing is it goes upside down, so the root is at the top. It keeps on adding layers of non-leaf pages in between. We call it N levels. Like Now, the total number of layers here in this case are four, and there are two non-leaf pages layers in between root and leaf pages. This is how I just thought to give you a glimpse of how things happen at a generic level.
[00:20:18] – Saurabh Pandey
But we are focusing on Db2, how the splitting happens in Db2 for zOS. Similar scenarios, index The splitting happens when the insert operation needs to insert the new key value in a page which is already full, which we have already discussed about. The Db2 indexes are typically B3 architecture where you have the leaf pages at the bottom, 11.0, storing key and RIDs. As the split happens when the leaf pages are full, the splitting happens and the value values get redistributed, allocates new pages, new keys gets inserted, and non-leaf pages gets created. There are a couple of types of split which can happen. One is the symmetrical split where half of the entries gets diverted to a new page, and the asymmetrical split where the basically V2 engine detects the pattern of ascending keys or descending keys. For ascending inserts, more entries are left on the original page to accommodate upcoming keys. For the other type of inserts, more entries are moved to the new page. Basically, this is a smart way of doing the split. The goal is to bring down the frequency of upcoming split. If we can bring We can write down the future splits, if we cannot make it zero, at least make it least. That is always best. Concurrency in logging, yes. During those Structural changes. When the split happens, there is a structural change. The Db2 basically latches those pages. It acquires a P-Lock when you have a data sharing environment.
[00:22:28] – Saurabh Pandey
It latches that page. When that index page split happens, there is a log record return ever as well to document what has happened. That happens as well. These two operations basically creates a delay as well as a CPU overhead as well. We’ll discuss more about it. Non-leave page splitting. Again, if If a parent page is also full, when the lower-level pages splits, the parent page undergoes a similar split. A new branch gets allocated and the pointers are redistributed between the old and the new branch pages. This is all been on the non-leaf-based spreading. Cascading split. This spreading process can go upward through multiple levels of index trees. Root page Yes, root space split happens as it cascades up, it goes till the root page, and there is a certain time come when the root page also gets split as it goes up. So Db2 creates a new root page in that case. As the splitting happens and it keeps on adding the layers, the oral height or the number of levels of the green next tree increases, which is Typically, not that great, not good. Why it is negative? Why index space splitting are negative?
[00:24:18] – Saurabh Pandey
Index disorganization. When the splitting happens, it basically disorganizes the physical sequence of the data. It moves the entries to empty pages, and it could be pretty far from the original page, which is not good. We want in the ordered fashion and it goes there. Basically, it creates a disorganization of data as soon as the page splitting happens. We want the pages to be physically contiguous. They should be continuous. What happens because of that? The performance of index scans go down. It goes down because Db2 has to make jumps for a certain insert or a delete or a select process. It has to physically jump between pages, which creates a delay. That is the first performance drawback which we get from index space splitting. What you If you do, you can run and reorg on that index or on a tablespace. Another impact, yes. If you see the performance bottleneck, the pay split operation is serialized. Why it is serialized? Because it has to make sure the integrity of E values are there, is enforced. The operation has to be in the serial order. It cannot do it parallelly. When you make the process serial, the split has to wait for the other operation to complete without hindering the application, and that hinders the application throughput.
[00:26:26] – Saurabh Pandey
Because it is a serial operation, you cannot do it in parallel. First the operation has to complete, then the next operation will happen. The split has to wait. It creates a wait time. If it is a data sharing environment, the impact could be pretty high. Yes. So splitting a page, as we discussed, you have to latch pages, you You have to write log records, you have to… New pages you have to create to the disk or pools. So all of this processing, all of this hustle needs CPU and I/Os. It consumes a lot of CPUs and I/Os, which is a big overhead. Split, as we discussed, it causes the synchronous log rights. All these synchronous log rights are an overhead on our CPU. Latch contention. As we discussed, it holds a PLO on the affected pages. How you can see that? You can run the trace and for class 70 Hex, for the class 6, you can get all the latch contention data and report it. If you have very high split activity, the latch contention will happen. So a lot of splitting happening and everybody is running towards acquiring that lock and contentions are happening.
[00:28:16] – Saurabh Pandey
That is a negative impact, not good. Insert throughput. If the splits are happening frequently, the throughput will go down for sure. If you’re inserting into the middle of the key range, that forces many splits, which are called the non-monotonic keys. If the key which is coming is in between of the range, that is one of the major sources of creating the splits. Then long term effects is fragmentation. Repeated splits may partially fill the page, and in the 50-50 split, sometimes one of the two pages remains empty, 50% empty. It creates fragmentation over time and it degrades the performance of your DML activity. Also, these multiple half empty pages helps in increasing the levels of the index. It’s like bloating. We call it bloating. Like the bloating which sometimes we get with our stomach and we don’t feel comfortable. If the bloating happens in index, the applications will I did not feel comfortable. I’m not sure if you have anything to add from anybody or any. I just wanted to give a pause here. All right, so let’s move ahead. There There are ways to diagnose the index splits. There are multiple ways. We can run traces in our environments.
[00:30:38] – Saurabh Pandey
You have statistics traces, performance traces, many other types of traces. Prior to Db2 13, we had a performance trace by using IFCID-359, which is a performance test class 4. You can target that and extract the reports for the Pace splits. Basically, this is to monitor those splits. What was the problem with IFCID-359? Because as you see, it’s a performance trace, which is a big overhead on CPU. This used to be a big overhead on CPUs, FK359. What happens is basically it creates It generates a record for every split that occurs in a Db2 subsystem, whereas you don’t want every split to be recorded. It’s an overhead on that trace. Performance trace itself is in CPU consuming trace. On top of that, it is recording every split. If the frequently splits are happening, it’s a load on the system, on the CPU. People If you don’t activate it, it’s not activated by default. People are concerned with the CPU overheads. People didn’t use to… We’re not interested in running it all the time. Also, it was lacking some key diagnostic information. It did not include the URID of the threads causing the split, or it didn’t have the Db2 member ID name in the data sharing environment.
[00:32:32] – Saurabh Pandey
A few of the key values were not there prior to Db2. 13. Another diagnostic issue which we had before Db2. 13 was the only place where you can identify the problematic index was SYSIBM.SYSINDEXSPACESTATS the column was REORGLEAFFAR. This column from this RTS table provided a hint where was the problem, but not in a detailed way. The problem with REORGLEAFFAR was it was incremented by actions other than just space split. It is not just space split which increments the value in this column. There are other things which can also. That digresses That’s the whole point of understanding or pinpointing the pace split and connecting with this column. Also, it did not indicate how much time was being consumed. Overall, before Db2. 13, for DBAs, it was not something… It was not comfortable for our DBAs to get a full picture about the pay splits. It was very difficult to pinpoint for them to what is the root cause for that and how to take the corrective actions. The picture was not clear, although the diagnostic mechanisms were already there. IBM did something in Db2 13, and they enhanced this whole index split management. How?
[00:34:31] – Saurabh Pandey
New trace record was introduced, IFCID 396. It’s a low overhead trace class, which is a part of statistics class 3. Active by default, it’s pretty low overhead. It generates records only for the splits, which takes unusually longer time. When we say unusually longer time, which means over one second in main framework. It focuses on the most impactful splits rather than collecting data for everything. It has the UR ID in data sharing environments, also the Db2 member ID in the data sharing environment. The benefit is it makes identifying process driving long running problematic splits much more straightforward. You can identify pretty easily. There is another enhancement which IBM did in RTS, the CIS index per stats. They added in 501 function level. Actually, it was yes, in FL501. They added three new columns, reorg total splits, which basically counts all index space splits since the last reorg. The reorg split time, it is the total accumulated time spent in milliseconds processing those splits. And reorg exe splits, which is the count of exceptionally long splits more than 1 second of the number of splits indicated by above columns, which aligns with the IFCID 396 as we discussed earlier.
[00:36:30] – Saurabh Pandey
Which is great. I mean, if you compare to just one column which was fuzzy, the REORG leaf far, then having three more columns, which are pretty much pretty helpful. Now DBAs can use it. It’s very clearer here. This is just an example from the catalog, how the columns look like. If you see the bottom table, you can see the newly added columns in the bottom right, reorg total splits, reorg split time, reorg exe splits. It’s very easy to get it from the catalog. Based on this information from this IFCID and the new RTS columns, DBAs can take targeted action. What can they do? They can increase the index space size. So larger pages accommodate more entries before becoming full, bring the split frequency. It’s pretty straightforward. Increase PCTFREE. We know PCTFREE and free page are mechanisms to give some more space when we do inserts in between. You can have more free space on each index page. So that whenever a new row will come, it will get some free space already. So it doesn’t have to split to create those free space in that page. You REORF frequently, we all know. These are the default.
[00:38:33] – Saurabh Pandey
If you don’t mention zero is the default for free page. Free page is one empty page. After X number of pages, you find one empty page. By default, it is zero. PCTFREE is 10%. You can increase PCTFREE, basically. Minimize What’s the key length? If the key is pretty high, pretty big, it’s not good. Practically, you should make your keys as short as possible. Smaller key columns means you can accommodate more entries on a single page. Compress index, well, it depends on shop to shop. Although it is from page splitting or recommending more on a page perspective, this is good, but it might create some performance thing from a different angle. Buffer pool tooling, always good to do that. You see just like FTBs, the fast travel to user block which can speed up the index probes. I think I’m done with my session. These a few links which I have given. You can go and refer to those.
[00:40:05] – Saurabh Pandey
Amanda, back to you and to the group over here.
[00:40:13] – Amanda Hendley
I wanted to just take a moment. If anyone has any questions, I see a question in chat from Mike. If you want to go ahead and review it.
[00:40:27] – Saurabh Pandey
Yeah. Okay. How do I unshare it? Stop share.
[00:40:38] – Amanda Hendley
There you go.
[00:40:41] – Saurabh Pandey
Yeah. Okay. I think we’re pretty good time-wise.
[00:40:48] – Amanda Hendley
Yeah. Do you see the question?
[00:40:51] – Saurabh Pandey
Mike asked- Yeah, I see that.
[00:40:53] – Amanda Hendley
Yeah. How do those fields get initialized following the migration to B13?
[00:41:01] – Saurabh Pandey
I think it comes with function level 501. As soon as you have Db2. 13 function level 501, those IFCIDs will be available and you can use those in your traces. There’s a gray bar, lower letter. Okay, sorry for that. You can go ahead I’ll go and check the manuals, but they are initialized. They’re already there in terms of as long as you have function level 5 over for 13. No, I think compression index, it helps in accommodating more data, but in terms of performance in a different way, I don’t think so it is that good. But it’s again debatable and usage of compressed indexes is totally depending on to the shop. It’s not very as all the DBS say, it depends. But generally, it is not helpful. No, RTS gets populated by itself. By using that trace, you can You can extract the report. So RTS is different and you can run a trace report by using that to get more detailed things. Shouldn’t be Db2 base engine. Db2 base engine should be doing that. Didn’t I? I didn’t understand. What do you mean? No, I was just answering Mike’s question. So it’s not It’s not the trace.
[00:43:00] – Saurabh Pandey
It was just the Db2base engine is populate that as RDS. Exactly. Any other questions or anything you want to add on that is also good to have here? I see a lot of experts here. Open to you all.
[00:43:29] – Amanda Hendley
While we’re waiting in case there are any more residual questions, I wanted to share with you some articles and news from the user group in Planet Mainframe. Just a few articles that I thought were interesting. So we know there’s been a lot of presentations about AI lately. So if you want to scan this QR code, it’ll take you to the IBM article about Db2, 12. 1.2 and AI. Then Two of the pieces that are published on Planet Mainframe currently are in here, the Cache and Clear Results and the Analytics Accelerator articles for you to grab. Those are also at planetmainframe.com if you don’t want to bother with the scan. And then a lot of times I’ll post a job related to Db2. And I know most of our audience here are professionals that may not fall into this category, but there’s such a unique opportunity here. If you know anyone that is pursuing an undergraduate or graduate degree in computer science related to mainframe, this is Global Technology Summer Analyst role. It’s for next year, and they’re looking for someone who’s graduating in ’26 or ’27. But I thought it was just such a unique opportunity.
[00:44:54] – Amanda Hendley
I wanted to share it with you so that you could take it back and share it with any young professionals. We know it’s always important to keep developing out the next generation of mainframers out there. I’ll leave those up for just a couple of minutes. And then we also want you to connect with us. Presumably, you’re already getting the newsletters, which will give you the recaps, the videos, and the PowerPoint presentations used in our sessions. But you can also grab those at the virtualusergroups.com website. You can also get involved with us. I thought I was going to point you to any one of these. I would send you over to our LinkedIn group. It is the most engaged of our virtual user group social media sites. Would You can always grab the videos on YouTube and again, we host them as well. And with that, it doesn’t
look like there are any more lingering questions out there, but I’ll invite you to join us next month on the 15th. Joe Winchester, who you’ve probably also seen present before, is a senior technical staff member at IBM. He’s keeping it a mystery from me right now, but Joe is going to be our presenter next month, and I hope you will join us there.
[00:46:14] – Amanda Hendley
Well, I hope everyone has a great rest of the day and rest of the week, and we’ll see you soon.