Global Temporary Tables And Host Variables, Take 2

In Global Temporary Tables and Host Variables, I complained that DECLARE GLOBAL TEMPORARY TABLE does not permit me to use host variables. I presented two ways to circumvent the problem. Today I revisit the topic to show another way that was not available six years ago.

First, create some global variables.

   create variable somelib.gv_Company   dec(3);
   create variable somelib.gv_Customer  dec(5);

Then use those global variables in your program.

exec sql
   set gv_Company  = :inCompany;
exec sql
   set gv_Customer = :inCustomer;
exec sql
   declare global temporary table Temp1 as
       (select *
          from sales
         where companyno  = gv_Company
           and customerno = gv_Customer)
     with data with replace;

DECLARE GLOBAL TEMPORARY TABLE has no qualms with the use of global variables.

Global variables are still new to me. I suspect I have not even begun to understand the many ways to use them effectively.

Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.

RELATED STORIES

New in DB2 for i 7.1: Use Global Variables to Track Environment Settings

Global Temporary Tables and Host Variables