IBMer Scott Forstie on TR Database Enhancements
Paul Tuohy:Hi everybody and welcome to another iTalk with Tuohy. Just strange it's been just over a year since I last spoke to today's guest, and it must be that time of year again. So hello, Scott Forstie.
Scott Forstie:Hi Paul.
Paul:Hi. So just for anybody who—I don't know what part of the world that you live in that you don't know this—but of course Scott is the business architect on Db2 for IBM i in Rochester and, I assume like the rest of us, locked down at the moment, Scott?
Scott:Yes. It's always blue skies and sunny in the database though.
Paul:Okay [laughs]. So of course Scott we've just had another technology refresh and I know that the database guys were really busy with this one. So—you want to give us maybe, just to start with just a quick overview of what happened with database stuff in the refresh.
Scott:Sure, sure Paul. So I would characterize this latest technology refresh update for IBM i 7.3 and 7.4 as our team overachieving. A lot of the ideas that we—we came up with, we not only were able to approach them but we were able to complete them and more. So this—if you go out there and look at those—those technology refresh enhancements, I think it's a pretty impressive list. It falls into different groupings. The first is advancing the—the topic of advanced SQL programming, what you can do with SQL, so something good for anybody in application development, of course; IBM i services, which are the very popular alternative to field commands and APIs; and then finally, new things for the database engineers, services for the database engineers.
Paul:Okay. Okay well I—I mean I've had a look at this list. I actually have it open here in front of me as we're talking and yeah, I'm not going to go through it all with you, Scott. We—I mean I don't—I think a two and a half hour iTalk is just-just pushing it a bit too far. Okay so here's what I'm going to do. I'm going to ask you the impossible, okay? So I want you to pick—
Scott:Oh boy.
Paul:To pick one, just one of the IBM services, your favorite one and talk to me about it.
Scott:Okay. Systools has a new service called Delete Old Spooled Files [DLTOLDSPLF]. This is my choice for your challenge because it comes directly from a community member who asked me to give her something tovto better manage spooled files. And by better managed, be able to easily identify and remove things that are out of scope, out of her criteria for what should be allowed to—to maintain on their IBM i. I—I built this thing in maybe an hour and ended up showing it to our Large User Group community and within minutes, I had four requests for enhancements to it. Later that evening I spent another hour changing the service set to accommodate all their good ideas. The end result of it is what's before you: a procedure you can call and pass in any number of parameters you want to use for filters, and it will go out and find those spooled files that meet that criteria and get rid of them, making that—that notion of being able to manage this—thissystems management topic—extremely simple and even automated.
Paul:Cool. So it's a funny thing on this. As you were talking right because I remembered this as you were talking right so I just clicked on the link here on the page, and sorry The technical description I'm looking at here beside them has got two big boxes. The first one says "Easy Peasy" and the second one says "Lemon Squeezy" [laughs].
Scott:Yeah, that's—that's my personality coming through, Paul.
Paul:I tell you the technospeak coming through here is just way too much.
Scott:Do you see the color of the font is yellow?
Paul:Yes [laughs]. Okay so that's an IBM i service. Okay, give me your favorite Db2 for i service.
Scott:Okay so we got a different direction but we stay in systools where you not only get the tool but you get the source behind the tool: Validate Data, Validate Data. The idea here is have a tool that you point at a member in a file or a file and we discover all members or all files within a library. This service will go row by row and validate numeric data type columns. By validate I mean it will determine whether there's an invalid instance of a—of a data type there, and it will report it back each row, each column that had that invalid data, so that those people how are not using SQL can—can be proactive in their identification of invalid data.
Paul:Okay so—so I assume that this will run on physical files, in other words something defined with DDS.
Scott:Yeah. Any database physical file member.
Paul:Cool.
Scott:Yeah and—
Paul:So—so this is—this isto allow people to be proactive against getting those decimal data errors.
Scott:Exactly. This is something you'll get forvthe protection you get for free with using SQL, but if you're not using—
Paul:Yeah.
Scott:SQL, do you have instances? Turn this service loose. It's not going to run very fast if you have a huge amount of records, but at least you'll have—have the answer once and for all.
Paul:Cool. That one is yeah. That's quite impressive. It's the—and as you say of course if you are just—you know if your table is defined with DDL, it's not an issue—
Scott:Right.
Paul:Because it's something that—that can't happen a lot of people not being aware of that but yeah, for people who are still stuck with their DDS databases—and by the way, this thing of the decimal data error, you do realize it's something I've only ever heard about because of course it's not something you would ever see with any of my code [laughs]. Sorry. It was a worth a shot. Okay, so—sothe other thing I'm going to ask you about is the functional enhancements, but this time you don't get to pick because I want you to talk—
Scott:Awww.
Paul:I want you to talk about—
Scott:Okay.
Paul:My favorite one which is the using SUBSET on EXECUTE.
Scott:Yeah, so this is a brilliant idea of course, because it came from you [laughs].
Paul:Oh okay. Thank you.
Scott:So the notion here is anybody using SQL probably has a wide variety of different scenarios where their—theirSQL statements would have different predicate values. For example: are you piecing together, and'ing and or'ing different criteria on the WHERE clause? Well sure you are, and if your code needs to handle many—many different combinations of those, prior to this enhancement you have to have unique code pass for all those variants. With Open using Subset, the subset is the—the new part of the syntax. Now you can leverage our ability for extended indicators to—to tell us whether to ignore a part of that predicate, meaning you're going to have a single open statement rather than an N number of open statements.
Paul:Yeah, I mean I've got to tell you: This is one of the—one the most beautiful enhancements that you guys have ever done within embedded SQL. I have to stay Scott—and I'm sorry it's an extremely elegant solution as well—the way that it works. By the way I do also thank you because now I can—that's about 10 minutes gone from one of my presentations about how you use SQL and how you use SQL descriptors [laughs], which was the way that you used to have to really have to go beforehand to do this—or sorry to do the equivalent of this beforehand. By the way maybe just to clarify this for people because one of the other things—and it only struck me actually yesterday when I was actually playing with this—that this is one of the reasons that a lot of people end up doing their you know structuring their dynamic SQL statements incorrectly—in other words without using markers they go back to the old way of where they put together just the string you know with the quote, quote, quotes, quotes, quotes and everything like that being concatenated in.
Scott:Right.
Paul:In other words was just horrible, horrible string handling and incredibly prone to errors: like people putting a quote in the middle of what they pass to you, which sort of screws it up and things like that when you're doing that sort of concatenation. So yeah, this is a definitely one of my favorites of what you guys have done in a long, long time, yeah.
Scott:Great. We hope a lot of people get some good advantage out of using this.
Paul: Yeah and it also means now that the steam can stop coming out of Rob Beston's ears as well [laughs]. Okay and if anybody wants an explanation of that, you've got to buy myself or Scott a beer at some stage to get it explained [laughs]. Oh dear. Okay so I'll tell you what: I'm going to nice to you, Scott. Do you want to pick one more that you want to talk about?
Scott:No, just go out and read them all. They're—most of them in both 7.3 and 7.4. There are a couple that are only in 7.4, like the compare file utility that I think will be also very popular. You can compare two database files, a subset using hash row or the entire database files using compare files.
Paul:All right. Okay. So is the next TR going to be as good?
Scott:Been working on that. Luckily I've been at home a lot lately, a lot of time to work on the next TR. There are some exciting things we're already working on, so stay tuned.
Paul:Yeah, okay well since we're talking about you being at home a lot [laughs], so—so what do you guys do when you're—because you know usually at the end I ask about oh you know where's the latest cool place that you've been you know.
Scott:Sure.
Paul:But what's there-I mean with you. I talk to you. You usually end up talking about beta, the latest exotic food market that you've been to in your travels.
Scott:Yeah, yes. That's me.
Paul:So—so what are you doing to keep sane, being stuck at home at the moment?
Scott:So yeah. I'm the house cook, and so I've been cooking a lot, and one—one of the traditions we've been using is after the dinner is done, we play three games of Boggle.
Paul:Okay.
Scott:You've heard of Boggle, right?
Paul:Yeah, yeah. I know what Boggle is, yes.
Scott:Yeah so you're trying to find as many valid word combinations in 3 minutes' time and write them down. Whoever get the most unique ones is the winner. One of the things I've discovered out of this, Paul, is that there's this—there's something called or known as a dumb word.
Paul:A dumb word. Okay.
Scott:Yeah, yeah so I usually win these games—just as a side note—but if some of my score is raised because I wrote down a word that shouldn't be a word, it's a dumb word [laughs].
Paul:And—and—and who decides whether or not it should be a word?
Scott:Well I use the Scrabble dictionary but, you know, the rest of the family I guess is the judge of whether my words are dumb [laughs].
Paul:I'm sorry. I was asking what you did for relaxation, not what do you do that could lead to family conflict that stops people from speaking to each other for two weeks [laughs]?
Scott:Oh, those could be the same thing, you know.
Paul:Yeah, that's what leads to harmony: lack of conversation. Oh dear. Okay well I think—I think that's a good note to leave this on, Scott. Thanks a million for taking the time to talk to me. One of the things by the way since you mentioned it when you said that, you know, you're at home quite a bit? I am looking forward to the next TR because I know now that you're not doing a whole lot of traveling and you have more time to work on all of these things. So I'll be expecting great things towards the end of the year.
Scott:Duly noted.
Paul:Okay, thanks Scott.
Scott:Thanks, Paul.
Paul:So everyone, that's it for this iTalk. Tune in again for the next one and bye for now.