Re: IDDU, S36 & AS/400 SQL & RPG -Reply

[Vern Hamberg <hambergv@xxxxxxxxxxxxxx>]

Hi, John

Just some additions and clarifications:

At 10:35 PM 12/9/97 -0500, you wrote:

-snip-

>>While it is generally true that SQL don't know da difference 'tween a
>>"CREATE TABLE" objects and a "CRTPF" object, there are some
>>differences, at least at the system level.  One is that SQL tables can
>>not have more than one member, even though SQL can access
>>multiple member "CRTPF" files using OVDBF.  So somewhere down in
>>the bowels of the system, there is (are?) a difference(s).
>>Scott Cornell
>>Mercy Information Systems
>
>I'd be willing to bet that it is not 'down in the bowels of the system'.
>I think it may be 'at the top of the system'
>
>By that I mean,  Down in the bowels I think CREATE TABLE equates/runs
>the CRTPF CPP.   I think it may be the fact that there is not a 
>MBR parameter on the CREATE  TABLE SQL command. 
>
>Try creating a table with SQL and then go ahead an do a CHGPF and 
>change it to MAXMBRS(*NOMAX)  and add a couple of members.  I 
>bet you can.  I don't know for sure, I haven't got my machine on
>at this moment.  


You'll find that you can't—there're are some additional values in the file
description, under Database File Attributes. Here's the message you get
when you try to use *NOMAX:

>Message ID . . . . . . :   CPD3213       Severity . . . . . . . :   40
   
>Message type . . . . . :   Diagnostic
   
>Date sent  . . . . . . :   12/11/97      Time sent  . . . . . . :
11:35:33  
>
   
>Message . . . . :   Maximum-member value not valid for file SQLTEST.
   
>Cause . . . . . :   File SQLTEST in library PGMRVMH is either a Structured
   
>  Query Language (SQL) table, view or index, or a non-SQL file in SQL data
   
>  base PGMRVMH.
   
>    SQL tables, views, indexes, and non-SQL files that are in an SQL data
base
>  must have only one member; however, the value that you specified on the
   
>  maximum number of members (MAXMBRS) parameter is either a number greater
   
>  than one or *NOMAX.  For more information about SQL tables, views and
   
>  indexes, see the Programming: Structured Query Language Programmer's
Guide, 
>  SC41-9609.
   
>Recovery  . . . :   Change the value in the MAXMBRS parameter to 1 or
*SAME,  
>  and try the request again.
   


>The other major differences is that the CREATE TABLE creates a 
>journal receiver automatically and a few other thing I think.


The CREATE COLLECTION statement is the one that adds the journal receiver
stuff, as well as various SQL catalog views to the library that is created
(same name as  collection name)

You can CREATE TABLE into a non-SQL library. That's what I did for SQLTEST,
as follows:

        create table pgmrvmh/sqltest (field1 char(10) not null with default)

Lots of interesting stuff here. E.g., you can't retrieve records via an SQL
INDEX, although it's a logical file. However, the system uses it for
ordering records with ORDER BY clauses.

Cheers

Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN 55401
(612) 371-1111 x480


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@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
+---