AS400 member

I need to query data through SSIS from what I was told is an AS400 DB2 member table. I am assuming this is a sub-table of the main table. I was going to write a correlated sub-query in SQL to get this data, however our AS400 contracted programmer says that there is an easier way and she pointed me to the main table's member. I do not know how to go about accessing this.

Has anybody had experience with this? The AS400 programmer is familiar with SQL syntax, however she does not know how to have SQL grab the data from a member table.

If all else fails, I will just construct my correlated sub-query.

Thanks for the information.

I assume you are using a multi-member physical file PF(the AS400 refers to files rather than tables). The default member is *FIRST and generally speaking they are only created with a single member (you could treat it like a normal table). Multi-member is a throw back to the days of card files and tape files where you had header/detail/trailer records (typically banks/insurance) with different record layouts (long before SQL was conceived. the 70s). You can get a list with the command DSPFD Library/File *MBRLIST

If you want to retrieve data from the above Library/File.member in SQL you can try the following (i have never needed to run sql on multi-member files)

Select * from Library/File.member OR Select * from Library/File(member) OR

Select * from Library.File(member)

Let me know if you need more help

|||

These types doesnot work.

I have a situation. Its like we to migrate from IBM DB2 to SQl Server. But how will we move mulri membered files? Can anyone help?

|||

Just make sure you have the latest Client Access (OLEDB) drivers for DB400. there is a user group for As400 (now called iSeries) where i am sure u will get resolution (comp.sys.ibm.as400.misc)

As far as migration is concerned

You will have 2 kinds of multi-member files.

Each member has the same columns