Re: Multiple INCLUDE statements in SQLRPGLE

There are about 5,000 names, 25,000 ZIP codes, and 30 pages of business
requirements driving this application. I use stored procedures extensively
throughout the program in question but significant changes in volumes and
business operations have exposed a weakness in my design. The single issue
I'm working with is performance: a typical request might require 5,000
calls to this stored procedure to check all available rates, with some
requests making many more calls. We have 10,000 invoices a day and 30,000
requests (most through pricing bots) through the internet. The stored
procedure does a simple database read but it's another read nonetheless;
that's why I'm trying to push the logic out of the SP and into the database
via JOIN's. I agree that a stored procedure is the right programming
technique in most cases but not for this extremely database-intensive
special case, where some customers have more than 70,000 rows of pricing
options--think of a from/to matrix of 275x275. Did I mention I can set
up account numbers, states, and terminals into groups using the same
mechanism? This capability greatly simplifies the pricing department's
workload--they like it too much!

I'd moved the table into memory; that made no difference. As of today,
with four cores and a memory doubling to 128 GB, with a big hunk of that
memory supporting several dozen instances of the web pricing application,
we have reasonable response time for this program (every other application
performs with subsecond response time). One other issue: the hardware
(supporting three application partitions plus the console) virtualized all
the drives. My performance experts have suggested the big production
partition should access those drives directly (physically) while leaving
the finance and development partitions virtualized. The customer is
working on that angle.

Birgitta's response makes a lot of sense and I'm going to prototype it. I
prefer to have database logic (manifesting itself as complex WHERE's and
ORDER BY's) in the database and not in the program, so this will be a step
in that direction.

/* Off-topic: Birgitta, I have learned a huge amount from your posts and
articles over the years--thank you!!! */

One other approach I'm testing: modify the stored procedure to load the
table into an array when the application initializes, do a lookup to find
the first making entry, and then iterate (no more than two or three times)
to test for effective and expiration dates. This change would be easy to
install for the short term but a medium-term consideration is an
SQL-imposed limit of between 32,751 and 32,767 entries in the array (when I
use DIM(32767) in the qualified array data structure, the precompiler
throws an error; 32,750 doesn't). Another problem is having to spoof the
array as ascending (which it will be but the compiler doesn't believe it)
so %LOOKUP doesn't have to scan all 25,000 entries before calling it
quits...but as I think about this, I've realized we'll almost aways hit an
entry. If I give up on the ascending array angle, I can load it in a
manner than pushes the most-frequently used entries to the beginning of the
array.

Stay safe, stay healthy.

Regards,
Reeve

On Sun, May 10, 2020 at 7:37 AM Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>
wrote:

To me it looks like you will be better of by creating stored procedures or
UDTF... This is exactly what they are therefore.

lør. 9. maj 2020 kl. 13.18 skrev Reeve <rfritchman@xxxxxxxxx>:

I'm trying to stack multiple INCLUDE's to generate static SQL. The goal

is

to move common code out of the application source and into copy books for
ease of development and maintenance. I have a need to include a JOIN in
some cases, and while I could simply have two versions of a single
consolidated copy book (one with the join and one without), that solution
doesn't appeal to me.

This is the first copy book:
EXEC SQL
SELECT cmcust INTO :cmcust FROM arp001

and this is the second:
WHERE cmstat = 'A'

and this is the third:
AND CMCLS = 'R'

...and there will be additional selections.

Here's what the code might look like:
/include qcpysrc,testcopy1
JOIN arp002 ON cmcust = nmcust
/include qcpysrc,testcopy2
/include qcpysrc,testcopy3
and cmcls = 'R' and nmtype = '2';

The source out of the preprocessor includes an /END-FREE and the compiler
tags the "-" in column 11 as an error even when the source member

includes

a /FREE in it. The first SQL line isn't terminated so the preprocessor

is

likely not looking for an /END-FREE.
Position 11 Token - was not valid. Valid tokens: FOR USE

When I've added a /FREE statement to these source members, the error

still

appears. I've tried COPY as well; still getting errors in the

preprocessor

output. Using RPGPPOPT(*LVL2) and V7R3M0.

I'll be grateful for any ideas!

Stay safe and stay healthy.

-reeve
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com