Explore topic-wise InterviewSolutions in .

Environment VARIABLES: NZ_HOST, NZ_DATABASE, NZ_USER and NZ_PASSWORD

1. Online: Normal or usual state.
2. Stopped: Netezza will shutdown after completing current queries, no new queries allowed.
3. Offline: WAITS for COMPLETION of current queries, new or queries in queue receive error.
4. Paused: Same as above, but no error displayed. TYPICALLY caused during Netezza bootup or startup.
5. Down: Just plain down, could be due to Netezza server problem or user initiated.

Environment variables: NZ_HOST, NZ_DATABASE, NZ_USER and NZ_PASSWORD

FPGA: Field Programmable GATE Array (FPGA) is located on each SPU. Netezza is different from other architectures. Netezza can do a “hardware upgrade” through software by using FPGA. Hardware is reconfigured during install.

While reading data from disk, FPGA on each SPU ALSO HELPS in ‘filtering’ unnecessary data before GETTING loaded into memory on each SPU. This way, FPGA does not overwhelm with all the data from disk.

FPGA: Field Programmable Gate Array (FPGA) is located on each SPU. Netezza is different from other architectures. Netezza can do a “hardware upgrade” through software by using FPGA. Hardware is reconfigured during install.

While reading data from disk, FPGA on each SPU also helps in ‘filtering’ unnecessary data before getting loaded into memory on each SPU. This way, FPGA does not overwhelm with all the data from disk.

Zone map in Netezza is similar (concept wise) to partitions in Oracle. Netezza maintains map for DATA so that it does relies on zone map to pull only the range it is interested in. For example, if we need to pull out data from Jan 2009 TILL June 2009 from a table that is distributed on date column, zone map helps us to achieve this. Zone map is MAINTAINED by Netezza automagically, no user intervention needed. Zone mapping is done at a block (EXTENT) level. Netezza has zone maps for all COLUMNS (not just distributed column) and includes information such as minimum, maximum, total number of records.

Zone map in Netezza is similar (concept wise) to partitions in Oracle. Netezza maintains map for data so that it does relies on zone map to pull only the range it is interested in. For example, if we need to pull out data from Jan 2009 till June 2009 from a table that is distributed on date column, zone map helps us to achieve this. Zone map is maintained by Netezza automagically, no user intervention needed. Zone mapping is done at a block (extent) level. Netezza has zone maps for all columns (not just distributed column) and includes information such as minimum, maximum, total number of records.

Sort DATA first, BASED on historical data (for EXAMPLE, DATE) and load this in using NZLOAD.

Sort data first, based on historical data (for example, date) and load this in using nzload.

Typically only schema and other DATABASE objects are CACHED in appliances. Data is not cached, in general. In most cases, data is not saved any where (in any cache or on host COMPUTER) and is STREAMED directly from SPU to client software.

Typically only schema and other database objects are cached in appliances. Data is not cached, in general. In most cases, data is not saved any where (in any cache or on host computer) and is streamed directly from SPU to client software.

Loads by pass a few steps that typically a query would go through (a query GOES through plan generation, optimization and transaction management). Loads are done in terms of “sets” and this set is based on underlying table structure (thus loads for two different tables are different as their sets are based on table structures). Data is processed to check format and distribution of records calculated very QUICKLY (in one step), fills into ‘set’ structure and writes to storage structure. Storage ALSO PERFORMS space availability and other admin tasks, all these operations go pretty quick (think of them as UNIX named pipes that streams data and SPU STORES these records).

Loads by pass a few steps that typically a query would go through (a query goes through plan generation, optimization and transaction management). Loads are done in terms of “sets” and this set is based on underlying table structure (thus loads for two different tables are different as their sets are based on table structures). Data is processed to check format and distribution of records calculated very quickly (in one step), fills into ‘set’ structure and writes to storage structure. Storage also performs space availability and other admin tasks, all these operations go pretty quick (think of them as UNIX named pipes that streams data and SPU stores these records).

