System iNetwork

Greg, of the four options you list, I have used option 3: IO service programs with several procedures. However, rather than "returning" data structures, they are "shared": the calling program or procedure imports the data structure exported by the IO service program procedure. What is "returned" is a small data strucure containing success/fail and other control information; what is "shared" is the data set. The data set is not a record format, and the relationship of data sets to tables is many-to-many.

We used this approach to develop a layer of IO service programs for data access to be used by middle tier business logic programs or procedures. The IO procedures were all embedded SQL.

I think setters and getters could be included in this approach where it seemed appropriate, but we shared data structures between the IO and BL layers, and the business logic modules would do their getting and setting from the data structures.

This was solely an RPG and SQL application; we were not concerned with interfacing with Java. An IO layer which could be used by both RPG and Java might be better implemented with internal SQL stored procedures which could be called by either Java or RPG. This might be an option 5 for you to consider.

I do not see options 1 and 2 as being very attractive.

And yes, of course, tightly coupling your business logic to your database makes database changes expensive. The virtues of a layered architecture are pretty clear, I would think.

BTW, Joel Cochran has some useful online articles about this at the IT Jungle website. His approach is similar to, but not identical with, the approach I have experience with.

Our shop has decided on method 2. 1 module for each file. 1 Service program for files that can be grouped in a functional area. This I believe is the first step of separation. My only problem with this method and the others is access is not based on the function performed. I believe database access should not be closely tied with the business logic at all. Functional calls to a "Technical Services" layer would be more appropriate IMHO. Now you have functional calls that match the process you are performing. The database access is part of the technical services layer which gives true separation.

The functional call would return just the information the business logic requires. In some cases just a status code and in others data that would be displayed or manipulated by the business logic. This also requires a change to SQL instead of RLA to decrease the need for recompiles.

Sounds like alot of programming, but if you look at your existing code most of this logic already exists.

I don't think any of the options are attractive because what value have they really added? They could be seen as steps to a better goal, but if I still have to recompile and haven't gotten separation. Then what have I done?

I believe database separation has to be taken in steps, but you can't take 1 and then say you've achieved your goal.

Just my 2 cents

Option 2 seems to be the preferred option among the 200 or so RPG developers in my shop. My problem with option 2 is that it does not achieve any decoupling from the record format. A change to any field means a recompile for all the programs and modules that use the record format, just as if they still had the F spec entry. Isn't one of the main advantages of a layered approach the insulation of objects in one layer from change in another? Only the most elementary Java applications would allow a change to the database to necessitate a change or recompile of the Java.

Yes, the problem with option 2 is that it unnecessarily imposes the record format concept on SQL data access. Unless there are compelling reasons for doing this, it limits the payback from an SQL-based IO layer.

The strength of a many-to-many relationship between data structures and database tables is that the data structures serve the business logic needs. Changes to the database need not require changes to all data structures which draw columns from changed tables, and business logic modules which call procedures for data structures are even more insulated from database changes.

Option 2 when I've seen it done, and I'm sure most had this in mind when they cited it, was a bit more function oriented than a literal chain to one file and return the record format of that file.

You could think of the data structure coming back as a function data structure, the source and computation of each field can change but it's essentially an interface that your business logic requires. It does decouple from source and format for the future, but provides what the logic was counting on however it is assembled.

This might include for example the server assembling a result set of detail records to return with the master record. The point being that it's not the literal format of the data structure that is depended on but the overall aggregate of data structure that logic is written to compute with. The source may change, but the logic still requires the aggregate data structure.

Option 2 returns the entire set so that custom structures aren't required to be created and kept in synch with myriad uses of each server, but option 3 does fine tune the return with custom data structure calls. This is the option I used in my architecture that I wrote of a few threads ago.

It is more fine tuned than returning everything but bundles everything that would require a number of gets and sets, too many to be practical. But it also requires importing common data strcutures with which to read and write in the server and client programs, so non-trivial changes to the logic requires a recompile of the server program.

Whether option 2 or 3, the data structure wouldn't be the identical record format. What SQL advocates do is use very large standard size fields, a tradeoff of size across the board, storage, parms, and computation, but not needing to resize to meet additional requirements, say quality specs to the thousandths instead of the hundredths as the larger fields would usually have enough room to handle additional requirements.

So fine, just set up everything huge like they do and voila, look ma, I can do that too. What a brave new world. And business wonders why computers that are a gazillion time more powerful than what they used to have are slower.

rd

During my career, which has been over for a couple of years now, I found the simple chain or read sequence within a program to be more than sufficient. During my time I designed countless applications, wrote dozens of moderate sized and large systems involving a total of thousands of programs. I was writing highly modular code back in the early 1990's using called programs. Even on the earliest AS/400's, response time was subsecond and much faster than comparable monolithic programs.

The above mentioned approaches just look a little pompous and self important. Why not concentrate on better learning how the business works instead of how overcomplicated you can make an otherwise simple problem? If I were managing your IT department, I would be thinking of this as busywork and of you as a potential means for labor cost savings in the next downsizing.

I think your opening paragraph makes you read more than a little pompous and self-important.

You talk about your experiences writing modular code and then you rail against someone who's question boils down to: "What's the best practice for modularizing my code?" Once that best practice is found, it simplifies the development and maintenance of solutions to satisfy business requirements. Or they could just hack together some code to meet the business requirements and leave it at that.

To wander back onto topic, I think #1 is pointless (it will make it harder to identify which programs are affected by a change), #2 is probably the most practical, #3 is ideal (by delivering subsets of files, you reduce the likelihood of a field change affecting a program that doesn't use the field), and #4 is just plain silly (it's a simulation of OOP in RPG, which would introduce so much overhead as to eliminate the productivity advantage of writing in RPG in the first place....if encapsulation is the goal, it'd make more sense to use a language that was built for encapsulation).

Several comments seem to imply that it is fruitless to attempt to modernize RPG applications. I think that in large part they are right but only because the pool of RPG programmers is made of people that program without the goals that come naturally to the better OO programmers. Concepts like "DRY" (Don't Repeat Yourself), "TDD" (Test Driven Development), and accurately and succinctly modeling the business in software. I have almost twenty years on IBM midrange but all I know about software development as an engineering discipline I learned working with the Java people my company hired six years ago.

Do "highly modular" RPG subroutines, procedures and programs model the business in a manner such that the code isn't tightly coupled to the files and fields as defined in the DDS? That would be modular. And what if the RPG didn't always have the same work fields, strings and literals defined repeatedly in module after module? That would be modular. What if the code to do each action were in the code just one time? That would be modular. Will we ever see the day where RPG code in any way reflects modern software practices? Not likely.

Ancient Chinese wisdom - the last to know of the water are the fish. You have to know of an alternative to understand where you are.

Yes, a curmudgeon after my own heart. I find the theoretical and never ending separation into abstraction to be silly when no one anywhere really wants to go to another OS and database, they just talk that way. Then they write abstracted code that only works on their OS and database. Anything that isn't, isn't worth running.

My particular work was a series of business logic servers based on green screen programs to interface to a real time shop floor control system, and yes, the chains and reads and updates were right there, the core of the logic. But when massive critical enterprise logic is only in green screen programs, isolating it out to where interactive, batch, real time, and GUI interfaces can call it is definitely not busy work.

Although it will make you busy.

rd

I began my IT career writing RPG II on a Sys/3 Mod 10 back in the summer of 1973, AnotherView, and like you, I have designed and implemented countless applications using reads and chains for IO, and before RPG IV was released in the mid-90s we had to accomplish our modularity using subroutines and program calls. We did the best we could within the limits of the RPG language at any period in its intriguing history.

But in 2000/2001 I was involved in a project designing and implementing an ILE RPG system for a major financial company where we had a layered architecture with RPG/SQL procedures in service programs providing the database layer. That experience convinced me that a layered architecture can deliver on its promises for a procedural language like RPG as well as for OO languages.

Each company must do what best fits that company's environment and culture, but I think the desirability of a layered architecture has become a recognized best practice, and I can't imagine a new system of any significance being written today which does NOT adopt a layered architecture of some sort.

I know from personal experience that modern, highly modular, layered applications CAN be written in ILE RPG. It has been done; it is being done. For new RPG applications we should expect this. A more interesting challenge is how to modernize older RPG applications which are not layered and not as modular as they could be now (they may pre-date ILE RPG IV), IF it makes business sense to modernize them because they continue to provide great value and need to be blended with newer applications and technologies.

But Greg is probably right to suggest that there may be limits to the extent to which we can transfer best practices from the OO world to the procedural world of RPG. But I bet the folks at IBM who manage the evolution of RPG are doing their best to push out these limits. The challenge is on us RPG developers to use modern RPG in a modern way.

I have read most of the responses so far and I don't think my following opinion has been addressed, but forgive me if it has because I was skimming :-)

The number one thing we need to make sure of is that we are solving a problem! If your order header table is changing on a monthly basis then by all means encapsulating it is a good approach to ease recompilation efforts. THAT DOES NOT MEAN EVERY OTHER FILE ON THE SYSTEM SHOULD BE ENCAPSULATED! I see this all over the place with RPG programmers that get that first dose of "service program encapsulation cool-ness". Make sure there is need in each case before taking action.

The second point I want to make (which was covered above) is that modular code, whether it includes DB access, business logic, or both, should be based on need. If you don't need it then don't build it. To put forth an initiative to encapsulate every stinkin file or piece of business logic without even knowing if it will be reused is a plain waste of time. Sure you could label it as an "investment", but remember, investments need to pay out in the future otherwise the person who made the "investment" needs to stop being the broker making the ideas and authoring IT program structure direction.

There, I got it off my chest :-)
Aaron Bartell
http://mowyourlawn.com/blog

Aaron, your opinion is very appreciable.
It is full of good old "common sense".
Quite uncommon nowadays.

OK, I've read some of these threads and heard everything from we should all be fired to let's have common sense. I agree everyone's environment is not the same. But let's be real, about what the AS/400, iSeries, i5 (or what ever you want to call it is becoming. Not many users want their applications on greeen screens. There are some exceptions to that rule, but from what I've seen and heard the user community doesn't want GREEN. So we need to modernize our applications!! Our business logic is great and should be preserved. So how do you separate, some say buy a screen scraper and have at it, other rewrite it in Java. I'm not of that opinion, I believe in some instances we have to change the way we do things. In my development environment, we are struggling with a project to centralize 1400 AS/400's to 1 single box and have all remote users over a VSAT network. Think I want to send a 5250 data stream to 7000 plus users. Hell NO!! Maybe this type of situation hasn't come to your shop yet, but guess what, it will be it's just a matter of time!! So, I think if we don't start talking about these things and DOING some of them, we won't know what the best practices are and guess what happens when you can't deliver a company where they want to go? Just my opinion!!

The possibilities for layering and encapsulation are limited but we should not overlook them. Elimination of duplication would be a huge benefit to the code. When I see a constant repeatedly defined throughout some code I recognize that what it represents is probably important to the business and its function should be encapsulated in a procedure. I see the same IF statements repeated in the code where a record is read and the "code values" in it are compared to "special literals" to see how the record is going to be processed.

Have you heard the joke, "How many programmers does it take to screw in an object oriented light bulb? None. You send it a message and it screws itself in"? That is like the controller functionality we have in Java. Some benefits of a controller rely on polymorphism but, an RPG application could still encapsulate the controller function well enough. I see a lot of RPG.

I reviewed several SRVPGMs today. The top line in the comments of two say "cloned". I see that as a blatant admission of failure. The clone wasn't great code to start with and both the original and the clone have to be maintained for 10 to 15 years. Another way to eliminate duplication is to define surrogate keys in your tables. Programming is all about DRY - don't repeat yourself.

For more years than I can remember we have written modular apps, even way back on the S/34 and S/36. The 400 let us extend that into called programs dynamically bound, where explicit programs deal with explict business functions. This always minimised change impact. For years we have used, as required, called file access programs that serve other pgm requirements. File IO in a single pgm used across the entire system.

Anyone who uses monolithic programs (and I have examined some real MONSTERS, like over 3000 pages of compiler printouts) had a problem in the conceptual design in the first place.

IO is a major consideration, and RPG is still probably the fastest way to access and process data. Just like the 5250 data stream is efficient. GUI often isn't, but there are IMHO many people who do not understand the magnitude of data required under different situations. Hence faster machines and slower responses.

We write many cross app solutions, using server programs. Years ago (probably 15) we wrote a MS/ACCESS app that acted as the front end and chatted to 400 server apps, and DBFs with attached programs. Core business logic really belongs in one place, so any solution that scatters business logic becomes a new challenge. But combining the worlds in the right way is ideal, no matter how you do it. Modular is the best base thinking, no matter how you do it. But many apps have mud-ulare designs....

I agree that the 5250 screens often need refreshing, a subject that has been addressed between users and IBM dev-labs on many occasions over many years. But there are times when speed of capture is best on 5250 - again it depends on what the app is and who is using it. There are apps where GUI is best. Mix and match the best to fit the needs and the users is often a simple solution that lets you modernise easily. In other words I have never found one size fits all, or one solution method that is perfect.

Much of the time we have demands from users that deal with extracting information. Fine. We produce solutions that fit many needs easily, using 400 apps, and donwloading into things like EXCEL. Lots of "problems" get solved like this.

There is also a tendancy for the technical providers to focus on what they feel is needed whilst perhaps ignoring the business focus, those aspects that give the business competitive advantage. Thus some apps may need revision, but not all of them. This is the real challenge, to understand where the business needs to go, then decide what components need revision. More companies die because the apps are too restrictive to let the business change quickly. But that may not impact your debtors apps, or your accounting apps.

To respond to Greg's question we use 1 2 3 as well as the "classical" approach. Depends on the need for change now and in the future.

SQL, it seems to me, offers the possibility of having the module know of only the fields it needs and thus, the module can be decoupled from the fields it doesn't need. I don't want my code coupled to every field in the record format. I want to be as free as possible to modify the tables.

Object orientation makes it possible to closely model the solution domain to the problem domain. I originally thought that this just provided for ease of analysis and development but, having seen a Java project fail to develop an object domain model, the long term maintenance costs this project was going to impose became apparent. The RPG I see fails to encapsulate and reuse important business functionality. At my company, we need to have one SRVPGM that provides functionality over a Customer and another that provides functionality over the products, etc, etc. We have "modular" code but, to me, the modularity seems random, making the costs to maintain this code unnecessarilly high.

If you have a many-to-many relationship between the data structures produced by the IO procedures, on the one hand, and the tables used by the IO procedures, on the other, then you can design the data structures to serve the business logic, and the BL modules are less constrained by database design.

You in effect have a data mapping layer between the database and the application, such that those developers working at the business logic level need have little concern for the database design, as long as the developers working on the IO access layer can provide the data structures and result sets needed at the BL level.

The BL developer does not need to think in terms of tables and files and record formats, etc, but in terms of data sets and result sets as needed by the application. The IO access developer writes the SQL to serve the needs of the business logic level.

This is not what everyone needs to do, nor does anyone need to do it all the time, but this approach has worked very well for the design and implementation of new systems of some complexity.

Off topic: news announcement
"IBM Hopes to Stimulate i5 Sales with High-End Price Cuts "

Oh boy, any marketer reading such an announcement raises his high brows. Oh boy, does Ibm still have a lot to learn. I recommend them a beginners course "how to market the AS/400" (huh, oops iSeries, huh oops i system, sorry system i i mean system thing... you know what i mean)

..."SQL, it seems to me, offers the possibility of having the module know of only the fields it needs and thus, the module can be decoupled from the fields it doesn't need. I don't want my code coupled to every field in the record format. I want to be as free as possible to modify the tables."

Greg, I think you made an excellent point. To isolate the business layer from the i/o layer in order to limit the impact of change requests on either side, has his merits everybody acknowledges. But a solution discussed earlier like isolating the i/o by means of a service program and to use data structures to communicate with the business layer, has poor quality imho. To access the data file(s), you need that peticular service program. What if you, besides your rpg programs, have a report generator or datawarehouse tool that you want to act on the same interface? Won't work.
Oracle has presented a much better solution. The interface is still SQL based, the programmer access a virtual file (view), but behind the view, a whole lot of business logic (triggers, procedures, functions using pl/sql) is present to transform the interface to the physical data layer. The pl part (p-rocedural l-anguage) means you can include the classical do while and if then else constructs, providing you with a richer set of logical tools than pure sql alone. Once this layer defined, it can be exploited from a java client, an odbc/dot net VB client or any other tool. No need to call a service program with a parameterlist and action code 'insert record' or that kind of BS.

Yes, Ugeerts, you have pointed out a key limitation in using a layer of IO service programs to establish the IO tier. The strategy works well if your application is all RPG (or ILE), but presents challenges if you want Java or other technologies to use it.

With DB2, internal SQL stored procedures can overcome this problem just as Oracle stored procedures do. Java as well as ILE programs can call stored procedures, as well as other technologies. iSeries shops which want an IO tier which can service both ILE languages and Java and other products may find stored procedures the way to go.

I've been avoiding this discussion because it's getting more and more impractical every day, but I have to jump in here on this last argument.

How is wrapping stored procedures around your database fundamentally any different than isolating them behind service programs? The only difference is that you're also allowing unfettered access to your database for end users.

And don't talk to me about data mining; you should generate the data that needs to be mined and store it separately from your online transaction data. Typically data mining is NOT performed on realtime data, which is what we're talking about isolating here. Note that this is NOT the same as online queries, which extract specific realtime data for immediate reporting; that requires access to the current data, but can be done just fine through service programs.

Instead, we're talking about end users performing ad hoc reporting. This should be done on prepared copies of the data. You should NEVER give your end users carte blanche access to raw fields because they don't know the relationships between all the data. The trend of giving blanket access to raw data is simply horrible, because it locks your database design into your end users' queries. What if I want to change how I store dates? I have to now go and fix every end user query out there.

The fundamental question is whether you want non-programmers to have access to your tables and columns. The answer ought to be a resounding "NO!"

If you said "Yes" in a programming 101 class, your teacher would bash you over the head about "tight binding" between the presentation and data tiers. It's just a bad, bad idea when your presentation layer programs (and especially your end users) know the names of tables and columns in your database.

Joe

I don't think the use of stored procedures implies that users thereby have unfettered access to the database.

The only experience I have with a comprehensive wrapping of the database involved a layer of IO service programs consisting of embedded SQL procedures. This was an all-RPG application and it worked very well.

I have recently become more interested in the stored procedure option for two reasons: 1) Use of stored procedures by Java as well as RPG seems more straightforward, and the Java call of a stored procedure will likely perform better than Java use of an RPG procedure, and 2) performance.

