Re: Anyone have a well normalized DB2 database and use RPG?

In some cases, I agree. But actually, given the lifespan of most iSeries and Mainframe applications, it is a great idea to optimize them as much as possible without compromising the ability to maintain them. Blindly following any rule is usually a recipe for disaster.

I think the idea that everything has to be totally normalized is a handover from the 90s, when structured design and object oriented technologies were just taking hold. it’s take 15 to 20 years for the hardware technology to get fast enough to make those ideas really practical.

But take note - practices like Agile development often encourage much more frequent compiles and can be much more demanding upon the hardware. How long will it be until every compile runs in a couple of seconds? In some cases, the machines are almost that fast. But current best practices invoke the Rational product set, and that slows things down in a lot of ways. For example, compiles seem to take quite a bit longer. Back to the late 90s in terms of speed perhaps.

Also remember we are entering the age of BIG data - this is where normalization really has a positive effect in terms of storage and DB programming. But - data mining in BIG data requires highly optimized programs to be tim effective. A query that returns 100 million records is not uncommon and is considered rather small in some circles these days. In fact, it is not even unusual if applications issue multiple queries of this size.

Power technology can offer a way to handle those kinds of loads, but not, I think, with the average i sitting in an office. As BIG data moves down, you are going to see more and more programs optimized for performance until the hardware catches up. And then it will be something else.

On Oct 30, 2013, at 6:47 AM, Raul A. Jager W. <raul@xxxxxxxxxx> wrote:

That is the idea. The rules are the same, the performace has changed,
then you don't need to de-normalize anymore, in this particular
situation, wich is rery frecuent.

CRPence wrote:

On 10/29/13 3:25 PM, Raul A. Jager W. wrote:

It is usual to put a "balance" in master files, even if in a
normalized database must be calculated as the sun of the
transactions. This because cost of sum, but in 7.1 you can create an
EVI over the transaction table, and get the sum very fast, so no need
to "de-normalize" to gain performance.

Specifically, *if* the Encoded Vector Index is created with the
appropriate "EVI INCLUDE aggregates" that match the aggregates that will
be requested in such a query.

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/idxgrpingimp.htm
_i Index grouping implementation i_
"There are two primary ways to implement grouping using an index:
Ordered grouping and pre-summarized processing.
...

_EVI INCLUDE aggregates_

A more powerful example of pre-summarized processing can be facilitated
by the use of the INCLUDE keyword on the index create. By default,
COUNT(*) is implied on the creation of an EVI. Additional numeric
aggregates specified over non-key data can further facilitate
pre-determined or ready-made aggregate results during query optimization.

For example, suppose the following query is a frequently requested
result set, queried in whole or as part of a subquery comparison.

SELECT AVG(col2)
FROM t1
GROUP BY col1

Create the following EVI to predetermine the value of AVG(col2).

CREATE ENCODED VECTOR INDEX eviT1 ON t1(col1) INCLUDE(AVG(col2))
..."

--
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.