Re: SQL sort

On 03-Sep-2015 14:14 -0600, Booth Martin wrote:

I am fairly new with SQL so please excuse what are most likely very
simple questions.

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).