Netezza Table Stat Extraction for Schema Optimization Recommendations

This topic provides detailed steps for extracting table statistics that typically includes number of rows in the table, distinct rows in the table, table size, etc.

In this Topic:

Prerequisites
Extraction

Prerequisites

The Netezza table statistics should be updated. In case it’s not updated, execute the following command on all the tables in the database.

NEWTPCDS.ADMIN(ADMIN)=> GENERATE STATISTICS;
GENERATE STATISTICS

Extraction

There can be multiple means to extract the tables stats from your Netezza instance. You can use anyone given below.

nzsql: Execute query using the nzsql CLI and save results as a CSV file
Alternatively, you can use any tool that supports Netezza connection. For example, Aginity pro, DBeaver, etc.
Another way could be through the JDBC or ODBC driver – Use Netezza JDBC/ODBC driver if you want to export the required stats using any programming language

Netezza Table Stat Extraction Query

select DATABASE_NAME,Table_Name,TABLE_SIZE_BYTES,NUM_ROWS,Column_Name,
to_number(num_of_Unique_Values, 999999999999999999999999999999) as
num_unique_val
from(
SELECT
_V_TABLE.”DATABASE” as database_name,
_V_TABLE.TABLENAME as Table_Name,
case when tbl_stat.used_bytes is null then 0 else
tbl_stat.used_bytes end as table_size_bytes,
_V_TABLE.RELTUPLES as num_rows,
SUBSTR(_v_relation_column.attname || ‘
‘, 1, 25) as Column_Name,
case when _v_relation_column.attdispersion = ‘0’ then ‘0’
when _v_relation_column.attdispersion = ‘-1’ then
_V_TABLE.RELTUPLES::varchar(100)
else
TO_CHAR( (CAST((1.0/_v_relation_column.attdispersion)
AS BIGINT)), ‘ 999,999,999,999,999 ‘ ) end as num_of_Unique_Values
FROM _v_relation_column
left outer join _v_statistic on
( _v_relation_column.objid = _v_statistic.objid
AND _v_relation_column.attnum = _v_statistic.attnum
)
inner join _V_TABLE
on (_v_relation_column.objid = _V_TABLE.OBJID)
inner join _v_table_storage_stat as tbl_stat
on(tbl_stat.OBJID = _V_TABLE.OBJID)
WHERE
_V_TABLE.OBJTYPE = ‘TABLE’
AND ( _v_relation_column.schema=current_schema OR
upper(_v_relation_column.schema) in (‘DEFINITION_SCHEMA’,
‘INFORMATION_SCHEMA’))
ORDER BY
_V_TABLE.TABLENAME ,
_v_relation_column.attnum
) as a
;

Example: