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]