Skip to content

Posts from the ‘Netezza’ Category

May 20

Query to find permissions granted directly at to specific objects for a specific user – Netezza

You can run below query to find permissions granted directly at to specific objects for a specific user. This query will also report the object name, object type and the database name where this access is granted.
Here we will pull data from various system table and system views in system database.

select u.usename as "User Name", D.DATABASE, o.objname as "Object Name", oc.classname as "Object Type" from _t_object o, _t_user u,  
_t_object_classes oc, _v_database D where u.usename='TEST_ID' and                     
u.usesysid=o.objowner and o.objclass=oc.objclass and o.OBJDB=D.objid;

Apr 25

Query to find the size of each database in Netezza environment

Please run below query to find the size of each database in Netezza environment. The output of this query will be the database name and total space allocated to the database.
This data is stored in system views under system database.

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 "AllocatedSpace_GB"
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"

Now if you want to know the free space available to the database as well then try below query:

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

Apr 1

Query to find tables consuming more space in a disk – Netezza

Some times you may find that your Netezza data slice used space have grown a lot and now you want to check which all tables are consuming space on those data slices/disk so that you can try to find if those tables needs to be groomed, etc.

In order to make your task easy, you can use below query to find tables consuming more space in a disk. The below query will list all the tables in disk id 1,2,3,and 4 which are using more than 1000 MB on any of the disk. It will also provide valuable information like database name where that table exist, disk id as well as spu id in which this disk is residing.
You can alter the script by changing disk id as per your requirement.

select objname as table_name,
       database as db_name,
       hwid as spu_id, dsid,
       (allocated_bytes/1048576) as allocated_mbytes
  from _v_sys_relation_xdb sys,
       _v_sys_object_dslice_info ds
 where ds.tblid = sys.objid
   and dsid in (1,2,3,4)
and allocated_mbytes > 1000
 order by  allocated_mbytes desc, table_name,db_name, dsid;

Mar 20

Query to find database backup time – Netezza

You can use below query to find database backup time in Netezza environment. This query will provide the database backup time (Full / Differential / User backup) for each database along with the backup start and finish time. Query will not display any data for failed database backup attempts.
Here we will query system view _v_backup_history in system database.

select DBName, OPTYPE, starttime, lastupdate, (lastupdate-starttime)/60 as DurationInMinutes from _v_backup_history
where status='COMPLETED' and DBNAME is NOT NULL
and StartTime>'2012-10-01 00:00:00'
order by DBName, starttime

Unfortunately, we cannot retrieve the backup location as it is not stored in any system table/view in Netezza. For that we have to look into the backup log file.

Mar 12

Query to find all the resource groups in a Netezza system

You can run below query to find all the resource groups in a Netezza system. The output of this query will also give us the maximum and minimum resource allocation values in percentage.
To get these details we will query system view _v_group in system database.

select GROUPNAME as "Resource Group",GRORSGPERCENT as "Min Resource",RSGMAXPERCENT as "Max Resource"
from _v_group where grorsgpercent<>0
« Older Entries