Scott Forstie on Database Enhancements
Paul Tuohy: Hi everybody and welcome to another iTalk with Tuohy. Delighted to be joined today―sorry, to be rejoined today―by my good friend Scott Forstie. Hi ya, Scott.
Scott Forstie: Hi Paul, I’m doing great.
Paul: Good. So Scott of course, chief architect for Db2 on i working at IBM out of Rochester and so let’s great straight into it, Scott. We’ve just had another TR, technology refresh, announced, so and as always we have some enhancements to database in there, so do you want to maybe start off telling us about some of these enhancements?
Scott: Sure thing. So, like you said, we recently announced enhancements for IBM i 7.2 and 7.3. We’re enhancing for database both of those releases for many different roles in the operating system, whether it’s performance, application development, systems management, database engineering, database administration or even some newer technologies that need to find a path to the business. We’ve got database enhancements for all comers. Some of my colleagues and other business architects have enhancements as well, but my focus is on the database and there is a myriad of enhancements coming out here in September. In the SQL space, language of the database, we keep enhancing it, as you said, twice a year. In this go-around, there are some new built in functions and some enhanced built in functions. If you haven’t been watching us closely, the built in functions have been enhanced maybe the last four or five technology refresh cycles, so if you haven’t looked at chapter four in the SQL reference which I’m sure you’ve got that memorized.
Paul: Yup.
Scott: There’s―there’s a tremendous amount of capability that you can add into a single query. We like to think of, you use what you already own and get to final or end of job the fastest from a programming perspective, and built in functions can help the SQL programmer along that path. Some of the other things that we’ve done in the database come in that area of database engineering. We’ve found a way to generate SQL for DBL in perfect order, and you know that’s a bold statement: “perfect order.” I know that you’re familiar with generate SQL.
Paul: Yeah.
Scott: It’s available both through the Navigator interfaces, but also there’s an API and SQL procedure you can call. There’s a new procedure generate, SQL objects, which is this idea of “let the database do dependency management analysis, and give me back a script that will be in the order such that the dependent objects are always in the correct sequence.” So whether it’s views, indexes or constraints, triggers, functions, procedures, variables, we’ve got it all analyzed and now it’s consumable by the user.
Paul: Oh that is very sweet, Scott.
Scott: Yeah.
Paul: So this means if I have a schema and if I just went to the schema and said “okay give me a script that will generate that schema and everything in it,” everything will come and I can just go and change the schema name and everything will run perfectly and everything will get created in the correct sequence.
Scott: That is what exactly what we’ve envisioned as the money case. We’ve all heard that: “money case.” You know this is a big time saver.
Paul: Oh, it is indeed.
Scott: That’s what services and tools are all about, save time and work on other things. So you said sweet. All right. Another area is this thing called IBM i services, you know the ability to use SQL to manage the operating system. I think we’ve got some good traction on this topic. I hear from lots of clients both what it is doing and they are pleased that they are able to use SQL to manage the operating system, but also their ideas for future enhancements. Work management got special focus on this latest TR cycle. Active job info is the SQL alternative to work active job and we added in 80―count them, 80―new return columns. They’re tied to an optional parameter so the user or the programmer gets this: do you want a fast performance behavior of active job info? Okay, don’t ask for all 80 columns and you’ll get a quick answer. But if you want the deep details because you’re going to do more analysis or you’ve got special use cases that you want to code up, ask for the data and you’ll get it. This and many other IBM i services will get highlighted in the near future.
Paul: Yeah. Just if I can segue for a sec, Scott, since you’re just―
Scott: Sure.
Paul: Touching on the services, and I know that this thing area of the services has for you personally been a hobbyhorse―
Scott: Yes.
Paul: For a few years now. So I mean, over the last couple of years now, have you seen these services getting a lot of traction with companies?
Scott: Uh-huh. Uh-huh. I have, more and more. So you know, I’m kind of an odd duck. I do strange things for fun, like I will post to Twitter or to Linkedin an SQL query [laughs] just to see what kind of reaction I get. Okay, you know here’s the latest thing, an example of what you can do, and the feedback is really positive. What some people don’t understand is in Access Client Solutions, we’ve actually built in many working examples of the same into IBM’s tool for the IBM i user. So we’re trying to make adoption just so very, very simple, both for end users and software vendors.
Paul: Yeah, no I mean they are a very―I mean for those of us who know about them and even though the services side I mean because they have an awful lot to do with ops admin of which of course I’m not ops admin guy―
Scott: Right.
Paul: But even at that there are some of them that at times I’ve found use for. So sorry, it was a segue question. There’s one thing though, Scott―
Scott: Okay.
Paul: I mean in all of this that you’re talking about the TR stuff, I mean what I’m loving is the way that you’ve avoided the most important thing that you guys did in this TR, okay.
Scott: What’s that?
Paul: I mean for those of us―for those of us in the programming world, when we’re using embedded SQL, what has been the bane of our life since the induction of SQL is a certain message―and all the programmers will know what this is―when they see the words coming on the precompiler and they see that message that says “variable blah blah not defined or not usable”―
Scott: Uh-huh.
Paul: And you go “really, I wonder why that is?” And you go and look at the second level message text and there’s a list of whatever it was, the six or seven reasons that were in there as to why a host variable may not be usable, and it was just up to you to go and figure it out. And the change you’ve made is?
Scott: Well, great introduction. There are two messages, SQL5011 and SQL0312, and those beloved messages now have reason codes and extended details as to which particular reason is behind this failure. And I must say Paul, I’m glad that you think this is the most important and you like it, but I’m a bit―I’m a bit dumbfounded because I thought you coded perfect RPG and SQL?
Paul: Oh yeah. No, I’m talking about other people.
Scott: Oh, okay.
Paul: When they have the problem. I mean people will sometimes―
Scott: Community minded as always.
Paul: Call me and say “what is this, Paul?” you know and I have to answer them “I don’t know. I’ve never seen it [laughs].”
Scott: Well you handled that perfectly, yeah, of course.
Paul: If only that were true, Scott. Okay, no. So here’s one of these things though, Scott: on behalf of every programmer out there who uses embedded SQL, thank you.
Scott: You’re welcome.
Paul: This one really is another big time saver, because the number of times that we had to go and, you know, just try and figure out which of the six or seven reasons it was that was causing this, yeah, now it just tells us. It’s really, really nice.
Scott: Yeah, I get it and one could say, “well where has this been?” Sometimes you need the right person to advocate for a great idea―and no, we thank you for doing that. I presented this to the Large User Group yesterday and there was some great hoots and hollers for it. You know the programmers they said exactly what you’re talking about. They see the programmer experience has just been improved―
Paul: Yeah.
Scott: And we love to do that with our tooling―our, you know, messages in this case―whether it’s Rational Developer for i or ACS, those are all about improving that development experience on IBM i, so thanks for the feedback.
Paul: So there’s something though Scott, before we finish up―and I’ve got a funny feeling I kind of know what the answer to this is going to be. But the―okay when I look at this TR and the previous one, okay there’s lots of nice stuff that’s been in both of them, but volume wise and in terms of like of the weight that’s been in there in in these two TRs, it hasn’t been quite up to what’s been there in the previous ones. Now if I’m to put my sort of, deduction hat on here, because I know you’ve―I mean they are all the same people are still working there in Rochester and working away on database. I’d have to sort of guess maybe is there―
Scott: Right.
Paul: Is there something going on on database that you guys are working on?
Scott: Yeah, first off, your assessment is correct. The earlier TR this year, there were really no database enhancements. I attribute that to the team overachieving in 2017. If you look at the fall TR in 2017, it had major deliveries and there were multiple of them. We rounded out our Java script object notation support in the database and delivered many other enhancements. As the architect for those, you put some of these things in motion and you’re not quite sure when they’re going to be done. But the team rallied, got them all done, and guess what? That kind of left the cupboard bare for the spring, so we focused all our efforts on this one that is coming out in September. The other thing that’s at play here―and this is no secret―we’re working on the next two future IBM i operating system releases at this very time. So I have to balance the team’s focus on you know, service and support, technology refresh enhancements and major feature enhancements in operating system.
Paul: Hmmm. Yeah, you’re not going to give us any hint about what you guys are working on, are you?
Scott: Oh, no [laughs]. Not even close.
Paul: It was worth a shot.
Scott: Nice try.
Paul: Okay so listen, before we go, Scott, let’s―so recently you sent me a photograph. And you did it just to―you sent me this photo to annoy me, okay? And this was a photograph of you and your wife and your daughter standing outside of a theater in Chicago just about to go in and see Hamilton.
Scott: Oh yes. That’s right.
Paul: Oh yeah, that’s right. I mean of all the numerous photographs that you sent to annoy me―
Scott: Yeah.
Paul: Yes, this is the one that I’m talking about.
Scott: Yeah.
Paul: So but it is a thing that we―we do share is that we have an enjoyment of―
Scott: Absolutely.
Paul: Of musicals. So let me just ask you two quick things about this. So first of all, I’m guessing you like Hamilton because this is what? This is what your third time seeing it?
Scott: It was only my second time.
Paul: Oh, okay.
Scott: But yeah. I love the show, and right. You’d said we’re friends at the beginning. That’s true. I have to tell you I didn’t send it to cause you grief. I know how much you love to see me and my family have some enjoyable times, and we do share a love of theater―
Paul: Yes.
Scott: So why not show you “hey, we’re about to go experience something amazing.”
Paul: Yes but-but-
Scott: So that was what it was all about.
Paul: But you do know that I am annoyed because I’m just madly jealous that I wasn’t there going to it as well [laughs].
Scott: Well yeah and your son is brilliant performer. We were lucky enough to see him, and I must say in fact I was a partial producer of a show, if you recall.
Paul: Yes, indeed.
Scott: So I’m looking for my next opportunity to play that role.
Paul: Yeah, I think it will be a cold day in hell before he produces your show after what he went through on that one.
Scott: Oh. Oh, darn.
Paul: So let me ask you a quick thing then on―on, on musical theater now―not on theater in general―just on musical theater: What is your favorite show?
Scott: Oh, well it’s predicable to say the thing you saw most recently, especially if you love it, but my wife and I back in―back when we lived in New York saw “Les Mis” on Broadway and that was a real special time. Neither of us had ever seen anything of that magnitude, and so I don’t know if there is one favorite but that was kind of an equal level if you will―
Paul: Yeah.
Scott: To this Hamilton experience.
Paul: Yeah.
Scott: It did-I’ll just say one more thing: Both Lori and I when we get to the end of act one of Hamilton, we looked at each other, and we didn’t have anything to say. We were pretty much awe struck.
Paul: Yeah.
Scott: It’s a tremendous feeling to have, and I hope to do that again soon.
Paul: Yeah, it’s true. So hopefully sometime soon the two of us get to go to another show together, Scott. I’d enjoy that.
Scott: That’d be great.
Paul: Okay and I think that’s a good note to leave it on, so Scott, thank you for taking the time to talk to us, and that’s it for this week, everyone. Tune in again really soon for the next iTalk with Tuohy. Bye for now.