how to declare
a global temporary table
in the rpgle program. I was working on creating the temporary SQL file
in my rpgle program that I do not want to share with any other job/session which means only the present job/session in which this file gets created can be accessed. I know that I can easily achieve this by simply creating a table in the qtemp library since qtemp is unique to each job/session and no job/session can access each other qtemp library. But I just researched and discovered a global temporary table in SQL that is the same as a temporary SQL file
and no other session/job can access it. How to write a rpgle program using the declare a temporary table
to create the temporary SQL table
in the qtemp library? By rpgle I mean sqlrpgle since it’s a SQL file and I can understand we need to embed the SQL statement inside rpgle program to use this feature.
Declare global temporary table
statement defines a temporary table for the current session. The temporary table when declared then it is created in the work file database and its description is not populated in the system catalog files. This global temporary table cannot be shared with other sessions. Once the session ends, the global temporary table is dropped.Sample fully free sqlrpgle program to declare global temporary table.
Compiling and running this code will create a file GTM1 in the QTEMP library.
select * from qtemp.gtm1
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:
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 errorHost 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 errorSQL 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 errorSQL0090
whose text isPosition 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.
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 write a sqlrpgle program using the host variable in the declare temporary table in this way as well where you don’t have to use select twice as you used in your above example.
You can also include the record format name in creating a temporary SQL file using the declare global temporary table command in your fully free sqlrpgle program which is as follows:
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: