IBM Support

Accessing Files with Multiple Members Using SQL

Technote (troubleshooting)


Problem(Abstract)

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:

ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/VbOdbcT4.zip
o SQL Alias

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.
Other considerations when using an alias:
o An alias is returned by the catalog function as type "ALIAS". Some applications might not request or display this type of file in their list of available tables. iSeries Access ODBC will map the ALIAS to type SYNONYM if an application requests SYNONYM rather than ALIAS.
o There are several restrictions on SQL statements that can be used against an SQL alias name that references a member. See the SQL Reference section of the iSeries Information Center for further information.

Historical Number

13664534

Document information

More support for: IBM i
Host Servers

Software version: Version Independent

Operating system(s): IBM i

Reference #: N1018261

Modified date: 28 February 2013