No. SQL just doesn't support it, and it's a shame. We have this great capability
to reference existing definitions, and the move to SQL leaves it all behind. You
can sort of do RANGE, VALUES, and COMP with a CHECK constraint, but it's not the
same (and you can't reference it from a DSPF).

>DDS is legacy.  DDL is the path IBM has chosen for on-going development. 

Is there a way to add an edit code to a DDL defined field? In DDS, I use EDTCDE.
I dislike seeing commas in order numbers, zeros instead of blanks in numeric
fields, etc when I use SQL against a DDL described SQL Table.

Chris

I read that in i7.1, the SQE can process select/omit logical files.  So I 
guess when we convert to 7.1, we should set IGNORE DERIVED INDEXES back to 
no...?

Thanks Rocky.

Here's a good article discussing DDS vs SQL DDL, in particular the performance 
benefits of the DDL files: 

http://www.ibm.com/servers/eserver/iseries/db2/pdf/Performance_DDS_SQL.pdf

Ralph,

No - SQL won't be of much help in the situation you've presented - sorry.

Rocky

>DDS is legacy.  DDL is the path IBM has chosen for on-going development.  SQL 
tables are supposed to have a better performance than DB2 files, regardless of 
whether you're using native RPG or embedded SQL to access the data.

Not correct - the files are DB2 files - the database on the System i is DB2 - 
it's not DB2 vs SQL - it is DB2 and DB2 ONLY and ONLY DB2. The only notable 
exception is MYSQL - which is using DB2 as the underlying engine.

DDS is legacy - SQL is the standard QUERYing language - hence the acronym - 
Structured Query Language. Now - IBM has made enhancements to DB2 to 
facilitate performance for using SQL - but the database is the same databse.

DDS is legacy.  DDL is the path IBM has chosen for on-going development.  SQL 
tables are supposed to have a better performance than DB2 files, regardless of 
whether you're using native RPG or embedded SQL to access the data.

tony: it's in QAQQINI. (see below)

How do I go about changing the Ignore Derived Indexes to *yes ?

Thanks Dale - Yes, I understand the DDS process and use it exclusively. 
However, I was wondering if SQL is in some way a mo'betta way than the DDS way 
of life.

SQL can't use the multi-format LF. You either continue using the multi-format
with native I/O, or you have to reengineer the application.

You can replace the flat files (the underlying PFILEs for the multi-format LF)
with either DDS PFs or SQL TABLEs. The LF won't care, and S/36 EE programs won't
know the difference, as long as the record length of each PF is the same, with
equivalent keys (same start position, same total length, ...).

Rocky said "I'm not sure I understand your question - could you elaborate a 
little?"

I must maintain s36 Rpg programs, many of which process 'flat' files that are 
made up of multiple record formats i.e. rectyp 'A' has cust type data, 
rectyp 'B' has shipping data, rectyp 'C' has 'item detail' data. In order to 
create DDS for this type of file I create one PF per record type and then a LF 
over them all which is named the same as the original flat PF. Plus, I create a 
record selector program which is attached to the logical. I won't go into the 
selector program here. My question is, can using SQL tables instead of the 
process described above make my life easier when getting rid of the flat files, 
or is it a similiar process used in SQL too?

Thanks - Ralph     

There are certain LF types that cause the Query Engine Selector to default to 
CQE.  Primarily these are DDS defined: Joined Logical Files, Logical Files 
with select/omit clauses, Logical Files with Internal Field definitions.

If the Query Selector sees any of these it kicks over to the CQE, UNLESS you 
set the SQL defaults IGNORE_DERIVED_INDEXES to yes.  Then it will pass to SQE 
and basically flag these invalid (per SQL definition rules) LF's as not to be 
considered when selecting from LF's to use at run time.

Tony,

Again, I think you also need to set IGNORE_DERIVED_INDEXES to yes.  When I 
initially went through exactly what you are, I saw in iSeries Navigator that 
my SQL was still using the CQE (I think because of existing logicals over the 
PF) until I made this change.

Executing your SQL against a physical table gives the Query Optimizer the best 
chance of finding and/or concocting the best access plan for data retrieval.  
Executing against a DB2 View would come next.  Executing against a DDS LF 
would be the least desireable (and silly, since SQL is far more flexible than 
DDS).  After running against the Table, you should check the iSeries Nav Index 
Advisor to see if you could create a permanent Index that would help it 
optimize your query in the future.  Performance can also be influenced by 
different ways of constructing your SQL statements - like any other 
programming, some ways are better than others.

Well - I knew you were using a DDS LF as it won't let you use a SQL Index... I 
didn't realize this until today since you can use an Index in an RPG F 
statement (as long as it's not an VECTOR ENCODED index).

I was using logicial over my embedded sql.  And yes I was referring to DDS 
Logical File.  I will will the physical over my SQL and see if the performance 
improves.  Thanks for your input

>I can't speak to the ramificatations of using views and indexes, so I'll 
leave that to the SQL experts here!

Actually - the system enforces it as you can't SELECT on an index - complains 
about it not being a table, view or a physical file. You can only select 
against table, view or physical file - it treats a DDS created LF essentially 
as a view - though likely not nearly as effective as using a view.

A view is simply a window into the table - because of this there isn't any 
difference in performance with a view vs a table - you're addressing the exact 
same data and the system has to determine the proper access path(s) exactly 
the same way. This is why CQE is used as a SQL view has no indexing - no keys 
so SQL has to take that into account.

IMO, it's usually just easier to refer to the table rather than a view - but 
there are times a view can simply things - especially if there are a number of 
joins.

>> You will almost always hurt performance by specifying a DDS LF.

This is my understanding also.  Maybe there's a chance you may get lucky in 
some scenarios and get equivalent peformance from a perfectly-suited LF, but 
more than likely you'll get much worse performance.

The original poster's question was LF vs. PF, and the answer is PF and to set 
IGNORE_DERIVED_INDEXES to yes so that any LF's you may have over the PF are 
not taken into consideration and you'll get the performance of the SQE.

I can't speak to the ramificatations of using views and indexes, so I'll leave 
that to the SQL experts here!

>>To me, when I run SQL over a LF, I am somehow short-circuiting the optimizer.
Sometimes this may be warranted, but I gotta say, IBM has done a good job here.
I'm not sure I have every outwitted the optimizer in creating a better
performing query.

By LF I assume you are referring to DDS Logical File - or SQL Index - from the 
system perspective the DDS LF, SQL View and SQL Index are all logical files 
(LF) - but they aren't all the same. If you create a table via SQL (CREATE 
TABLE) and a view over that table (CREATE VIEW) and have appropriate indexes 
(CREATE INDEX) you can use the table or view interchangably in your SELECT 
statement with no differences in performance - both will use the exact same 
access paths. If you specify the index then all bets are off - you certainly 
will not improve performance but you may certainly hurt performance.

You will almost always hurt performance by specifying a DDS LF.

>>So the answer is YES it does matter if your embedded SQL does not use the 
physical file, and performance WILL suffer accordingly.

The noted exception to this is that you can use a view and get the same 
performance results as using a table. But don't confuse the term view with a 
DDS created logical file - they are vastly different. A view has to be created 
by CREATE VIEW in SQL.

I agree with the poster who said to always use a PF--a TABLE is better too.
Where I disagree is that it makes no difference performance wise. It's been at
least five years, so take it for what it's worth: 

I had a logical file with two key fields and three additional fields
utilizing AND'd field selection with the COMP() keyword. As a counterpart I
created a logical with five key fields (the same five I just mentioned). 

The PF had about 250k records in it.

I opened two sessions, and with STRSQL, did a SELECT * FROM the LF and for the
PF, did a SELECT * FROM adding a WHERE clause to match the LF's field selection.
In at least two dozen attempts, the query that selected from the PF
having the five key fields beat the query over the LF *every time* and by a
significant margin. It never ever came close. 

This is anecdotal only of course, but I was sold.

Regarding using a view, it's just my opinion, but I tend to use these sparingly.
Surely if the query is a bit complex and widely used, creating a view from it
probably has its advantages, but those advantages have more to do with system
design rather than performance.

To me, when I run SQL over a LF, I am somehow short-circuiting the optimizer.
Sometimes this may be warranted, but I gotta say, IBM has done a good job here.
I'm not sure I have every outwitted the optimizer in creating a better
performing query.

If you use a logical file in SQL process, then processing is passed to the 
CQE.  The CQE is a legacy query engine generally used by OPNQRYF, QUERY400, 
and other legacy processes - IT HAS NOT been optimised for data base access.  

By specifying the Physical File or SQL Table the SQE will make it's own 
decisions to use existing indices or build one on the fly, dictated by the 
continuing optimization that IBM has made to the SQE and continues to make.  
You should not just go out and create further logical views as the SQE may 
never even use them.  Let it make the best decision for you which it bases on 
many criteria (select ?, where ?, order by ?, group by ?, etc, etc).

So the answer is YES it does matter if your embedded SQL does not use the 
physical file, and performance WILL suffer accordingly.

Ralph,

I'm not sure I understand your question - could you elaborate a little?

Thanks

Rocky - "P.S. If the file is a DDS created file rather than SQL defined table - 
you may want to think about recreating the data as a table. It's easy to do and 
if done right you won't have to recompile any programs - it will be seamless to 
your applications but allow you to have the performance benefits."

Can an unexternalized data file containing multiple record types by recreated 
as a single table, or (like DDS) does there have to be one externalized file 
per record type? Thanks

> In general, no. There are some access ptahs the query engine won't consider, 
and if you have a lot of access paths on the PF, the optimizer may not examine 
all of them unless you force it, but basically if you name the LF, it will 
look at the based on PF and all dependent LF's (DDS or SQL).
or SQL).

