How to Get Netezza databases Allocated, Used, Available Space?

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:Netezza
  • Reading time:2 mins read

You may have to query Netezza system tables to get information about system, for example, get Netezza databases allocated, used, available space.

These kind of information will help you to manage Netezza server and databases. As a server maintenance activity, you may have to regularly verify the Netezza database size to minimize the performance degrade.

Get Netezza databases Allocated, Used, Available Space

Below query provides the information of size of each database available in the Netezza server. This query is useful for the Netezza administrator who frequently verify the databases size.

SELECT ORX.database::nvarchar(64) AS "DatabaseName",
case when sum(SOD.allocated_bytes) is null then 0 
          else SUM(SOD.allocated_bytes)/1073741824 
 end AS "Allocated_DB_Size_GB",
case when sum(SOD.used_bytes) is null then 0
          else SUM(SOD.used_bytes)/1073741824 
 end AS "Used_DB_Size_GB",
       ("Allocated_DB_Size_GB" - "Used_DB_Size_GB") as "Available_DB_size"
FROM _V_SYS_OBJECT_DSLICE_INFO SOD
INNER JOIN _V_OBJ_RELATION_XDB ORX
ON ORX.objid = SOD.tblid
GROUP BY "DatabaseName"
ORDER BY "DatabaseName";

 DatabaseName | Allocated_DB_Size_GB | Used_DB_Size_GB | Available_DB_size
--------------+----------------------+-----------------+-------------------
 SYSTEM | 0.017578 | 0.000732 | 0.016846
 TRAINING | 0.090820 | 0.003784 | 0.087036
(2 rows)

Read:

Tags: