COBOL sub-routine as an access module for a table

COBOL technical questions
1

Hi,

Has anyone worked on a project where you have written a code with similar specifications writtten below? If yes, can you share your experience on how good or bad this is compared to the traditional approach of embedding the SQL directly in a module?
SPEC:
Lets assume that we have a table called EMPLOYEE. and an application written in COBOL.
Instead of embedding the SQL to access this table in every program that has to access this table, we create a sub-module that contains all the SQL statements against this table. So whenever there is a need to access this table, we just call the sub-module with enough input.

1 Like
2

Hi madhubalan

I do not think it is an approach you want to take.
I see no advantages to having all SQL to a table in a single program.
What we do where I work is a have almost every single SQL in its own program.
This has some disadvantages, compared to having the SQL in the program that needs the data, but it enables you to reuse the individual SQL statements.
One advantages is that you can create programs that only contain business logic, enabling you to stub calls to your SQL programs, making it possible to create true component tests.

The major disadvantage to having all your SQL in one program, is that if you change the interface to that program, you will have to fix every single program that uses the program.
If you have SQL in individual programs, and you need an interface change, you only need to change the few programs that use that SQL.

This is what we have been doing in the past, we are moving towards promoting individual SQL and programs to each service.
This gives the following advantages:

  • your SQL only needs to fetch the data that service needs, thus saving CPU.
  • if you need more data ie. change the interface, you only need to update two programs, the caller and the program with the SQL.
  • since this service is the only user of the SQL, you can throw away the SQL when the service reaches OEL, without having to consider other users.

I hope this helps you

Regards
Rune

3

Thanks you so much for you detailed explanation, Rune.

even if there is a situation where “where clause condition” is repetitive, would a stub not be more beneficial instead of writing many queries with same where clause?

/Madhu

4

That depends on what you value, and the tools you have available.

If you have tools to easily create and modify SQL queries, then you can reach a point, where it is faster to create a new module, than to find the one you need.

The “stub” might be fetching more columns than you need, resulting in wasted CPU. This might not be a problem if the SQL is only run a couple of thousand times a day. But when you run it millions of times a day, it adds up.

And there is the issue of maintaining your services. If the service is the only user of a SQL, then you can modify it as you want, to, without having to consider other interfaces.

There are exceptions to this. You might be exposing data that a lot of programs need. But then you would be exposing a clearly defined interface that almost never changes. An example could be a currency exchange rate program. You don’t want anybody to access that kind of data, for several reasons.

Regards,
Rune

5

Anytime I have needed to create a separate data access layer I have used stored procedures. Accessible from multiple programs. Can contain logic necessary to orchestrate multiple table accessed. If necessary could be a cobol stored procedure but better if owned by database.