Netezza: how TEMP tables GENERATE STATISTICS when created as CTAS

This runtime illustration how Netezza generates full table statistics automatically during CREATE TABLE AS (CTAS) operation. Running nz_responders yields output: 

Read more how Netezza generates statistics on tables in  Maintain table statistics automatically

The IBM® Netezza® system automatically maintains database statistics and estimated dispersion statistics, which are not as accurate as the statistics that the system maintains when you run the GENERATE STATISTICS command. These statistics are, however, generally better than no statistics.

The Netezza system maintains certain statistics when you perform database operations.

• When you use the CREATE TABLE AS command, the system maintains the min/max, null, and estimated dispersion values automatically.
• When you use the INSERT or UPDATE commands, the system maintains the min/max values for all non-character fields.
• When you use the GROOM TABLE command to reclaim deleted records, the system leaves the min/max, null, and estimated dispersion values unchanged, and updates the zone map.
  Because the groom process merely physically removes records that were logically deleted, their removal has no effect on any statistics, though it does affect where in the table the remaining records are, hence the effect on zone maps.

The following table describes when the Netezza automatically maintains table statistics.

Table 1. Automatic Statistics

Command	Row counts	Min/Max	Null	Dispersion (estimated)	Zone maps
CREATE TABLE AS	yes	yes	yes	yes	yes
INSERT	yes	yes	no	no	yes
DELETE	no	no	no	no	no
UPDATE	yes	yes	no	no	yes
GROOM TABLE	no	no	no	no	yes

Parent topic: Database statistics

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