Extender for Db2 > Using the Extender for Db2 and SQL > Table Naming Conventions

Table Naming Conventions

Topics:

Users must follow table naming conventions when accessing tables. SQL requests must be structured to contain either:

Mixed destination requests containing both local Db2 subsystem tables and database tables accessible to the server are not permitted.

The Extender for Db2 directs each SQL SELECT statement issued against a table based on the set of rules outlined in this section. There are three types of tables known to the user.

Fully-Qualified Tables

Fully-qualified table names consist of three-part names and contain an explicit location. The syntax for a fully-qualified table is 

location.creator.tablename

where:

location

Is the location of the data. If location is one of the current servers in the Client communications configuration file, then the location is a valid server location. This is an 8-byte (character) field.

creator

Is the creator of the table. This is an 8-byte (character) field. Currently, it is ignored for tables residing in the server.

tablename

Is the name of the table. For the server, it must be a Master File name or a system-defined catalog name.

For example,

EDASERVE.JAMES.EMPLOYEE

indicates that the table EMPLOYEE was created by JAMES and can be accessed through a server called EDASERVE. The name of the server is determined at installation. The server's communications configuration file contains the name of the server and is established at installation.

Partially-Qualified Tables

Partially-qualified table names have one- or two-part names and do not contain an explicit location. For partially-qualified table names, the Extender for Db2 parses the SQL CONNECT TO server command to determine whether to route the request directly to Db2, or to the server. Partially-qualified table names can be listed in the EDAPARMS configuration file under the WORKTABLE keyword.

The syntax for a partially-qualified table is

creator.tablename

where:

creator

Is the creator of the table. This is an 8-byte (character) field. It is ignored for tables residing in the server.

tablename

Is the name of the table. For the server, it must be a Master File name or system-defined catalog name.

For example,

WATSON.SUPPLIER

indicates that the SUPPLIER table was created by WATSON.

While parsing the SQL request, the Extender for Db2 determines if the object has a one-part or two-part name. If so, the Extender for Db2 handles the object as a partially-qualified table. Based upon the most recent SQL CONNECT TO issued, the Extender for Db2 resolves the destination of the partially-qualified table name to be the current server.

  • If an SQL CONNECT TO is issued to a valid server, the request is sent to the server. Otherwise, it is sent to Db2.
  • If no SQL CONNECT TO was issued, the Extender for Db2 checks the EDAPARMS file to determine if a default server was declared using the EDASERVER keyword. For information about the contents of the EDAPARMS file, see Configuring the EDAPARMS File.
    • If a default server is declared in EDAPARMS, the request goes to that default server.

      However, if the partially-qualified table name matches a worktable specified in the EDAPARMS file, the default server is overridden and the request is sent to the local Db2 subsystem.

    • If there is no default server and no worktables are defined, the request is sent to Db2. For more information on support for the SQL CONNECT verb, see Connecting to Multiple Servers.

Product Work Tables

Each product that uses the Extender for Db2 can have its own work tables and profile logs, all located outside of the server. They must be listed in the WORKTABLE keyword list in the EDAPARMS configuration file. The Extender for Db2 considers all of them non-server and sends corresponding queries referencing these tables to Db2.

Table names should be 8-byte (character) fields. The rules in Partially-Qualified Tables also apply to naming product work tables.

WebFOCUS

Feedback