Re: Need ideas to reduce open/close in RPG program

Hi Vinay,

I'd likely do something like this, which would be a great combination of
simplicity and good performance.

insert into TABLE_THAT_TRACKS_FAILURES
select CT.TABLE_NAME, UI.RESULT_SQL_CODE, { other failure input data }
from CONTROL_TABLE_CONTAINING_TABLE_NAMES CT
cross join table( UPDATE_OR_INSERT_EACH_TABLE( CT.TABLE_NAME, { pass other
data needed here for updates/inserts } ) )
as UI ( RESULT_SQL_CODE )
where UI.RESULT_SQL_CODE <> 0

Notes:
* UPDATE_OR_INSERT_EACH_TABLE is an SQL table function that takes your
table name as input, along with whatever other data you need to perform the
update / insert. It performs the update or insert, checks for an error,
and returns the SQL code resulting from the update or insert to the calling
result set.
* One REQUEST to the database updates all tables and tracks the failures.
* Granted, the SQL table function is performing multiple I/Os, but that is
happening at the database level with only data and SQL function code
involved. Carving out programs external to the database improves
performance. SQL function code runs very fast.
* I use SQL functions to do things like this all the time, and properly
implemented it runs really fast, with a tiny code volume.

Granted, your shop is resistant to SQL, so toss this out as a potential
solution but don't really sell it much. See if they bite. At least, they
should be given an opportunity to see what they are missing out on by being
resistant to SQL. They are making their job a lot harder than it needs to
be.

I might tinker with an SQL trigger solution as well, where only one table
is directly updated with a trigger on it, and the trigger's job would be to
propagate to the other tables (N - 1). That would also be a very small
code volume. I've not tried to use the code similar to what I posted above
inside a trigger, but it probably would work inside one. On the other hand,
updating the first table with one piece of code, and the other N - 1 tables
using a different piece of code, might deter me from tinkering with
involving a trigger, since now I have code in 2 places where I'd prefer 1.

SQL functions have some limitations. If they surfaced in this effort, I'd
push the functionality into SQL triggers and/or SQL stored procedures.

Mike