Netezza: Generate FULL Statistics using SQL / force

Is there a way to force netezza to generate full statistics on a table using SQL. I know it is possible to do via command line but that require nzsql client to be installed on the machine that initiates this command. According to the details mentioned here – https://www.ibm.com/support/pages/understanding-generate-statistics-statement it may be possible to force NZ to assume that the target table (on which we want to run full stats) is small / medium (set sample_stats_min_Rows = 1000000;) so that it generates full statistics with the “GENERATE STATISTICS ON ” command. However, it seems that like the sample_stats_min_rows is not a valid option anymore as it gives an error ERROR [HY000] ERROR: 'SAMPLE_STATS_MIN_ROWS' is not a valid option name. We have seen a massive difference in performance with full stats against express statistics and hence the request. The version we have is Release 7.2.1.10-P1

Answer

nz_genstats has the option to generate full statistics. if you dig into the script, this is what it’s setting to accomplish this

full stats

SET ENABLE_JIT_DISPERSION=FALSE;
SET ENABLE_JIT_STATS=ON;
GENERATE STATISTICS ON $TABLENAME;

for express stats

SET ENABLE_JIT_DISPERSION=TRUE;
SET ENABLE_JIT_STATS=ON;
SET JIT_DISP_MIN_ROWS=999999999999999;
GENERATE EXPRESS STATISTICS ON $TABLENAME;

for basic stats

SET ENABLE_JIT_DISPERSION=TRUE;
SET ENABLE_JIT_STATS=ON;
SET JIT_DISP_MIN_ROWS=1;
SET ENABLE_GENSTATS_DISPERSION=FALSE;
GENERATE STATISTICS ON $SQL_TABLENAME;