Finding out the size of a Netezza table using UNIX SAS

What syntax / tables can be used to determine the size (Gbs) of a Netezza table? I am accessing via UNIX SAS (either ODBC or libname engi开发者_开发百科ne). I assume there is a view which will give this info?

So you're interested in two system views _v_obj_relation_xdb and _v_sys_object_dslice_info. The first (_v_obj_relation_xdb) contains the table information (name, type, etc.) and the second (_v_sys_object_dslice_info) contains the size per disk information. You probably want to take a look at both of those tables to get a good idea of what you're really after, but the simple query would be:

select objname, sum(used_bytes) size_in_bytes
from _V_OBJ_RELATION_XDB 
join _V_SYS_OBJECT_DSLICE_INFO on (objid = tblid) 
where objname = 'UPPERCASE_TABLE_NAME'
group by objname

This returns the size of the table in bytes and I'll leave the conversion to GB as an exercise to the reader. There are some other interesting fields there so you might want to check out those views.

You could also use (_v_sys_object_storage_size )

   select   b.objid
            ,b.database as db
            ,lower(b.objname) as tbl_nm
            ,lower(b.owner) as owner
            ,b.objtype
            ,d.used_bytes/pow(1024,3) as used_gb
            ,d.skew
            ,cast(b.createdate as timestamp) as createdate_ts
            ,cast(b.objmodified as timestamp) as objmodified_ts

     from _v_obj_relation_xdb b inner join
          _v_sys_object_storage_size d
          on    b.objid=d.tblid
          and lower(b.objname) = 'table name'

The size on disk (used_bytes) represents compressed data and includes storage for any deleted rows in the table.

The table rowcount statistic (reltuples) is generally very accurate, but it is just a statistic and not guaranteed to match the "select count(*)" table rowcount.

You can get this information via a catalog query

select tablename, reltuples, used_bytes from _v_table_only_storage_stat where tablename = ^FOOBAR^;