Re: Global Temporary Table

On 11 Jun 2012 06:52, Michael Schutte wrote:

<<SNIP>>

DECLARE GLOBAL TEMPORARY TABLE NEW_FILE LIKE OLD_FILE;

Will create the table in QTEMP and will exist as long as the JOB
is active. The Job could mean Workstation if that's where it's
being called from.

When you execute this statement, you would want to check for
SQLCODE = -601. If equal then table already exists. So now
you need to delete all records to clear it.

Personally, I prefer to drop the table and rebuild it. Too
many times, I've needed to change the table and had phone calls
saying something wasn't working because the user still had the
old version of the table active in their workstation job.

The syntax includes -WITH REPLACE- clause. But since the OP is not journaling [explicitly noted], then they are also not using isolation. That makes me wonder if the other capabilities of GLOBAL TEMPORARY TABLE [e.g. ON COMMIT; NOT LOGGED ON ROLLBACK] versus just a standard CREATE TABLE into QTEMP might not be justified if only for the WITH REPLACE capability.? That is, aside from the capabilities for implicit journal and commitment control for the GTT, what value do they have over simply CREATE TABLE [()\AS\LIKE, the CPYF, or the CRTDUPOBJ] the OP might do otherwise?

Regards, Chuck