I am currently working on a Java application which is essentially a decision support application which needs to do focused data mining to get data for the tool. Data requirements are intense and response time is important, so we benchmarked six different data access strategies, four involved SQL and two involved native IO. For this particular application, SQL in general outperformed native IO by about a factor of three. The internal SQL stored procedure gave the best performance.

Some of our data needs can be achieved with direct SQL queries from the app, but the more intense needs will be accomplished using called stored procedures.

We have two reporting tools popular with our users. One uses the approach you recommend, Joe, of preparing a copy of selected data and downloading it to a server dedicated to that reporting tool. The other tool provides direct read-only access to any number of databases, but the access passes through a metadata layer defined and controlled by IT. We use this tool to provide user access to both our DB2 and Oracle databases. User access is controlled both by security and by metadata definitions (they don't see tables; they see query subjects (similar to views)).

See, this is the thing I hate. This thread was originally about modernizing legacy applications and has now devolved into yet another discussion of data mining.

Let's be clear: data mining is NOT application programming. Never has been, never will be. Has nothing to do with programming. It's about getting ad hoc access to large amounts of raw data in short periods of time. SQL was designed for this and is and always will be the best answer.

Any architecture you do for data mining should be separate from what you do for OLTP, since the two requirements are so vastly different. Designing one architecture for both is kind of like trying to design a good underwater fighter jet. It don't make sense.

So if you want to use stored procedures for your data mining, have at it. Let me know when you get back to talking about application architectures (at which point I guarantee direct program calls will blow the doors off of stored procedures).

Joe

The main weakness of stored procedures as a backend model to simultaneously serve RPG and Java concerns is that DB2/400's SQL PL doesn't support consumption of result sets. This limitation can be circumvented by table functions, but it's really hackish. Other versions of DB2 support consuming result sets directly (and simply).

Gee, Joe, you are so passionate about things!

I don't know if the term "data mining" has become synonymous with the production of large result sets, but if it has, then certainly SQL is the proper choice for producing large result sets.

If the production of large result sets has nothing to do with programming, then I suppose all IO access methods have nothing to do with programming, except that they are actions performed by the program.

There are many applications which need large result sets. Manufacturing planning and control systems and supply chain management systems (where my work has largely been concentrated) rely on large result sets from historical and forecasted data in order to do their work. Whether the use of large result sets from time-phased tables is always thought of as data mining I do not know, but is is very common outside of ad hoc access to large sets of data for reporting purposes.

But you are right: for single row or small result set retrievals to support online transaction processing systems, stored procedures would be an odd choice of data access. Therefore the idea of using solely stored procedures to encapsulate a database is unsound. Stored procedures may be an excellent strategy for returning large result sets, but there are better methods for getting small result sets.

Which is why our decision support app will use direct SQL calls from the Java app IO tier for single-row or small result sets, and the call of stored procedures from the IO tier for the generation of the large result sets.

BTW, I had advocated using direct SQL calls for all data access, but my preferred method came in second place to the stored procedure (usually; sometimes it was in first place). But I can't argue with demonstrated facts.

Before our benchmarking, I had generally disfavored the idea of using stored procedures to return result sets. I had thought stored procedures were mainly used either to return a value calculated from the database or to perform a batch process on the database. In fact, most examples of stored procedures from Oracle or DB2 manuals show them as being used for those purposes. But now I have learned that DB2 stored procedures can be the best choice, at times, for returning large result sets. Always learning something new in this field.

RDean: In recent years I have written many Oracle PL/SQL programs, and one of the first things I noticed as I began working with the DB2/400 internal SQL stored procedure was that it did not support array processing within the procedure. I suppose this is somewhat related to the inability to consume result sets.

But for just returning result sets, a DB2/400 stored procedure, if it can return the desired result set, can be called by either RPG or Java, and that is an attractive characteristic.

BTW, wouldn't another way of overcoming the limitations of the internal SQL stored procedure be to use an external stored procedure instead? That way RPG could provide the array processing or the consumption of the result set, and yet in the end a result set would be returned to the calling program, whether RPG or Java. (Of course such a solution would only be useful if one wanted to produce a large result set and make it returnable to whatever may call the procedure).

"Manufacturing planning and control systems and supply chain management systems (where my work has largely been concentrated) rely on large result sets from historical and forecasted data in order to do their work."

Um, not really. In fact, that's the difference between application programming and data mining. In almost any ERP you're doing work at a very low level, typically the item/location level and in relatively short time increments. You may be reading through milloions of records in any given run, but you're usually generating discrete packets of data (for example, demand for a specific day). The rules are generally complex and data driven and don't lend themselves to a single SQL instruction, no matter how complex. This is why native I/O (which is basically nothing more than good old ISAM) performs so much better (not to mention is much more maintainable).

Data mining, on the other hand, typically executes the exact same operations over thousands or millions or records at once, generating large result sets which are then massaged to generate information. The fields, orders and calculations are ad hoc, so there is little or no ability for the programmer to identify logic shortcuts or strategic denormalizations or any of the other techniques application programmers use to optimize teir algorithms. Thus SQL engines are usually able to optimize things as well as can be expected (although I would still guess that in many cases a good programmer would be able to modify the system in such a way that a given specific query could be faster using ISAM over SQL).

To take a page from quantum physics, application programming is the paricle nature of data access, while data mining is the wave nature.

"If the production of large result sets has nothing to do with programming, then I suppose all IO access methods have nothing to do with programming, except that they are actions performed by the program."

That's not what I said. I said that the generation of large AD HOC sets of data has nothing to do with programming. I thought I was pretty clear about that, but I'll restate my position. When the order, selection and calculation of data is entirely under the purview of the end user, then application programming is not part of the process. The end user effectively becomes the programmer (and you had better hope they actually know how the database is set up).

Unfortunately, if you don't understand this distinction, these two very different business requirements might superficially look the same. And when people confuse the two, they end up making bad architectural decisions... and end up with fighter jets with snorkels.

Joe

First, Joe, I agree with you about user-controlled data mining. Perhaps I should not have used the term when describing the data retrieval needed by our decision support system.

But I have to disagree about the use of large result sets for ERP and SCM systems. Many such systems which need to use large sets of time phased data to perform their functions will use SQL to build the result sets, and then apply logic to the results as they fetch through them.

In former times I was deep into MAPICS, and I agree with you about the power of native IO, but . . .

Many ERP and SCM systems are not written in RPG, and hence the use of SQL is sort of taken for granted. Our Oracle-based supply chain mgmt system, for example, is a Java J2EE application.

"Many ERP and SCM systems are not written in RPG, and hence the use of SQL is sort of taken for granted. Our Oracle-based supply chain mgmt system, for example, is a Java J2EE application."

Again, the topic is devolving from a "best practices" to one where previous choices are dictating the architecture. You're implying that because some people have developed ERP systems on SQL databases that the "standard" approach is to use a result set.

This is circular reasoning. Since your only tool in SQL is the result set, then of course you have to use a result set. But I insist that ISAM access will far outperform SQL in anything but the simplest of requirements generations. Again, it's the particle nature of application programming vs. the wave nature of queries.

And that's why RPG on the System i is hands down the best environment for business applications. In situations where you need ISAM access, you have it, but where you need SQL you have that too. Nobody else provides that.

More and more this blog is starting to (as so many do) become a recitation of your reasons why you do things rather than a research of best practices. I'm not criticizing, simply observing what seems to be an inevitability in the blogosphere.

Joe

I would hope that this blog can be a place for exploring options and the reasons for choosing various options, and respect that all choices are relative to their specific context.

I was merely pointing out that SQL is commonly used and did not mean to imply that therefore it should always be used. I did mean to imply, however, that it can give acceptable performance.

I understand, Joe, that you are a strong advocate of native IO. Many of my colleagues here at Glazers are also strong advocates of native IO. I respect that preference.

But I am very pro-SQL. Prior to the year 2000 I used native IO almost exclusively; SQL was an incidental alternative used only in special cases. But then I spent a year on a project designing and implementing an ILE RPG application with a layered architecture where the IO layer consisted of service programs containing procedures which used embedded SQL only. Native IO was not permitted by client management.

That project taught me that SQL works very well. There were no performance complaints. The application was largely interactive and users was distributed around the country, all accessing a central iSeries.

Now I don't mean to imply that because SQL worked fine for that project everyone should stop using native IO and start using SQL. But that experience certainly changed my own views about SQL as an alternative to native IO for RPG applications. Subsequent experience as an Oracle developer deepened my appreciation of SQL.

I think the SQL vs. native IO choice is very interesting. My current thinking is that for many interactive applications performance is often a secondary issue to code architecture, because there is little appreciable difference in the performance given by SQL vs. native IO for small data retrievals (if you need a measuring device to notice the difference, it is not appreciable).

But what I find quite interesting is how the use of SQL or native IO can affect the structure of the code and the layering of the application (if this is desired). A lot of the discussions of SQL vs. native IO seem to focus on performance instead of on how IO strategies affect application architecture and program code structure.

For some applications layering may be unnecessary and the use of SQL instead of native IO may run counter to the code structure of the program. For preexisting applications already committed to native IO, the introduction of SQL could disturb the architecture. With new applications performance and architectural concerns can be evaluated afresh and up front.

Two questions I would like to propose for discussion are these:

1. In what ways can the choice of SQL or native IO affect the structure of the program?

2. In those cases where SQL and native IO give comparable performance, what criteria might be used to choose between the options?

First, you make some bad assumptions, Max. I am NOT pro-native, at least in the sense that it would make me anti-SQL. I am in all things pro-correct-tool. I firmly believe in the right tool for the right job (I use both native I/O and SQL as needed). I believe that in the absence of external business influences that there usually is a "best" architectural solution; for example, it's hard to beat RPG on the iSeries for programming business logic. At the same time, I also believe that a proper requirements definition can lead to a perfectly sound business decision in program design that isn't architecturally "optimal".

However, what I do hate is when someone gets comfortable in a technology and then uses that technology inappropriately. For example, your client that forbade native I/O. I don't know the circumstances, but I've never seen a place where this requirement was made by technically competent individuals (with the exception of software companies trying to sell the same mediocre code to multiple customers, or consultants trying to charge multiple clients for the same work). Especially since you more than likely were forced to use extensions to the language that were not portable (TOP as opposed to FIRST nn ROWS comes to mind immediately). At that point, the vaunted platform independence of SQL is no longer in place, and without that big selling point the language is nothing more than a really bad substitute for ISAM access.

I won't hash the SQL vs. native debate. I can't; it's simply common sense to use the faster, better, more maintainable solution. I have no common ground with someone who is willing to accept lower performance in order to write code that is hard to maintain and nearly impossible to debug interactively. SQL is the wrong tool for OLTP and those who insist on it -- well, I don't spend much time with people who paint their house with a bulldozer, either.

Joe

I have spent most of my AS/400 career positioning within large files and reading by key forward and backward, using the data read on each IO to do IO's on additional files and make decisions every step of the way.

But for two years in the early 90's I was the programmer for an insurance company running an insurance package on the AS/400. They wanted additional client/server apps against the AS/400 developed in R:Base in SQL, and that's what I did.

Creating a cursor and reading through result sets was similar to SETLL and reading by key, but the SQL apps were relatively simple and I didn't have to do the complex interaction with multiple files I routinely do in RPG in more complex apps, but I was able to do all my record at a time processing with SQL using cursors.

When I looked at SQL not long ago to do some things on my web site, I saw that IBM has extensions in DB2/400 SQL to do forward and backward reading by key, which looked to me like they implemented in SQL the capabilities of native RPG IO opcodes such as READE and READPE. But it wasn't clear to me that other SQL's had that.

I looked at commercial software and what databases they ran on, and I saw specific databases listed that you could run the software on. In a recent example, thay even said they ran on IBM database, but when you got to the technical specs it was Informix.

One last point: SQL has been around almost as long as RPG. There is nothing "modern" about it. It is not a given to me that "modern" development is in SQL, or that the alleged reason it's being used is that it is database independent. It's not.

So we have an OS/400 computer and RPG native IO that's the best at transaction processing of massive data in the world (better even than COBOL on the mainframe perhaps), and we have people running around who say let's write it in something that runs somewhere else, even though it doesn't.

When business specifies SQL for that reason, they are buying into a myth propagated by most of IBM and everyone else who doesn't have RPG native IO, but they are being misled deliberately. SQL is not cross platform.

Can SQL for DB2/400 do what we do in RPG? Well, IBM spends full time trying to make sure it does. But in my opinion complex interaction among several files by key is what RPG is for, and trying to emulate that with cursors and proprietary SQL enhancements is not a good AS/400 application architecture.

Every major ERP up till now has been in RPG, COBOL, and German COBOL. If Oracle and their SQL ways could write ERP's in SQL, they wouldn't have had to buy JDE and Peoplesoft. Their trying yet again with Fusion.

Sooner or later maybe mediocrity will prevail. The AS/400 and RPG will have to be gone by then for that to happen.

rd

Thanks, Joe and Ralph; you both make excellent points.

It amazes me, Joe, how soon I forget after completing a project involving SQL in RPG, whatever difficulties I may have encountered because of the difficulties of interactive debugging of embedded SQL. My practice is to develop the SQL separately in advance of embedding it to minimize the extent to which I experience that pain.

I was not privy to the client management decision to use SQL only, but I know IBM was involved in designing the IO tier and I suspect they may have been involved in that decision. I think we can probably attribute it to "architectural philosophy".

I don't think there was an RPG developer on that project who didn't find it odd, and initially scary, to not have the ability to resort to chain and read operations. But then again, many of us found the highly normalized database design unfriendly to native IO anyway -- too many tables to chain to.

In the end, it all worked out well: the IO layer design did a good job buffering the application programming from the highly normalized database, and the system worked well. Given the overall system architecture, including the highly normalized database design, I think SQL was the best choice for that project.

Perhaps we can conclude that the more normalized the database is, the more useful SQL is likely to be; and the less normalized the database is, the more convenient native IO is likely to be.

Ralph, I hope someone someday writes a detailed history of our profession and its tools; I think it would be fun for us old-timers to read. RPG was first released in 1964, but the more potent RPG II was released in 1969. The next year Dr. Codd of IBM laid the theoretical foundation for SQL in his description of a relational model for database design. But it wasn't until the late 1970s that SQL products started hitting the market. As I recall, IBM was slow at bringing SQL to the AS/400.

But you're right: SQL is not modern; it has been around for close to 30 years. What "might" be "modern" for the iSeries community, however, is the increased use of SQL, along with procedures (brought to us in 1994 with RPG IV), to develop a different breed of architectures than what we developed in the 70s, 80s and early 90s. And yet, the theoretical foundations for highly modular layered architectures dates back to . . . the early 1970s! Why then did it take IBM until 1994 to give us RPG IV?

Why was ibm late to bring SQL to the as400? Why did they gave us rpg iv in 1994? Why did free format rpg emerge late in 2002?
There is one constant factor here; ibm is about late to bring everything to the as400.
Back in 1999, i was on a project where the code base for a major application was written in rpg400 but the sources needed also to be ported to a unix cobol environment using an Oracle database. The porting from rpg400 to cobol was completely automated (some German company provided the language conversion tools), but performance wise, the unix environment was unacceptably slow. So we started using Embedded sql in rpg400 to replace rpg's native record i/o, and, as you can quess, the unix application ran about 5 times faster (a batch job of 60 min ran in 12 min), but the same job on the as400 side ran about 10 times slower (the 60 min batch job took 600 min or 10 hours).

Indeed, replacing a simple rpg chain by a statement like "select field1, field2.. from table where keyfield = :keyvalue" is pure horror for the as400, yet for Oracle, it is the only and fastest way around.
Now let's take a look at all modern languages, merely the Oracle Pl/SQL's, the SAP ABAP language, the Java's, the Visual studios (VB, C sharp...) the office automation tools (macros in excel or ms access), the scripting languages embedded in HTML, of this world. Do they use record access methods to access the DB? Does the possibility even exist in those languages? The answer is NO (except maybe to access files outside the DB like pure textfiles). The rest of the world is using SQL syntax to access a DB, only rpg and cobol still provide record access. My conclusion is: instead of writing an application on the as400 using native record i/o, write the application on a modern machine in a modern language using "modern" sql.
I have witnessed several IT hardware decision processes NOT in favor of the as400, and the most common reasons against, apart from the legacy-obsolete image, mentioned were:
1. the inability of ibm to bring a native (not web based) gui to as400
2. the inability of ibm to bring the performance of as400 sql on par with the other major rdbms

Imho, these are the major reasons which led to the demise of the as400 in the last 5 years.

When I said German COBOL, that's SAP. That is what it has been referred to. Does German COBOL sound like SQL?

I researched record level access in other databases. Oracle and SQL Server have it. There's a reason for that.

The writer mentions Oracle and Microsoft. Neither had a manufacturing level ERP with SQL. MS sought to buy SAP, German COBOL, in addition to buying Great Plains and others. Even lightweight PC ERP's like Great Plains are written in PC database record level access.

Oracle bought Peoplesoft, much of its ERP written in COBOL, and JDE, written in RPG.

Both Oracle and MS are struggling to put out their own ERP's now, Fusion and Green. Green has been delayed. How much is due to trying to do ERP record level access with SQL? A lot, I think.

When all is said and done, the SQL they have will not be database independent but instead be highly tuned to the record level access API's of Oracle and SQL Server respectively.

We already use something better. It's called RPG.

rd

I'm surprised that you refer to an Oracle record level access API. I don't know why such a thing would be needed. As far as I'm concerned, record level access in any version of SQL is merely a select statement with the intent to retrieve a single row. It is used all the time.

The only time I have heard the expression "record level access" in OracleLand is in reference to security, not retrieval method. But I'll have to check with our Oracle DBA to see if there is some well-hidden alternative RLA method to a focused select statement. I would be very surprised if there is.

I have experienced very good performance with single-row retrievals using IBM SQL on the iSeries, but I haven't done millisecond benchmark comparisons to the chain operation. I am being tempted to do so, just to see what the extent of the difference really is at V5R3.

I think ugeerts' main point is that IBM has been tardy in bringing many technologies to the AS/400, and having them perform well right away. I know of people who have a bad impression of iSeries SQL because of bad experiences with early versions of it. They should give it another try. I think IBM is telling the truth when they say they have been continually improving it.

Hi Max. I Googled record level access several months ago as part of research for a personal project. I didn't take notes, but my recollection was that Oracle had an API for it and MS was enhancing their's.

As I recall, SAP's paid version of their database also had it.

I decided to use another database so I didn't go into further research on any of these, but I and I'm sure everyone else will appreciate any corrections if needed. :)

