The Four Hundred

Skip Marchesani is one of the top consultants and educators on the topic of DB2/400. When he is not speaking at technical conferences and user group meetings, he works with individual companies and organizations by providing customized education on OS/400, as well as database design and review.

The annual iSeries and AS/400 Technical Conference, sponsored by Barsa Consulting Group and Tramenco, is one of Marchesani's top priorities. He serves as conference director for this event and presents topics at several sessions. His sessions at the June conference included SQL topics such as SQL triggers, DB2 UDB and SQL, and managing database objects with SQL. In addition to the iSeries and AS/400 Technical Conference, he is a featured speaker at COMMON, IBM Technical Conferences, and the New England Systems Technical Conference.

Marchesani's consulting work has included projects with organizations such as IBM, Tiffany & Co., CIGNA Corporation, Welch Allyn, the Camden (New Jersey) Courier-Post, and the Rochester Community and Technical College in Minnesota.

During his 25-year career at IBM, Marchesani was responsible for evaluating user interfaces, determining which functions would be implemented in the evolving operating systems, and how the latest functionality with each operating system release should be taught to the users. His work grew into a specialty in databases.

Marchesani knows SQL like Roger Clemens knows the strike zone (he's also a big New York Yankees fan), so I asked Skip to give us his best pitch when it comes to SQL on OS/400.

In today's environment, the number of AS/400 and iSeries professionals who understand that SQL is an excellent application development and data manipulation tool, and use it on a daily basis, is increasing. My guess is that between 20 and 35 percent of the overall professional population is aware of that, and it's getting better over time.

Pick any environment today where you have a PC on the network that's querying data from an AS/400 or iSeries. When you have a client getting data off a server, and you're using somebody's tools, the underlying mechanism that moves that data back and forth is SQL. People may not even intend to be using SQL, but they are dealing with it. They are using it with any type of data warehousing or data mining application or decision support application. It's all SQL based.

SQL didn't exactly take the AS/400 world by storm when it was introduced. Would you give some background and perspective on the origin of SQL as it relates to OS/400?

When IBM first announced the AS/400, SQL became part of the relational database. A lot of people who were schooled in relational database theory--and who understood that SQL was the relational database language to be using--decided they would use it for all their file I/O and database manipulation in their RPG and COBOL application programs.

We had people jumping into SQL on the AS/400 with both feet before they understood how deep the pool was. There were, for instance, people with database experience on other platforms coming to the AS/400 and writing SQL code just like they did on the other platforms. Lo and behold, it ran very badly. In other words, the performance was not up to expectations. The reason it ran badly was because people did not take the time to find out how to write good SQL code on the AS/400. Therefore, back in V1R1 of OS/400, people started saying SQL performs badly on the AS/400. That reputation spread like wildfire and it stuck. It still exists, to a certain extent, today.

Another thing that dates back to the V1R1 SQL performance fiasco was that people started asking, "Do I have to use SQL to access the data?" The answer, at that point in time, was no. Everything that you could do with SQL, you could also do with the native interface. Therefore people drew the incorrect conclusion that they did not need to learn and use SQL.

If I look at the native interfaces--READ, WRITE, UPDATE, DELETE, CHAIN, GET BY KEY, in RPG and COBOL--and I look at the SQL interface, each one has its strengths. The native interfaces' strength is doing single-record random retrieval. The SQL interface's strength is its ability to do set or multiple-record processing. The more complex the selection criteria becomes, the better SQL performs, compared with the native interface.

So if you want to do single-record random retrieval, high-performance transaction processing, typically you're not going to use SQL to do that. Other platforms, by the way, don't use SQL to do that, either. But if I want to process groups of related records or do inquiries into a database to look for specific sets of data, then SQL is going to outperform native interface.

Well, if I take an application that's going to process a million records in a file, and it's going to do single-record random retrieval of all million records without retrieving in any set sequence, then the native interface will typically be faster.

Conversely, if I want to write an application that processes all the customers in that same million-record file that live in New Jersey, that bought a certain product in the past six months, and have spent more than $10,000 in the past year, that application will typically run faster if it is written using SQL.

Over the years, IBM has added a lot of new functionality and performance to SQL. Why has it been the relational database language of choice over the native interfaces?

IBM said, back around the time of V4R3, that all future enhancements to the database, DB2 UDB, are going to be on the SQL side of the interface rather than on the native interface. This decision was mainly because SQL was the vehicle that hardware and software vendors agreed upon to move data between platforms, and it's the industry-standard language for relational databases. And IBM did not have enough resources, in terms of people, time, and dollars, to continue to enhance both interfaces. Although there have been a few exceptions, the new stuff on the database has been SQL only.

IBM basically said this is the way it's going to be. Get smart. Learn SQL. If you look at the lead time between when IBM announces a function in the operating system or database and the time people start using it, a couple of years may pass. People are just now getting on board with SQL because they are being forced to use SQL only.

I'm sure there is a group of legacy customers whose applications are running fine the way they are and they see no need to take advantage of the new stuff. But still, at some point in time they are going to be forced to learn SQL.

It's the same as the commitment to learn RPG, COBOL, or any other application development language for the first time. You have to say to yourself, "I am going to learn this and use it as part of my everyday job. By using it I'm going to gain experience. I'm going to make mistakes and learn from the mistakes. And I'm going to become proficient at it."

SQL is just another programming language, and you have to gain proficiency with it. You have to understand how it works and how to write good code.

How can someone jump into the SQL pool and avoid the problems users faced back in V1R1?

Back in V1R1, there were essentially no tools to assist users in tuning their SQL application or in diagnosing why SQL didn't do what they wanted it to. Today there are lots of tools that can assist the user in diagnosing performance problems and tuning their SQL application.

Skip, you have a reputation as a pretty good storyteller. Tell us about how you first learned SQL?

I started to work with the AS/400 in November 1986. I was put on the team that was responsible for doing all the V1R1 education beginning 18 months prior to the AS/400 announce date. Our team leader was Jack Rioni. In early 1987, the team was in Rochester, Minnesota, sitting through an AS/400 class, preparing to teach others.

The group that was teaching the class included John Sears, the IBM database guru. It was right after lunch, in a room that was too warm, and John was teaching [an] introduction to SQL. I was thinking to myself, "I don't need to learn this stuff," and before long I tuned out what John was saying and fell asleep.

So at the end of that particular session, Jack Rioni comes up to me and says, "Hey, Skip, I forgot to tell ya that I got you penciled in to teach SQL, and I noticed you fell asleep in the class."

So I went to John Sears and told him I just found out I have responsibility for teaching SQL. And John says, "You just slept through my session."

I said, "Yeah, I slept through your session, but tell me how I'm going to learn this?"

He told me to take his SQL introductory presentation that he had used to teach the class and create the two example files on the system. Then he told me to use Interactive SQL and its prompter to follow along with the presentation keying in the data, and doing the examples in the presentation.

So that's how I taught myself SQL. I used the Interactive SQL interface and the prompter, along with John's presentation.

The introduction-to-SQL presentation I do today is a derivative of that presentation that John did.

I think it is fair to say you are somewhat of an SQL fanatic, so in your mind is there a misconception about what SQL has to offer?

We have been talking about only one tiny aspect of using SQL in the system--embedded SQL in a high-level language--to do all the file I/O.

It's also an application development tool and a database manipulation tool. I have a presentation listing 12 different ways of using SQL. One of my favorites is Query Manager, an SQL-based query tool that people should be using instead of Query/400.

I like to use this analogy when comparing Query/400 with Query Manager: If you want to dig a hole in your backyard, do you want to use a shovel and a wheelbarrow or do you want to use a back hoe and a dump truck? Query/400 is the shovel and wheelbarrow. SQL [Query Manager] is the backhoe and dump truck.