Re: Multi-member files - Big picture feedback

I followed up on  the hyperlink that Rob posted about multi-member SQL defined 
tables which  included a number of reasons for having multiple members, 
including performance  considerations pertaining to record level access, 
copying, saving, restoring,  reorganizing, indexing, and generally working with 
partitioned data.  There were  many good reasons for partitioning physical 
files into  members.
  
 I tried using the  CHGPF command to set the MAXMBRS parameter to *NOMAX of an 
SQL generated  physical file, which produced an error with the following help  
text:
  
 "SQL tables,  views, indexes, and non-SQL files that are in an SQL data base 
must have only  one member...", so I guess that confirms that the DB2 
MultiSystem product must  be installed, first.
  
 Another  alternative is to simply use DDS to create physical files and set 
MAXMBRS to  *NOMAX.
  
 I also followed up  on a number of references to performance considerations 
between SQL defined  tables and indexes, and DDS defined tables and indexes, 
and found a mixed bag  considerations that were essentially irrelevant for most 
applications, and  concluded that SQL and DDS are essentially comparable as 
well as compatible.   There is no clear winner in the SQL vs. DDS debate.
  
 I use DDS for most  table definitions simply because it's more readable and 
easier to work with.  If  you need SQL DDL then iSeries Navigator provides a 
nice utility for generating  SQL DDL from physical files.
  



----- Original Message ----
From: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, May 31, 2006 11:19:12 AM
Subject: RE: Multi-member files - Big picture feedback

Pete,

Rob touch on the idea, but let me reiterate it.

You can use multi-membered files with SQL.  Using v5r3 partitioned
tabled support, you get the benefits of multi-member files without the
drawbacks.

CREATE TABLE FOO (A DATE) PARTITION BY RANGE(A) (STARTING('2001-01-01')
ENDING('2010-01-01') EVERY(1 YEARS))

SQL access methods, including ODBC and JDBC, see a single table but data
is actually stored in multiple members.  I don't know if RPG sees a
single table or if you have to use OVRDBF or the EXTFILE keyword on the
f-spec.

I would create all physical files using SQL DDL.  Since you can't have
an index on a SQL view, you may need DDS logicals for some RPG native
access requirements.  But for most RPG requirements an SQL index works
fine.


HTH,

Charles Wilt