rd

Max, when you say "Ibm is late in bringing new technology and have them perform well right away" is exactly the point I'm making. The project I mentioned ran in 1999, probably on an older as400, maybe still a cisc machine. Ibm lost face in those days, at the height of the ICT bubble with so many companies that even if they claim they are now furiously improving sql performance, it is simply *NOT* believed anymore by those who have decided to leave the as/400 platform in the mean time.

BTW: by record access i mean the syntactical construction of using a start or setll, read or read previous (read, readp), write record etc. statement as opposed to the sql construct select from , declare cursor, insert into etc. which allows to work on multiple records or result sets at the same time.
The reason why record access performs better on iseries is well known; the as400 database and those of its predecessors in the 80's was ISAM based, SQL was just a layer that was put on top of it in the early 90's.
Oracle on the other hand, was SQL based from the beginning.

"Perhaps we can conclude that the more normalized the database is, the more useful SQL is likely to be; and the less normalized the database is, the more convenient native IO is likely to be."

Nope. The issue has to do with the type of programming you're doing. Again, it's the wave nature vs. particle nature concept I've been trying to get across.

Take a simple price lookup. If you want to get the total for a line, you simply multiple the quantity by the unit price. This ought to be a simple SQL, right?

Now let's add real business rules. First, items have many prices. The price you get may well be different than the price I get. It may depend on the customer, or the customer class. It may depend on whether I have a coupon, or whether there is a sale going on this week. It may depend on whether I've reached a certain threshhold of purchases this order, or even this year. It could even depend on other products I've bought.

