Re: SQL logical file equivalent

Hi,

thanks all to try to convince us to use SQL .
Still I do feel sad about this:

Apparently most of the known issues that existed way back in 2001 , are stillt here
(see the Paul Conte article "Club Tech iSeries DB2 UDB & SQL/400 dd 09.20.01" )

<quote>
* Traditional data definition functions that are NOT available with SQL

-- Field reference file
-- Multi-member files
-- Multi-format logical files
-- Access path (SQL index) features:
+ Access path over multiple members
+ Select/omit access path
+ Access path over derived fields
+ ABSVAL, DIGIT, UNSIGNED, ZONE field attributes for access path
+ ALTSEQ attribute for individual access path fields
(SQL uses sort sequence for all index columns)
+ FCFO, FIFO, LIFO sequencing for duplicate key values
</qoute>

- new development is all about reuse etc.
When it comes to File descriptions, all this reuse stuff suddenly doesn't count anymore.
On the contrary.
No more durty old fashioned repository, such as a "Field Reference Files"
Allthough 95% of new fields do reference already existing ones,
in SQL one should redefine everything;
length, type, description, headings,etc, editing ,

Ah, No , how sad !
Ever since 25 years ago in my mainframe PL1 time, one of the things I should and could define,
was how a numeric field must be edited.
In the modern SQL world I still can't find how I should or might do this.
(or no one ever edits numeric data anymore?)

- Multi-member or Multi-file Logical files don't have a real alternative in indexes, as far as I know.
(my questions in the newsgroup some time ago confirm this).

- "Select/Omit should be avoided "?
In a real world application and database I cannot think of a situation where Select/Omits would not be appropriate.
Let's take our sitation: legally we have to keep 20 years of paiments in our files.
Of course, most of the time we need and use only the last year in selections etc.
A simple LF with select/omit provides this without any problem ( and - using OVRDBF- totally tranparent for all applications) .
Will a "optimized SQL query using the SQE engine" give the same performance and same simplicity as such a clean simple LF ?
IMO this kind of situation is a standard daily situation in nearly any information system?


- SQE migth be very fast comparing to other techniques in complex batch selects,etc,
only, at our shop, what interests us the most is fast direct access .
I suppose SQE would do here a good job too, if IBM would solve this select/omit issue?


- Another bizar fenomenom:
Some tests we've run through seem to indicate that the SQL optimizer engine does not have any knowledge of table/file Reorganizes done over the most used accessPath.
Even with the SQL engine, I would think that using accesses acording to the reorganized files would benefit great for data-throughput ? and that is the major role of the optimizers , isn't it ?


Can we hope that IBM makes some work out of these 'known issues ever since 2001",
so that all "traditional data defintion functions" one day all will have a good replacement ?

luc

----- Original Message ----- From: "BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Friday, April 20, 2007 6:54 PM
Subject: AW: SQL logical file equivalent

Hi,

Just as a side note in case someone tries this...if you do a SELECT

directly from a SELECT/OMIT LF you'll be forcing the SQL to the CQE..which
is dog slow compared to SQE...

Even worse:
If you define logical files with select/omit clauses over your physical
file, all SQL statements that use this physical file (even without
specifying directly a logical file with select/omit) are routed to the CQE
(classic query engine) and cannot profit from the advantages of the newer
SQL Query Engine (SQE).

... At least as long as you won't change the option IGNRORE_DERIVED_INDEX in
the QAQQINI file to *YES and use this changed QAQQINI file.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Holden Tommy
Gesendet: Friday, April 20, 2007 16:31
An: Midrange Systems Technical Discussion
Betreff: RE: SQL logical file equivalent


That's a good approach...for RLA. For SQL processing I'd say just create a
view with your selection criteria and just handle ordering in your SQL
statement in the RPG program. With SQL processing you don't really *need* a
select/omit LF. Just as a side note in case someone tries this...if you do a
SELECT directly from a SELECT/OMIT LF you'll be forcing the SQL to the
CQE..which is dog slow compared to SQE...

But I have used both approaches for files in the past and they both work
great. In some client shops they do not have DB2/SQL product so I still use
the field list LF you're talking about.


Thanks,
Tommy Holden


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
Sent: Friday, April 20, 2007 9:18 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL logical file equivalent

Why not do that?

I was trying to say (perhaps poorly <G>) that you SHOULD create your logical
files by explicitly specifying the fields you want. You should NOT create
the LF by omitting the field list and allowing all table columns to flow
into the LF.

I'm suggesting this approach so your 3GL RLA access is isolated from any
table changes in the base table. If you omit the field list and just inherit
the complete field list into the LF than the addition of a new column to the
base table will result in a change in the format level id of the LF and
you'll have to deal with that impact -- something I'm trying to avoid.

Make sense?

-Walden

--
Walden H Leverich III
Tech Software
(516) 627-3800 x3051
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)


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