Re: SQL-defined files/tables

[D.BALE@xxxxxxxxxxxxx]

>>So my question is whether I lose any functionality by
>>defining a database file using SQL vs. DDS?

>The only one that comes to mind is the indexed logical
>file. It can't be created with SQL. Views don't have
>indexes. This isn't a problem if you're retrieving the
>data with SQL, which will use the best available index
>automatically.

So a "view" would be like a non-keyed logical?

What about an "index"?  Is that an access path without a "view"?  Would you
use an index in conjunction with a table or a view?  What are the object types
of a table, view, and index?  PF, LF, and "??"?

Dan Bale
IT - AS/400
Handleman Company
248-362-4400  Ext. 4952

-------------------------- Original Message --------------------------
D.BALE@handleman.com wrote:
> Is it my impression that SQL can define a database file with all the
> attributes available to us in DDS, both PF & LF?  I understand that
DDS has
> only a subset of the attributes that can be defined in SQL (as of
V4R5?).  So
> my question is whether I lose any functionality by defining a
database file
> using SQL vs. DDS?
The only one that comes to mind is the indexed logical file. It can't
be created with SQL. Views don't have indexes. This isn't a problem if
you're retrieving the data with SQL, which will use the best available
index automatically.

>
> Also, is it possible to use SQL to define a database file on the
AS/400 if the
> SQL LPP isn't installed?  In my past utilization of SQL on an AS/400
which
> does not have the SQL LPP installed, I've used a shareware command
that
> formats the SQL string and executes it in a QMQRY using STRQMQRY.
>
> If so, is there reference material available that shows examples and
how
> things coded in DDS would be coded in SQL?  I.e.,
>    COLHDG('Item' 'Number')  ===> SQL ????????????
>    EDTCDE(3)                ===> SQL ????????????
>    key fields
>    joins
>    logical file stuff, like select/omit

You can execute any SQL command that can be prepared with STRQMQRY,
and that includes CREATE TABLE, CREATE INDEX and CREATE VIEW. I'm
about 87% certain that you can also execute CREATE PROCEDURE, but you
currently need to have the C LPP in order to specify an in-line
procedure. The applicable documentation is in the SQL reference under
CREATE xxxxxx,  SELECT, JOIN and UNION (these last three can be used
in CREATE VIEW).

> It seems to me that if IBM is serious about making DDS obsolete for
databases
> and making the AS/400 "open to the masses", it had better consider
making the
> entire SQL product a part of the OS, instead of charging money which
most
> shops aren't going to spend - "Buy SQL?  Why, when we already have
DDS?"

I believe that is the indended direction, with pretty much everything
(even the C compiler) being included when you purchase the application
development toolset. This hasn't happened yet, but I guess it will
with V5???.

HTH
Pete
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---