Handling Multiple-Member Files

The AS/400 supports a file concept known as multiple-member files, in which one file (or table) can possess several different members. Each member is a part of the same file or table and shares the same schema, but the members are uniquely named and have unique data. ODBC and OLE DB have no built-in mechanism for accessing multiple members. By default, ODBC always accesses the first member in a multimember file. To enable ODBC-based applications such as Data Transformation Services (DTS) to access multiple-member files, you need to use the AS/400's SQL ALIAS statement. The ALIAS statement lets you create an alias for each member you need to access. Then, your ODBC application can access the alias, which in turn connects to the appropriate member. These SQL aliases are persistent, so you need to create them only once. The following statement shows how to create an alias:

CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)

This statement creates a new alias named FILE1MBR2 for the multimember file MYFILE. The ODBC or OLE DB application then connects to that specific member, using the alias name FILE1MBR2 to access the second member in the file MYFILE.