And that's just the unit price. I haven't talked about taxes, which opens up a whole different set of decisions.

Lots of things go into figuring out the price, and the vast majority of them look something like this:

READ FILEA BASED ON FIELD1
IF FIELD2 IN FILEA IS NON-BLANK, THEN READ RECORD FROM FILE2 USING FIELD1 AND FIELD2 AND USE FIELDB
OTHERWISE, READ FILE3 USING FIELD1 AND CURRENT CUSTOMER NUMBER
IF FIELDD OF FILE2 IS > 0 THEN USE FIELDD OTHERWISE READ FILE4

And so on. This is the database-driven "particle" nature of real application programming that simply doesn't translate to SQL. By the time you manage to create an SQL statement that handles that level of complexity, it's 50 lines long with tons of CASE and COALESCE statements and SUBSELECTS and WHERE EXISTS to the point where it's not readable, much less debuggable. Trying to add a single new rule often requires rebuilding the whole thing from scratch.

Whereas with record-level I/O, it's a breeze. You prototype the rules the way I laid them out and basically you just execute those instructions.

And here's what's even more important: it's relatively easy to view the logic branches to identify ways to optimize the processing based on expert knowledge of the database. While SQL can indeed perform optimizations, those optimizations are by definition more general in nature, and don't take into account the real world (and possibly changing) population of the database; it can't! Only a human being can make those sorts of decisions.

SQL is the wrong choice for these situations, just as RPG is the wrong choice for heavy stream file processing. There are no "mitigating factors" or "special circumstances" under which SQL somehow becomes better. It just never is. The beauty of the System i as opposed to, say, an Java- and Oracle-based system is that we have a choice with RPG and they really don't.

So RPG and native I/O is the best solution for that problem set, while SQL is the best tool for different problem sets. The beauty of the System i is that it's the only system that allows both.

Do your benchmarks. You'll see significant performance benefits for single-record fetches and even higher benefits for single-record updates. The numbers may astonish you.

Anyway, I'm done here. Until you prove that SQL is faster than native I/O, I will continue to be amazed and horrified that so many otherwise intelligent people would continue to use a slower, harder to maintain and harder to debug tool. It's simply contrary to common sense, and another reason why business people distrust programmers.

Joe

Max, I would like you to perform a thought exercise and tell me why you think a single-record SQL statement "SELECT * FROM FILEA WHERE CUSTOMER = 1000" would perform faster than a CHAIN. What overhead is there in the CHAIN that is not in the SELECT? On the other hand, you can probably spot a number of things where the SELECT has more overhead than the CHAIN.

Does this sort of common sense thinking come into play when you make your decisions? Or is the computer just a big magical box where the laws of physics don't apply? For example, maybe parsing the SELECT statement takes NEGATIVE CPU cycles, which would make the SELECT statement faster!

I'm sorry if that sounds a little condescending, but since this entire blog has now devolved to an attempt to justify SQL where it clearly doesn't belong (as well as a bit of really nasty RPG bashing), I don't feel I'm really diminishing anything.

Joe

First of all, Joe, I don't engage in RPG bashing. I've used the language since 1972 and I have enjoyed growing with RPG through the years. I've seen lots of styles of RPG coding and I've written a lot of code. I like free form RPG IV written in a highly modular style, and I know from experience both that systems with layered architectures can be written in RPG, and that SQL procedures can make positive contributions to code structure.

Second, your opinion that there are places where SQL does not belong is your opinion. Some will disagree with you. It has a lot to do with architecture, database design and coding style.

Your programming example assumes that SQL must do in one statement what your native IO does in as many reads and chains as it needs plus the interwoven business logic. There are other ways of structuring a solution, some of which could use SQL. For example, assuming a modular design, you might start with a:

extPrice = qty * getPrice(item,cust,p3, p4);

The getPrice procedure could use a SQL statement to get the component values and then use a Select/When logic structure to determine the price to return. Not messy; easily comprehensible.

I do not see the issue as whether a chain is faster than a single-row retrieval, but whether the difference is significant anymore, given the performance increases of iSeries SQL. I prefer benchmark tests to thought experiments.

Being pro-SQL does not make me anti-RPG. I like this language. I'd like to see it survive.

Max, I didn't say you were bashing RPG. I said the thread was.

Second, as to where SQL belongs, I expected this particular argument. When someone tries to imappropriately position SQL, the discussion always degenerates to "the performance loss isn't significant." You can set your watch by it.

Well, if performance isn't significant, what is, Max? We've already determined that SQL isn't platform independent, and that it's difficult to debug. So what is the benefit I'm gaining in return for the performance?

It's clear that your primary purpose in this blog is to justify SQL over native I/O, and that any argument I make will be met with some version of "SQL performs well enough", which is the final refuge of the SQL zealot.

Well, let's put this to the test, shall we? I'll challenge you to a simple programming exercise. I'll create a pricing scenario similar to the one above but with specific details. We'll then write our programs and compare the results.

Joe

I have been at clients where they only allowed use of SQL for DB access. Primarily because IBM said so. It appears to be IBM's direction to move exclusively toward SQL vs native IO. Not sure why other than that is what everyone else does so it must be the cats meow.

I worked on a large billing project where there were millions of transactions to process in a short window of time. The DB was Sybase and it could not handle the volumes. So the developers copied all the reference tables into an ISAM Informix DB, processed the transactions (pricing can be rather tedious for billing eCommerce network traffic) and then bulk copied the results up into Sybase. This was a new system to replace an AS/400 using JDE. Gotta love it. Of course it had a GUI interface for table setups (yippie skippie).

