Re: SQL views over multi member physical file

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