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
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Tuesday, May 30, 2006 6:50 PM
To: Midrange Systems Technical Discussion
Subject: Re: Multi-member files - Big picture feedback

Thanks Dave.  Actually, the member vs non-member is not so much about 
relational design (although I am by no means an expert in DB 
design...it 
is a science in itself).  No, in this case it is mostly an issue of 
being able to do things "better" as the opportunity presents itself.

I am fairly at home with SQL and SQL techniques.  I write a 
fair amount 
of Java code, where appropriate, but I also love learning and 
applying 
new things in the RPG world. And, it goes without saying that the  
System i is my system of choice.  The struggle is properly leveraging 
the technologies. The question is, given the i5 platform and RPG as a 
language, what is the best way to do future development?

And, sure, "best" is a relative term. But I think that what you have 
recommended is pretty mainstream.  I suppose that the 
relative merits of 
SQL vs native I/O from a performance standpoint could be debated, 
certainly Joe's approach that says use both, if necessary, is 
a viable 
approach. I was just looking for general recommendations.  And, I got 
them (from you and others), thanks!

Pete Helgren

Dave Odom wrote:

Pete,

Since you have an opportunity to move to a modern way of 

doing things,

you can take advantage of the features of DB2/400, SQL, etc. 

and it will

have "legs" regardless of platform and almost regardless of logic
language.   

Having recently been in contact with folks in  IBM and seen their
strategic presentations for the i5, I can tell you they are 

going more

and more SQL and true relational, and less and less "native 

I/O", MUCH

less 5250 and more GUI and more.   

The struggle you may be having about how to do things in a true
relational design and giving up "members", may be due to a lack of
training in and understanding of relational database design and
application design against relational tables vs. keyed files.  If so,
that's certainly fixable and there are numerous books about how to
properly perform relational design.    Both inside and outside this
forum there are a HUGE number of people that can help you design your
database and application in a way that provides the business function
necessary, protecting the integrity of your data, all with 

performance. 

Don't think there was any "platform war" just legacy vs 

modern database

and application design methodologies and experience.    No 

ivory tower

here.

Sincerely,

David Odom     
 

-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.