I use SQL and native IO and find each have their place. If you have change management in place DB changes are a breeze. If you dont have change management, it needs to be a top priority to put in place (IMHO)

I am not anti GUI and pro 5250. But I do find it amusing when business's cut off their nose despite their face to follow the lemmings off a cliff.

That's a great idea, Joe! I enjoy exploring various coding strategies, and I love to code in RPG. And heck, this may give me an opportunity to see where I fall on the openminded/blockheaded scale. Let's do it!

As for what is gained by using SQL: In some cases SQL outperforms native IO, as our recent benchmarking showed for a decision support application. Also, the use of cursors produced from joined tables can offer an alternative to the code pattern of a read followed by conditional logic followed by a series of chains followed by conditional logic. In some of these cases there may be gains in both performance and code structure (depending on desired style of code structure). And SQL can offer a quick way of getting a set of data items from several tables with one IO action.

I think we both agree that where SQL significantly outperforms native IO it should be used, and where native IO significantly outperforms SQL native IO should be used. I think it is also the case that where both provide comparable performance, OTHER criteria will come to the forefront. These other criteria may in some cases favor SQL and may in some cases favor native IO.

If you're running benchmarks where SQL is outperforming native reads, you might want to a activate record blocking in your "F" specs, which gives a tremendous boost to the performance of native reads.

Also, if you're comparing native reads to something like:

select flda, fldb, fldc from filea

where filea has a large number of fields, then you might want to create a logical file or view that has been reduced to just flda, fldb, fldc, to make a more apples to apples comparison.

I keep hearing that SQL outperforms native I/O under certain circumstances, but in every test I've run, I was able to come up with a native method that produced the same results, faster than SQL.

This is not to say that I always advocate native I/O over SQL. Just that I tend to challenge SQL performance claims.

"Also, the use of cursors produced from joined tables can offer an alternative to the code pattern of a read followed by conditional logic followed by a series of chains followed by conditional logic."

Not to be contrary, but so what? To me, this statement, without some concrete examples, is really without substance. Unless the "alternate code pattern" has some measurable ROI, why does it matter?

The phrase "desired code structure" is the tail wagging the dog. If you have a predetermined code style, then this entire thread is justification, not research.

The other issue is "significant" vs. "comparable". Please define these terms. To me, a 10% performance difference is significant. What is it to you? My guess is that whatever number makes SQL "comparable" will be the answer. If I'm wrong, then please state a number.

I also still have some issue with the whole idea that if SQL is "comparable" then it should be used instead of native I/O. This denigrates ISAM access, which is just as valid an access method as SQL. SQL per se is not better than ISAM. Especially since your earlier post about doing a bunch of SELECTs followed by a big old WHEN construct is basically the SQL equivalent of ISAM access, and not a very good one at that.

Joe

Thanks, Nathan. The native IO strategies used logical files and the SQL strategies used DDL-created indices. This interactive app requires aggregation of data from a variable date range over a variable set of items involving a varying set of files, all determiined by user input. The SQL solutions used a "group by" over a subquery of "unioned" selects; the native IO strategies used CHAINs, SETLLs and READEs. The developers who wrote the native IO programs are, in my opinion, top-notch seasoned developers, and are not SQL zealots. I think this was a case where SQL was just the better performing option.

Gosh, Joe, I sometimes feel like you just want to argue with me. It seems you read more into my words than is intended. To treat your four points in order:

1. The ROI on coding style is reduced maintenance costs; that's why IT departments have standards. That's why structured programming became dominant, that's why modularity and layered architectures are recommended, and was likely a prime motivation for the development of object-oriented languages. Since most of the cost of an application is maintenance cost, this can be a huge ROI, if the adopted standards do in fact promote readability and lessen maintenence time.

2. By the expression "desired code style" I merely meant to condition the claim that there was a benefit of that nature only if the code style was a desired code style. I meant to respect the differences in preference which developers and departments have.

3. We may have to negotiate the definition of "signicant" and "comparable" so we don't argue over the results of our exercise. I would define the terms differently for batch and interactive settings. To me, in an interactive setting a difference not appreciated by humans is not significant; a 70% difference in milliseconds which cannot even be noticed by the user is just not significant in my book. In such cases other criteria should be given greater weight. For batch processing 10% seems a little low; 12 min. is significant over 10 min.? 34 min. is significant over 30 min.? I don't know if a 10% difference would play a major role in a design decision if other criteria were also deemed important. I would have probably picked 20% out of the air, but if you want 10% for a batch process I can agree to that.

4. I definitely did not say that if comparable, SQL should used. I clearly said that if the methods are comparable other criteria should come to the forefront. Is this not rational?

Also, you mischaracterized my suggested solution from the earlier post, where "select" was singular, not plural. And thinking about it further, the SELECT logic structure might be unecessary. Maybe something like this would work in the subprocedure:

Select COALESCE(cp.DiscPrice, sp.price, i.StdPrice)
INTO PriceToReturn
From ItemMaster i
LEFT OUTER JOIN SpecialPrices sp
ON i.item = sp.item
and sp.Override_Flag = sp.Override_Flag
LEFT OUTER JOIN CustPrices cp
On CustNumber = cp.CustNumber
and i.item = cp.item

But maybe we should just get on with our exercise, Joe. I am curious to test IBM's claims about improving SQL performance.

Even top-notch seasoned developers may miss performance enhancement techniques available in native access methods.

On the other hand, decision support applications are a good case where SQL is helpful, if not required. I'm the author of a CL command named SQL2STMF (SQL To Stream File) that dynamically generates SQL result sets and outputs them to stream files in fixed length TEXT, CSV, and XML formats, which are useful for data interchange between systems, generating files in batch mode for data warehouse applications, and such.

When it comes to transactions, database updates, and business logic like the examples outlined by Joe Pluta, I think native I/O is the best tool for the job. In some cases SQL can be more of a hindrance than a help.

Max and Joe I am interested in this test as well. Please ensure the transactions being processed are significant (ie 500,000 or more) and the pricing tables and other reference tables (ie customer master, item master, etc.) used have a significant number of rows to make it a realistic test (ie 50k to 100k rows or more). I would also like this proof of concept to use a normalized DB design.

I look forward to seeing the results.

Thanks,
Steve

Oracle Call Interface APIs

It had been suggested that Oracle may have developed APIs equivalent to RPG's RLA operations. I consulted our local Oracle Brahmin (DBA), and he says no, but the poster may have been thinking of OCI (Oracle Call Interface) APIs, which permit application developers to work closer to the database and wrest some control from Oracle over database work. OCI APIs can be used by many programming languages to gain greater control of the database, but the IO operations used are the standard select, insert, update, delete, etc.

So am I. Last time a ran a comparable benchmark was in 1999. The setup was something like this:
1. generate a file which represents the inhabitants of a small country or US state, say 10 mio records occupying a disk space that is at least 10 times the size of main memory, to make sure results are not influenced by memory disk caching. The records are identified by a personal identity number which is just a sequential number.
Write half of the records with native i/o, the other half with sql's insert statements. Let the job run on an unloaded system. Measure times.

2. Use a random generator to generate a random persons id as key field. Use native chains to retrieve about 5 mio records, then use sql's "select from" to do the same. Let the job run on an unloaded system. Measure times.

3. consider variations with update's and delete's.

Sql will come out bad i'm afraid, despite ibm's claims of recent progress, but lets see.

PS: most shops have a heterogeneous environment, besides the as400 there is the usual Windows Sql server or Unix Oracle server. Why not run the same test there (of course sql only) and compare systems?

Googling again, it looks like I was referring to the SQLLIB runtime library calls, but looking closer I see it's used by precompilers and undocumented.

Thanks for the correction, Max.

rd

