Re: SQL created table slows reporting tool

[Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>]

I am not sure what to ask this vendor.So far he has just said yeah make
some logicals.

What I am to do is create some reports that are typically used by the
users. I am not sure do  I make logical files now
For example, they run alot of open orders reports by month. and then some
according to product category.


On Thu, Dec 26, 2013 at 12:14 PM, Paul Nelson <nelsonp@xxxxxxxxxxxxx> wrote:

> Heh. If you know that the input file contains strictly new records, why
> bother with that logic at all?
>
> One of my clients wanted to create a couple of new companies and duplicate
> the records of what I'll call his master company for the two new companies.
>
> His software provider quoted him 40 hours for writing code to accomplish
> this task. I did it in 4 hours just by knowing a few back door tricks I
> learned back in the late 70's. He now has a SEQUEL script that prompts him
> for the original company number and his new company number. The script
> takes
> care of the rest.
>
> It uses zero lines of code. One of the programmers at the software provider
> asked me to show him how I did it. He starts a new job in January at a
> company that already has SEQUEL, and wanted to get a head start.  :-))
>
> 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:56 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL created table slows reporting tool
>
> I hear you Paul.  I have a tough time explaining to the OVER 40 crowd why
> their updates take so long.
> They read this huge input file.  Instead of summarizing it by the
> subtotals and only writing at subtotal time they do a
> read
> if no record found,
>   write
> else
>   update
> endif
> Thus having over a million extra I/O's.
> It's like they've never heard of control breaks, total time, etc.
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   "Paul Nelson" <nelsonp@xxxxxxxxxxxxx>
> To:     "'Midrange Systems Technical Discussion'"
> <midrange-l@xxxxxxxxxxxx>
> Date:   12/26/2013 11:37 AM
> Subject:        RE: SQL created table slows reporting tool
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> << 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
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.