Global Temporary Tables and Host Variables

Published: March 31, 2010

by Ted Holt

Global temporary tables are a marvelous, powerful feature of SQL, allowing SQL programmers to create temporary tables as needed in order to build complex queries. One problem you will encounter occurs when you want to use a host variable within a program to limit the amount of data loaded into the table. You may not use a host variable in a Declare Global Temporary Table command. Fortunately, this is an easy limitation to circumvent.

First, let's look at an example of the problem. In the following code fragment, I attempt to create a global temporary table using data for one customer only.

D MyPgm
D  inCompany                     3p 0 const
D  inCustomer                    5p 0 const

 /free
     exec sql
        drop table session/Temp1;
     exec sql
        declare global temporary table Temp1 as
            (select *
               from sales
              where companyno  = :inCompany
                and customerno = :inCustomer)
          with data;

This does not compile. In fact, even SEU won't accept it.

The solution is to create the file and load it in two steps. Here's one possibility.

exec sql
    drop table session/Temp1;
exec sql
    declare global temporary table Temp1 as
       (select * from sales)
     definition only;
exec sql
    insert into session/Temp1
       (select *
          from sales
         where companyno  = :inCompany
           and customerno = :inCustomer);

The phrase "definition only" in the declaration tells the system to create the table based on the fields in the select clause, but not to put any data into the table. Once the table exists, I load it with an insert, which does accept host variables.

I rarely use this technique. In this example, I intentionally kept the select simple, pulling data from one table only, but it would be more realistic to pull data from more than one table, like this:

select h.companyno, h.customerno, h.saledate, h.shipdate,
       h.termcode, t.termdesc,
       co.custname, co.class,
       co.address1, co.address2, co.city,
       co.state, co.postalcode, d.lineno, d.itemno,
       d.quantity, d.price,
       d.quantity * d.price as extended,
       i.itemdesc, i.itemtype, i.itemclass
  from saleshdr as h
  join customer as cu
    on cu.companyno  = h.CompanyNo
   and cu.customerno = h.CustomerNo
  join salesdtl as d
    on h.orderno = d.orderno
  join items as i
    on d.itemno = i.itemno
  join company as co
    on cu.companyno = co.companyno
  join term as t
    on h.termcode = t.termcode
 where companyno  = :inCompany
   and customerno = :inCustomer);

Imagine replacing both of the select statements in the first example with that or worse. The first select would not have to have the where clause, but having to keep the rest of the two instances of the select in sync when changing the query seems less than ideal to me. The first technique is OK for very simple queries, but most queries are not simple.

Here's a second technique that uses similar principles and doesn't require duplication of the select statement.

exec sql
    drop table session/Parms;
exec sql
    declare global temporary table Parms
       (CompanyNo  dec(3,0), CustomerNo dec(5,0));
exec sql
    insert into session/Parms
       values(:inCompany, :inCustomer);
exec sql
    drop table session/Temp1;
exec sql
    declare global temporary table Temp1 as
       (select h.*
          from session/Parms as p
          join sales as h
            on p.companyno  = h.CompanyNo
           and p.customerno = h.CustomerNo)
     with data;

In this example, I create two temporary files--Temp1 to hold the sales data and another one called Parms. The Parms table has only one row (record), into which I load the values of the host variables. From there, I use joins (often an inner join, as in this example, but also often a cross join) to access the host variable values in queries.

I find this technique more useful, especially when the values of the host variables are called for in the building of many temporary tables within a program.

The ability to create global temporary tables is a great boon for SQL programming, and fortunately, the lack for support for host variables in global temporary table definitions is not a show-stopper.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot