 |
 |
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
| |
