Using the Blueprint

In the previous post, Dan Cruikshank illuminated the concept of creating a database "blueprint" through the process of data modeling.  Continuing my conversation with Dan, I asked him about using the blueprint, i.e. data model.  Specifically, using SQL to access the data (the answer is YES!) and whether or not it makes more sense to use embedded SQL in a traditional high level language like RPG, or to use separate, and modular Stored Procedures.

Dan's answer and insight follows...

__________


The answer to the question as to use SQL Stored Procedures (or External Stored Procedures) versus embedded SQL in a monolithic program depends on the environment in which the application exists. What that means is if you are developing both host centric as well as external (i.e. web or mobile) applications which need to perform common IO functions, such as consuming result sets, inserting, updating and deleting rows from and to a common set of tables, etc., then using stored procedures would be my recommendation. If all development is strictly host centric (should not be in this day and age) then embedded SQL would be ok, but not best practice, in my opinion.

From an application methodology perspective, we tend to recommend a Model/View/Controller (MVC) approach for design and development, where the data access and user interface are separated from the high level language code. In the case of data access this would be done via stored procedure calls.

This is not a performance based recommendation; it is more about re-usability, reduced development costs and shorter time to market. Done properly, better performance is a byproduct. Not to mention higher value returned to the business!

In addition, with the added SQL DML support for result set consumption in IBM i 7.1, it is now easier for the host centric applications (i.e. RPG and COBOL) to share a common set of procedures which return result sets. From an SQL DML perspective, prior to 7.1 this was limited to insert, update and delete procedures although accessing a result set from a stored procedure was available via CLI calls (not widely used in traditional IBM i shops).

__________


If you need additional knowledge, skills or guidance with what Dan is sharing, please do not hesitate to reach out.