Problem
One QC process need to obtain tables and their row counts in a database in Netezza. We use the below SQL query to do so:
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;
Now oneday an issue occurs, we add a column to one table, but this query output will not include the altered table.
Solution
This issue pushes us to look into the query and the system tables. After making research, the more internally principle of Netezza is gradually discovered.
In Netezza database system, it will use ids(a number) to represent objects(tables, views, stored procedures...) internally. For tables, it will use id 4905 to represent.
SELECT * FROM _T_OBJECT WHERE OBJCLASS = 4905;
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:
SELECT * FROM _V_SYS_TABLE_VERSION_OBJECT_DEFN;
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, Netezza 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 performance cost for using the UNION ALL view instead of having all of the data exist in a single table.Therefore, it is a best practice to reconstitute the table by using:
GROOM TABLE <tablename> VERSIONS;
And after 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 where statement in QC script to:
WHERE _T_OBJECT.OBJID =_T_CLASS.OID AND _T_OBJECT.OBJCLASS IN (4905,4961)
In this way, the query will give expected result even if there is altered table. But again it is suggested to apply the groom clause reasonably soon after table is altered.
(EnD)
Related articles
http://netezza-dba.blogspot.com/2014/06/netezza-versioned-tables.html