If you use Indexes then the above isn't quite so true - the optimizer compares 
the results received against what it thinks it should've received - if there's 
a disparity it will go out and look again for other indexes that will improve 
the performance. This is one of the areas that makes the SQE better than CQE. 
Also helps to not IPL as often as the action plans are cleared every time you 
IPL - making the system "relearn" all of it's performance issues. This is why 
sometimes a job seems to run worse after an IPL. If you don't IPL the action 
plans stay intact and the system remembers what it's learned.

A view can indeed have it's advantages - as indicated if you are selecting a 
sizeable number of rows but really only want a portion of the fields - a 
SELECT * over a view will return less data - outside of volume of columns 
there's no performance hit on using a view over a table. Since a view doesn't 
have a key there isn't any performance hit on creating multiple views - you 
can have 5,000 views over a table and not make any difference to performance.

P.S. If the file is a DDS created file rather than SQL defined table - you may 
want to think about recreating the data as a table. It's easy to do and if 
done right you won't have to recompile any programs - it will be seamless to 
your applications but allow you to have the performance benefits.

My understanding and experience is that, between a LF and a PF, you should 
always use the PF.  If you use a LF, the CQE will be used.  The newer SQE will 
be used if you use a PF and you have IGNORE DERIVED INDEXES set, even if your 
files are defined with DDS (mine are).

