Solved

@Tom @Kent @Momi @Dave

Posted on 2008-10-02
8
639 Views
Last Modified: 2012-05-05
Continuation of question:
http://www.experts-exchange.com/Programming/System/AS_-_400/Q_23778490.html#a22620089

@Tom - Thanks for answering with a question. Let's me layout the whole picture.

We're running as400 iseries R5V3M0.
99% of all AS400 work is done in RPG.
But, we're now tasked to create an executive portal using Sharepoint which contains dozens of very high-level roll-up type reports.

The reports often require summing dollars & tons & quantities from 10,000-20,000 records from tables holding 2-4 million rows and requiring joins across 3-4 different tables.
Our first attempts were to build SQL Views. Views are DONE, they run, and produce accurate data ... but ...
... the nature of a view is to be rebuilt every time the query is called ... SLOW ....

So now we're looking at DDS logical tables. If our logical tables are built WITHOUT the DYNSLT option, then IT IS OUR UNDERSTANDING the view is (in essence) kept in a pre-built constantly maintained state, thus eliminating the need to build the 10,000-20,000 record view each and every time the roll-up query is run.

So, we're trying to duplicate (rather complex multi-table-join roll-up record extraction) from SQL logic into a Logical DDS definition. Thus, some of the "strange" questions about DDS Logicals I've been asking.

Question: IS OUR UNDETSTANDING about life cycle of Logical Files correct? Are logical files kept in a pre-built constantly maintained state?
0
Comment
Question by:volking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 400 total points
ID: 22624546
Hi Fred,

This sounds exactly like an MQT (Materialized Query Table) or Materialized View.  It may be that the logical file structure on the AS/400 is the way that it is implemented here.

I know that on any other DB2 platform, I'd think seriously about implementing your needed structure as an MQT.

Let's see what the AS/400 guys (Dave, Tom) think.


Kent
0
 
LVL 5

Author Comment

by:volking
ID: 22625411
@Kent - MQTs are news to us! We got excited reading about them ... sadly though ... it appears MQT which use "MAINTAINED BY SYSTEM" are not supported in V5R3. Unless I'm reading docs wrong, only "MAINTAINED BY USER" is supported.

Running this ....
--------------------------------------------------------------------
CREATE TABLE CSIDEV.SLSNAME AS
(
     SELECT
          TPTBLLS.TABLE_CODE_TL AS CODE,  
          COALESCE(TPTBLLS.TABLE_DESCRIPT_TL, 'ABSENT') AS FULLNAME
     FROM JBSV11DB.TPTBLLS TPTBLLS
     WHERE
          (TPTBLLS.TABLE_NUMBER_TL = 1004)
     AND
          (TPTBLLS.ITEM_NUMBER2_TL = 1)
)
     DATA INITIALLY IMMEDIATE
     REFRESH DEFERRED
     ENABLE QUERY OPTIMIZATION
     MAINTAINED BY SYSTEM
------------------------------------------------------------------------------------
Errors with ...
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token SYSTEM was not valid. Valid tokens: USER. Cause . . . . . :   A syntax error was detected at token SYSTEM.  Token SYSTEM is not a valid token.  A partial list of valid tokens is USER.  

sigh ....
0
 
LVL 33

Assisted Solution

by:shalomc
shalomc earned 100 total points
ID: 22625469
MQT rocks.
Use DATA INITIALLY IMMEDIATE to have the access path available all the time.
I also highly recommend doing index optimization, and don't create them indexes as logical files :)
use EVI (encoded vector index) when possible for greatest performance squeeze.
Have a source file where you store the SQL DDL statements, and execute them via RUNSQLSTM.
 ShalomC
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:shalomc
ID: 22625486
Drop the REFRESH DEFERRED part.
"Maintained by user" means that you can add, insert and update records in the MQT. So don't :)
ShalomC
0
 
LVL 5

Author Comment

