Select all data from IBM DB2 member table
The best solution, is to install the DB2 Multisystem product and recreate the table as a local "partitioned table".

Behind the scenes, DB2 for i creates the table as a multi-member PF basically just like you have now.

However, SQL statements referencing the table will auto-magically see all the data.  Old RPG applications that use OVRDBF to override to a specific member will continue to work as before.

Unfortunately,  DB2 multisystem is not cheap.  If you don't already have it, i doubt you'd want to get it.

You can create a DDS logical file with DTAMBRS(*ALL) or use OVRDBF MBR(*ALL) to create a single "view" of the data.  But unfortunately, only RPG programs using record level access (RLA - aka "native RPG I/O op-codes) can use them.  SQL access results in a CPF4268 - Object *ALL in *N type *MEM not found.

The only SQL statement in DB2 for i that knows anything about members is the CREATE ALIAS statement.  So you could issue a CREATE ALIAS as needed to access the member you need.  Or you could pre-create alias' for every member.

You can then use the alias' in a select statement like so

select * from myalias1
UNION ALL
select * from myalias2


It'd be nice if you could create a view from the statement above, unfortunately you can't.  Trying to do so results in an SQL7030 - Alias MYALIAS1  for table MYTABLE in MYLIBRARY not valid for statement.

The only way to encapsulate the statement I'm aware of would be to define it as a stored procedure or user defined table function (UDTF).