Can I use a library list for resolving unqualified table and view names?
The i5/OS® operating system supports a limited capability to use the operating system naming option when accessing DB2 Universal Database™ for iSeries™ data from a DRDA® client program other than System i™, such as those that use the DB2® for Linux®, UNIX®, and Windows® product.
Previously, only the SQL naming option has been available when connecting from unlike DRDA clients. System naming changes several characteristics of DB2 UDB for iSeries. For example:
- The library list is searched for resolving unqualified table and view names.
- When running a CREATE SQL statement, an unqualified object will be created in the current library.
- A slash (/) instead of a period (.) is used to separate qualified objects' names from the library or collection in which they reside.
- Certain authorization characteristics are changed.
For details, read about system naming in the SQL reference. For more information about the implications regarding authorization, see Planning and design.
DB2 for Linux, UNIX, and Windows supports the specification of generic bind options on two of its program preparation commands, the precompile (PREP) command and the (BIND) command. i5/OS naming can be specified on either of them as in the following examples drawn from a Windows batch file:
For DB2 Universal Database for Linux, UNIX, and Windows, Version 8, and later:
DB2 PREP %1.SQC BINDFILE OS400NAMING SYSTEM ... DB2 BIND %1.BND OS400NAMING SYSTEM ...For DB2 Universal Database for Linux, UNIX, and Windows, version earlier than Version 8:
DB2 PREP %1.SQC BINDFILE GENERIC 'OS400NAMING SYSTEM' ... DB2 BIND %1.BND GENERIC 'OS400NAMING SYSTEM' ...Note that on the Windows development platform, single quotation marks are used around the generic option name/value pair. On an AIX® or UNIX platform, quotation marks should be used.
For OS/400® V4R5 and V5R1, the name of the option is AS400NAMING, not OS400NAMING.
The only valid value for the OS400NAMING option besides SYSTEM is SQL, which is the default value and is the only possible option from a non-i5/OS client prior to the introduction of this feature.
If you use the OS400NAMING option on the (BIND) command but not on the (PREP) command, then you might need to code a parameter on the (PREP) command that indicates that a bind file should be created in spite of SQL errors detected by the system. In the case of DB2 for Linux, UNIX, and Windows, use the SQLERROR CONTINUE parameter for this purpose. The capability is described as limited because in certain situations, the client-side software might parse an SQL statement intended for execution on the remote system. If a slash instead of a period is used to separate a schema ID from a table ID, as is required for system naming, the statement might be rejected as having incorrect syntax.
Parent topic:
User FAQs
Related concepts
Planning and design
Related reference
SQL reference