This thread started off discussing I/O strategies, and what's clear from the discussion's transition to the RLA vs. SQL debate is that you want to isolate I/O code so you can pick-and-choose (if you think that's a good idea) and change your mind when you find out you were wrong or IBM or your company management changed the rules.

Early in the discussion Aaron Bartell suggested: "To put forth an initiative to encapsulate every stinkin file or piece of business logic without even knowing if it will be reused is a plain waste of time."

This is dead wrong.

Encapsulation is a programming strategy that should be used pervasively. The point isn't mainly for code reuse, but rather to manage complexity.

A professional programmer should no more attempt their work without programming tools, such as encapsulation, factoring, decoupling, etc., than a surgeon should perform an operation without sharp surgical knives. OTOH, I don't recommend amateurs attempting even the simplest surgery, regardless of the tools at hand. Similarly with programming.

This "encapsulation" stuff isn't "woo-woo" programming -- it's basic competence.

= = = =

As to SQL vs. RLA. I'm one of the "gray beards" in the industry, and I swear I must have met Joe Pluta back in the early 70's when I first started programming in COBOL on a mainframe. At least from my recollection there was this guy who sounded like Joe, ardently advocating that my cohorts and I continue to implement application I/O as channel programs written in BAL (assembler), rather than incur all the overhead of COBOL's built-in I/O. :-)

Before injecting a little reality into the SQL vs. RLA discussion, let me say that you don't need to "rip-and-replace" stable, working RLA just to use SQL.

And, it's fine if you want to selectively use RLA in new code where it makes a truly significant difference. Especially if you've isolated your I/O code so you can easily change it if the need arises.

But SQL is a "no brainer" for your I/O strategy.

SQL generally performs well when used appropriately and performs better than RLA in some cases.

Most importantly, IBM, along with the entire industry, has settled on SQL. IBM is putting all their DB work into additional functionaility and performance into SQL.

All the major development tools target SQL.

Java, .NET and LIMP Web application platforms all target SQL.

Almost all education, training, books, Web resources, and consulting revolve around SQL.

SQL on i5 has a HUGE amount of functionality that DDS and RLA+OpnQryF lack.

SQL is simple to code if you have the skill and don't have an "attitude."

SQL's column-oriented approach and its creation of access plans on-the-fly provide valuable "data independence".

SQL for i5 provides several performance advantages for "roll-your-own" CGI, as well as simplified "optimistic" concurrency that's essential for many Web applications.

As to benchmarking SQL vs RLA performance... For simple, high-volume batch processes that run in relative isolation (e.g., at night), you can get meaningful comparative results by simple benchmark scenarios.

For real world, multi-user, multi-application production environments, simple benchmarks break down. This cuts both ways, and trying to extrapolate from benchmarks that aren't run under a simulated multi-user load can skew the results either way. (I would put no stock in the "toy" test that Joe posed.)

My advice to iSeries developers and their managers who want to chart a sound development strategy for the future is this:

* Follow professional programming practices, including encapsulation, etc., in all your development as a matter of course.

* Adopt SQL as your standard practice for DB definition and access. (And limit RLA to exceptional cases, if at all.)

* Isolate I/O (of all kinds) so that most of the application code calls procedures (or "methods" or "services") that have an interface at a higher level than a file format (or SQL table structure).

* Visit IBM's "History of Computing" museum and catch the "Mummified Assembler Programmer" exhibit next time you're in Poughkeepsie.

Cheers!

-- Paul

I'm going to take exception with a few of Paul Conte's suggestions, hoping not to diminish the ones I agree with and arise from his insight and experience.

I'd suggest using DDS for base tables and logical files, except in the rare cases where BLOB and similar data types are required. I'd also suggest that storing references to stream files in records is better than using BLOBS in most cases. DDS is easier to work with. Stream files are easier to work with.

If you need SQL DDL in order to migrate database definitions to another platform, use iSeries Navigator to generate it after the tables have been created. iSeries Navigator is cumbersome for defining tables and views. If you need SQL views, I'd suggest using the RUNSQLSTM command against source members, instead.

SQL is an important element of IBM's server homogenization strategy, but that doesn't mean that RLA should be pigeon-holed or relegated to a corner as suggested, unless you think server homogenization is a good strategy for your business. It has been interesting to see a number of Java frameworks arise that insulate developers from SQL by creating RLA based interfaces around it.

Regarding benchmarks, it has been my experience that differences in I/O performance generally increase as the number of users, threads, and workloads increase. I wouldn't suggest that there is magic in SQL that makes it scale better under complex multi-user workloads.

Nathan, I may be going out on a limb here, since I do not know which frameworks or Java objects you are referring to, but when you mention that "It has been interesting to see a number of Java frameworks arise that insulate developers from SQL by creating RLA based interfaces around it", this might be misleading for those who don't know Java.

I assume you are referring to those data mapping objects which in a 5-tier layered application separate the business logic domain from the data source layer. To compare those to RPG RLA operations is imaginative and perhaps insightful, but we may be talking apples and oranges here. The role these objects play and the effect they have on OO code structure is quite different from the role of RPG RLA operations.

There are a variety of approaches to providing this layer of data mapping or persistence objects, including the development of persistence frameworks like Hibernate, and I am assuming you are referring to this category of Java objects. If you are referring to some other category of Java objects, let us know more about the details.

I am curious about a few things:

1) What change management tools is everyone using for deployment of SQL databases (and revisions). I doubt using techniques like RUNSQLSTM over production environments would fly with SOX auditors.

2) As far as data encapsulation it seems to be the consensus of those in the know to create a common structure to be used across business process for data retrieval regardless of if it is needed or not. Or is what is being bandied about to have proprietary data retrieval structures for each business process (that seems unrealistic).

3) How many layers of Db encapsulation are we talking about. Are those that are in the know adding an additional layer under these common structures (i.e. setters/getters).

4) I am also curious if there is a method similar to SETLL to check for existence via SQL.

Thanks,
Steve

Hibernate is one of the Java database frameworks that come to mind, though I wasn't thinking of it specifically. It's a little hard to discuss specifics because of the number of frameworks that have sprung up, but it seems generally accurate to say that they tend to insulate developers from SQL.

I agree that it's not an apples to apples comparison, and I don't want to trivialize the architecture of OO frameworks, but I occasionally see methods exposed through record, and field objects that parallel native RLA operation codes, XML based data definitions that parallel DDS, and table objects that parallel "F" specs. To me it doesn't seem overly imaginative to note parallels.

I hope you feel free Max, to share your thoughts about how database frameworks improve the structure of applications.

Steve - I'm not suggesting that end users have unfettered access to RUNSQLSTM. Just that it allows SQL DDL statements to be stored in source members and provides a convenient way of creating SQL Views.

I'm not against SQL. I do think IBM's server homogenization strategy (SQL, n-tier), combined with it's server assimilation strategy (Java), competes against and threatens its platform integration/server consolidation strategy, and may even be killing the System I as a platform.

With server homogenization/platform assimilation the natural tendency is to deploy to the lowest common denominator (Wintel).

I don't think it is overly imaginative to note high-level parallels between system architecture strategies; in fact it can be insightful. But we also have to be careful to note differences in motivation and effect, and perhaps even historical context (RLA predates RDBMS), in order to keep our insights truly insightful.

The emergence of the data mapping layer illustrates an important point Paul Conte made above: "Encapsulation is a programming strategy that should be used pervasively. The point isn't mainly for code reuse, but rather to manage complexity."

As patterns of data mapping activity were recognized -- to map data between the IO access objects and the business logic objects -- the motivatation to encapsulate in order to manage complexity led to the idea of a data mapping layer between the BL and IO layers. This is now I think the standard model for J2EE development.

You don't need persistence frameworks to do this. It can be done with Enterprise Java Beans or other Java objects. The persistence frameworks provide consistency and convenience. They are optional. A data mapping layer can be implemented without them.

