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

Netezza: convert ABSTIME into current time zone timestamp.

PROBLEM:

/* ERROR [HY000] ERROR:  Type 'abstime' not supported by IBM Netezza SQL or has invalid size */

 CREATE TABLE MY_V_TABLE AS SELECT * FROM _V_TABLE;

At Netezza, management tables and system views have ABSTIME internal datatype this is a subset of GMT timestamp.  Creating a table as CTAS with have thei datatype will result in error.

---

SOLUTION:

Converting internal ABSTIME into your local time zone TIMESTAMP solves the problem.

SELECT CREATEDATE                                              as "ORIGINAL_DATATYPE_ASTIME"

      ,CREATEDATE  + ( select TZOFFSET from _VT_PG_TIME_OFFSET)  as "LOCAL_TIMEZONE_TIMESTAMP"

      ,CREATEDATE -INTERVAL'6 HOUR'                            as "LOCAL_TIMEZONE_TIMESTAMP"

  FROM _V_TABLE;

Notice: the management view _VT_PG_TIME_OFFSET shows the local time zone of physical location of Netezza Appliance box. it might be in different timezone.

select * from _VT_PG_TIME_OFFSET;