Please check whether these logical files that you name views have a key.
If those logical files have a key (what I suspect), they include an access
path (index) and the access path must be updated with any insert, update or
delete statement performed on the base table.
The access path contains a bitmap for every key value. In the bitmap is a
bit for each row in the file/table that is set to *ON or *OFF depending
whether it matches the key value or not.
The larger the base table, the more different key values available, the
larger the bitmaps, the more time consuming is the update, the more logical
files (with keys) or SQL indexes are built over the base table, the more
time consuming is an insert, update or delete.
SQL Views on the other side never have a key/access path, so it is possible
to have thousands of views built over the same physical file/table without
any performance decrease when an insert, update or delete is performed on
the base table.
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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Kok.Gregory@xxxxxxxxxxxxxx
Gesendet: Friday, 20.5 2016 13:36
An: midrange-l@xxxxxxxxxxxx
Betreff: Logical Files (Views) - Impact on Performance (I/O)
Hello Midrangers.
We recently implemented a new system on our IBM i720 (v6r1m0). Performance
is since negatively impacted and the bottle-neck is massively I/O related.
Disks are not keeping up.
Looking at the size of the systems database, it is 53% Logical Files
(Views).
All these views (LF's) were created (CRTLF) with the attribute FILETYPE =
DATA. (Most LF's reference a single Physical File only, not multiple PF's)
These LF's are occupying physical space on disk (53%... more than half the
actual database) and I'm concerned there is an I/O impact because of this ?
Can someone elaborate on what the FILETYPE = DATA setting does ? Is it
duplicating physical file data and possibly the source of severe I/O
activity ? (Whats the difference between data records and source records
?) Is there any best practice / recommendations re the use of LF's ?
CRTLF (Command - Create Logical File)
Attribute : File type (FILETYPE)
Specifies whether each member of the logical file being created contains
data records, or contains source records for a program or another file.
*DATA
The logical file contains data records.
*SRC
The logical file contains source records. This value cannot be specified for
join logical files.
Thanks,
G
----------------------------------------------------------------------------
---------
This e-mail is subject to the Columbus Stainless [Pty] Ltd Email Legal
Notices available at:
http://www.columbus.co.za/EmailLegalNotice.htm.
----------------------------------------------------------------------------
---------
This e-mail message has been scanned for Viruses and Content and cleared by
MailMarshal
----------------------------------------------------------------------------
---------
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
