Right or wrong my shop is considering replacing RPG operands(READ, WRITE, CHAIN, UPDATE) with SQL statements.
This project could run into well over a 2000 hours.
I have been thinking about this and came up with this thought.
Would the IBM RPG development team consider changing the RPG compiler to convert the RPG file operands to SQL statements under the covers?
I have looked at compiler listings for RPG programs that contain SQL statements and see that they are converted into RPG statements that call programs.
Maybe the same could be done with the RPG file operands.
This topic has been locked.
5 replies Latest Post - 2012-08-30T22:43:24Z by barbara_morris
Pinned topic Replacing RPG file operands with SQL
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-08-30T22:43:24Z at 2012-08-30T22:43:24Z by barbara_morris
scjt2001 270001YSH518 Posts
JonParis 1200007C6K113 PostsACCEPTED ANSWER
Re: Replacing RPG file operands with SQL2012-08-30T19:05:45Z in response to SolidAuIn this article we referenced an excellent piece by Dan Cruikshank on the move to SQL. http://www.ibmsystemsmag.com/ibmi/developer/modernization/A-Debate--DDS-vs--DDL/
Can't emphasize enough the need for tools to help you. Against our advice one client of ours went ahead with a conversion on their own with no tooling to assist and it was an unmitigated disaster in part because all of the opportunities for decimal data errors, date errors, etc. were not covered.
Dan and his team also have developed RPG Open Access handlers that convert to SQL under the covers. He has published the basics of the code they used here on Developer Works: http://www.ibm.com/developerworks/ibmi/library/i-roaforsql/index.html - the code can be downloaded.
This will not introduce you to to Open Access - for that try this: http://www.ibmsystemsmag.com/ibmi/developer/rpg/Getting-a-Handle-on-RPG%E2%80%99s-Open-Access/ which also links to some other articles.
I can give you more references if you need but these should get you started.
By the way - if you decide to go this way, Dan and the team can probably save you some money by selling you their tooling then you don't need to reinvent the wheel!
barbara_morris 120000DX5W383 PostsACCEPTED ANSWER
Re: Replacing RPG file operands with SQL2012-08-30T22:43:24Z in response to SolidAuThe RPG compiler couldn't change the I/O opcodes to be handled as SQL statements. That simple-looking call to SQLROUTE that you see in the generated RPG source isn't anywhere near as trivial as it looks. The SQL precompiler puts most of the information about each SQL statement into a hidden part of the program object, and SQLROUTE somehow figures out where to get the rest of the information about the statement from its parameters.
For a conversion from RLA to SQL that will take full advantage of SQL, I think the change should involve an analysis of the entire handling of the file, not just each I/O individual statement. For example, if you have a loop that does a read and then conditionally does an update, you might remove the loop and code just a single SQL UPDATE statement.