 |
 |
Archives of the TeradataForum
Message Posted: Thu, 01 Aug 2002 @ 08:00:48 GMT
| Subj: | | Re: Volatile table & VB |
| |
| From: | | Dieter Nöth |
Hi Barbara,
| | Volatile tables are only available for the session in which they are created (unless the rows are preserved as Margaret
replied) | |
If you use ON COMMIT PRESERVE ROWS, the table is still only visible for the current session (and it's the same for GLOBAL
TEMPORARY tables, despite the name).
The ON COMMIT Option only deals with transactions, delete or preserve the rows. The problem is the Teradata session mode, because
it's using an autocommit (unless you use bt/et). So you insert/select data into the volatile table and the last step is a "Finally,
we send out an END TRANSACTION step to all AMPs involved in processing the request." thus emptying the table again, unless you
override the default DELETE option.
| | and the 3 commands from VB are possibly treated as 3 sessions, so the results are not available to the second/third
statements. Where possible I create macros which handle all parameters and run all commands as 1 session. Sometimes the main macro
merely calls subsidiary macros but it is all handles as 1 call from the source program. | |
Impossible in this case, because the first macro creates the volatile table, a macro is a transaction and after a DDL command
there must be an commit. In this case it's better to use GLOBAL TEMPORARY tables, because you don't have to use CREATE in the macro,
you just start with an INSERT.
Dieter
| |
