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

[Mike Jones <mike.jones.sysdev@xxxxxxxxx>]

Another thing you should consider, is what is the tolerance level for a
slight delay in spraying these updates across the 15 tables.  For example,
can all the stakeholders tolerate a 1 minute delay?

If they can, then allow 1 minute, or X minutes, of rows to build up in a
single table, and then spray them using SET based SQL into the other tables
once every X minutes.  Quite frankly, it will be difficult to find a
solution that can compete with that on a performance and system burden
level.

SET based SQL solutions are almost always a better choice than using SQL
one row at a time.

Mike

On Sat, Dec 12, 2015 at 10:53 AM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

> 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