Solved

@Tom @Kent @Momi @Dave

Posted on 2008-10-02
8
628 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
8 Comments
 
LVL 45

Accepted Solution

by:
Kdo 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 32

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
 
LVL 32

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now