Extended Dynamic Support and Resolution of Unqualified Objects

Users of IBM i database middleware products that utilize extended dynamic support should be aware of certain settings and events that may seem to cause incorrect object qualification or resolution, but are actually functioning as intended. This document pulls together information from different parts of the SQL Reference to explain this behavior. For the sake of brevity, this document refers to unqualified table names, but it applies equally to the following items: alias, constraint, external program (not to be confused with a stored procedure), index, node group, package, sequence, table, trigger, and view names.

Typically the default collection is used to resolve unqualified tables when the SQL naming convention is used. If no default collection is specified, the authorization identifier schema is used to resolve unqualified tables. For dynamic SQL, the runtime authorization identifier schema is defined as the schema with the same name as the user ID used to make the connection. When extended dynamic (SQL package) support is used, the qualification rules for static SQL are applied rather than the rules for dynamic SQL. Static SQL uses the authorization identifier schema. However, in this case, the SQL statement is saved in an SQL package so the authorization identifier might not be the user ID that the connection is made under. It is the owner of the SQL package. Applications often have a mix of SQL statements. Some of the statements are saved in the SQL package and some are not.  The presence or lack of a statement in an SQL package can result in what seems to be inconsistent behavior as the default schema seems to switch between two schemas.

Not all SQL statements that are prepared are saved in an SQL package. In most cases, it is parameterized statements that are saved. For a listing of the types of SQL statements for which enhanced prepares are performed, refer to iGSC technote: SQL Package Questions and Answers.

If this behavior is not accounted for, the result can be message SQL0204 or Db2 might resolve to an object other than the one that was intended. Care must be used when there is no default collection specified on the connection. In this case, it is important to ensure that SQL packages are not shared between users.

The IBM i Access Client Solutions Windows Application Package ODBC driver provides an easy-to-use graphical interface to allow the data source to use a specific SQL package per application. To use this feature, open the data source in the ODBC data source administrator. Click the Packages tab, and press the button entitled "Customize settings per application" to open the "Package Information" window where a package can be uniquely identified. Note the "Application name" list is editable, and it requires the registered application name. It might be necessary to use the application once with the data source so that it appears on the list.

The IBM Toolbox for Java or JTOpen JDBC driver can also use extended dynamic (package) support. It is disabled by default ("extended dynamic" = "false").

Refer to the javadoc for a complete listing of the IBM Toolbox for java JDBC driver properties.

A majority of scenarios are unlikely to see a performance benefit from enabling extended dynamic support.

The following sections of the "Db2 for i SQL Reference" manual describe the rules that result in this behavior:

SQL Path
Qualification of unqualified object names
Authorization IDs and authorization names