Accessing Files with Multiple Members Using SQL
SQL has no object type similar to an IBM OS/400 or IBM i5/OS multiple-member physical file. There are mechanisms which will allow an SQL-based interface to work with non-default members of mult-member files.
Resolving the problem
SQL has no object type similar to an IBM OS/400 or IBM i5/OS multiple-member physical file. When a SQL statement is run against a multiple member file, IBM DB2 UDB for iSeries will default to using the first member. Applications that use middleware such as ODBC, JDBC, OLEDB or .Net data providers to access multiple member files can use one of the following methods to access specific members.
|o||Stored Procedure Call to OVRDBF
OS/400 or i5/OS commands can be run in ODBC by calling the execute command system API. Use this technique to run an OVRDBF command to override to the proper member before running the SQL statement, for example:
CALL QSYS.QCMDEXC('OVRDBF FILE(USER1) TOFILE(MYLIB/USER1) MBR(COMPANY) OVRSCOPE(*JOB)', 0000000066.00000)
where 0000000066.00000 is a fixed decimal field with a length of 10 and 5 decimal places. The value 66 in the sample above must be the length of the string (the number of characters including spaces between the single quotes).
Note: The number must be zero filled.
An example of this is included in the Microsoft Visual Basic ODBC sample program saved in file vbODBCT4.zip available from the following ftp server:
OS/400 R430 and later support an SQL alias statement. Create an alias for each member that must be accessed, then reference the alias from the application. The alias is a persistent object -- it must be created only once. The member referenced in the CREATE ALIAS does not have to exist when the ALIAS is created. Any SQL tool, such as OS/400 or i5/OS interactive SQL (STRSQL) or iSeries Navigator's Run SQL Scripts, can be used to create the alias, for example:
CREATE ALIAS MYLIB.FILE1MBR1 FOR MYLIB.MYFILE(MBR1)
CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)
The application then specifies the alias, MYLIB.FILE1MBR1 or MYLIB.FILE1MBR2, depending on which member it wants to access.
More support for:
Software version: Version Independent
Operating system(s): IBM i
Reference #: N1018261
Modified date: 28 February 2013