Re: SQL-defined files/tables

[rob@xxxxxxxxx]

Phil,

A comment may be confusing.
You can have an indexed logical file - use CREATE INDEX.  Perhaps what you
meant is that DDS does have the capability to do a combination of CREATE
INDEX and CREATE VIEW.  CREATE VIEW allows you to select certain fields and
to create computed fields but doesn't allow you to sort.  CREATE INDEX
allows you to sort but doesn't have much other capability.

Dan,

You can create the separate QMQRY members for each file using SEU then do a
CRTQMQRY to 'compile' them into QMQRY objects even if you do not have SQL
installed.  Then to create the files use STRQMQRY.  This way you have
permanent source for the file.  However, the neat thing about SQL is that
the file itself should contain the SQL statement used to create it in the
DSPFD of the file.  Well, at least the logicals do:
...
Maximum members . . . . . . . . . . . . . . : MAXMBRS        1
Number of members . . . . . . . . . . . . . :                1
SQL view create statement . . . . . . . . . :
  CREATE VIEW VIEWA (COLA1, COLA2, COLA3, SUM) AS SELECT COLA1, COLA2,
   COLA3, COLA2+COLA3 FROM QTEMP.TABLEA
Record format selector program  . . . . . . : FMTSLR     *NONE
Records to force a write  . . . . . . . . . : FRCRATIO   *NONE
Maximum file wait time  . . . . . . . . . . : WAITFILE   *IMMED
...
Beats the heck out of me why IBM does this for the logicals and not the
physicals.

One other SQL limitation might be the multiformat logical file.  But
perhaps I just threw up a challenge.

Rob Berendt

==================
Remember the Cole!


                                                                                
                                         
                    pbhall@execpc.com                                           
                                         
                    Sent by:                   To:     MIDRANGE-L@midrange.com  
                                         
                    owner-midrange-l@mi        cc:                              
                                         
                    drange.com                 Subject:     Re: SQL-defined 
files/tables                                 
                                                                                
                                         
                                                                                
                                         
                    12/07/00 04:03 PM                                           
                                         
                    Please respond to                                           
                                         
                    MIDRANGE-L                                                  
                                         
                                                                                
                                         
                                                                                
                                         




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



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