1

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?

0

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

  • Thank you for input. I think i'll just write a script to go though each member and pull out data. IBM does not make it easy to use their systems. – Ruslan Sep 27 '16 at 13:45
  • IBM makes it very easy to use their systems. The problem is trying to combine certain legacy functionality with new "standardized" functionality. The RDBMS inside IBM i OS has been around longer than SQL has. Personally, I got rid of my multi-member files 20 years ago so that I could use SQL without issue. – Charles Sep 27 '16 at 15:07
1

This question: https://stackoverflow.com/questions/353424/list-of-source-members-in-a-file-with-sql provides sql to list the members in a table:

SELECT TABLE_PARTITION FROM SYSPARTITIONSTAT
WHERE TABLE_NAME = 'MYTABLE' AND TABLE_SCHEMA = 'MYSCHEMA'

You can then loop through the list & use sql along these lines to obtain access to each member in turn:

CREATE ALIAS QTEMP.MYMBRB FOR MYSCHEMA.MYTABLE (MBRB)

allowing you to pull the data:

SELECT * FROM QTEMP.MYMBRB

Hope this helps.

Or, if you are happy to use IBM i CL commands, there is probably a way of doing it using OVRDBF (which has a MBR(*ALL) option), but I am not in a position to try that route right now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.