IBM i > TRENDS > iTALK WITH TUOHY

Birgitta Hauser Discusses the Importance of SQL

Birgitta Hauser

Paul Tuohy: Hi everybody and welcome to another iTalk with Tuohy. Delighted to be joined today by somebody who at this stage I think I can probably call an old acquaintance because our paths cross so much. Anybody who ever attended COMMON US or any of the numerous conferences in Europe or anybody who have ever asked a question about SQL on a forum, there's a good chance that your question will have been answered by my guest today, Birgitta Hauser. Hello Birgitta.

Birgitta Hauser: Hi Paul

Paul: So thank you for joining me. Now the thing here again, Birgitta, I'm going to start off by just being very honest with you and tell you that, as always when I'm talking to you I'm going to get annoyed, considering that English is your second language and you probably speak it better than I do. [Laughs]

Birgitta: Oh no. I don't think so.

Paul: Well I think we'll let the listeners be the judge of that. So Birgitta I know that most of the people listening will have come across you in some form either online or in person attending one of your sessions at a conference but do you want to just give people a very brief description of what it is you do for a living?

Birgitta: Okay. I'm located here in the southern part of Germany. It's around 40 minutes away from Munich and 70 kilometers away from the famous castle, which is named Neu Schwanstein, which is a model of all those Disney castles. And yes, I'm working for a small software house located here in the south of Germany. We are only a handful of people working on the software house. My job is something like made for all means: I'm working in consulting, helping my customers modernizing their legacy applications. I'm working—I'm a traditional RPG programmer but my favorite was always the database and yes, I'm working on consulting and education. I'm also programming in one of our tools, which is a tool that allows RPG programmers to write web applications without knowing anything about HTML, Java script, etc., So that's my job and yes.

Paul: Okay. So since you touched on there of course, Birgitta, the thing you're probably best known for of course is the database side. So I think maybe a good broad question to start off with: Why do you think SQL is so important on IBM i?

Birgitta: Okay. SQL itself is very important because everything, all of our information, is stored somewhere in the database or somewhere—and SQL allows me to get fast access on my data. My first job I ever had, I was a controller and I had to use the data, I had to combine them, had to read them, had to prepare them for my manager, and in that first job I found out I had to Query/400 authority. Then I made all the reports, etc., with this query and yes, it starts my love and so I go on. I started programming RPG but I learned very fast to write embedded SQL programs. On the i SQL is very important while very powerful. We can use a big bunch of scalar functions. We can do almost everything with SQL and it facilitates the programming so I could reduce my RPG codes from 1,500 statements to 50 statements because I use SQL. So we can combine data, we can join them, we can merge them; we can accumulate them. There are a lot of things that are possible and the most important thing is the data. It is not the end of this that is the data. We need access to the data and we need fast access to the data. That's why SQL is so important.

Paul: I think one of the key things actually that you touched on there is fast access to the data. I think this is historically a thing on the platform that a lot of people—I think maybe because when sort of SQL started, you know it did get a bad rep at times for crippling the system at times.

Birgitta: Yeah.

Paul: Again, when things weren't done properly. But it is that absolute speed especially at times over traditional RPG.

Birgitta: Yeah.

Paul: No arguments with me on that one. So the thing Birgitta then that because you as I do at times at well, we are quite involved with sort of introducing people to the wonderful world of SQL so what do you think people should sort of aim to try and learn first? I mean, should they go for you know the "oh, I'm going to replace my DDS" so they should be looking at the DDL, the data definition language side of SQL? Or do you think they're better starting with the "no, let me just get at the data that's in there" so they should start with the DML, the data manipulation language side of SQL?

Birgitta: So I think it's a little like the question what is first, the chicken or the egg? But in either way I think if I already have a DDS described database whether it is very good normalized or not, the most important thing is how to access this data, how to pull out the data, how to accumulate them, how to join them, how to bring them together. With all those scalar functions we have in the system, we can do a lot of things and even the latest enhancements,—for example the OLAP ranking functions or the multidimensional grouping that are so great things that we can do with it, but we need to know it. I have seen programmers still writing programs with embedded SQL but in the same way they do it with RPG and that's not the goal.

