Temporary Tables in InterSystems IRIS

Temporary tables are tables available for a current process only (and destroyed when process ends).

What are you approaches to creating temporary tables?

Here's the two I know:
  1. Process-private Globals storage can be used as a data global in storage definition. That way, each process can have its own objects for the class with ppg storage.  Here's how. Here's how 2.
  2. InterSystems TSQL supports #tablename temporary tables. A #tablename temporary table is visible to the current procedure of the current process. It is also visible to any procedure called from the current procedure. #tablename syntax is only supported in TSQL procedures (class methods projected as procedures with language tsql).

Any other ways to achieve similar functionality? 

Replies

CREATE GLOBAL TEMPORARY TABLE <mytable> {} has similar effects as process-private globals...

Indeed, that's the recommended SQL way of achieving what Eduard described about PPGs. Drawbacks are that queries on these tables cannot be parallelized (as that implies multiple processes, of course).

Our TSQL support is meant for Sybase customers wishing to redeploy their TSQL applications on IRIS (especially now that SAP/Sybase is terminating support for those platforms). Just temporary table support by itself wouldn't be a reason to start building TSQL applications and abandon IRIS SQL/ObjectScript, of course :-). However, for a recent TSQL migration we did some work on our TSQL temp table support and were considering to roll that out to regular IRIS SQL, so this thread is a good place to share your experiences and requirements so we can make sure to do that properly, as needed. yes

Hi,

I recently needed a temporary class (not just a table) to store data while it was manipulated (imported, queried, modified, etc. and eventually exported) . I eventually set up all the storage locations with PPG refs as noted above, which works, but did wonder if there was a class parameter I had missed that just indicated the extent was temporary. Or maybe an alternative to extending %Persistent?

Mike

I'm not really sure what do you mean by

if there was a class parameter I had missed that just indicated the extent was temporary  

what do you want to achieve with this?

Hi,

I don't want to achieve anything else, it's just that "cache being cache" there's often another way to do the same thing and it might be easier. :-) 

Mike