Very rarely a driver may return aggregated results that are STILL getting processed BACK to client. In this case, client may assume that calculation is complete, INSTEAD of updating with latest or final results. Obviously, driver has to wait for Netezza to complete operation on host COMPUTER, before delivering results.

Very rarely a driver may return aggregated results that are still getting processed back to client. In this case, client may assume that calculation is complete, instead of updating with latest or final results. Obviously, driver has to wait for Netezza to complete operation on host computer, before delivering results.

In CASE of conflict in which the same RECORD is set for MODIFICATION, Netezza rolls back recent TRANSACTION that is attempted on the same record, in fact same table. This is generally acceptable in DW environments. Netezza does support serialization transactions and does not permit dirty READS.

In case of conflict in which the same record is set for modification, Netezza rolls back recent transaction that is attempted on the same record, in fact same table. This is generally acceptable in DW environments. Netezza does support serialization transactions and does not permit dirty reads.

In Netezza, PUBLIC group is created automatically and every one is a memeber of this group by default. We can CREATE as many groups and any user can be MEMBER of any group(s). Group can not be a member of another group. Group NAMES, user names and database names are unique. That is, we can not have a database called sales and a group also called sales.

In Netezza, public group is created automatically and every one is a memeber of this group by default. We can create as many groups and any user can be member of any group(s). Group can not be a member of another group. Group names, user names and database names are unique. That is, we can not have a database called sales and a group also called sales.

They are LOGICALLY deleted and ADMINISTRATOR can RUN nzreclaim, we MAY also TRUNCATE table.

They are logically deleted and administrator can run nzreclaim, we may also truncate table.

Login into SYSTEM DATABASE and give that permission to USER by SAYING “grant CREATE table to joe;”

Login into system database and give that permission to user by saying “grant create table to joe;”

List. Grant list, select on table to PUBLIC (if logged into SALES database, this allows all users to QUERY tables in sales database).

List. Grant list, select on table to public (if logged into sales database, this allows all users to query tables in sales database).

No, DROP DATABASE will TAKE CARE of it.

No, drop database will take care of it.

Not NULL and default. NETEZZA does not apply PK and FK.

Not null and default. Netezza does not apply PK and FK.

Specifying not NULL RESULTS in better PERFORMANCE as NULL VALUES are tracked at rowheader level. Having NULL values results in STORING references to NULL values in header. If all columns are NOT NULL, then there is no record header.

Specifying not null results in better performance as NULL values are tracked at rowheader level. Having NULL values results in storing references to NULL values in header. If all columns are NOT NULL, then there is no record header.

RESPONSE: Newly created table from CTAS gets DISTRIBUTION from the ORIGINAL table.

Response: Newly created table from CTAS gets distribution from the original table.

Just empties DATA from table, keeping table structure and PERMISSION intact.

Just empties data from table, keeping table structure and permission intact.

FIRST COLUMN (same as in TERADATA).

First column (same as in Teradata).

No, the column that is USED in distribution clause cannot be used for UPDATES. Remember, up to four columns can be used for distribution of data on SPU. From practical sense, updating distribution columns RESULT in redistribution of data; the single most PERFORMANCE hit when large table is involved. This restriction makes sense.

No, the column that is used in distribution clause cannot be used for updates. Remember, up to four columns can be used for distribution of data on SPU. From practical sense, updating distribution columns result in redistribution of data; the single most performance hit when large table is involved. This restriction makes sense.

ZONE MAPS work BEST for integer data TYPES.

Zone maps work best for integer data types.

NETEZZA logically DELETES the ORIGINAL ROW by flagging the deletexid column with the current transaction id and inserts a new row with the updated VALUES.

Netezza logically deletes the original row by flagging the deletexid column with the current transaction id and inserts a new row with the updated values.

USE nzload to load the data from a file into a table

CREATE an EXTERNAL table and then load the original table USING the external table.

Use nzload to load the data from a file into a table

Create an external table and then load the original table using the external table.

