When adding an access path (logical file or SQL index) the underlying bitmaps must be updated with each record.
... if you copy (mass) data into an empty file it is much better to generate the access paths (indexes or logical files) after the data is copied.
... and There are differences between logical files, indexes and views.
A DDS described logical file can include:
1. Field Selection
2. Join Clauses
3. Select Omit Clauses
4. Key Information
The default page size of a logical file is 8K
A SQL View is a logical file based on a SELECT Statement which can include EVERYTHING that is allowed within a SELECT-Statement with one exception, i.e. ORDER BY is not allowed.
A View is always unkeyed.
Even though a view can be used in composition with native I/O, because of the lack of any key its use is only restricted.
A view is used for hiding complexity of the SELECT-Statements within the source code and for moving business logic out of the source code into the database.
A view can be specified in a SQL Statement like any table or physical file. It is even possible to create a view based on another view.
A SQL Index includes all the key information.
A SQL Index cannot be specified in a SQL Statement, because it is the query optimizer who decides if and which access path (SQL index, DDS logical file, primary key constraint, unique key constraint). But a SQL index can be used in composition with native I/O like any keyed logical file.
Since Release 6.1 it is possible to generate derived indexes, i.e. indexes with generated new key columns (for example in composition with a scalar function, for example UPPER(ColX)).
Since Release 6.1 it is also possible to add where conditions to an index (Sparse Indexes).
In Release 6.1 these enhancements where first for native I/O it was not before release 7.1 when the query optimizer slowly started to use these indexes.
An index is based on a single physical file or SQL Table, i.e. join clauses are not allowed.
Most logical files can be replaced with SQL indexes (there are even wizards in ACS which will generate the appropriate CREATE INDEX statement out ouf a DDS described logical file).
There are only 2 types of logical files that cannot be replaced with SQL Indexes:
1. Joined logical files (Replacement: SQL View with the JOIN CLAUSE / translated SELECT/OMIT CLAUSES and an additional index with the appropriate key fields)
2. Multi-Member logical files (No Replacement, you may rewrite your programs using embedded SQL and UNION clauses)
Here an article where the different types of logical files are explained.
https://developer.ibm.com/articles/i-sql-indexs-and-native-io/
BTW also physical files and SQL tables are not 100% identical, but can be used in the same way:
1. DDS described physical files can include a (non-)unique key. SQL tables are always unkeyed. For physical files and SQL tables a primary key and multiple unique key constraints can be defined. When converting from DDS to DDL the unique key will be translated into the primary key constraint.
2. When writing data into a DDS described physical file, the data is NOT checked. For example it is possible to insert invalid numeric values with a CPYF and option *NOCHECK ... or with (old) RPG programs using non-initialized internal data structures when writing a new record.
When reading data from a DDS described physical file (independent which technique is used) the content of the columns is checked and may be converted.
When writing data into a SQL Table the data is checked, so it is not possible to insert invalid values into an SQL table (not even with a CPYF *NOCHECK). The copy will stop with the first invalid value
When reading data from a SQL Table (independent which technique is used), the data is not checked.
Using SQL Tables is faster than using DDS PF, because you'll perform more reads than updates or deletes.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
"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!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vance Stanley via MIDRANGE-L
Sent: Samstag, 23. April 2022 18:24
To: Vance Stanley via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Vance Stanley <w_vance_stanley@xxxxxxxxx>
Subject: Re: Adding an LF decreased performance drastically
I might add my understanding is limited on the differences between ddl and dds. However, One of the benefits might be to Identify logicals that might be used less frequently and with indexes that are not frequently utilized. Those might be canditates for views. That would be one step in reducing overhead in a large physical file.
On Saturday, April 23, 2022, 10:49:31 AM CDT, Vance Stanley via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
We experienced better performance updating a GL history file which had well over a billion records. That was with an older OS. I cant remember which version.
On Saturday, April 23, 2022, 07:41:33 AM CDT, Vern Hamberg via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
Hi Vance
Now _that_ is a statement that begs for more information! Can you give more detail of the benefit of going with DDL. If you are thinking of what happens when updating a PF with LFs, I believe that indexes over the tables would have the same issue of updating them when changing so many records.
Cheers
Vern
On 4/23/2022 7:35 AM, Vance Stanley via MIDRANGE-L wrote:
Maybe time to switch to ddl on that table.
On Friday, April 22, 2022, 02:23:00 PM CDT, x y <xy6581@xxxxxxxxx> wrote:
IBM"s advice in the past (IIRC) was to remove the LF members if
you're changing more than ~15% of the records. You can automate this
with a quick-and-dirty CL program: DSPDBR to an outfile, read the
file, remove the members, maintain the PF, read the DSPDBR OUTFILE, add the members back.
On Fri, Apr 22, 2022 at 11:18 AM <smith5646midrange@xxxxxxxxx> wrote:
We have a job that writes about 1.5B records to a PF. When the job
starts, it does a CLRPFM and then starts dumping records into the PF.
We recently added an LF over the PF with no mods to the original
program (LF will be used later in the stream by something else) and
the job went from approx. 2 hrs to an estimated 11 hrs. We killed it
after the normal 2 hour window with less than 20% of the records
written.
While trying to determine why, one thing that we noticed is the job
I/O count was different with and without the LF. We found that
without the LF, the output blocking factor is a pretty decent number
of records (I/O count is WAY less than RRN). However, when we
created the LF, the I/O count and the RRN are almost identical (like
blocking is ignored). The LF is not unique keyed, just a normal keyed logical.
I've never noticed this before but I've never really paid attention
to the I/O count on output files. Is this a normal thing for an LF
to eliminate the blocking factor of the output and make it 1 for 1 or
is there something that we muffed up in the create of the LF?
I know we can change the LF to *DLY and stuff like that but we just
want to understand what happened and why so we know for future reference.
Thanks.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
