Three-part names in QMF commands to access data in a remote database

You can use QMF to access tables and views that are stored in a remote database by using a three-part name to refer to the table or view. By using three-part names, you can access and manipulate the data that you need without first having to use the QMF CONNECT command to connect to the remote database.

The name takes the following form:

location.owner.name

The parts of the name syntax have the following meanings:

location
The name of the database in which the table or view is stored. Location names can be up to 16 characters.
owner
The ID of the QMF user who created the table.
name
The name of the table or view.

The following example displays a table named STAFF that is owned by user Q and in a database named NEW_YORK:

DISPLAY TABLE NEW_YORK.Q.STAFF

You can also use three-part names in SQL queries to select data from tables in remote databases. For example, this query displays the same table as the previous DISPLAY TABLE command.

SELECT * FROM NEW_YORK.Q.STAFF

If you use three-part-names to perform operations at remote databases, keep in mind the following:

  • You can retrieve data from more than one table only if the tables are at the same location.
  • You can update tables that are at remote locations, but you can create tables at remote locations only if you first issue a CONNECT command to connect to the remote location.
  • An alias for the three-part name can be assigned for use in querying a remote table. For example, the alias NYSTAFF can be assigned to the table NEW_YORK.Q.STAFF. See your administrator for a list of aliases for tables and views that you need to access.
  • You can issue a GRANT statement at a remote location if you first connect to the remote location. You can grant privileges on a table that resides at the current location to users at other locations by using the clause PUBLIC AT ALL LOCATIONS on the GRANT statement. You cannot use a three-part name in GRANT statements if the three-part name refers to an object at the local Db2® database.
  • By default, three-part names cannot be used to access remote tables that contain LOB data. However, you can set the DSQEC_LOB_RETRV global variable to 2 or 3 to access LOB metadata or data with a three-part name. Or, you can use the CONNECT command to connect to the database, and then run the query to access the remote table.
  • Commands that include three-part names cannot:
    • Be directed to DB2® for VSE and VM databases
    • Be issued in queries or procedures that are run after QMF is started as a stored procedure