CREATE an EXTERNAL table.

USE nzsql UTILITY with -o OPTION.

Create an external table.

Use nzsql utility with -o option.

NZLOAD UTILITY is USED load data from a file into a table. It is used to load bulk data quickly and simultaneously rejects ERRONEOUS content.

Nzload utility is used load data from a file into a table. It is used to load bulk data quickly and simultaneously rejects erroneous content.

WHENEVER you delete a ROW in a table, it is not physically deleted. It is logically deleted by FLAGGING the deletexid field in the table. NZRECLAIM utility is used to REMOVE the logically deleted RECORDS.

Whenever you delete a row in a table, it is not physically deleted. It is logically deleted by flagging the deletexid field in the table. NZRECLAIM utility is used to remove the logically deleted records.

Whenever it is not POSSIBLE to do a COLLOCATED JOIN, NETEZZA EITHER redistributes the tables or broadcasts the table. When the table is a small one, then netezza broadcasts the table. Otherwise netezza redistributes the table.

Whenever it is not possible to do a collocated join, netezza either redistributes the tables or broadcasts the table. When the table is a small one, then netezza broadcasts the table. Otherwise netezza redistributes the table.

When you join tables which are distributed on the same key and USED these key columns in the join condition, then each SPU in netezza WORKS 100% INDEPENDENT of the other, as the required data is available in itself. This type of joins is CALLED collocated joins.

When you join tables which are distributed on the same key and used these key columns in the join condition, then each SPU in netezza works 100% independent of the other, as the required data is available in itself. This type of joins is called collocated joins.

1. To CHECK the distribution of rows run the FOLLOWING QUERY
2. SELECT datasliceid, COUNT(*)FROM <table name> GROUP BY datasliceid

CTAS will GET DISTRIBUTION from the ORIGINAL table.

CTAS will get distribution from the original table.

USE Create Table As (CTAS) to redistribute the data in a table. While creating the NEW table specify the DISTRIBUTE on CLAUSE to distribute the data on the new COLUMNS.

Use Create Table As (CTAS) to redistribute the data in a table. While creating the new table specify the distribute on clause to distribute the data on the new columns.

Integer

Integer

No, the COLUMN that is USED in DISTRIBUTION CLAUSE cannot be used for UPDATES.

No, the column that is used in distribution clause cannot be used for updates.

NETEZZA DISTRIBUTES the DATA on the first column and it USES Hash partitioning

Netezza distributes the data on the first column and it uses Hash partitioning

You can SPECIFY up to FOUR COLUMNS in the DISTRIBUTION CLAUSE.

You can specify up to four columns in the distribution clause.

There are two partitioning METHODS AVAILABLE in netezza:

1. RANDOM partitioning: DISTRIBUTES the DATA randomly.
2. Hash Partitioning: Distributes the data on the specified columns.

There are two partitioning methods available in netezza:

• Create materialized views with few columns which are frequently queried.
• Specify ORDER by clause on the most RESTRICTIVE columns (columns USED in where clause).
• Periodically or manually REFRESH the materialized views.

• You cannot INSERT, update, delete or truncate a MATERIALIZED view. Any CHANGES on the base tables will REFLECT into materialized views.
• You can specify only one base table in the from clause.
• Base table can’t be a external table, system table or a temporary table.
• You cannot use a where clause in the materialized view.
• Expressions are not allowed as columns.

A MATERIALIZED VIEW has the same DISTRIBUTION KEY as the BASE table.

A materialized view has the same distribution key as the base table.

A materialized view reduces the width (number of columns) of DATA being scanned in the BASE table by creating a thin version (FEWER columns) of the base table that contains a SMALL subset of frequently queried columns.

A materialized view reduces the width (number of columns) of data being scanned in the base table by creating a thin version (fewer columns) of the base table that contains a small subset of frequently queried columns.

Generate statistics is USED to gather statistics about each table column’s proportion of duplicate values, MINIMUM values, maximum values, null values, unique values and updates the system catalog tables.

