Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 01 Aug 2002 @ 08:00:48 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

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.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020