RE: QTEMP vs GLOBAL TEMPORARY TABLE

So no reason for me to change. Thanks



-----Original Message-----
From: CRPence [mailto:crpbottle@xxxxxxxxx]
Sent: Wednesday, December 23, 2015 1:26 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: QTEMP vs GLOBAL TEMPORARY TABLE

On 18-Dec-2015 11:39 -0700, Luis Rodriguez wrote:

On 18-Dec-2015 11:01 -0700, Alan Campin wrote:

Unless I am wrong, a CREATE TABLE even in QTEMP is registered in
system catalog which is very slow and expensive. IBM need a way to do
with other databases can do, Create a temp table that is very fast to
create, use and destroy so they came up with Global Temporary Tables.
The table is not registered in the system catalog and can only be
seen by the current session so it is very fast to create and use.

Just did some tests and it seems that a CREATE TABLE QTEMP.xx is not
registered in the system catalog either (at least, the table name does
not appear in SYSTABLES).

What I think is that a DECLARE'd table seems to be more "SQL-friendly"
than the ones created in a more conventional way. You can, for
example, do a ROLLBACK against a GLOBAL TEMPORARY TABLE, which is, of
course, not possible when using a "traditional" QTEMP table.

They are quite effectively identical [DECLARE GLOBAL TEMPORARY TABLE ... and CREATE TABLE QTEMP/...] for effect, except what the clauses on the GTT allow; neither will be registered to the catalogs [although in past messages I have suggested that is possible to implement, though I doubt ever that would be done], and either can be used with isolation\commitment control [although the GTT according to the clauses specified, which might not match expectations if implemented instead as CREATE TABLE QTEMP/...]. Neither is likely to perform any better than the other, given consistency in implementing journaling.

Just as a GTT can be journaled [implicitly to QSQTTJRN in the QRECOVERY library of the iASP], so too can a TABLE created into QTEMP as a non-GTT be journaled [to a chosen journal, even implicitly as I recall, via the QDFTJRN Data Area support]; i.e. isolation and thus ROLLBACK and COMMIT are available in QTEMP just as in any other library:
[http://archive.midrange.com/rpg400-l/201512/msg00173.html]