The difference between ‘generate statistics’ and ‘generate EXPRESS statistics’ is based on how the column uniqueness is calculates. The ‘generate express statistics’ calculates ESTIMATED dispersion values based on the sampling of rows in the table. ‘Generate express statistics’ uses approximation in generating the stats where as ‘generate statistics’ uses all the rows in the table.

Generate statistics is used to gather statistics about each table column’s proportion of duplicate values, minimum values, maximum values, null values, unique values and updates the system catalog tables.

The difference between ‘generate statistics’ and ‘generate express statistics’ is based on how the column uniqueness is calculates. The ‘generate express statistics’ calculates estimated dispersion values based on the sampling of rows in the table. ‘Generate express statistics’ uses approximation in generating the stats where as ‘generate statistics’ uses all the rows in the table.

ZONEMAPS are created and REFRESHED for every SPU when you GENERATE statistics, Nzload operation, INSERT, update operations, Nzreclaim operation.

Zonemaps are created and refreshed for every SPU when you Generate statistics, Nzload operation, Insert, update operations, Nzreclaim operation.

An extent is the smallest unit of disk allocation on a SPU. Zonemaps is internal mapping structures to the extents that take ADVANTAGE of the internal ORDERING of data to eliminate extents that do not need to be SCANNED. Zonemaps TRANSPARENTLY avoid scanning of unreferenced rows. Zonemaps are CREATED for every column in the table and contain the minimum and maximum values for every extent.

An extent is the smallest unit of disk allocation on a SPU. Zonemaps is internal mapping structures to the extents that take advantage of the internal ordering of data to eliminate extents that do not need to be scanned. Zonemaps transparently avoid scanning of unreferenced rows. Zonemaps are created for every column in the table and contain the minimum and maximum values for every extent.

A snippet is a small UNIT of work that are CARRIED out in SPU.

A snippet is a small unit of work that are carried out in SPU.

While reading data from the disk, the FIELD PROGRAMMABLE Gate Array (FPGA) on each SPU FILTERS out unwanted data. This process of data elimination removes IO bottlenecks and frees up DOWNSTREAM components such as the CPU, MEMORY and network from processing extra data.

While reading data from the disk, the Field Programmable Gate Array (FPGA) on each SPU filters out unwanted data. This process of data elimination removes IO bottlenecks and frees up downstream components such as the CPU, memory and network from processing extra data.

Specifying Not NULL on each COLUMN in table RESULTS in BETTER PERFORMANCE. Netezza tracks the NULL values at rowheader level. Having NULL values results in storing references to NULL values in header. If all columns are NOT NULL, then there is no record header.

Specifying Not Null on each column in table results in better performance. Netezza tracks the NULL values at rowheader level. Having NULL values results in storing references to NULL values in header. If all columns are NOT NULL, then there is no record header.

Yes. As there are no PRIMARY KEY constraints in netezza you can insert duplicate ROWS.

Yes. As there are no primary key constraints in netezza you can insert duplicate rows.

The only CONSTRAINT NETEZZA supports is Not NULL. There are no PRIMARY key and foreign key constraints in netezza.

The only constraint netezza supports is Not null. There are no primary key and foreign key constraints in netezza.

• Online: Normal or USUAL state.
• Stopped: Netezza will shut down after completing CURRENT queries, no new queries allowed.
• Offline: Waits for completion of current queries, new queries in queue receives error.
• Paused: Same as above, but no error DISPLAYED. TYPICALLY caused during Netezza boot up or STARTUP.
• Down: Just plain down, could be due to Netezza server problem or user initiated.

The ENVIRONMENT VARIABLES REQUIRED are: NZ_HOST, NZ_DATABASE, NZ_USER, NZ_PASSWORD

The environment variables required are: NZ_HOST, NZ_DATABASE, NZ_USER, NZ_PASSWORD

• NETEZZA
• GREENPLUM
• Vertica
• TERADATA
• Dataupia
• ORACLE EXADATA