Re: DBCS issues revisited

[CRPence <CRPbottle@xxxxxxxxx>]

  As a query interface, that the VIEW has no key should not matter; 
i.e. a query must specify the ORDER BY explicitly to ensure order of 
data.  Thus a VIEW can replace a keyed LF, and a keyed select\omit 
logical file need not become two files.  The INDEX would be 
optional, there only for optimization.  If an INDEX were desired, 
that requires only adding some suffix like "_IX" to any VIEW name; 
i.e. for LF named ORIGINAL.XXYYZZLF, CREATE VIEW ALTERNATE.XXYYZZLF 
and CREATE INDEX ALTERNATE.XXYYZZLF_IX   The query names the VIEW as 
from where data is retrieved, and the INDEX is something the SQL may 
or may not use to implement the query and\or access the data.

  If the SEQUEL queries are using the CQE [IIRC, that product may 
use the QQQQRY API] using the effective KEYFLD(*FILE) of OPNQRYF in 
a manner other than naming those key fields on an ORDER BY clause, 
then changing the query to ensure the ORDER BY is explicit versus 
/inferred/ from the keys on the file named might be necessary. 
Perhaps changing the queries to explicitly specify the collation 
specifications is a better option in that case.

Regards, Chuck

Dennis Lovelady wrote:
> <<SNIP>>
>
> Of course the saga continues as now it's been determined that
> some of the SQLs in question are against keyed logical views that
> have select/omit involved.  I realize that indexed views are
> unique to System i, and wonder if there is an SQL-equivalent for
> them.  I've written a program that will create a view over the
> physical using auto-generated SQL with appropriate CAST(), and
> now I'd like to expand that to also generate for these LFs.
>
> It seems that QSQGNDDL is (somewhat) happy retrieving the SQL
> source for these files either as an index or as a view, but of
> course the generated source pair fails miserably when trying to
> create an Index and a view of the same name.  So I wonder if
> there's an SQL way of doing that, or if I have to resort to
> building DDS and generating from there?  Again, the goal is to 
> take a working SEQUEL (not SQL) statement like "SELECT COMPNAME
> LEN(20) FROM MY_LF" (which now fails because of the column type
> for COMPNAME) and allow it to execute by casting a new type for
> COMPNAME in a view/index called MY_LF to be created in a new
> library.
>
> <<SNIP>>