"QAQQINI is a file in the QSYS library. If this file is copied to the QUSRSYS 
library, its options are in effect for all users on the system. However, if 
you do not want its settings activated globally, you can copy the file to 
another library and use the CHGQRYA command to instruct the database to only 
use the settings for the file in that specific library and only for the job 
that issued the command.   
  
One of the QAQQINI settings is IGNORE_DERIVED_INDEX. The default value of this 
setting is currently *NO, which means that the optimizer will not ignore 
derived indexes, so impacted queries must be processed by CQE. Changing this 
value to *YES allows SQE to process the query even when a derived key or 
select/omit index exists over a table in the query. When the query is run with 
this setting in place, SQE ignores the derived indexes over the referenced 
table and is allowed to process the request."

The above info about QAQQINI is from an MCPress write-up on the subject, and I 
can confirm that when I did this back when I first started using embedded SQL, 
I was able to verify this on my system using iSeries Navigator.

My question is does it matter performance wise if my embedded sql is over a 
physical file or logicial file. -- In general, no. There are some access ptahs
the query engine won't consider, and if you have a lot of access paths on the
PF, the optimizer may not examine all of them unless you force it, but basically
if you name the LF, it will look at the based on PF and all dependent LF's (DDS
or SQL).

Should I create logicial views  for each lookup or can I use the physical file
for all the lookups. -- You can use the PF. If you're reading a lot of rows, a
view will return less data if you use a SELECT *, but you can manage that anyway
by naming specific columns. VIEWs have other advantages, though, because the
SELECT statement can contain almost anything.

There's no reason to do SQL (embeeded or otherwise) over a logical file unless 
you're after the view - but a view doesn't have a key anyway.

Always refer to the physical file or a view (which in the system is a "logical 
file" but it's one of those "it's the same but only different" sort of thing).

The system will use the appropriate LF/Index to give it the best performance 
for your query. If it can't find an appropriate access path(s) that works it 
will create one on the fly... in this situation it would behoove to create an 
index so it doesn't have to keep doing this each time.

Tony,

I believe that if you use a DDS defined logical file in your SQL statement the 
process will use the Classic Query Engine (CQE) instead of the SQL Query 
Engine (SQE). SQE is the newer and supposedly better engine.

My question is does it matter performance wise if my embedded sql is over a 
physical file or logicial file.  I do four different process that looks up 
records basic on the select criteria from a screen.  Should I create logicial 
views  for each lookup or can I use the physical file for all the lookups.    
 
Thank you