RPG or SQL?

In a word… YES!

Seek not to know the answers, but to understand the questions 

I am often asked questions that involve some form of “SQL vs. RPG”.

I have come to believe that the real question is: should I use SQL to access and process my data, or should I use RPG record level access combined with my own program logic.

Of course, the answer is: it depends.

SQL is the language of database. It has nothing to do with formatting or presentation. SQL relies on a high level language to get data to/from end users. SQL embodies the latest and greatest features, functions and techniques for getting the most out of DB2 for i.

RPG is the most popular and most used language to solve business problems on AS/400, iSeries, System i, IBM i (take your pick). It embodies the latest and greatest features, functions and techniques for getting the most out of IBM i.

So, what to do?  As I stated earlier, it depends.

Let's break the topic up into two parts:

1) Existing applications

2) New applications 

Existing Applications 

Let me say right up front, there is no reason to modify or throw out existing programs that happen to be written in RPG (also no reason to replace record level access with SQL statements in an existing program). There is no defendable rationale for this behavior. RPG with native record level access is fast, efficient and a good return on investment - past or present. Unless of course these programs no longer meet business requirements...

Given that SQL is the language of database, and the industry standard, IBM i native record level access has not been significantly enhanced for many years. As such, a lot of new and powerful capabilities inside of DB2 for i are not accessible from high level languages via record level operations.

For example, you want to include the ability to store a potentially large and variable character field for your customer’s comments - a character large object (CLOB) column type is available to meet this requirement. Ah, but CLOB columns are only accessible via SQL.

Or you need to store XML in your database. IBM i 7.1 provides the ability to not only store XML as a specific column type, but also to invoke DB2 functions and procedures to process the XML. Once again, this feature is only via SQL.

Of course, as soon as you have this CLOB or XML column in your database, someone will want to search the column for particular words or phrases. Something like, find all the customer comments that contain the word “dissatisfied”. The free of charge license program product OmniFind Text Search Server for DB2 for i (5733-OMF) will let you create a text index over the (CHAR, VARCHAR, CLOB, BLOB, XML) column. And SQL will allow you to issue a linguistic text search. Nice.

Not Keeping Up Costs Money Too 

I’m reminded of a past conference audience member who raised their hand when I asked "who is using Microsoft SQL Server?" This particular person was a long time RPG programmer who had recently embraced the PC server world. When I followed up and asked with genuine interest "why are you moving data to SQL Server and processing it there?” the answer was "we need to do some searching of text data and couldn't do it in RPG".  Say WHAT! After calming down a bit, I reminded the person that built right into their beloved IBM i was the ability to index and search text documents residing in a DB2 column. There was no valid business or technical reason to abandon DB2 for i in favor of SQL Server; especially when the data originates in IBM i.

Since I'm a database performance and scalability aficionado, I also like the idea of cheating using an encoded vector index (EVI) when aggregates are included and maintained within the DB object data structure. For example, when processing a user request to report revenue totals by year, quarter and month, DB2 can access the existing results within the EVI and avoid doing all the work of reading and summing up the rows. Again this capability is available in IBM i 7.1 but only if using SQL to issue the query.

Speaking of performance, avoid deriding SQL’s speed without first understanding the impact of proper indexing, fair share of memory, optimization goal and reusable ODPs. While DB2 for i does not require high levels of administration, some basic knowledge around how the query optimizer and database engine actually work is a critical success factor.

There are hundreds of examples where DB2 for i has capability built in and ready to go. At the end of the day, if you have existing applications that are unable to meet new or changing business requirements, selective application and database modernization (or re-engineering if you will) is likely required. This modernization will need to include the use of SQL if you want to fully take advantage of what's inside IBM i.

New Applications 

In terms of programming languages, I subscribe to the "get on with it" school of thought. In other words, it is very easy to spend all your time, energy (and money) maintaining a state of analysis paralysis debating which particular language is best, or most popular today. Simply put, if the language meets your business and technical requirements, use it. And if that happens to be RPG, be comfortable in the fact that this language is robust and proven. Go ahead, mix and match, use and reuse if suits you. RPG is here to stay.

When it comes to information management and data processing in new applications, I am a bit more stringent in my advice. The recommendation is to use SQL. It is the best and only way to achieve:

• High levels of database centric processing
• Set at a time efficiency
• Advanced information management

By taking advantage of SQL, you will gain more productivity, higher performance and more scalability for information management and data processing. You will get the best return on investment by embarking on good data centric design through proper data modeling, and by allowing DB2 to do as much of the data processing as possible via set based operations. By the way, when it comes to optimizing queries, DB2 for i is state of the art.

So, whether you are updating an existing work horse application, or developing a new system, with IBM i you can have the best of both worlds. Go ahead, embed SQL into RPG!  Feel comfortable and secure in using the appropriate language(s) and techniques at your disposal. While the engineer is always protective and proud of his or her tools and techniques, meeting the needs of the business is job one.