Top 100+ Netezza Interview Questions And Answers
Question 1. What Are The Data Warehouse Appliances You Know?
Answer :
Netezza
Greenplum
Vertica
Teradata
Dataupia
Oracle Exadata
Question 2. What Are The Environment Variables That Are Required To Connect To Netezza?
Answer :
The surroundings variables required are: NZ_HOST, NZ_DATABASE, NZ_USER, NZ_PASSWORD
Microstrategy Interview Questions
Question three. What Are The Different States Of Netezza?
Answer :
Online: Normal or standard country.
Stopped: Netezza will close down after completing present day queries, no new queries allowed.
Offline: Waits for finishing touch of modern queries, new queries in queue receives error.
Paused: Same as above, but no mistakes displayed. Typically triggered at some stage in Netezza boot up or startup.
Down: Just undeniable down, may be due to Netezza server hassle or consumer initiated.
Question four. What Are The Constraints On A Table Are Enforced?
Answer :
The simplest constraint netezza supports is Not null. There aren't any number one key and foreign key constraints in netezza.
Microstrategy Tutorial
Question 5. Can You Insert Duplicate Rows In Netezza Table?
Answer :
Yes. As there are not any number one key constraints in netezza you could insert duplicate rows.
Informatica Interview Questions
Question 6. How The Not Null Specification On A Column Improves The Netezza Performance?
Answer :
Specifying Not Null on every column in desk outcomes 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.
Question 7. How Fpga Can Be Helpful In Improving Query Performance?
Answer :
While studying statistics from the disk, the Field Programmable Gate Array (FPGA) on every SPU filters out undesirable records. This technique of facts elimination removes IO bottlenecks and frees up downstream components which includes the CPU, reminiscence and network from processing more data.
Informatica Tutorial Data Warehouse ETL Toolkit Interview Questions
Question eight. What Is A Snippet?
Answer :
A snippet is a small unit of work which might be executed in SPU.
Question nine. What Are Zonemaps?
Answer :
An quantity is the smallest unit of disk allocation on a SPU. Zonemaps is internal mapping structures to the extents that take benefit of the internal ordering of facts to cast off extents that don't want to be scanned. Zonemaps transparently keep away from scanning of unreferenced rows. Zonemaps are created for every column within the table and comprise the minimal and most values for each extent.
Teradata Interview Questions
Question 10. How The Zonemaps Are Created And Updated?
Answer :
Zonemaps are created and refreshed for every SPU while you Generate facts, Nzload operation, Insert, replace operations, Nzreclaim operation.
Data Warehouse ETL Toolkit Tutorial
Question 11. What Is Generate Statistics And Generate Express Statistics Or What Is The Difference Between Generate Statistics And Generate Express Statistics?
Answer :
Generate facts is used to accumulate statistics approximately every desk column’s proportion of replica values, minimum values, most values, null values, unique values and updates the machine catalog tables.
The difference between ‘generate facts’ and ‘generate specific records’ is based totally on how the column distinctiveness is calculates. The ‘generate specific statistics’ calculates estimated dispersion values primarily based at the sampling of rows in the table. ‘Generate explicit statistics’ uses approximation in generating the stats where as ‘generate information’ makes use of all of the rows inside the desk.
IBM DB2 Interview Questions
Question 12. What Is The Use Of Creating Materialized Views?
Answer :
A materialized view reduces the width (range of columns) of statistics being scanned in the base desk through developing a skinny model (fewer columns) of the base desk that consists of a small subset of frequently queried columns.
Microstrategy Interview Questions
Question 13. What Is The Distribution Of Materialized Views?
Answer :
A materialized view has the identical distribution key because the base desk.
Teradata Tutorial
Question 14. What Are The Limitations Of Materialized Views?
Answer :
You can not insert, update, delete or truncate a materialized view. Any adjustments on the base tables will replicate into materialized views.
You can specify most effective one base table in the from clause.
Base table can’t be a external table, system desk or a transient desk.
You cannot use a in which clause in the materialized view.
Expressions aren't allowed as columns.
Question 15. What Are The Best Practices Of Creating Materialized Views?
Answer :
Create materialized perspectives with few columns which might be regularly queried.
Specify order via clause at the maximum restrictive columns (columns utilized in where clause).
Periodically or manually refresh the materialized views.
RDBMS Interview Questions
Question sixteen. What Are The Partitioning Methods Available In Netezza?
Answer :
There are partitioning methods available in netezza:
Random partitioning: Distributes the information randomly.
Hash Partitioning: Distributes the facts on the desired columns.
IBM DB2 Tutorial
Question 17. Up To How Many Columns You Can Specify In Distribute On Clause?
Answer :
You can specify up to 4 columns within the distribution clause.
DB2 Using SQL Interview Questions
Question 18. If You Did Not Specify Any Distribute On Clause While Creating A Table, What Distribution Netezza Uses?
Answer :
Netezza distributes the records on the primary column and it uses Hash partitioning
Informatica Interview Questions
Question 19. Can You Update The Columns Used In Distribution Clause?
Answer :
No, the column that is utilized in distribution clause can not be used for updates.
DB2 Using SQL Tutorial
Question 20. What Data Types Are Most Suited For The Columns Specified In Distribution Clause?
Answer :
Integer
TeraData DBA Interview Questions
Question 21. How Do You Redistribute A Table?
Answer :
Use Create Table As (CTAS) to redistribute the data in a table. While developing the brand new table specify the distribute on clause to distribute the facts on the brand new columns.
Question 22. If You Did Not Specify Any Distribution Clause, How The Create Table As (ctas) Will Distribute The Rows?
Answer :
CTAS gets distribution from the unique desk.
Question 23. How Do You Check The Rows In A Table Are Equally Distributed In All Spu’s Or Not?
Answer :
To test the distribution of rows run the subsequent query
SELECT datasliceid, COUNT(*)FROM <table name> GROUP BY datasliceid
DB2 SQL Programming Interview Questions
Question 24. What Is Collocated Join?
Answer :
When you join tables which are dispensed on the equal key and used these key columns within the join condition, then each SPU in netezza works 100% unbiased of the alternative, as the desired statistics is to be had in itself. This form of joins is known as collocated joins.
Data Warehouse ETL Toolkit Interview Questions
Question 25. When Netezza Redistributes A Table And When It Broadcasts A Table?
Answer :
Whenever it isn't possible to do a collocated be a part of, netezza either redistributes the tables or proclaims the table. When the desk is a small one, then netezza proclaims the table. Otherwise netezza redistributes the table.
Question 26. How Do You Remove Logically Deleted Records?
Answer :
Whenever you delete a row in a desk, it isn't always physically deleted. It is logically deleted by way of flagging the deletexid field in the desk. NZRECLAIM software is used to dispose of the logically deleted data.
Talend Interview Questions
Question 27. What Is Nzload?
Answer :
Nzload application is used load statistics from a document right into a desk. It is used to load bulk statistics fast and concurrently rejects misguided content.
Teradata Interview Questions
Question 28. What Are The Ways To Load A Data From A Table Into A File?
Answer :
Create an external table.
Use nzsql utility with -o option.
Question 29. What Are The Different Ways To Load Data From A File Into A Table?
Answer :
Use nzload to load the records from a file into a table
Create an external table and then load the authentic table using the outside desk.
Oracle Performance Tuning Interview Questions
Question 30. How Netezza Updates A Row In A Table?
Answer :
Netezza logically deletes the original row by using flagging the deletexid column with the modern transaction identity and inserts a brand new row with the updated values.
Question 31. What Data Type Works Best For Zone Maps?
Answer :
Zone maps paintings best for integer records kinds.
Question 32. Can We Update All Columns In A Netezza Table.?
Answer :
No, the column this is used in distribution clause cannot be used for updates. Remember, as much as four columns may be used for distribution of statistics on SPU. From practical experience, updating distribution columns result in redistribution of statistics; the single maximum overall performance hit while large table is worried. This restriction makes feel.
Structured Query Report (SQR) Interview Questions
Question 33. When No Distribution Clause Is Used While Creating A Table, What Distribution Is Used By Netezza?
Answer :
First column (same as in Teradata).
IBM DB2 Interview Questions
Question 34. Why Do You Prefer Truncate Instead Of Drop Table Command?
Answer :
Just empties information from desk, retaining desk shape and permission intact.
Question 35. Create Table As (ctas), Does It Distribute Data Randomly Or Based On Table On Which It Received Data?
Answer :
Response: Newly created desk from CTAS gets distribution from the unique table.
Question 36. Why Not Null Specification Is Better In Netezza?
Answer :
Specifying now not null effects in better performance as NULL values are tracked at rowheader stage. Having NULL values consequences in storing references to NULL values in header. If all columns are NOT NULL, then there may be no report header.
RDBMS Interview Questions
Question 37. What Constraints On A Table Are Enforced?
Answer :
Not null and default. Netezza does not observe PK and FK.
Question 38. Do We Need To Drop All Tables And Objects In That Database, Before Dropping A Database?
Answer :
No, drop database will take care of it.
Question 39. What Permission Will You Give To Connect To A Database?
Answer :
List. Grant list, pick on table to public (if logged into income database, this permits all users to question tables in income database).
Question 40. How Can We Give A Global Permission To User Joe So That He Can Create Table In Any Database.?
Answer :
Login into gadget database and give that permission to user with the aid of pronouncing “supply create table to joe;”
DB2 Using SQL Interview Questions
Question 41. What Happens To Records That Are Loaded During Nzload Process, But Were Not Committed?
Answer :
They are logically deleted and administrator can run nzreclaim, we can also truncate table.
Question forty two. Can A Group Become A Member Of Another Group In Netezza User Administration. Can We Use Same Group Name For Databases?
Answer :
In Netezza, public institution is created mechanically and every one is a memeber of this organization by default. We can create as many companies and any person can be member of any group(s). Group cannot be a member of any other organization. Group names, user names and database names are unique. That is, we can't have a database known as sales and a collection also referred to as income.
TeraData DBA Interview Questions
Question forty three. Does Netezza Support Concurrent Update Of The Same Record?
Answer :
In case of struggle in which the equal record is set for amendment, Netezza rolls again latest transaction this is attempted at the equal file, in truth same desk. This is normally ideal in DW environments. Netezza does assist serialization transactions and does no longer allow grimy reads.
Question forty four. When Are We Likely To Receive Incorrect (combination) Results?
Answer :
Very not often a driver may go back aggregated effects which might be still getting processed again to customer. In this example, purchaser may assume that calculation is whole, instead of updating with modern day or final results. Obviously, driver has to watch for Netezza to finish operation on host pc, earlier than turning in consequences.
Question 45. How Is Load Achieved In Netezza And Why Is That Quick / Fast?
Answer :
Loads by pass some steps that usually a query would go through (a question is going via plan era, optimization and transaction management). Loads are carried out in terms of “sets” and this set is based totally on underlying table shape (consequently masses for 2 exclusive tables are extraordinary as their units are based totally on table systems). Data is processed to test layout and distribution of information calculated right away (in one step), fills into ‘set’ structure and writes to storage shape. Storage also plays area availability and different admin responsibilities, some of these operations pass pretty quick (think about them as UNIX named pipes that streams data and SPU stores these statistics).
Question forty six. Does Everything Gets Cached In Netezza (or Any Other Data Appliance)?
Answer :
Typically most effective schema and different database gadgets are cached in home equipment. Data isn't always cached, in popular. In maximum instances, records is not stored any wherein (in any cache or on host laptop) and is streamed directly from SPU to client software.
Question 47. How Do You Deal With Historical Data, With Respect To Zone Maps?
Answer :
Sort information first, based totally on ancient records (for instance, date) and load this in the use of nzload.
Question 48. What Is A Zone Map?
Answer :
Zone map in Netezza is similar (idea sensible) to partitions in Oracle. Netezza continues map for records so that it does relies on quarter map to tug simplest the variety it's far interested in. For instance, if we need to drag out data from Jan 2009 till June 2009 from a desk that is dispensed on date column, zone map allows us to reap this. Zone map is maintained through Netezza automagically, no consumer intervention wanted. Zone mapping is accomplished at a block (quantity) level. Netezza has quarter maps for all columns (now not just disbursed column) and consists of facts consisting of minimal, most, overall number of statistics.
Question forty nine. Explain Fpga And How Is It Useful For Query Performance?
Answer :
FPGA: Field Programmable Gate Array (FPGA) is located on every SPU. Netezza is different from different architectures. Netezza can do a “hardware improve” via software program through the usage of FPGA. Hardware is reconfigured at some point of installation.
While reading data from disk, FPGA on each SPU also allows in ‘filtering’ unnecessary facts before getting loaded into memory on each SPU. This manner, FPGA does not overwhelm with all the records from disk.
Question 50. What Are 4 Environment Variables That Are Required. What Are Different States On Netezza?
Answer :
Environment variables: NZ_HOST, NZ_DATABASE, NZ_USER and NZ_PASSWORD
Online: Normal or normal state.
Stopped: Netezza will shutdown after finishing cutting-edge queries, no new queries allowed.
Offline: Waits for finishing touch of modern queries, new or queries in queue acquire mistakes.
Paused: Same as above, but no errors displayed. Typically brought about for the duration of Netezza bootup or startup.
Down: Just undeniable down, may be because of Netezza server hassle or person initiated.
