Db2 – Select all data from IBM DB2 member table


Old as400 system has this thing called 'Members'. Basically you can have multiple members on a table and then work with only selected one.
More Info

When you select from a table that has members you have a few options. Just doing a select will retrieve data from the first member. Or call a stored procedure to set the member for the table and retrieve it. Or create an alias for the table with members to access it. But there is no way to just get a

Is there a way to select ALL data from the table from ALL the members?

Best Answer

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
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).