On 03-Sep-2015 14:14 -0600, Booth Martin wrote:
I am fairly new with SQL so please excuse what are most likely very
I am looking to understand the pros and cons with SQL.
On large files with data that will be used in various applications
and in various sort-by schemes, are logical files a help?
Keyed logical files [for which a Keyed Access Path is created] can be
helpful, but mostly they are helpful for selection [WHERE clause
predicates] and joins [JOIN predicates] more so than for ordering [ORDER
BY], because often actual /sorting/ is more efficient than utilizing the
key [per /random/ I\O] because a subset of selected row data often can
be accessed as /pages/ of effectively contiguous memory.
If so, does one reference the physical file and then SQL finds the
best logical, or does it help to specify a specific logical file?
The SQL is effectively restricted to operating on the physical and
using optimization for choosing a compatible keyed and\or selective
Access Path; i.e. the Logical Files with the SQL attribute are
disallowed for a table-reference, and depending on what level of SQL
support, only the Classic Query Engine (CQE) knows what to do with a
table-reference that includes a DDS LF. For a level of support on which
the SQL Query Engine (SQE) can process the query of a DDS LF, the
selection of the LF is translated [I have seen examples where that fails
horribly, and unless someone reported what I described, such defects may
persist] into the equivalent SQL predicates and the query is performed
against the underlying PF(s) irrespective the table-reference. The
key-order of an LF specified as a table-reference is immaterial to the
SQL query; only the ORDER BY and the associated Sort Sequence (SRTSEQ)
of the query request is considered by the SQL for collation.
What is the right way to collect a set of records equal to SFLPAGE?
There are /paging/ query examples that could be found on the web.
Many discussion, even some here [archives] about the performance getting
progressively worse as the user progressively pages through the rows.
So far, I believe my biggest problem is that I haven't yet figured
out how to ask the right questions.
Do not limit searches for examples of DB2 for i SQL; any DB2 examples
for embedded SQL are likely to be helpful generally. Even non-DB2 SQL
can be helpful, but the syntax variants and non-standard SQL from other
SQL products may not have direct translations into DB2. Although other
DB2 variants will not have subfile paging, the concept of paging through
the selected data is not unique to the IBM i Display File (DSPF).