Scott Forstie on SQL Services and IBM i 7.5 Capabilities
Spotlight for the B side: IBM's Scott Forstie on SQL services, geospatial functions and IBM i 7.5 capabilities
Scott Forstie: Well thank you very much Charlie, and hello listeners. It’s always a delight to be around Charlie. I think many of us know why that is. He is such a generous and happy person, so I always feel a certain amount of energy around you. So thanks for having me as a guest. Good to cross paths yet again, and let’s have a good podcast.
Charlie: Great, thanks for that. Thank you for that, Scott. So Scott, let’s just start with your title. I think it’s a fair thing because business architect, while that’s an interesting title, I think it somehow gets lost in translation. What exactly does a business architect do and what other things are you doing with IBM right now?
Scott: You’re right to conclude that. It’s sufficiently vague. A business architect can do whatever they want to do, so that’s one thing I love about my job. But in seriousness, I lead the database development team. I’m from IBM. I’m from Rochester, Minnesota—you know, the famous blue buildings and all that are ones I know very well. Our database development team is just amazing to work with, so the reason I love going to work is working with them and then working with people like yourselves and our clients of course just is the sort of thing that feeds me. That’s what I love to do and see that smile on the client’s face at the end of the day and an amazing client experience. What do I do? Well I decide everything that the database team is going to be working on. I’m there for clients if they are in a bind or need some help. I’m an active programmer. I’ve written most of the things you find in SYSTOOLS. I go beyond the database, working on architecting and delivering IBM i services. I’m involved in every service that gets created, SQL service that gets created. I have my fingerprints on them. Sometimes I’m known for a certain flare of testing. If people want to see if their code really works, they give me a call and I unleash the dragon on it. I even work with things like Navigator for i, browser technology, ACS. These are some of my favorite tools and I’m invested in them, Charlie. I work on improving them every week.
Charlie: Wow, so that’s quite a resume you have there, but it’s incredible to me all the different tasks that you’re responsible for. Certainly you’re not doing it all by yourself—
Scott: Oh no. We have a great team like I said, and kudos to them for the incredible things we’re bringing to the marketplace. I’ve had clients note to me Charlie, they say wow, it really seems like you’re accelerating the pace. I’m like yes, that is part of my strategy. Deliver what clients want, respond to those ideas—and we get the second most ideas of any part of IBM, and you know it’s part of my goals. Let’s see how much we can do in the space where clients would benefit: small, medium, large. Let’s get a bunch of them done.
Charlie: I think that’s really awesome, Scott. So Scott, the reason why I invited you here today because we were together recently at a conference, and one of the topics we were having was about music and—
Scott: Oh yeah.
Charlie: And more specifically about records, and even more specifically about vinyl albums—which is maybe a lost thing, maybe a relic of our past, but it’s not really the case. Obviously there’s been a big resurgence in vinyl records, so I think even today’s generation can associate with this, but one of the things in particular that we were talking about was the B side. And what I mean by that is we know that when an artist releases an album for example, in the old days anyway, you got to hear the A songs if you will. And there’s something about a vinyl record that’s different than digital music and technology, and that is that you can touch and feel it. You can listen to it in the sequence that the artist had intended, for example—
Charlie: But then there’s this topic of songs being released as a single, and then on the single on the back would be the B side. I did a little research on this and the B side were typically the bonus tracks or—I hate to use the word throwaway track—but bonus tracks, things that didn’t quite make the album.
Scott: Well they were pressing a 45 RPM record and they wanted to release it so it would get play on radio, but it would just be tragic if you had the other side blank. So they started, well okay artist, we need some song to put on the other side—the B side as you said—and that was fun for the listener because you had this little bonus, this little extra thing that you got to discover that wasn’t on radio right away, and sometimes those songs became very well-known songs.
Charlie: I’m so glad you said that Scott, but I also did some research on that very point—and I think you have as well—and I’ve identified a couple of B sides. And when you hear these titles, you say it gives you pause and you say wait a second, I didn’t know that. How can that possibly be a B side? That song is as popular as the other song—
Charlie: In the entire catalog in my mind. This song, how could it possibly be a B side?
Scott: Should we compare our lists?
Charlie: Let’s compare lists. We’ll go one for one. How’s that?
Charlie: We might have duplicates. I have four.
Scott: Okay, I’ve got four as well.
Charlie: Okay, great. So you go first.
Scott: Okay so the first one, this is one I think everybody knows, and even if you’re not a fan of this group, you enjoy this song at sporting venues. Queen with “We Will Rock You.”
Charlie: Absolutely. Okay well I’m down to three now, but okay.
Scott: Okay. I might have a match on yours. Let’s see.
Charlie: Okay one that I got back to the 60s first with mine, and it’s “Unchained Melody” by the Righteous Brothers.
Scott: Oh okay.
Charlie: Who would have thunk that that was a B side. It’s so popular and anybody who has seen the movie “Ghost” can relate to that song.
Scott: I love that one, but it’s not on my list.
Charlie: There we go. So, we have—
Scott: My next one Charlie is—and I grew up listening to the music my mom chose. So it was things like Simon and Garfunkel and the Beatles—
Scott: And I just love those artists, even to this day. But when I looked at this, “Revolution” from the Beatles was on a B side. It was a bonus track and that song is just amazing on just so many different levels, but the thing I love most about that song is when John Lennon just like screams. It’s part of the song but it’s just something I really treasure, “Revolution.”
Charlie: That’s not on my list, so there you go.
Charlie: All right so the next one on my list goes to back to 1969 and that is, “You Can’t Always Get What You Want” by the Rolling Stones.
Scott: Yeah, you nailed one on my list. Okay. I’m down to my last one. So why do you like that one, Charlie?
Charlie: Well because like so many things in life, especially in music. When you hear these songs they always just bring you back to a certain moment in time, maybe even to when the first time you heard that particular song, you’re immediately transported back to that moment in time and maybe there was something extra special in your life at that time, whatever it happens to be. And you can remember where you were, what you were doing, who you were with, even the smells, the sights, everything about it. And that’s really to me the magic of music in general, that it can do all those things for us.
Scott: So, it’s evocative for you. For my part though the Rolling Stones's “You Can’t Always Get What You Want” just reminded me on some of the best art that I’ve ever experienced, and a lot of its music, it tells you something that when you hear it, you know that’s true. Yeah, yeah, that is spot on and they’re right. You can’t always get what you want. So, my last one Charlie is—and I love this band and it might be surprising, but there’s no vocals usually. Booker T. & the M.G.’s “Green Onions” wasn’t on the A side. “Green Onions,” dear listeners, was on the B side and I would love “Green Onions” when I was at roller skating rink and they’d put that on. Oh man, everybody got off their seats and got onto the rink and started skating.
Charlie: Wow. Well that’s not on my list—I’m surprised that we don’t have more that are matching. So my final one goes back to 1970, and it’s “Color My World” by Chicago.
Scott: There you go. That’s an excellent one. I love that band but that is not one of my favorite Chicago songs.
Charlie: Okay, yeah. I mean I love it because it was such a unique composition of musicians, you know with big brass and everything else. So I think anyway they were a big part of my growing up anyway but—
Scott: They were truly amazing. Yup, agree.
Charlie: I agree. I absolutely agree. So this was a fun exercise. I really enjoyed this and I also happen to know that you actually happen to have another favorite band, and it’s actually called B Side.
Scott: B-Side is the name of a track, and now we’re talking about a recently released piece of work, not something from years ago. Khruangbin—and it’s hard to pronounce and it’s even harder to spell—but if you look on your Spotify or whatever your favorite app is and look for B-Side, they have a song out right now. I think I sent it to you, Charlie. It’s got a groove. Leon Bridges sings on it, so again it’s kind of like a shout-out back to the Booker T. & M.G.’s, because the band Khruangbin is just instrumental, but sometimes they team up with a vocal artist and this is one of those cases.
Charlie: I did listen to the track you recommended—“Texas Moon,” I think it’s called.
Scott: “Texas Moon” is another one of their songs that’s just amazing, but B-Side is the B-Side.
Charlie: See how much we can learn about each other, even however many years you’re my friend Scott, I think we still continue to amaze each other on how much—this is one of the great things about this whole community in general that people are friendly and it just speaks volumes.
Scott: Do you know my favorite New York pizza?
Charlie: Your favorite? In fact I think I do. I think you—
Scott: I think you do.
Charlie: I think it’s called Grandma's Pizza.
Scott: That’s right. See? You know me well.
Charlie: I do know you well.
Scott: Anybody who has the pleasure of a Charlie tour, make sure you get a Grandma’s slice.
Charlie: Okay. All right well, Scott, this was very fun to do this, so thank you everybody for joining us on our podcast and join us again [laughs]. This is the other side of the IBM i community, but seriously there’s a real meaning behind those little discussions that we’re having Scott, and that is as we started saying, we talk about one of the new release of IBM i 7.5, which was announced in May of this very year 2022. And there are certainly some very big hitters in that release and one that comes to my mind—well a few that come to my mind immediately are Db2 Mirror certainly, new security features, many new database features, and of course Merlin—
Scott: Merlin, yup.
Charlie: Which has been getting a lot of press.
Scott: A lot of attention—
Charlie: A lot of—
Scott: A lot of radio play.
Charlie: A lot of radio play. Yes, exactly a lot of radio play.
Scott: Yeah, and rightfully so, rightfully so.
Charlie: Right, exactly. It’s in the top 40 in fact, I understand.
Scott: Okay, Casey.
Charlie: Casey, yeah. You got the ground—
Scott: Keep reaching for the stars.
Charlie: Reaching for the stars, but like so many other—or like I mean all the releases of IBM i in particular and like many other albums that we talked about, there’s always a B side, the bonus tracks, and these are the ones that you know so even though we did announce, we did discuss a couple of the ones that were maybe have even overshadowed the main release, there are many features that have come out as part of 7.5 initially or maybe more recently as part of the technology refresh which will be generally available in December—
Scott: Yeah, right.
Charlie: And that was the driving force of our discussion today. Because I think that there are so many different things in 7.5, and these things—while they’re equally as important, in my view—are not getting potentially the fair play that they should be getting because of the other larger hitters. I’ll name a couple and then—
Charlie: And I’d like you to expand on them. Another thing that I always find curious, Scott—and as a speaker I hear this, but more I hear it from IBM developers themselves—and what that is is they know what’s available in a new release for example, and yet they’ll go to a conference and they’ll hear the attendees speaking either directly to the speaker or amongst themselves and how they’re using these new features in their shops in ways that were never either originally intended to or just as part of the creative process of bending these things perhaps, and making maybe even new uses out of them well beyond the original scope. Which I think is wonderful by the way, but I think that’s a curious thing which I really love. So the ones that I pointed out by myself here, the ones that I’m getting excited about are like for example the geospatial ones. I think they’ve got some real value. So I’m going to leave it there and I’m going to hand the mike back over to you. What can you tell me—what are the geospatials? What does that even mean and how might we be using them?
Scott: Okay so for that topic, geospatial functions are Watson, IBM Watson research technology that they’ve created. So it’s industry leading technology for this endeavor of understanding where objects are located on the earth and how they relate to other objects. You can compare and contrast individual objects, or you can do what’s known as creating regions that are based upon different points, and it goes back to that geometry we all learned way back when being able to have polygons and other geometrical shapes where you compare and contrast. Do they cover each other? Do they intersect? Do they not intersect? How do we put this in terms of data and Db2 for i and computing where it might have some business value? So the elevator pitch as it were would be most clients on IBM i, the data sitting on that IBM i is the artifact of their business, and the artifacts of their business many times include addresses for different regions, and the addresses can be converted into latitude and longitude points—so, places on the earth. So the data is just sitting there, ripe and ready for geospatial analytical study. So we’re almost at the third floor, so I got to get off the elevator at that one. So why should they care about geospatial analytics? Well, leading technology in the industry and guess what? You’ll own it. It will be built into Db2. No up charge, no license you have to acquire and administer, no moving data. It’s something you’ll own, so I want you to know about it and hopefully get some value out of it.
Charlie: And if I had a fleet of trucks, for example, this would be something I would be very interesting in using perhaps or learning more about it.
Scott: It’s a natural fit for that Charlie, I agree with you. You know whether in that industry of moving auto parts or whatever industry you’re in, look to think about the cases where places on the earth might be relevant and interesting to study. You know a truck is about to arrive at a location is just an obvious one. It’s within five kilometers or three miles. You know send a text to your counterpart so that they know it’s right almost there. We can accelerate these things. I’ve seen clients talk about being able to do analysis of regions of a country and understand where their competition is located and where they’re not located. Maybe use it that way. One of the clever things, and like you said we’re surprised and I join you with this thing. I’m surprised and delighted to hear what people—their creativity on using the technology. A gentleman in the UK wants to outfit two million cows with RFIDs and used this to track information about the cows in their business. It’s a dairy business. I didn’t expect to hear that, but pretty exciting stuff.
Charlie: That is exciting. You know based on what you just said Scott, I think in a year or two or in some short span of time, we’re going to be wondering to ourselves why this isn’t another B side. This is going to surprise us as a B side.
Scott: It very well could be, yeah. So just to wrap it up, it’s going to be free, right? It’s not charged. It’s just a matter of deciding to do something in this space, whereas you could have done geospatial before with IBM i, but you probably had to move the data. So that’s a risk and you have to do some extra work to move it and more. So you have to set up some place it’s moving to, then you have to acquire software—a vendor product perhaps or build your own—so that’s an investment to be able to do the calculations and a lot of those treat the earth like it’s flat, which is incorrect as we all know but you didn’t have something better. Well Watson’s is better. It takes into account that the earth is not two-dimensional.
Charlie: Right. That’s completely true. All right well that’s a great start to the B side conversation. Another one of the announcements that really caught my attention was the Boolean, the ability to handle Boolean values. That to me is yet another game changer and can really have in my opinion a profound effect on how you design code going forward. What can you tell us about the Boolean data types?
Scott: So the Boolean support—I’ll even broaden it to support in IBM i 7.5, like Charlie said, and it's all about thinking in the language of true and false. True and false would be Boolean, and it’s high time that it got into the database. I’m aware you know that it was lacking. I’m so happy we were able to fit it in. It was a huge effort, Charlie. We had to touch every language, every compiler, every command—everything that relates to data. When you add a new data type, it’s a big challenge. We went the extra mile. We also extended our JSON support within SQL to be able to publish and shred Boolean, so clients can move up to 7.5 and they can discard the previous Boolean technologies, like the 1s and 0s implementation of representing true and false to something that actually is true and false. There are some bonuses along the way. We’ve added in a bunch of built-in functions and aggregate functions and constants. We even extended the queries to have the language of Boolean so you can have a where clause that does have a this equal that. It can just say where this so different query support is also available all at 7.5.
Charlie: I stand by what I said. I think this is a real game changer in how we’re going to be using our applications, and along with that whole idea Scott, you know there’s this whole theme that IBM has. It’s the let’s create them, right? In fact, to extend that, let’s create a new level of integrated simplicity, and I really agree with that. One of the things that I know is in discussion in many shops is from the ground up how APIs have completely changed application development as a whole, and certainly there have been enhancements to IWS certainly. But the ones that really capture my attention are the new services—because in that area you know there were the days, and I’m sure you can speak to it, there were days when SQL was largely regarded as a pure database language, structure query language. But today what SQL is doing, what IBM is doing with SQL—and not only that but the speed in which new services keep coming out—is really just mind boggling to me. So what can you say about some of the new IBM i services and how people might be using them in their shops?
Scott: So, a couple of things. First, I’ve been there for the entire trip. I was there at the start and I was pushing this topic of using SQL as an alternative to the tried and true methods, and I have to tell you Charlie, the first year or two was pretty tough. There weren’t a lot of people buying into it. They were like, why is this guy pushing SQL? That’s for the database people. I’m in ops, I’m in admin, I’m in security. This is not for me. Flash forward to where we are today, and everybody is using it and the ideas for the next set of things that we should provide—whether they’re like you said, enhancements or a service that doesn’t exist anywhere in the operating system today—they keep coming. I see them almost every day, certainly every week, and they’re a joy to work on because you can see what clients achieve with them. I like to automate as much as I can. I think the SQL provides the opportunity to have hyper automation of topics because there’s so much power within the language of SQL. So you combine SQL with the operating system, and you can accomplish amazing things.
Charlie: Yeah, I mean one that comes to my mind immediately is things like with user spaces. You know some of these services are now allowing us to use maybe more complex APIs in a much easier fashion.
Scott: It has opened the door to onboarding—not just onboarding staff but onboarding and keeping them. I’ve seen time and time again: we just hired this new Python programmer. No, he or she didn’t know IBM i, but they were productive like the next week because of these SQL services, using the database connection methodologies, and they discover the data as they need to.
Charlie: Yeah, so you’ve really uprooted the paradigm into a much better place, I should say.
Scott: I like to be disruptive, so thank you, Charlie.
Charlie: As long as we are using it, by the way.
Scott: Well sure and as you talk about using it, here’s my SQL challenge. Look at your select lists, look at your predicate, and there’s probably room for improvement. Do you always have an order by? You should. On your select list are you using built-in functions and the transformative nature of the language, or are you just returning columns? So think of the final output. Think of better data than you’ve ever seen before. The sky’s the limit, dear friend, whether it’s bringing order to the IBM i. I talked at the last conference about taming the IFS with SQL services. By the end of the class they were all like, get out of my way, I want to go connect to my IBM i and start doing this.
Charlie: What else do you want to talk about? We started with B sides.
Scott: Yeah, I got one for you, man.
Charlie: What other can you share with us here?
Scott: So these are a little older. They’re like some of our songs. They don’t all have to be brand new so in the 5250, I think anybody who has used a 5250 has used the F9 key, right? You retrieve.
Scott: Yeah so and some years ago we enhanced it to have F8. So you can press the F9 just way too fast and then F8 lets you go the other direction. That was the A side. The B side is I don’t know if many people know that the F9 was enhanced when we did F8. So you can type in a leading character in F9, and it will go right to the previous commands that match your leading characters.
Charlie: That’s a good hidden gem right there.
Scott: I think so, too—so that’s my B side at least the first one. The second one is ACS. Everybody knows I love ACS for so many reasons, but here’s the B side version: Printer output. Using that instead of work spool pile changed my life. I had data rendered so that I could see it more easily in larger format, in PDF format. I could share it. I became more productive thanks to ACS and printer output. Navigator. Under Navigator there is a section right in the middle, and I don’t think it gets noticed, but it’s called My Stuff. So we’ve architected within Navigator for the Navigator user to very quickly and easily go to the things that belong to them. So give that some attention. Rightly so I know performance gets most of the attention, and the active jobs and yada, yada, yada, disk status, all that stuff. Great, great, great. But you know there’s a B side sitting there.
Charlie: And that my stuff, what a way to create a subset of exactly what you’re working on to improve your entire experience using the product and navigating the system as a whole.
Scott: My last one here is under ACS under schemas. So this is a very database-y thing, for procedures, functions, views, and tables. If you right click on any of those objects in any of those libraries or schema, there is an option to query and run SQL scripts. We will build the query for you, open the Run SQL Scripts and populate it and with a connection. You’re ready to go. You don’t have to start from scratch on SQL.
Charlie: Scott, this is awesome. This is really so great to me because like if you think of a grand piano in the hands of a maestro, we can you know, do amazing things. I mean I can hear “Turkish March” by Mozart all day long and it’s a wonderful composition. I can maybe do “Mary Had a Little Lamb,” maybe if I try really hard, but I guess my point to that is we have now the tools and the resources here to do amazing things with our applications, and it’s incumbent upon us to understand what the B side tools are out there because they provide as much value, in my view, as anything on the A side as well, providing you know how to use them creatively.
Scott: And I’ll just add to that, that like we saw with the music, if you share what you learn with others, you will have an enhanced experience like the music that we shared back and forth and why we enjoyed it. Now I have a different appreciation for it. Sharing that how to information amongst colleagues in different ways, managers who are on the call, you know fund some lunch and learns for people so that they come to the table and then do this sort of sharing back and forth, and the whole team will benefit.
Charlie: And I’m going to give a quick shout out to your Git hub, your GISTs.
Scott: Oh thanks.
Charlie: Why don’t you just tell people a little bit about that in 30 seconds or less?
Scott: Yeah, so I am the SQL tutor in DB2 for i, and the whole idea there is people contacted me with requests for help with SQL. Like can SQL do this? Can it do that? My answer is of course, usually, yes, and I decided to share the outcome of those interactions in a place where anyone in the community could find them. So SQL for everyone and they’re all—somebody had a problem or a challenge and here’s the SQL solution, so working SQL and GitHub under the GISTs. You can find it by just Google Db2 for i SQL tutor. You’ll come to a site that’s the aggregation of it—so it’s easier to navigate than going to GitHub itself—and have a little fun with SQL. That’s the whole idea there, Charlie. There’s over 100 of them.
Charlie: Wow. So, there’s something for everybody, in other words.
Scott: At least something for everybody, and even if you just want to learn SQL—you know, grow your proficiency with another technical topic, how do we do that? It’s by repetition, right? That’s how you learned all that great piano music you talked about. You practiced. You found time for it, everybody or whatever cadence. So that’s one my hints to those listening. How do I get better at it? Use the best tools, run SQL scripts is my favorite. Do it frequently and demonstrate success to yourself and others, and you’ll feel good about doing it again.
Charlie: Scott, that was perfectly said. Thank you for that and thank you on behalf of the IBM i community for providing all that value to us—especially at the right price—but I really [laughs] I want to echo what Scott said because I have actually myself gone to those and I’ve gotten some great ideas. Even if you just do a copy and paste, I guess it gives you good fodder for future development. So thank you again for that, Scott.
Scott: You’re very welcome. You can comment on those—and a person commented on one of those GISTs just this week and his point was hey, this example where you’re showing how to convert nondate data into dates. You know there’s an example that shows that. He said it doesn’t work for me because I use a different format for my date. I was using ISO instead of USA. There are different formats for dates, so we had a nice back and forth and I showed him how he could overcome it. I got something out of it and now the GIST is improved.
Charlie: I’m just thinking out loud, but just to wrap this Scott, I wonder if we just go back to our original conversation about B sides and albums, things like that. I wonder what the album art would be for IBM i 7.5 if we had to pick something, or pick a genre perhaps or something like that? I’ll put you on the spot: What do you think a good album cover would be, album art for 7.5?
Scott: Oh boy, I wish I had a good comeback right now.
Charlie: Well, that’s okay. We can save that for another discussion too, but—
Scott: I did save one bonus B side though, so—
Scott: Another artist I enjoy is David Bowie.
Scott: You’ve heard of him?
Charlie: Maybe. Maybe.
Scott: So “Suffragette City,” that song rocks and it was a B side.
Charlie: There is no way that that’s a B side.
Scott: Well, see it’s written down right here. “Suffragette City”—breezy but crazy awesome. Take the time to listen to those B sides.
Charlie: Absolutely. Listen to the B sides. I think we’re not doing justice all these things we talk about in IBM i about calling them B sides but they’re B sides in that they’re maybe a little bit overshadowed but it doesn’t mean—they’re no less valuable than you know, the A side if you will. Scott, what can I say? It is always a pleasure to chat with you for sure and I want to thank you very much for your time and for sharing your knowledge with us and—
Scott: Hey, you’re welcome. It’s a pleasure Charlie as always, so thank you and have a good one.
Charlie: Thank you. Everybody just as a quick reminder, please visit the TechChannel website. There’s lot of other good information out there. There’s a wealth of other podcasts and articles and blogs, really worth your time and until next time everybody see you again. Bye now.
About the author
Charlie Guarino // President, Central Park Data Systems
See more by Charlie Guarino