Troubleshooting
Problem
SQL has no object type similar to an IBM i multiple-member physical file. There are mechanisms that allow an SQL-based interface to work with non-default members of multmember files.
Resolving The Problem
SQL has no object type similar to an IBM i multiple-member physical file. When an SQL statement is run against a multiple member file, DB2 for IBM i defaults 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.
- Stored Procedure Call to OVRDBF
IBM i 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 must be the length of the string (the number of characters including spaces between the single quotation marks ).
Note: The number must be zero filled.
An example 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 - 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 IBM i interactive SQL (STRSQL) or Access Client Solutions' 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:
- 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. ODBC maps the ALIAS to type SYNONYM when an application requests SYNONYM rather than ALIAS.
- 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.
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
13664534
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
Version Independent
Operating system(s):
IBM i
Document number:
642505
Modified date:
27 December 2019
UID
nas8N1018261
Manage My Notification Subscriptions