Topic
5 replies Latest Post - ‏2012-08-30T22:43:24Z by barbara_morris
SolidAu
SolidAu
10 Posts
ACCEPTED ANSWER

Pinned topic Replacing RPG file operands with SQL

‏2012-08-30T16:58:18Z |
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.
Updated on 2012-08-30T22:43:24Z at 2012-08-30T22:43:24Z by barbara_morris
  • scjt2001
    scjt2001
    18 Posts
    ACCEPTED ANSWER

    Re: Replacing RPG file operands with SQL

    ‏2012-08-30T17:25:15Z  in response to SolidAu
    You can write a handler to convert these operation to use SQL behind the scene.
    • SolidAu
      SolidAu
      10 Posts
      ACCEPTED ANSWER

      Re: Replacing RPG file operands with SQL

      ‏2012-08-30T17:30:00Z  in response to scjt2001
      I have no idea what a handler is. Where can I read about this?
      Thanks.
      • JonParis
        JonParis
        113 Posts
        ACCEPTED ANSWER

        Re: Replacing RPG file operands with SQL

        ‏2012-08-30T19:05:45Z  in response to SolidAu
        In 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!
  • SolidAu
    SolidAu
    10 Posts
    ACCEPTED ANSWER

    Re: Replacing RPG file operands with SQL

    ‏2012-08-30T19:54:12Z  in response to SolidAu
    Thank you Jon. This certainly gives me a place to start. Completely new concept for me. I read some of the info already. This is going to be fun.
  • barbara_morris
    barbara_morris
    383 Posts
    ACCEPTED ANSWER

    Re: Replacing RPG file operands with SQL

    ‏2012-08-30T22:43:24Z  in response to SolidAu
    The 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.