How to find Size of a Table in Netezza ?

The below query you can use to find the size of a table in Netezza.

SELECT 
A.DATABASE AS DATABASENAME
,LOWER(A.OBJNAME) AS TABLE_NAME
,A.OBJTYPE AS OBJECT_TYPE
,B.USED_BYTES/POW(1024,3) AS USED_SPACE_IN_GB
FROM _V_OBJ_RELATION_XDB A INNER JOIN
_V_SYS_OBJECT_STORAGE_SIZE B
ON    A.OBJID=B.TBLID
AND UPPER(A.OBJNAME) ='TABLE_NAME'
WHERE DATABASENAME='DATABASE_NAME'  AND OBJECT_TYPE='TABLE' ;

Compress in Netezza

There is no concept of specifying COMPRESS explicitly at column levels inside the table structure in Netezza.

Netezza compression is built in from Netezza 4.5 onwards, achieves typically over 30% savings.

Error: Function 'REPLACE(UNKNOWN,UNKNOWN,UNKNOWN)' does not exist.

Replace function working fine in one database(for ex: SYSTEM) but it's throwing an error when the same Replace function used in other database(for ex: AYD).why?

Answer:

Because AYD is the new database which has created by admin and NZSQL kit needs to be installed for that new database.

Error: Cross database access not supported for this type of command

The above error we will get when we perform any DML operation without the changing the target database in the Title bar menu.

The Target database name in the DML query and the database in the title bar should be same especially when we performing the DML operation.

How to fetch data using simple select query in Netezza

Hi All,

 As everyone knows that we can get the data using simple query like below for many databases.

select * from <databasename>.<tablename> ;

For Netezza:

In Netezza,we use double dot operator(..) in between databasename and tablename just like below.

select * from <databasename>..<tablename> ;

INITCAP function usage in Netezza

In Netezza, with in a string every first letter after space will be turned into an upper case and remaining letters will be into lowercase.
In Orcale, it'll converts the very first letter after space or any special symbol into uppercase and remaining letters into lower case.
In Teradata , we don't have this function.

Example:
In Netezza:
Select INITCAP ('ONLINE jOBS');

output: Online Jobs

Select INITCAP ('ONLINE-JOBS') ;
output: Online-jobs

In Oracle:
Select INITCAP ('ONLINE JOBS') from dual;
output: Online Jobs

Select INITCAP ('ONLINE-JOBS') from dual;
output: Online-Jobs

REPLACE Function in Netezza

Replace function in Netezza is similar to Oracle replace function.

You can replace any character/set of characters in a string with a required character/set of characters(or) numbers (or) special symbols as per requirement.

Given below is the example:

Select REPLACE('pageup','up',down');

output would be : pagedown

=======

Hope it will be Help!!