Also see related: Netezza: ALTER TABLE to MODIFY a column datatype
In Aginity run SQL to list all ALTERED tables that need to be GROOM ... VERSIONS :
In Aginity run SQL to list all ALTERED tables that need to be GROOM ... VERSIONS :
SELECT
tab.database as "DATABASE"
,tab.schema as "SCHEMA"
,MAX (
CASE WHEN tab.objclass = 4910 THEN SUBSTR(tab.objname, 3)
WHEN tab.objclass in (4951,4959,4963) THEN NULL
ELSE tab.objname
END ) AS "TABLENAME"
,TO_CHAR (nvl(SUM(used_bytes),0), '999,999,999,999,999,999') AS "SIZE (BYTES)"
,count(distinct(decode(objclass,4959,objid,4963,objid,null))) AS "# OF VERSIONS"
FROM _V_OBJ_RELATION_XDB AS tab
left outer join _V_SYS_OBJECT_DSLICE_INFO on
( tab.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid
and _V_SYS_OBJECT_DSLICE_INFO.tblid > 200000 )
WHERE tab.objclass in (4905,4910,4940,4959,4951,4953,4961,4963)
and tab.objid > 200000
GROUP BY "DATABASE", "SCHEMA", tab.visibleid
HAVING "# OF VERSIONS" >= 2
ORDER BY "DATABASE", "SCHEMA", "TABLENAME";
At Netezza host run the script to identify the altered tables:
[nz@netezza ~]$ /nz/support/contrib/bin/nz_altered_tables
# Of Versioned Tables 18
Total # Of Versions 38
Database | Schema | Table Name | Size (Bytes) | # Of Versions
-------------+----------+------------------------------+--------------------+---------------
TESTDB | MYUSERSD | FILTER_DATA | 16,515,072 | 2
TESTDB | MYUSERSD | MASTER_DATA_TESTS | 7,864,320 | 3
TESTDB | MYUSERSD | PREMIUM_TEST_TABLE_DATA | 6,815,744 | 2
TESTDB | MYUSERSD | REPORT_MASTER | 25,427,968 | 2
TESTDB | MYUSERSD | UTILI_TEST_TABLE_DATA | 6,291,456 | 2
TESTDB | MYUSERSD | WORK_TEST_TABLE_DATA | 0 | 2
TSTDB2 | MYUSERSD | AUDIT_IDW_DATALOAD | 31,195,136 | 2
TSTDB2 | MYUSERSD | ANOTHER_SUBSET_FILTER_DATA | 40,501,248 | 2
TSTDB2 | MYUSERSD | MASTER_MASTER_DATA_TESTS | 5,898,240 | 3
TESTDB5 | MYUSERSD | ORDER_ITEMS | 110,182,924,288 | 2
TESTDB5 | MYUSERSD | LARGE_DATA_DIM | 6,422,528 | 2
TESTDB5 | MYUSERSD | ORGANIZATION_DIM | 62,914,560 | 2
TESTDB67 | ADMIN | ALLEGED_DATA_SUBSET | 0 | 2
TESTDB67 | ADMIN | AUDIT_DOWNLOAD | 1,703,936 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_1 | 30,146,560 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_2 | 0 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_3 | 0 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_4 | 0 | 2
(18 rows)
At Netetezza host run the script to GROOM the altered tables:
[nz@netezza ~]$ /nz/support/contrib/bin/nz_altered_tables -groom
# Of Versioned Tables 18
Total # Of Versions 38
Database | Schema | Table Name | Size (Bytes) | # Of Versions
-------------+----------+------------------------------+--------------------+---------------
TESTDB | MYUSERSD | FILTER_DATA | 16,515,072 | 2
TESTDB | MYUSERSD | MASTER_DATA_TESTS | 7,864,320 | 3
TESTDB | MYUSERSD | PREMIUM_TEST_TABLE_DATA | 6,815,744 | 2
TESTDB | MYUSERSD | REPORT_MASTER | 25,427,968 | 2
TESTDB | MYUSERSD | UTILI_TEST_TABLE_DATA | 6,291,456 | 2
TESTDB | MYUSERSD | WORK_TEST_TABLE_DATA | 0 | 2
TSTDB2 | MYUSERSD | AUDIT_IDW_DATALOAD | 31,195,136 | 2
TSTDB2 | MYUSERSD | ANOTHER_SUBSET_FILTER_DATA | 40,501,248 | 2
TSTDB2 | MYUSERSD | MASTER_MASTER_DATA_TESTS | 5,898,240 | 3
TESTDB5 | MYUSERSD | ORDER_ITEMS | 110,182,924,288 | 2
TESTDB5 | MYUSERSD | LARGE_DATA_DIM | 6,422,528 | 2
TESTDB5 | MYUSERSD | ORGANIZATION_DIM | 62,914,560 | 2
TESTDB67 | ADMIN | ALLEGED_DATA_SUBSET | 0 | 2
TESTDB67 | ADMIN | AUDIT_DOWNLOAD | 1,703,936 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_1 | 30,146,560 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_2 | 0 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_3 | 0 | 2
TESTDB67 | ADMIN | TESTDATA_TABLE_4 | 0 | 2
(18 rows)
A 'GROOM TABLE <tablename> VERSIONS;' will now be performed on each of the above tables.
========================================================================================
Grooming TESTDB.MYUSERSD.FILTER_DATA @ 2016-10-24 18:01:53
NOTICE: Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.
NOTICE: If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "FILTER_DATA"'
NOTICE: Groom processed 0 pages; purged 0 records; scan size unchanged; table size unchanged.
GROOM VERSIONS
Elapsed time: 0m1.769s
Grooming TESTDB.MYUSERSD.MASTER_DATA_TESTS @ 2016-10-24 18:01:54
NOTICE: Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.
NOTICE: If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "MASTER_DATA_TESTS"'
NOTICE: Groom processed 35 pages; purged 0 records; scan size unchanged; table size shrunk by 1 extents.
GROOM VERSIONS
Elapsed time: 0m3.603s
Grooming TESTDB.MYUSERSD.PREMIUM_TEST_TABLE_DATA @ 2016-10-24 18:01:58
NOTICE: Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.
NOTICE: If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "PREMIUM_TEST_TABLE_DATA"'
NOTICE: Groom processed 0 pages; purged 0 records; scan size unchanged; table size unchanged.
GROOM VERSIONS
Elapsed time: 0m2.188s
Grooming TESTDB.MYUSERSD.REPORT_MASTER @ 2016-10-24 18:02:00
NOTICE: Groom will not purge records deleted after transactions that started after transaction 0xcaf2a1, due to the backup at 2016-01-30 00:36:49.
NOTICE: If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "REPORT_MASTER"'
NOTICE: Groom processed 106 pages; purged 0 records; scan size shrunk by 2 pages; table size shrunk by 2 extents.
GROOM VERSIONS
Elapsed time: 0m3.110s
....
Refer NPS 7.2 documentation: GROOM TABLE and ALTER TABLE
