The error "SESSION.tabname IS AN UNDEFINED NAME" occurs when joining a temporary DB2 table


Errors might occur when you do a join operation using a DB2 temporary table and DB2 permanent tables, where the temporary table comes after any permanent tables in the FROM clause. The following errors occur:

ERROR: DB2 prepare error: DSNT408I SQLCODE = -204,
ERROR: SESSION.tabname IS AN UNDEFINED NAME.

As a result, the join is performed in SAS rather than being passed down to the database management system (DBMS). If, however, the temporary table occurs before any permanent tables in the FROM clause, the code works correctly and the statement is passed to the DBMS.
For example, the following code works correctly, where DB2TEMP is the temporary libref:

proc sql;
connect to db2(connection=global);
   create table test2 as
   select a.object
   from db2temp.keys a,
        mydb2.keys b
       where a.object = b.object
       ;

The following results in the -204 error:

proc sql;
connect to db2(connection=global);
   create table test2 as
   select a.object
   from mydb2.keys a,
        db2temp.keys b
       where a.object = b.object
       ;

The solution in this case is to use "connection=global" for all librefs when temporary DB2 tables are used.