AW: defining files with sql but using setll/read to access

IBM recommends that you create all indexes first as SQL indexes and then

create any DDS logicals.

If the index already exists, the OS will just reuse the index of the SQL

Index and the performance is better.

If you delete the index later, the DDS will keep the SQL index

Beginning with release 6.1 you can replace your keyed logical files by SQL
indexed (except joined logical files) all the rest is now covered within
SQL.
IBM recommends to first create indexes, because an index has a default page
size of 64K while a keyed DDS described logical file has a default page size
of 8K.

A DDS described logical file can share access path with an SQL index
(because of the larger page size), if the SQL index has the same or more key
fields in the same sequence. An SQL index can only share access path with an
other SQL index (or a keyed logical file with the same or larger page size),
if the key fields are an exact match.
That means if you create an index with the key fields KEY1, KEY2, KEY3, KEY4
and create a DDS described logical file with the key fields KEY1, KEY2,
KEY3, there will only be a single access path, that must be actualized
whenever a record within the base table get changed. And the DDS described
logical file gets the larger page size of the index inherited. If you create
a second index with the key fields KEY1, KEY2, KEY3 you'll get a second
access path, that also must be actualized, whenever a record within the base
table get changed. If the DDS described logical file is created first and an
SQL index with the same key fields after, a second access path will be
created, because of the larger default page size of the SQL index.

The more access paths are created and must be maintained the worse the
performance.

SQL queries perform better if an access path with a larger page size can be
used. That's why the optimizer may decide in favor of a SQL indexes.
Even for native I/O there will be a slightly better performance using SQL
indexes instead of DDS described logical files.

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: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Alan Campin
Gesendet: Friday, 14. August 2009 01:30
An: RPG programming on the IBM i / System i
Betreff: Re: defining files with sql but using setll/read to access

When you create a index in SQL, the OS just creates a logical file keyed by
the key of the index with all the fields. Looks like any standard logical
except if you do a DSPFD it says file type is INDEX. Your program doesn't
know the difference.

IBM recommends that you create all indexes first as SQL indexes and then
create any DDS logicals. If the index already exists, the OS will just reuse
the index of the SQL Index and the performance is better. If you delete the
index later, the DDS will keep the SQL index.

Unless you rename in your create script, the OS will give it a default name
like CUST000001.

So you have a choice. Create the index with a long name and rename to your
short name

RENAME DRIVER_BY_DRIVER_CODE TO SYSTEM NAME DRIVERI01;

or just create the SQL index and then create your DDS logicals to reuse the
index.

On Thu, Aug 13, 2009 at 4:49 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

Yeah Jim, I believe so - you don't have a subset of the fields in an
index, as you can with standard logical files. With a view, you'd have a
subset and no key - an unkeyed logical basically.

Vern

JDHorn@xxxxxxxxxxxxxx wrote:

I think this means that -when you specify an index the compiler gets

the

physical file definition for all the fields?

Jim Horn

____________________________________________________

------------------------------

message: 7
date: Thu, 13 Aug 2009 14:00:21 -0500
from: Vern Hamberg <vhamberg@xxxxxxxxxxx>
subject: Re: defining files with sql but using setll/read to access

Just like any other PF or LF - for a table, put its name in an

F-spec -

if it has a primary key that you want to use, declare as keyed in

the

usual manner.

For an index, just put its name in the F-spec and declare it keyed.

I think you can even use a view, but it has to be treated as

arrival

sequence. Right, you other SQL mavens?

All is the same as you know how to do!!

HTH
Vern

JDHorn@xxxxxxxxxxxxxx wrote:
> As time goes by, I am certainly seeing the advantage of using

sql

to
> access files, especially for search and display.
>
> In an rpg program, what is a good way to use a sql defined

file

for

> setll/read access?
>
> Jim Horn
>

This email is intended only for the person or entity
to which it is addressed and may contain information
that is privileged, confidential or otherwise protected
from disclosure. If you are not the named addressee
or an employee or agent responsible for delivering
this message to the named addressee, you are not
authorized to read, print, retain copy, and disseminate
this message or any part of it. If you have received this
message in error please notify us immediately by email,
discard any paper copies and delete all electronic files
of this message.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.