Re: SQL views over multi member physical file

[CRPence <CRPbottle@xxxxxxxxx>]

  A database physical file implements the SQL TABLE, with at least the 
one restriction that there is just one member.  The SQL has no concept 
of member, so the existence of the member is merely an /implementation/ 
detail; the DB2 for i SQL has an _extension_ to enable reference to a 
specific member of a PF via the ALIAS or OVRDBF.  A database physical 
file also implements the SQL PARTITIONED TABLE which does not have the 
same one-member restriction of the non-partitioned TABLE; instead, a 
different restriction of 256 members IIRC.  Because the database PF 
object which implements these other SQL objects has an overall 
limitation of ~32K members [for its non-SQL definition as a TABLE], the 
PF is quite capable of implementing both a single-member and an up-to 
256-member [i.e. 256-partition] SQL TABLE.  The database [and\or SQL] 
feature need only implement any additional restrictions and support any 
additional methods, beyond what is allowed for non-SQL utilization.  One 
method allowed for an SQL TABLE, partitioned or otherwise, is the SQL 
CREATE VIEW.  Note: the references to 256 in the above might be 1024 
instead.?.?  As to what data is selected by the VIEW, that depends on 
the SELECT just like with any other VIEW; e.g. the WHERE clause locates 
the data according to the hash or range partitioning that had defined in 
which member\partition the data resides.

  Both for the apparent lack of a key and the desire for non-relational 
access of the data, as described in the OP, the use of SQL partitioning 
is unlikely to be of much use for the scenario.

Regards, Chuck

On 08 Aug 2013 14:05, Alan Shore wrote:
> At the moment if you could see my face, you would see my look of
> 'HUH!!!!' How would this be done? Curious if nothing else.
>
> Charles Wilt on Thursday, August 08, 2013 4:57 PM wrote:
>
> > Note that you can create views over a partitioned table; even
> > though it's implemented as a multi-member PF. :)
> >
> > On Thu, Aug 8, 2013 at 4:50 PM, Alan Shore wrote:
> >
> > > Thanks for the reply Charles, but I am now leaning more to
> > > creating a brand new file (non-multi member) so that it will be
> > > easier to create views over
> > >
> > > Charles Wilt on Thursday, August 08, 2013 4:33 PM wrote:
> > >
> > > > Question: how do you determine what data goes in what member?
> > > >
> > > > The reason I ask, If you create an SQL DDL defined
> > > > "partitioned" table, the table is actually implemented as a
> > > > multi-member physical file.
> > > >
> > > > select * from table
> > > >
> > > > actually returns all data in all members in that case.  No
> > > > alias needed.
> > > >
> > > > Downside: you have to have the DB2 Multisystem license program
> > > > (5722-ss1 option 27) installed which is a extra $$$ option.
> > > >
> > > > http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbmult/partitionedtables.htm