DB2 Temporary Table Migration

HCOSS supports the DECLARE GLOBAL TEMPORARY TABLE statement. When executed, HCOSS creates a SQL Server temporary table. HCOSS only supports use of the SESSION qualifier for global temporary tables, HCOSS does not support use of regular tables in the SESSION schema.

HCOSS does not support the direct migration of temporary tables created using the CREATE statement. However, you can accommodate this type of temporary table by converting the CREATE statement to a DECLARE statement, and adding it to the start of any program that references the table created by the converted CREATE statement. Use the following guidelines when adding a DECLARE statement to a program:
  • You must convert the application to use the SESSION schema as a qualifier for access to global temporary tables that were CREATEd on the mainframe
  • You can DECLARE a table multiple times; executing a DECLARE statement for a global temporary table that already exists has no effect
Once the DECLARE statements have been added to appropriate programs, HCOSS converts the tables as follows:
  • When DECLARE GLOBAL TEMPORARY TABLE is executed, HCOSS creates a temporary table.
  • Because SQL Server temporary table names always start with #, HCOSS generates a temporary table name using the original table name used in the mainframe code, plus a prepended # sign. For example, if the original table name is EMP, the SQL Server temporary table is named #EMP.