I think there is not one topic in the IBM i community which has been talked so much about as modernization. And here I am also talking about it :) .
The modernization drum has been beaten for as long as I can think of and every time I hear about it it is just about putting lipstick on a pig and moving things to the web with the least effort possible no matter what the result looks like or what the underlying programs still look like. But I won't handle the frontend or web topic here.
From the top of my head I only remember a single article where they have used a staged approach with first bringing it all to the web with a screen converting product but then also doing the next step and doing a full rework of the whole application. Most people are only doing step one but missing the really important step two.
But this post is about the backend accessing the database and the first thing I hear is
Use SQL views! Abstract the database tables away and use views!
In some cases it probably makes sense. BUT NOT IN ALL!!! Come on! Use your mind! If the database table has already names that make sense, if there is no additional logic to be captured in views ... if there are no not used columns or misused columns ... (fill in some more conditions) ... then why should I use a view?!?!
What I also hear and read a lot is
Make a database access layer!
In my opinion this is something which has been swept into the IBM i community from other platforms where you don't have such a good database access method like we have in RPG where database access is built into from the start. On other platforms you don't have native database support at all. On these platforms database access is much more complex and it all comes with additional software libraries, keyword ORM. Some might say now
Hey, but doesn't f. e. Java has JDBC and JPA?
Yes. That is right. But that is something which doesn't come with the stock JRE (ok ... JDBC does come with standard JRE). The JPA interfaces come with the JEE Runtime and then you still have no implementation to work with. (I won't cover JDBC here because this is not something you would like to work with in the first place when using Java and like to access the database). For JPA you need a JPA implementation (like EclipseLink or Apache OpenJPA) and additionally the database driver so how much built-in is it really? I don't mean that it doesn't work. I had developed and maintained a project for seven years which used JPA on an x86 based platform and it worked great (also much thanks to OSGi, which will soon move under the Eclipse umbrella, and Apache Karaf) but my opinion is that it is not as much built-in as the database support is built into the RPG language.
So coming back to the database layer ... I don't think that you need an additional layer in RPG that does nothing else but database access. So rather than having like two or sometimes three layers for justing getting the data to the next level (database layer, business layer, transformatiton layer for transforming from internal model to external model) I like to put everything in a single layer. I don't say that you never need any additional layer but only a single one ... but use your mind. Start with the simple approach and look how far it takes you and at what point it doesn't suffice. And how much more does it cost to have a more complex approach and is it worth it? What extra value does an extra layer gives you which you really need?
The simple approach for me would be to optional make SQL views if needed or just plainly use the database tables. Don't blindly make SQL indexes. Use visual explain to check where you need an index and where the database engine doesn't use any index you created in good faith. Start with one layer to access the database and make your data available to any layer which presents the data to the frontend. Put a data structure for the data and necessary prototoypes in a copybook to let the next layer interface with the data access layer. Don't let any database specifics trickle through to the next layer. By this it doesn't matter if the next layer is a 5250 program, any other service program or a web service done with ILEastic. And by doing this it doesn't matter if some or all of the data comes from the local database or if some or all of it is pulled from some other (web) service.
It does not matter! ... and it shouldn't!
So if you really have read the article this far you probably already got the message I try to bring to you with this post
Don't always generalize!!! Use your mind!
Don't make a database access layer just for the case of having such a layer especially if it doesn't make anything but a straight read and write without doing anything else. For some data I don't even write any layer but let the last layer access the database directly because the data doesn't need any additional management and is extremely simple and rather static and in some cases also read-only. In this case the database itself is the interface to the data. Is this the best way for all cases? NO!!! But you need to decide that from case to case. There is no one-size-fits-all!
So the overall message is
Use your mind!
Have a great new year!