IBM i: The Tables Have Turned
IBM i application architect Sven Jansson on SQL partition tables and the exciting new possibilities for organizing data
This transcript is edited for clarity.
Charlie Guarino: Hi everybody, this is Charlie Guarino. Welcome to another edition of TechTalk SMB. In today’s meeting, I’m very happy to be sitting with a senior application architect and IBM i consultant, and also a speaker in the IBM i community, Sven Jansson. Sven, it’s so great to be with you here today.
Sven Jansson: Thank you, Charlie.
Charlie: I forgot to mention just in our little bio introduction already, you’ve been doing this for over 30 years already—or almost 30 years, I should say.
Sven: Almost 30 years. Started in ’95.
Charlie: So you’re hardly a newbie to the community, but more recently you’ve been speaking on the circuit, which is how we kind of met. We first met about 10 years ago or so in Sweden.
Sven: Yep, that’s true.
Charlie: And it was great and I’m proud to call you a friend and we’ve been talking about all kinds of cool things over the last couple of years, but what really caught my attention on some things you’re talking about now are SQL partition tables. I know you’re doing sessions on that and I thought it was worthy of a nice conversation because I know so many people don’t really have a good grasp on what these actually are. So let me just start our conversation with that. What is an SQL partition table? What are they? How do they work and maybe some of the quick benefits of it?
Sven: Alright. So SQL partition tables on IBM i came I think with Release 7.1, I ain’t sure, but around there, I think it was 7.1 or maybe 7.2. But since 7.5 partition tables are free—before 7.5 IBM would charge you a little bit money for it. What it is, it’s a way to get your tables to be big. Before partition tables we all know that the limit for physical files or SQL tables are 1.7 TB of data and 4 something billion rows, whatever comes first. So IBM released partition tables to be able to store more data than that in one table. So that’s what partition tables are all about. So how they do that: in physical files, we have had members for long, long, long, long time and you can have a lot of members in a physical file, but you can never have more than in total 1.7 TB of data. With partition tables, you get the opportunity to actually have members in your table and each and every member have the maximum limit of 1.7 TB of data. So partitions—we’ll not say members, but that’s what they are. We’ll say partitions. So each and every partition can have 1.7 TB of data and you can have up to 256 partitions in one table. So mathematics as the 300-400 TB of data, if you maximize that. And I can say where I have used it, I haven’t been up to that much, but I can say that the performance is not changed, which is kind of amazing. So yeah, that’s what partition tables are.
Charlie: Let me ask you a question. The numbers you’re mentioning to me, even in just one table, are pretty staggering. 1.7 TB is a ton of data—or 4 billion rows. I mean just to have one member, one table is a lot of data, and you’re saying that you have applications or you’ve seen applications in place in production that have requirements with 300 TB of data?
Sven: Not maybe 300 TB of data, but where I used it was when I was working for one of the larger Swedish banks with their credit card applications. And a credit card transaction is—I mean, when you go to a bar or the store and you buy something and you pay with your card, it’s not just your card number, the amount and where you’ve been. It’s much more data into a card transaction than that. Then even if you’re not a big bank or a big financial institute, if you have 300,000 credit cards or maybe 500,000 credit cards—which will, I guess, in U.S. measurements will be very low—that gives you a chunk of data, especially if you have people that are using its cards like maybe not daily, but at least weekly. I mean every time you go buy something, you use your card. You go to a grocery store to buy food or you’re in a restaurant or in a bar or maybe in the cab or at the airport, you use your card. So that is a lot of data, and 1.7 TB of data when you are using credit card information—you think that’s a lot of data, it is, but it runs out fast. It runs out very fast. So at this bank, what we did before partition table was that the requirements from the business was that they want to see 18 months of data. So the latest 18 months of data, that’s what they want to see when they go into their 5250 or whatever GUI they’re using. And that grows fast. And so what we did before partition table was that we divided this into two physical files. So we had one physical file with about three months of data and we had one fiscal file with 18 months of data, and we combined these. So we had to do a lot of work and every time we had to roll the data, we had to do a lot of manual work and all things around that. So what we did was when we were looking to partition tables, we started with a new table and to see how it worked in production. If we said that if this doesn’t work, it’s okay, we can recover it. We have a little job to do, but that’s okay. We can recover everything. But it works perfectly. And so yeah, that’s how we did it. So then we moved over to partition tables… It’s amazing how it works, actually.
Charlie: I guess you’re right. Thinking about it, it is not that difficult with a high-volume business to quickly have 4 billion rows. I mean it sounds like a lot, but if you have millions of credit cards out there each doing one transaction a day, it doesn’t take long to hit 4 billion over a certain period. And obviously it’s a lot more than that, but the concept of using multiple tables is not new. Certainly this problem has existed well before partition tables have become available. So you had to change an application to handle multiple individual tables. How is using SQL partition tables, how might that affect an application? If I’m going to convert an application from going from traditional multiple tables to using partition tables, how does that affect the application?
Sven: In this case it was—I would not say easy, that’s not the right word—but it was not that complicated. It was about 11 physical files. All this credit card information was in 11 different physical files. And when partition tables is at its best is when you have data that over time gets colder and colder. You need to read the data and see the data, but you might not want to change the data frequently. So the newer the data is, the more changes you do. But when the data grows old, you do less and less and maybe no changes at all. When it comes to the credit card industry transactions that are older, if I remember correctly, it’s six months. You are not allowed to change them. Say that you see on your bank statement and you say, oh, I have not been to John F. Kennedy [airport] and purchased this coffee. I was in Los Angeles at the time. Well then you have a chance to correct that. But once that transaction is, I think it is six months, then you’re not allowed to do that. It is too old. So credit card information is perfect for partition tables because the older it gets, the less changes you do, but you still want to have the information available. So we had our transactions divided into two key columns. We call them batch numbers and batch sequence numbers. So in a batch you have a lot of transactions in the sequence number, and that’s an old story, but it comes from the times when you had a paper, a credit card, and you had this machine and you had a little paper in there with carbon in between it and those big chunks of machines. Today you have this card reader, but in the old days you didn’t have that and people were typing in these transactions when they came in. So that’s how it is in there for this application. So what we did was that first of all, we had most of our physical files, at least these 11, they were encapsulated in database APIs or service programs, which made life a lot easier because partition tables will not work on physical files. It only works on tables. So we sat down and we took these batch numbers and batch sequence numbers and we said, how much data is a month? Oh it’s 500 GB. Oh, okay. So we said that each and every month is a partition. So that’s how you tell the database that I want to have my partitions set up with this column, and the column starts within this number and ends in another number. So say that you have an ID. So you tell SQL that in this table I want to have partitions, and my first partition starts with ID number 1 and goes to ID number 100.
Charlie: So one of the things I’m hearing from you is that you can’t just create these partition tables without any thought going into it. There’s no magic number to say create these and you’re on your way. You have to put some thought into it in advance to how you’re actually going to create these partitions.
Sven: You have to engineer this stuff. So you have a plan, but once you have created these partitions and the range values, so to speak, in each and every partition, then it works automatically. So that’s what we did. We had these physical files, we created tables instead, we added in the partitions and then we copied in the data from the physical files to the tables and it just worked. Easily said, but it just worked. But from a performance point of view, if you are reading the table with your key columns, that is also your partition column. So you can only have one. The database knows that, okay, I’m going to look for ID 45. I know it’s in this partition, I don’t have to look in the others, it’s in here. So if you have 200 partitions and, say, 100 GB of data in each and every one, it doesn’t matter because it will look in that partition.
Charlie: But if I had different partitions, let’s say by date, which might be a traditional separator—we have different partitions per month, for example. But if I was doing a big select statement—for example an SQL and I needed to read across states—I think the beauty of these is that I can read or it will read automatically across the partitions without me having to worry about any of the underlying plumbing. It does it all for me.
Sven: It’s true. In the old days when we had physical files and members, we had to do an override database file to say I want to read data for this member. And today we have in SQL, aliases, that’s true. In partition tables, you don’t have to care about that. You do your select statement and it will look through all your partitions if it’s not the key column of course, but if it’s whatever, ZIP Code or whatever you’re searching for in that table, it’ll look through all the partitions. You don’t have to care about that. So to think about though is your index strategy… So you have to think a little bit around that. So you need to have indexes so you don’t do it table space scan or whatever. So that’s true. You don’t have to think about it. It works through all the partitions.
Charlie: But I would think with Db2 being as efficient as it is, I would imagine that the index advisor could be very helpful. If you’re doing a lot of queries, I would imagine that you’re getting some support from the system itself.
Sven: Yep, you do. So that’s a good way to use that one.
Charlie: So if you’re consistently searching or selecting certain rows or things like that—ranges—it might recommend an index to further improve performance instead of having to keep creating temporary indexes.
Sven: Yeah. And if your SQL is running not frequently, very seldom, maybe you should think about doing things differently. I mean if that SQL you’re running is say once a year, maybe you don’t need that index for just that time. Maybe you need to say that, okay, I can do this in different aliases instead, because you can still use aliases. Say okay, I just want to read from this partition. So that’s even possible. So you can mix and match both of them, but if it’s an everyday thing, you don’t have to think about which partition the data is in. You just run your query.
Charlie: How about the names themselves of the partitions? Can you choose a name or choose some kind of global format that it just keeps like a journal, it just keeps adding, incrementing some value on it?
Sven: You can have your own naming standard, whatever you have for tables today or programs or whatever… You have to name your partitions. There is no automatic thing from Db2 for i. So you have to name them. And it’s the same naming standards as for tables.
Charlie: Let me go back a little bit. You were talking about different batch IDs. For example in your credit card example, you’re capturing every single transaction and you have—I’m just making some simple numbers. A batch number 1 to 100 goes in the first partition and then 101 to 200, and so on and so forth. But what happens if the nature of my data changes and now I find that for some reason of just pure happenstance, batch numbers 200 through 299 are not that big and I want to combine later on 200 through 500. Do I have the ability to do that to merge partitions or once it’s set in stone, it’s set in stone?
Sven: You can. It’s not done automatically. You have to do manual work, but you can do it. You have the opportunity to move partitions out of your table without losing data. Say you have three partitions: 200, 300, 400. You want to make that to one partition. So what I will do is move out those and put them in another table, create the new partition and just copy the data back. That’s what I would do.
Charlie: And then remove the original first three.
Sven: Yeah, exactly, because they are already removed. You attach and detach.
Charlie: Oh, I got you right. Just like a receiver. Just like a journal receiver.
Sven: Yeah, telnet.
Charlie: Yeah, same idea. But the onus, the responsibility is on us to do that. The system is not going to do that for us.
Sven: No. And also say that you have partition 101-200 and then you come with the ID or batch number or whatever we have as an example, that’s 300, then you cannot insert that. It will say, no, no, no, no, no, this partition does not exist. Also, you don’t have to know which partition is which. You just insert the data. As long as the partitions are there, the ranges are there, then you don’t have to know which partition the data ends up in and you just do your insert or update—or delete if you want to remove data. It will do that automatically for you.
Charlie: That’s a wonderful thing to not have to worry about.
Sven: Like in the old days. Still back to older i database files or aliases. So we don’t have to do that.
Charlie: That alone is a good reason for people to look at this if they have this situation where they have large amounts of data. But you know what, the more I’m thinking of this, the way you’re describing it to me, I don’t think you necessarily need to have the order of 4 billion rows before you start considering this. There’s no reason why you couldn’t do this with a much smaller table, just for the convenience of having different members.
Sven: That’s how we started when I did this. We took this small—there was a new feature that we added in, and we know this table won’t be growing this big, but it was part of that area and the application. And so we said, okay, let’s start here and see how it works. So yeah, that’s how we started. And from there it was a success story… I talked to a colleague just a couple of weeks ago. I don’t have the numbers, but they’re close to 10 billion rows in one of the biggest tables. And performance is even better than it was before.
Charlie: Wow.
Sven: Not much, but a little bit. He said it works. It works perfectly.
Charlie: So you need to have some volume, but you don’t need to have these staggering amounts of volumes to consider this as an option.
Sven: No, no. I will say start if you’re interested in this and you want to learn more, but you want to be in a production environment because that’s where all things happen. Start with a small table and see how it works. Think it through how you want to do stuff. Do the engineering, do the math, do the pen and paper. So yeah, for me it was an eye opener when I heard this the first time. I was like, okay, we can use that over here or we can use that over here.
Charlie: And you did mention now with IBM i 7.5, this is a free option to everybody.
Sven: Yeah. If I remember correctly, it’s option 27. So even though you are on 7.5 or are about to install or upgrade to 7.5, you have to order—it’s called Db2 Multisystem. And if I remember correctly, it’s option 27. So you have to order that from IBM, but from 7.5 it’s free.
Charlie: That’s amazing. So there’s no reason why we shouldn’t be using this more.
Sven: No. And the good thing is that if, I don’t know why anybody would like to do that, but if for some reason you are still declaring your files or tables in the F specification, you can use the indexes as logical files if you want to and this will work perfectly in those. I mean for SETLL, read equal chain and all that, the only thing to think about here if you are doing that is that the indexes need to be added with a word or a verb saying not partitioned, otherwise you will get the first partition in that index when you do a declare F. So if you want to have all the partitions within that index, using it as a logical file add, not partition to it. It doesn’t make any difference if you’re just running only SQL, but if you are using the native stuff. So that’s something to take with you.
Charlie: Something to take with you. Right, exactly.
Sven: Oh, but say that I have 100 TB of data in my table. What about backups? Here’s the good thing: I guess most are using BRMS. All the shops that I’ve been to are using BRMS. You can say to BRMS that you only want to backup changed data. That goes for the partitions, too. If the partition is not changed, the partition itself or the data within the partition, BRMS will not back it up.
Charlie: So you mentioned backup/recovery, that’s a big thing, but another topic that always comes up in database, or another concern, is security.
Sven: It does, all the time.
Charlie: And are there any security features or techniques or anything else like that that we need to be concerned about that are over and above a traditional table? Things like triggers or journaling?
Sven: When you use these, just use them as regular tables. I have not used a trigger in here, but I cannot say it won’t work. If there’s a need for it, use it.
Charlie: Encryption?
Sven: Encryption, yeah, or RCAC or any of this stuff. I guess the more you add onto it, just thinking out loud now because I have not used it, but I guess the more you add to around a partition table, RCAC or anything else, I’m guessing performance will be a little bit to think about, but it depends on your application. I mean if your application is like most, then I will not consider it. But if you really are in a performance tied application, then give it some thoughts, but I can’t say it won’t work. And it’s the same with security.
Charlie: Are there any new native SQL services that might help with production tables?
Sven: No. There are a few index views in SQL. Like CIS columns versus tables—no CIS indexes, where there is information about the partitions within the table, like the ranges of how the partitions are divided into the table. But other than that, no. I guess there are third party tools out there that might come in handy, but other than that, no.
Charlie: So this is certainly an interesting topic. I think it really will make people reconsider how data is being stored on their disk—how data is being managed, I should say. Data management, things like that.
Sven: Another thing when we’re at that subject and backups, when you change a partition or add a new partition to a table, BRMS is looking for the change date on the partitions. So if you add a partition or change a partition, the change date will change on all your partitions in that table. That’s something to consider when you are adding new partitions. That’s how we started. Every fourth Saturday in the month or something like that we had a little RPG program that closed the partition that was using and we added a new one. But one Saturday night, the BRMS job took a little bit more time than usual, let’s say that. So everybody was like, what’s going on? And so we discovered that, oh, this change date is changed every time you are changing or adding a new partition on all the partitions. So we had to reconsider how we did this because we didn’t want the backup to take longer. It’s just something for everybody that’s listening to have in mind.
Charlie: What else comes to my mind is AI. And how I’m viewing AI in this conversation is just the large amounts of data. Because when you start having many billions of rows of data… I can have enormous amounts, billions and billions and billions of rows, and no person could possibly do that on their own. But AI can go right through that number and identify patterns, things like that. So this is a perfect way to compliment AI into one of your applications. It’s an interesting discussion point, I think.
Sven: Yeah, I think this will be having an impact on using AI, like anything else that we are using for AI.
Charlie: Yeah, for sure. This is such an interesting topic to me. It’s making me rethink possibilities.
Sven: It is a lot of possibilities.
Charlie: We’re not constrained. And you’re absolutely right—4 billion seems like a lot of rows, but as we said earlier, we don’t necessarily need to even be in that high of a transaction. If we have the requirement to split our data up, we have this tool now—and again, this is not a new tool—but we have this ability now to do this and have the system maintained for us automatically. That’s really the key here, I think, is that it’s being maintained by the system for us.
Sven: And I’ve heard that there are people on the platform that have used partition tables in another way. They know their data, but as I said, it’s a performance gain, so if they know what partitions they’re having, they can address the different partitions depending on what data that comes in. Some IBM customer did that—so instead of having it horizontally, they did it in a different way and they said that the performance was amazingly better. And still, you have to think about that. You have one table that has a lot of data instead of those small tables. So that does give a little bit of a perspective.
Charlie: It’s fascinating to me. Yeah, database can be a very fun topic when you have all these great tools.
Sven: It is fun.
Charlie: Yeah, I agree. Alright, well listen, this has been a wonderful experience, and eye opening for me. I don’t have personally a lot of experience with these, but I can assure you that it’s something that I’m going to be playing with after this conversation because it’s something that’s worth a look. And anybody listening to this podcast, it’s really worth your time to explore all this and see how it can fit into your individual shops. I guess my final question is, if I’m looking to entertain the idea of using this, where do I go? How do I learn? Where can I find information about this? What resources might you be able to recommend where I can go to learn more about this great topic?
Sven: Well, there are different websites out there, and of course come to different conferences. There are probably topics around this, I would guess. I will be at COMMON this year, at POWERUp. So if you’re there and interested, please come to my session. I have a session on this, but I will have a demo on this too. So it’s not just talk.
Charlie: How exciting. That’s amazing.
Sven: It is.
Charlie: Sven, it’s always a delight to see you. I see you in so many different corners of the world every year. It’s such a pleasure to see you, and thank you so much for all you do and for all your speaking that you do. And yeah, what else can I say? Thank you, thank you, thank you. Really a pleasure.
Sven: Well, thank you, Charlie, for all the things that you’re doing.
Charlie: Sven, we’ll see you down the road. Thank you very much for your help and your time today on this great topic of SQL partition tables. It’s really an exciting topic. For those of you who listen to our podcast, make sure you listen for other topics we have coming out down the road. Certainly we have a vast library of recorded podcasts that you should explore. Always great topics, many great speakers, great experts, industry experts, things like that. Really worth your time. Thank you for joining us, everybody. Bye now.