Re: DBCS issues revisited

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