How To Display DBMS Temporary Tables in EG Server List

[RandallWilliamson]

Is it possible to display DBMS temporary tables in the EG Server List?

The target DBMS is Netezza, and using the DBMSTEMP=YES LIBNAME option works fine for our programmers.  However, the library always appears to be empty in EG.  This prevents casual users from utilizing Netezza temp tables like they do with the Work library.  Even for programmers, it would be helpful if we could use the Query Builder task with a temp table as a source.

I hope there is a configuration option, either on the Netezza or SAS side, that would allow the library to display temp tables as members, but I've found nothing so far.

Environment:  EG 7.12, SAS 9.4M3 (Linux), Netezza 7.2.1

[RandallWilliamson]

As an update, our admins submitted a question to SAS Support, and it seems that what I'm hoping for is not possible.  Their response:

"By design, Enterprise Guide can only display tables from SAS defined libraries and the default WORK library."

[SASKiwi]

I have no experience with Netezza but my understanding of temporary tables is that they are mostly used to construct result sets of data for a particular application and then discarded at the end of a database session. Often they are not persistent across database connections or are shareable between users. To make temporary tables more persistent requires the use of settings like the CONNECTION = GLOBAL option to persist a connection containing a temporary table so it can be re-used in another SQL step.

[RandallWilliamson]

Yes, using temp tables is similar to how we use the WORK library - it's a place to store data during your current session.  We are seeing many users attempt to join data sets in Work to Netezza tables, which creates performance problems for large tables. Our goal is to reduce the transfer of data between Netezza and SAS by making it easier for non-programmers.

[LinusH]

I can definitely see the benefit of this.
If you issue a ballot item for linking RDBMS temporary tables with a libref I will vote up!

[RandallWilliamson]

Libraries can be defined that automatically create temp tables for any data written there.  We can even write to those libraries using EG tasks, like Query Builder.  However, the QB task doesn't create a link to the table in the process flow, and you can't use the table as an input to a follow-up QB task.

I think we have about 400 EG users now, and many, perhaps most, never write code.  The programmers can submit something like this, and it works well within a session across step and program boundaries:

libname NZ_Temp
   netezza
   server     = &NZServer
   database   = &NZDatabase
   schema     = &NZSchema
   authdomain = &NZAuthdomain
   bulkunload = yes
   dbmstemp   = yes
   connection = global
;
data NZ_Temp.Test_Table;
   set SASHelp.Cars;
run;
proc sql;
   select * from NZ_Temp.Test_Table;
quit;

I suspect there's nothing SAS could do to make it work like I had hoped.  I've also checked the documentation for other tools that we use to query Netezza, and I've found nothing.

[Tom]

If you want to work harder you might be able to make something that EG could see.

Take your example table.

data NZ_Temp.Test_Table;
   set SASHelp.Cars;
run;

If you make a SAS view to it might be possible for users could see the view.

proc sql;
   create view NZ_Temp_Test_Table as select * from NZ_Temp.Test_Table;
quit;

[RandallWilliamson]

Now that's an interesting thought.  I haven't done much with views on the SAS side, only in Netezza, so I'll check into this.  However, the example I posted was just to show how libraries can work using the DBMSTEMP=YES option in the LIBNAME statement.

As a typical use case, users will query a very large Netezza table, bringing the results into WORK.  Once they finish tweaking the query, they join the SAS data set to another large Netezza table - a heterogeneous join, which creates performance problems.  They'll often do this through multiple QB tasks until they get the results they want.

Much of this is about training and monitoring.  We need to make our users understand how to use the system well, and I've set up a daily report from Netezza that lists the longest running queries from SAS users.  Soon I'll have the same for the creators of large SAS data sets.