by:volking
ID: 22625594
@ShalomC -
I'm running in V5R3 ....
When I try this
----------------------------------
     DATA INITIALLY IMMEDIATE
     ENABLE QUERY OPTIMIZATION
     MAINTAINED BY USER
----------------------------------
SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword ENABLE not expected. Valid tokens: REFRESH
=======================
and when I try this
-----------------------------------------
     DATA INITIALLY IMMEDIATE
     REFRESH IMMEDIATE
     ENABLE QUERY OPTIMIZATION
     MAINTAINED BY USER
------------------------------------------
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token IMMEDIATE was not valid. Valid tokens: DEFERRED.

Maybe you're working with better than V5R3?
0
 
LVL 5

Author Comment

by:volking
ID: 22625620
The only thing that works is ...
===================================
     DATA INITIALLY IMMEDIATE
     REFRESH DEFERRED
     ENABLE QUERY OPTIMIZATION
     MAINTAINED BY USER
===================================

Which bring up another question ..... but, I'll post into a different Q
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22629251
volking:

Your understanding of the permanence of a LF is okay, as is the way you relate it to a VIEW. However, it leaves out a critical element.

A SQL VIEW is implemented similarly to a LF *FILE object. The same object typing is used for both. The particular kind of LF that implements a VIEW is an non-keyed LF; i.e., there is no index within a VIEW object. LFs can be either keyed or non-keyed. A VIEW is always non-keyed.

A SQL INDEX is also implemented similarly to a LF *FILE object. The same object typing is used for both. The particular kind of LF that implements an INDEX is a keyed LF; i.e., there is an index within an INDEX object. An INDEX is always indexed.

In short, the creation of a VIEW plus an INDEX is more like creating a keyed LF.

There are lots of details, of course, but that's a very basic version.

For both a keyed LF and an INDEX, the indexing is effectively the same. Both are permanent and maintained automatically. (That can be modified, but we don't care for now.) Also, the data that is presented through a LF is not part of the LF. It is accessed from the physical file at run-time just as VIEW data is accessed at run-time.

However, when you access LF records by key through the encapsulated index, it appears as if it's effectively immediate access. It would appear to be just as immediate if you accessed data through a VIEW and your SQL included an ORDER BY clause and an appropriate INDEX existed that supported the ORDER BY.

In fact, when processing large sets of rows, the SQL INDEX will usually result in better performance. One reason is that the page size of an INDEX index is 64K where a LF index page size is only 8K.

A HLL program can be compiled over a VIEW or an INDEX just like it can be compiled over a LF for record-level access. However, there are some limitations.

If compiled over a VIEW, there is no keyed support since VIEWs don't have indexes. If compiled over an INDEX, there is no explicit VIEW; you only can get all underlying columns -- access by key can be faster, but more data is moved into program buffers.

A LF can combine the selected fields that you get from a VIEW with keyed access you get from an INDEX. But you give up the extra data formatting that a VIEW can provide and you give up the faster index access that an INDEX provides.

A VIEW cannot have an ORDER BY. The ORDER BY comes from the SQL statement that accesses a VIEW. SQL will check to see if any INDEXes or keyed LFs exist that satisfy the ORDER. If an appropriate index is found, it will be used. When that happens, you don't see the behavior that makes it look like VIEWs are re-built every time.

Still lots of details to go, but maybe more questions will come out of the mess I just wrote.

Tom
0
 
LVL 5

Author Comment

by:volking
ID: 22649812
@TOM
Thanks for taking the time to explain ... You've verified several of my assumptions AND brought a couple of new issues onto my radar screen. I sincerely appreciate the "full" explaination. Rather than continuing this Q, I'll start more Q's as I learn more.....
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The conference as a whole was very interesting, although if one has to make a choice between this one and some others, you may want to check out the others.  This conference is aimed mainly at government agencies.  So it addresses the various compli…
Today’s effective marketing is about coming down to the customers’ level and engaging in a whole new way. A text message is one of the most effective and influential ways that you can engage your customers. Here are eight ways that you can utilize t…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question