Accessing remote declared temporary tables by using three-part table names

You can access a remote declared temporary table by using a three-part name. However, if you combine explicit CONNECT statements and three-part names in your application, a reference to a remote declared temporary table must be a forward reference.

In a CREATE GLOBAL TEMPORARY TABLE or DECLARE GLOBAL TEMPORARY TABLE statement, you cannot specify an alias that resolves to a three-part name object at a remote location. You also cannot specify a three-part name object even if the location of the three-part name refers to the location where the object is being created or declared.

Example

You can perform the following series of actions, which includes a forward reference to a declared temporary table:

EXEC SQL CONNECT TO CHICAGO;              /* Connect to the remote site */
EXEC SQL 
  DECLARE GLOBAL TEMPORARY TABLE T1 /* Define the temporary table */
  (CHARCOL CHAR(6) NOT NULL)             /* at the remote site         */
   ON COMMIT DROP TABLE;                 
EXEC SQL CONNECT RESET;                   /* Connect back to local site */
EXEC SQL INSERT INTO CHICAGO.SESSION.T1 
  (VALUES 'ABCDEF');                      /* Access the temporary table*/
                              /* at the remote site (forward reference) */

However, you cannot perform the following series of actions, which includes a backward reference to the declared temporary table:

EXEC SQL 
  DECLARE GLOBAL TEMPORARY TABLE T1 /* Define the temporary table */
  (CHARCOL CHAR(6) NOT NULL)             /* at the local site (ATLANTA)*/
   ON COMMIT DROP TABLE;
EXEC SQL CONNECT TO CHICAGO;              /* Connect to the remote site */
EXEC SQL INSERT INTO ATLANTA.SESSION.T1 
  (VALUES 'ABCDEF');                      /* Cannot access temp table  */
                            /* from the remote site (backward reference)*/

Example using an alias

You can perform the following series of actions, which includes a forward reference to a declared temporary table using an alias. First you need to declare the alias at the requester. The name you give the alias must resolve to match the real name.

CREATE APPLT1 FOR CHICAGO.SESSION.T1

The CONNECT and DECLARE statements refer to the real declared temp table.

EXEC SQL CONNECT TO CHICAGO;
EXEC SQL DECLARE GLOBAL TEMPORARY TABLE T1 
 (CHARCOL CHAR(6) NOT NULL)
  ON COMMIT DROP TABLE;
EXEC SQL CONNECT RESET;
EXEC SQL INSERT INTO APPLT1 VALUES ('ABCDEF');