RE: SQL created table slows reporting tool

<< For example, if you have 200 indexes on one physical file then every time
you add a row to that table it has to update all 200 indexes.>>

Not to mention that if the physical is keyed, the index area for the file
itself needs to be updated. It seems I spend a third of my time explaining
this to the under 40 programming crowd that whines about performance when
they're loading bazillions of records to a keyed PF that has many dozens of
logicals, indexes, and views over it.

Me: "Did you sort the input data first?"
Them: "No, why?"
Me: "Do you understand the concept of an index area?"
Them: "No"
Me: "Does your process remove the logical file members before loading the
data?"
Them: "No, why?"
Me: "What is the DBMS doing if you don't take these steps?"
Them: "Ummm"

And so on, and so on. This happens every time the outsourcer hires a new
batch of bodies to replace the previous bodies that jumped ship for an extra
14 cents per hour.

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, December 26, 2013 10:22 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL created table slows reporting tool

The caution about indexes willy-nilly applies to both SQL indexes and DDS
logical files. The basic caution is that indexes put maintenance on the
system. For example, if you have 200 indexes on one physical file then
every time you add a row to that table it has to update all 200 indexes.
This used to be pounded in our heads. I'd mention the IBMer, he deserves
the credit, but too many would drift off into a geezerfest. This concern
is supposed to be somewhat alleviated by newer versions of the OS. Many
names, long respected in the industry, have said so. However there are
those who will reply that they do not care what those people say - when
they reorg a large table with a lot of indexes it runs much longer than
deleting all the indexes first.

Keeping that concern in mind, I think you would be SAFER to use the sql
indexes recommended by iNav over the DDS logical. The temptation to put
stuff in the logical file that really shouldn't be there would be
eliminated. That, and SQL tends to use more modern defaults, such as page
size and whatnot. While DDS tends to carry over defaults best left behind
when we all migrated off of AS/400's.

It's up to you to communicate with your change management vendor to see
how they could capture such a creation, and if not, how to best put this
into the process.

Rob Berendt