Aginity: keep connection open to preserve TEMP tables for Netezza session

You might be interested in how TEMP tables GENERATE STATISTICS when created as CTAS  and Netezza: TEMP tables don't share database namespace.

PROBLEM: 

Creating TEMP tables in Aginity you might encounter an error that a newly created  TEMP  has just disappeared. That happens because, behind the scene, Aginity closes a database connection after each query execution.  After closing database connection Netezza automatically cleans up all TEMP tables associated with gone session: 

/* Start time 2/8/2016 6:48:38 PM, end time 2/8/2016 6:48:38 PM. */

/* Duration 0.7309269 sec. */

CREATE TEMP TABLE T_NAME_2 AS SELECT * FROM _V_DSLICE;

/* Records Affected: 240. */

/* ERROR [42S02] ERROR:  relation does not exist NZDBA.ADMIN.T_NAME_2 */

SELECT COUNT (* ) FROM T_NAME_2;

SOLUTION:

To keep database connection opened keeping TEMP tables, change default Aginity behavior by setting a Query Option windows.  Right-click on Aginity Query Analyzer window and check "Keep connection open between executions":

Now, try execute one-by-one:

/* Start time 2/8/2016 6:52:12 PM, end time 2/8/2016 6:52:13 PM. */

/* Duration 0.7439256 sec. */

CREATE TEMP TABLE T_NAME_2 AS SELECT * FROM _V_DSLICE;

/* Records Affected: 240. */

/* Start time 2/8/2016 6:52:49 PM, end time 2/8/2016 6:52:49 PM. */

/* Duration 0.0579942 sec. */

SELECT COUNT (* ) FROM T_NAME_2;

/* Records Affected: 1. */

TEMP tables still alive until you keep the Aginity query window open