query table row counts for versioned table in Netezza

The story is from a QC process. We need to migrate a series of table from Netezza to SQL Server. Of course, QC whether the row counts between source and target tables is needed. We need to first get row counts of a batch of table  of Netezza using a SQL query.

Usually, database systems will use ids(usually a number) to represent objects(tables, views, stored procedures...) internally. For example, in Netezza, system will use id 4905 to represent tables.

<strong><span style="font-size:12px;">SELECT * FROM _T_OBJECT WHERE OBJCLASS = 4905;</span></strong>

So when we try to get all the table record counts in a database, we could use the following query:

<strong><span style="font-size:12px;">SELECT CAST(TRIM(RELNAME) AS VARCHAR(50)) TABLE_NAME 
   , CAST(CASE WHEN RELTUPLES < 0 THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES ) ELSE ((2^32) * RELREFS) + ( RELTUPLES ) END  AS BIGINT) NUM_ROWS FROM _T_CLASS ,T_OBJECT WHERE _T_OBJECT.OBJID =_T_CLASS.OID AND _T_OBJECT.OBJCLASS = 4905;</span></strong>

Now comes the question, after we add a column to a table, this query output will not include the altered table.

After making research, the reason is found. When a table structure is modified, for example, add/modify/delete a column, the Netezza system will internally use a different  id for the altered table, which is 4961, and the table is called versioned table now. We could use system view to check whether there is versioned tables in the database:

<strong><span style="font-size:12px;">SELECT * FROM _V_SYS_TABLE_VERSION_OBJECT_DEFN;</span></strong>

And Here is some explanation of versioned table:

Versioned tables come about as a result of doing an alter table. This results in multiple data stores for the table. When you go to query the table, NPS must recombine the separate data stores back into a single entity. This action will be performed automatically and on-the-fly. But it does result in additional query overhead. Therefore, it is a best practice to reconstitute the table as soon as possible by using: 

GROOM TABLE <tablename> VERSIONS;

And the groom sql is executed, the id is set back to 4905 for the table.

So as a result of versioned table, it's better to modify the QC query where statement to:

<strong><span style="font-size:12px;">WHERE _T_OBJECT.OBJID =_T_CLASS.OID AND _T_OBJECT.OBJCLASS IN (4905,4961)</span></strong>

But it is better to groom the table after the table structure is modified to avoid system additional query overhead.