Steve, although your questions are addressed to "those in the know" (and I don't fall into that category), I would like to take a shot at offering some answers:

1. We use a popular change mgmt system here, but I don't think I'm supposed to mention products by name. But our DB is still defined by DDS anyway, except for DDL indices. SOX doesn't apply to us, so I am ignorant of SOX compliance issues.

2. Encapsulation is a coding strategy, and ideally it would be pervasive, but in reality there are always those situations where someone finds an excuse to bring the IO into the BL with the hope there are no unintended consequences down the road. Also, most RPG apps alive today were probably developed before encapsulation became more common in the RPG world.

3. There should only be one layer of DB encapsulation, but in the Java world there is now usually a second data mapping layer between that and the BL layer. In RPG the data mapping role would probably be played by shared data structures, but data mapping procedures could mediate between IO procedures and BL procedures/modules if this made sense under the circumstances.

4. Yes, there is an EXISTS function which tests for the existence of certain rows. It is generally used in a subquery, either to condition an action or to set an existence flag.

Max: thanks for the replies. I have used several change management systems and none support SQL DB implementations. I did work as a Sybase Dba years ago and used a desktop tool that would redeploy the database based on the schema definition, however after generating the SQL script it would regenerate the entrire database. That was fine during development however it would have been a real headache post implementation and moving foward with new releases of the software.

I have had similar issues promoting SQL scripts for triggers and stored procedures. When you move from a development to test to QA to production environments having qualified libraries can be problematic.

Re several of Nathan's comments.

***
Unless you have some legacy or existing tool issue, SQL is definitely the better approach to define tables and views.

There are performance advantages for some (even many) tables and indexes.

The functionality of views is far superior to logical files.

To me DDS and SQL are both easily mastered, and there's no significant "ease-of-use" advantage to either, but more tooling does, and will, support SQL.

***
Homogenization isn't a reason I listed to use SQL. There are plenty of others that support the case for SQL.

***
Using toy, single-run benchmarks can provide some performance insight, but they aren't reliable to predict overall behavior when you're talking about I/O in a real-world mix of concurrent, multi-user applications.

It's not just that viewed in isolation, some operation all of a sudden becomes relatively faster than what it's being compared to. It's that the full load may shift the proportional impact of both methods under test. And, this can manifiest itself by revealing more severe SQL performance problems in a particular context, as well as demonstrating that SQL is adequate or better than RLA.

-- Paul

EXCELLENT!

Paul, I'm glad you joined, because I want everyone to see some of the statements that are peculiar to the SQL zealot.

"SQL generally performs well when used appropriately and performs better than RLA in some cases."

This is "what you don't know will hurt you". Note that he glosses over the fact that in some cases SQL performs far, far worse than RLA. Those cases (record at a time updates, navigating through the database based on field values, and so on) are the things that application programs do.

"Most importantly, IBM, along with the entire industry, has settled on SQL. IBM is putting all their DB work into additional functionaility and performance into SQL."

Another strange assertion: Everybody is doing it, so should you! If that had anything to do with reality, we'd all be using .NET to access the iSeries database as a big SQL server. And then we'd just get rid of the iSeries in favor of MS SQL Server!

Of course, Paul has written books on SQL Server...

"All the major development tools target SQL."

Following this line of thought, no major tools support RPG either. I guess it's time to learn .NET!

"Java, .NET and LIMP Web application platforms all target SQL."

All these other platforms target SQL because THAT'S ALL THEY HAVE. The single-mindedness of the SQL zealot seems to lead to slightly blurred thinking. BTW, I THINK he meant "LAMP" applications (Lotus Apache MySQL PHP) but "LIMP" certainly has it's own concepts.

"Almost all education, training, books, Web resources, and consulting revolve around SQL."

Another argument for medicority. This one would certainly have us dumping RPG. Hell, it would have us dumping the iSeries, since almost all training (etc.) is for Windows or Linux.

"SQL on i5 has a HUGE amount of functionality that DDS and RLA+OpnQryF lack."

Yes, but that simply means we can use either one as we need it. It's called "the best tool for the job". The SQL zealot's vision is called "wen all you have is a hammer, everything looks like a nail."

"SQL is simple to code if you have the skill and don't have an 'attitude.'"

Simple SQL is simple to code. Complex SQL is a pain in the rear. It is far easier to comprehend the individual lines of a good RPG program than it is to understand complex SQL. But we can see about that when I create the benchmark programs.

"SQL's column-oriented approach and its creation of access plans on-the-fly provide valuable 'data independence'."

We now begin the buzzwords. I can just as easily tell you that SQL's knowledge of the physical locations and name of tables as columns causes unacceptably tight binding between the business logic and the databess.

The truth lies somewhere in between. SQL does allow in certain instances for you to be able to make minor modifications to your database without having to change your code. Similarly, RPG allows you to make minor changes in your database without having to change your code. Sometimes it's as simple as a recompile, and with judicious use of logical views, not even that.

However, this is only for minor changes to the database. If you do anything that isn't just minor structure changes -- rename a field, change a field's attribute, move a field from one file to another, change how a field is calculated -- these all require the same level of changes in SQL as they do in RPG. Except in RPG we can typically use the program reference (DSPPGMREF) to quickly find all the objects that use that file. There is no equivalent tool for SQL access.

"SQL for i5 provides several performance advantages for "roll-your-own" CGI, as well as simplified "optimistic" concurrency that's essential for many Web applications."

Or, you could use a simple JSP Model II approach with an I/O server program using a combiniation of SQL and native I/O that will kick CGI's butt.

And now for the final ones, folks. The SQL guys will ALWAYS, ALWAYS, ALWAYS do this:

"For real world, multi-user, multi-application production environments, simple benchmarks break down. This cuts both ways, and trying to extrapolate from benchmarks that aren't run under a simulated multi-user load can skew the results either way. (I would put no stock in the "toy" test that Joe posed.)"

The refuge of bad performance is to say the benchmarks are skewed. Losers blame the umpires or the refs...

Do you think Paul would be saying this if he didn't know that benchmarks show native I/O blowing the doors off of SQL?

"My advice to iSeries developers and their managers who want to chart a sound development strategy for the future is this:"

My advice to you all to go to their the iSeries Core Decisions Roadshow page and see what Paul and Carson Soule are selling.

http://www.iseriesnetwork.com/coreroadshow/

Read the abstract for the first session in which Carson states:

"Is there still a role for 'traditional' ILE RPG and ILE Cobol programs? Or is your best strategy to develop applications that run under .NET on Windows or as CGI programs on Linux and rely on the iSeries for just your database server?"

That's Paul's vision of the future.

You decide.

Joe

Joe Pluta and Paul Conte are both highly regarded, deeply experienced members of our profession, and I regard both of them as "experts" whose views deserve careful consideration. I am pleased that they have chosen to make contributions to this discussion.

They represent different views which are both common in our field at this time. I surmise that behind the SQL vs. native IO debate there is actually a more fundamental difference in vision about system architecture and code design.

I think the issues here are important, and I hope our strong feelings about some of our positions do not get in the way of openminded professional dialogue. Much can be learned from this dialogue if issues are clearly articulated and fairly assessed.

A few observations:

1. The idea of "use the best tool for the job" is of course sound as a starting position, but I note additional points:

a) Performance may not be the sole or primary consideration, especially where the performance difference is not significant; and

b) Departments may wish to limit the extent to which SQL and native IO are mixed by establishing a preference or some decision rules.

2. Native IO will not always outperform SQL; our recent benchmark had SQL outperforming native IO by an average factor of three. Performance differences will vary by the processing task, database design, solution design and other factors. Of more interest to me is:

a) the circumstances under which one method significantly outperforms the other;

b) the significance and consistency of performance differences; and

c) those circumstances under which the differences are hardly ever significant.

3. I don't think we can infer Paul Conte's vision from somebody else's unanswered question.

4. There is an elegance to SQL if you acquire a taste for it. :)

"LAMP: Lotus Apache MySQL PHP"

HEE HEE! IBM would love that! I must be very tired.

LAMP is of course LINUX, Apache, MySQL and PHP.

Joe

Max, I don't understand. I've stated clearly multiple times the circumstances under which native I/O outperforms SQL.

Native outperforms when reading or updating single records. SQL outperforms when multiple records are involved.

With reads, the crossover point is about 10 records, with inserts and updates, about 100 records.

When doing normal OLTP operations, such as updating an order, SQL underperforms native by as much as a factor of ten.

Your benchmark was a data mining query, which is the best case scenario for SQL. Please use SQL for those purposes.

But do not use that as an excuse to justify SQL for OLTP, because it's wrong.

In case I wasn't clear enough: native I/O ALWAYS outperforms SQL for single-record updates, which are the primary activity of OLTP business applications.

SQL (nearly) ALWAYS outperforms native I/O on set-based instructions, including ad hoc queries over large databases.

Conclusion: use SQL for queries, and native I/O for OLTP.

So which part of this leaves ANY question in your mind?

Notice that I am not saying one practice is better than another; I'm simply saying that you should use the right technique for the right job. SQL zealots push a single technique for everything, and in doing so doing downplay perhaps the best performing solution on the planet. That's dumb.

Anyway, no more time needs to be wasted here. I may not even bother with the benchmarks, since it's clear that the SQL lovers will simply say that benchmarks don't matter. I have real work to do with people who aren't trying to justify a pre-conceived agenda.

Joe

P.S. "I don't think we can infer Paul Conte's vision from somebody else's unanswered question."

It's not "somebody else", it's Carson Soule, Paul's partner in the iSeries Core Decisions Road Show, talking ABOUT that same road show.

The question which remains, Joe, is the significance of the difference. If we are talking nanoseconds or milliseconds of difference per OLTP transaction, then that is simply not significant enough for performance alone to decide which tool to use, when there are other considerations, such as code architecture, which are important to the decision.

I know from personal experience that SQL could support OLTP back under V4R4, and I believe that SQL performance is even better today than it was then.

If you are writing traditional RPG programs where IO operations are intertwined with business logic, then native IO makes perfect sense; but if you are writing an application where the desired architecture is to encapsulate the IO activity in separate service program procedures, then I think most programmers would choose SQL. In the absence of hard evidence, I would assume no significant performance difference between the two approaches.

About improved SQL performance, IBM has just released a new Redbook which explains the differences between the older Classic Query Engine (CQE) and the new SQL Query Engine (SQE) available since V5R2. The new engine is faster for a number of reasons, including that of moving the optimizer below the MI (machine interface) into the SLIC (System Licensed Internal Code). This redbook can be downloaded from http://www.redbooks.ibm.com/redpieces/abstracts/sg246654.html

The truth comes out! This entire exercise was an attempt to rationalize an architecure of encapsulated SQL.

"if you are writing an application where the desired architecture is to encapsulate the IO activity in separate service program procedures, then I think most programmers would choose SQL."

And I, on the other hand, don't think a single competent programmer would ever suiggest this approach. But what does what you or I "think" matter? This is a waste of time, Max.

"In the absence of hard evidence, I would assume no significant performance difference between the two approaches."

I've given you hard evidence that there is significant difference. You have in effect stuck your fingers in your ears and shouted "I CAN'T HEAR YOU!" every time I did.

I consider this entire exercise to have been a colossal waste of time, and intellectually insincere.