Solved

@Tom @Kent @Momi @Dave

Posted on 2008-10-02
8
638 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
RPG to c# 3 396
Setup SFTP for internal iSeries servers 8 1,529
convert constant to free form 1 91
SQL Stored Procedure Authority 3 30
This article was initially published on Monitis Blog, you can read it here . When it comes to deciding which approach to website performance monitoring is best for your business, unfortunately, like so many options in life . . . it depends. In t…
This article was originally published on Monitis Blog, you can check it  here .   Some years back, I worked as the CTO.  During my tenure, I had a head of IT support reporting to me.  He did his job quite well and had a commendable sense of duty…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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