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).
Best Answer
INFORMATION_SCHEMA does not contain the long column names.