Skip navigation
Up to Discussions in TwinFin Talk
7869 Views 4 Replies Latest reply: Aug 20, 2010 12:36 PM by eric.stephani RSS
eric.stephani New Enzee
eric.stephani
4 posts since
Aug 19, 2010
Currently Being Moderated

Aug 19, 2010 4:29 PM

Query to report databases and sizes

We just started on the Netezza platform. Does anyone know the internal views/tables that would hold the current size of all the databases? I am looking for just a simple query to generate a report off of.

  • eric.stephani New Enzee
    eric.stephani
    4 posts since
    Aug 19, 2010
    Currently Being Moderated
    1. Aug 19, 2010 5:14 PM (in response to eric.stephani)
    Re: Query to report databases and sizes

    I found  nz_db_size.

  • AmbarishDon New Enzee
    AmbarishDon
    67 posts since
    May 11, 2010
    Currently Being Moderated
    2. Aug 20, 2010 1:19 AM (in response to eric.stephani)
    Re: Query to report databases and sizes

    nz_db_size is really good utility to get report of database size. There are many supports available along with nz_db_size which will give information required for reporting.

     

    -AmbarishDon

  • James Karioki New Enzee
    James Karioki
    47 posts since
    Apr 7, 2009
    Currently Being Moderated
    3. Aug 20, 2010 9:32 AM (in response to AmbarishDon)
    Re: Query to report databases and sizes

    In addition to using nz_db_size, you may also find the following useful:

     

    nzstats -type database

    nzstats -type table

     

    Additionally, you can pivot the outputs of the above commands by adding '-orient vertical' to the commands as follows:

     

     

    nzstats -type database -orient vertical

    nzstats -type table -orient vertical



     

    The first command gives you the db stats and the second one provides you with table stats (row size, no. of columns, skew etc).

     

    James.

  • eric.stephani New Enzee
    eric.stephani
    4 posts since
    Aug 19, 2010
    Currently Being Moderated
    4. Aug 20, 2010 12:36 PM (in response to eric.stephani)
    Re: Query to report databases and sizes

    If anyone is curious. I pulled the actual query out of the query history and cleaned it up a bit for what I want to use it for.

     

     

    SELECT current_timestamp as "SAMPLE_TIME", t.database::nvarchar(128) AS "DATABASE_NAME" ,
    case when sum(d.used_bytes) is null then 0 else SUM(d.used_bytes)/1048576 end AS "USED_MB",
    case when sum(d.allocated_bytes) is null then 0 else SUM(d.allocated_bytes)/1048576 end AS "ALLOCATED_MB"
    FROM _V_SYS_OBJECT_DSLICE_INFO d INNER JOIN _V_OBJ_RELATION_XDB t ON t.objid = d.tblid
    WHERE t.objid > 200000 AND d.tblid > 200000
    GROUP BY "SAMPLE_TIME", "DATABASE_NAME"
    ORDER BY "DATABASE_NAME"

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points