At what point are DB2 declared global temporary tables automatically deleted...?

Question

When are DB2 declared global temporary tables 'cleaned up' and automatically deleted by the system...? This is for DB2 on AS400 v7r3m0, with DBeaver 5.2.5 as the dev client, and MS-Access 2007 for packaged apps for the end-users.

Today I started experimenting with a DGTT, thanks to this answer. So far I'm pleased with the functionality, although I did find our more recent system version has the WITH DATA option, which is an obvious advantage.

Everything is working, but at times I receive this error:

SQL Error [42710]: [SQL0601] NEW_PKG_SHEETS_DATA in QTEMP type *FILE already exists.

The meaning of the error is obvious, but the timing is not. When I started today, I could run the query multiple times, and the error didn't occur. It seemed as if the system was cleaning up and deleting it, which is just what I was looking for. But then the error started and now it's happening with more frequency.

If I make strategic use of DROP TABLE, this resolves the error, unless the table doesn't exist, in which case I get another error. I can also disconnect/reconnect to the server from my SQL dev client, as I would expect, since that would definitely drop the session.

This IBM article about DGTTs speaks much of sessions, but not many specifics. And this article is possibly the longest command syntax I've yet encountered in the IBM documentation. I got through it, but it didn't answer the question of what decided when a DGTT is deleted.

So I would like to ask:

What are the boundaries of a session..?

I'm thinking this is probably defined by the environment in my SQL client..?

I guess the best/safest thing to do is use DROP TABLE as needed..?

Does any one have any tips, tricks, or pointers they could share..?

Below is the SQL that I'm developing. For brevity, I've excluded chunks of the WITH-AS and SELECT statements:

DROP TABLE SESSION.NEW_PKG_SHEETS ; 

DECLARE GLOBAL TEMPORARY TABLE SESSION.NEW_PKG_SHEETS_DATA

    AS  (   WITH        FIRSTDAY AS (SELECT (YEAR(CURDATE() - 4 MONTHS) * 10000) + 
                                            (MONTH(CURDATE() - 4 MONTHS) * 100) AS DATEISO
                                     FROM   SYSIBM.SYSDUMMY1
            -- <VARIETY OF ADDITIONAL CTE CLAUSES>
            -- <SELECT STATEMENT BELOW IS A BIT LONGER>
            SELECT      DAACCT                                      AS DAACCT,
                        DAIDAT                                      AS DAIDAT,
                        DAINV#                                      AS DAINV,
                        CAST(DAITEM AS NUMERIC(6))                  AS DAPACK,
                        CAST(0 AS NUMERIC(14))                      AS UPCNUM,
                        DAQTY                                       AS DAQTY
            FROM        DAILYTRANS
            AND         DAIDAT >= (SELECT DATEISO+000 FROM FIRSTDAY)                -- 1ST DAY FOUR MONTHS AGO
            AND         DAIDAT <= (SELECT DATEISO+399 FROM FIRSTDAY)                -- LAST DAY OF LAST MONTH
    ) WITH DATA ;

DROP TABLE SESSION.NEW_PKG_SHEETS ;

mao · Accepted Answer · 2019-08-30T06:54:48.623

The DGTT will only get cleaned automatically up by Db2 when the connection ends successfully (connect reset or equivalent according to whatever interface to Db2 is being used ).

For both Db2 for i and Db2-LUW, consider using the WITH REPLACE clause for the DECLARE GLOBAL TEMPORARY TABLE statement. That will ensure you don't need to explicitly drop the DGTT if the session remains open but the code needs the table to be replaced at next execution whether or not the DGTT already exists.

Using that WITH REPLACE clause means you do not need to worry about issuing a DROP statement for the DGTT, unless you really want to issue a drop.

Sometimes sessions may get re-used, or a close/disconnect might not happen or might not complete, or more likely a workstation performs a retry, and in those cases the WITH REPLACE can be essential for easily avoiding runtime errors.

Note that Db2 for Z/OS (at v12) does not offer the WITH REPLACE clause for DGTT, but has instead an optional syntax on commit drop table (but this is not documented for Db2-for-i and Db2-LUW).

Well there's a chapter right from the book called "Right In Front of My Face". Thank you, that works perfectly. =-) — spinjector, Aug 29 '19 at 21:15
As a note for those on DB2 for LUW or DB2 for z/OS: the equivalent there would be `ON COMMIT DROP TABLE` at the end of your definition. — bhamby, Aug 29 '19 at 22:43

At what point are DB2 declared global temporary tables automatically deleted...?

1 Answers1