Paul: Yeah.

Birgitta: So the goal is to bring everything or to move business logic into the database, into SQL select statements, into SQL use. The other thing is most programmers, or a lot of programmers, do not have any chance to change the database because a program gets rewritten but "please don't touch my database."

Paul: Yeah.

Birgitta: Even worse, if you have third party tools, you cannot change the database so I think the most important thing is to learn how to get access to the data, how to learn the select statement, how to do an update on it, how to do a delete things and then if you understood this, you can learn about database design, logical file—okay you should know views but a view is nothing else than a stored SQL statement. So you need to know the select statement before you can wrap it within view.

Paul: Yeah.

Birgitta: Yeah, then performance is another thing, but yet you first need to get access to the data.

Paul: Yeah. No, I think I would agree with you on that. I think a lot of the difficulty is that when people if they start with DDL and aren't really aware of what is possible with DML-

Birgitta: Yeah.

Paul: Then they miss things in the DDL, because they don't understand the relevance of it when you actually come to use it. Where on the other side if you are familiar with DML, you can sort of then see the deficiencies that you have in your DDS defined database so yeah. Yeah.

Birgitta: Even so, you should go from DDS to SQL because DDS is stabilized since a few releases. So all new enhancements—and these are a lot of enhancements are only done in the SQL.

Paul: Yeah.

Birgitta: Like in the temporal tables, large objects, or all those generated always—new columns that are introduced with SQL—with release 7.3 so you cannot do them in DDS. Same thing with DDS described tables. Try to use an SQL logical file with several select omit clauses or to join logical files or to join—yes, use joins in logical files—it's much more complicated than it is in SQL. The good thing is you even can use those SQL data base objects, whether it's a table or it's an SQL index, with in composition with native I/O. So even if you cannot specify an index in a select statement, but you can use it with native I/O and that's great.

Paul: Yeah.

Birgitta: That's really great.

Paul: Yup. Best of both worlds. Actually, there are a couple of things that you touched on in there—and that's going to lead me to my next question, Birgitta here which is really a bit of a unfair question I think coming up. So looking back over all of these great enhancements that IBM has done with the database, which do you think is the most impressive?

Birgitta: For the database on the IBM i, I think it is a thing that is not—that we don't view. Because the DB2 for i is integrated in the operating system, so we do not have to care about where my data is located. We do not have to care about table spaces. We do not have to care about to initialize indexes. We do not—a lot of people don't even know that there are statistics under the cover. They don't know it because we don't have to care about it. It's done automatically. Then there are a lot of other things that we see. Okay, they are important—for example exchange data with SQL with XML or those groupings, etc., but I think the best, the most hidden secret is the database is integrated in the operating system and the optimizer is optimized for this integration in the operating system. So we do not have to use—care about a lot of things, so that's why in my opinion we do not have as much DBAs (database administrators) on the IBM i, compared with for example Oracle and other databases because it is done automatically.

Paul: Or even DB2 on other platforms, on Linux-

Birgitta: Yeah, okay. It is much more complex than it is on the DB2 for i. So I think it is a really great thing that we have the database integrated in the operating system and we—okay we have to look at the indexes so we can get a good performance, but we do not have to do as much work as guys on other databases. For example, this week I taught Oracle guys DB2 for i. They said "what? That's done automatically?" Yeah. "If an index is recreated,—is updated, is it locked?" The index is always updated so we don't have to care about that. The access plans, are they automatically updated or do we have to do ourselves? No, it is automatic. We do not have to care about it.

Paul: Yeah. At times we don't actually know how lucky we are, and I think it is only when people actually go and work on one of these—like on a database on another platform or actually when you go and talk to a DBA and you see how much work they have to do-

Birgitta: Yes.

Paul: Which we just take for granted. So let me throw the opposite question at you then. So what is it that you dislike about SQL?

Birgitta: I am a fan of SQL, and I really like everything. Maybe a few things—there are a few things that I wish that IBM integrated in the SQL, or even better, in the SQL programming so I can better use it in composition with SQL, RPG and maybe with a web application. But what we have already, it is really great. So there is not a lot that I hate.

Paul: Okay. So do you want to give us just one little hint of something you think that you would like to see them put in into SQL?

Birgitta: Okay. Okay if I think about programming, I'd like to have something like data structures in a SQL programming or I wish they would finish all those JSON publishing functions that are blanked because JSON becomes more and more important. If you can do it directly with SQL writing out the JSON and getting back the JSON—same thing with XML, so it's really great. We already have the functions in the systems library to access web services, so if you don't need an additional programming language anymore in between so it would be a great thing. Yes and-

Paul: Okay. Well I mean for what it's worth Birgitta, I mean I agree with you, but the one thing that always gets me about SQL is that I think syntactically it is a horrible language to work with. [Laughs]

Birgitta: It depends.

Paul: It's like that when I'm sort of reading—I'm reading a 200-line sentence with no punctuation in it at times. [Laughs]

Birgitta: You can do it if you—okay, SQL itself it is a big thing. You have to structure it and you even can structure an SQL statement. It's like programming with subroutines or subprocedures or not. So you can write SQL statements, split them into either views or common table expressions, even if you have to write an XML document which is joined from—which is based on multiple tables and you have to convert it in a hierarchical representation so it's even possible to write it in composition with common table expression so that you still can read it. It's possible, but you have to learn it.

Paul: Yes.

Birgitta: Same thing for writing RPG functions and procedures. I remember when I started ILE, I thought "oh, we are modular." Today I get tears in my eyes when I think what we thought that's modular.

Paul: That is true. [Laughs]. That is true. Okay Birgitta-

Birgitta: The thing with SQL, you can structure it.

Paul: Yes.

Birgitta: You only have to learn it. If you write it in one big statement, yes you're right. You have to structure it. You have to break it into smaller pieces and put them together. Same thing with procedures.

Paul: Yeah. I think that actually comes back on though partly what you were saying about "it would be nice if we had the equivalent of data structures." Because sometimes I find that is just when you're doing that select of 40 columns-

Birgitta: Yeah.

Paul: That is—and of course actually on that as well, I must say as an aside that ACS of course—the new run SQL scripts in there, which has a formatter in that as well, is also of a great help.

Birgitta: That already helps, so it is colored. It 's formatted but sometimes with a complex SQL statement, so all those formatters are—yeah. Get lost sometimes.

Paul: Okay so Birgitta we're just coming short on time here. Just before we go, though—as is always my want on these—I must say even though I don't share your passion for your pastime, I do kind of envy you because you have a great place to practice it. So do you want to tell people what you like to do when you are not dealing with the database or your job?

Birgitta: Okay. Outside my job—so yeah, I have a big family which is located here in the south of Germany in the Black Forest—in the south of France so if I have time in my vacation, I am traveling there and on the weekend so just after my work just to get out I like to go hiking, because here the region is really nice. I have 5 kilometers I'm in a very nice area, which is called Lechauen. You can walk 2-3 hours without going on the same way. I have a few 25 kilometers up to the Ammersee, which is a nice lake. Here is a region where we have a lot of tourism, so there are a lot of hiking trails. That's what I like on the weekend, just to come down and go off and walk.

Paul: Yeah, I think you have probably one of the most beautiful parts of the world to do that in. Okay Birgitta, I think we are out of time. So Birgitta thank you very, very much for taking the time to talk to me.

Birgitta: You're welcome.

Paul: I look forward to seeing you. I think I'll be crossing paths with you again sometime in June, I think.

Birgitta: In June I'll be at the Brussels conference and the European COMMON conference. Before I'm at the COMMON conference in Orlando. I'll be also at the WMPCA in two weeks.

Paul: Okay. Enjoy that. I spoke there last year. Great conference. Okay, Birgitta. That's it for this iTalk everybody. Tune in again in a couple of weeks for the next one. Bye for now.

Birgitta: Okay. Bye.

Paul Tuohy has specialized in application development and training on IBM midrange systems for more than 20 years.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

IBM i > TRENDS > iTALK WITH TUOHY

Paul Tuohy on IBM i Highlights of 2016

IBM i > TRENDS > iTALK WITH TUOHY

Aaron Bartell on RubyOnRails, Node.js and Litmis

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters