Tuesday, October 25, 2016

Netezza: find ALTERED tables that need GROOM ... VERSIONS




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 :

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



No comments:

Post a Comment