How to use the host variable in a declared global temporary table statement?

whenever you try to use the host variable in the declared global temporary table then you will get the error “Host variable not permitted here” in SEU. The free format sqlrpgle code to use host variable in a declared global temporary table would look like below:

     Dl_empid          S             10A               
      /free                                            
       exec sql                                        
       drop table session.gtm2;                        
                                                       
       exec sql                                        
       declare global temporary table session.gtm2 as  
       (select * from pf1                              
       where empid = :l_empid)                         
       with data;                                      
       return;                                         
      /end-free                  

Once you write the above code and try to save the sqlrpgle source member in SEU then a syntax error appears on your display screen. Once you press Enter to save it. You will see the error Host variable not permitted here. Take F3 to exit from the source member and try to compile it using the CRTSQLRPGI command or option 14 on the respective source member. You will notice the compilation error SQL precompile failed. To see what was the error Just type WRKJOB on the command line and press Enter, Type 4, and press Enter to work with the spool file. Press SHIFT+F6 to come to the bottom and type option 5 on your program compile the spool file whose user data is CRTSQLRPGI and press Enter. Just come to the bottom of the spool file and you will notice severity 30 error SQL0090 whose text is Position 23 Host variable not permitted here..

Therefore, to resolve this by using a host variable in a declare global temporary table command we can go for an alternate approach. Till now we have confirmed that we cannot use the host variable directly with the declare global temporary table command. But what about using it indirectly i.e. in two steps, first creating the file and then loading it? Let’s try for it in a sample sqlrpgle program.

     Dl_empid          S             10A   inz('1')     
      /free                                             
       exec sql                                         
       drop table session.gtm2;                         
                                                        
       exec sql                                         
       declare global temporary table session.gtm2 as   
       (select * from pf1)                              
       definition only;                                 
                                                        
       exec sql                                         
       insert into session.gtm2                         
        (select * from pf1                              
         where empid = :l_empid);                       
                                                        
       return;                                          
      /end-free       

The expression definition only in the declaration tells the system to create the table using the fields found in the select clause, but it also tells not to insert any data. After the table is created, I load it using an insert that takes host variables into account.

Compile the source code using option 14, run it and your table GTM2 would get created in the QTEMP library. This time in the global temporary table you would end up loading records for empid = ‘1’.
select * from qtemp.gtm2

You can also create a temporary SQL file using the LIKE clause in the declare global temporary table command in your fully free sqlrpgle program which is as follows:

**free                                               
exec sql                                             
 drop table session.gtm5;                            
                                                     
exec sql                                             
 declare global temporary table session.gtm5 like pf1
 rcdfmt recfl2;                                      
return;