IBM Netezza Interview Questions and Answers
Q1) What is Netezza?
- IBM Netezza is a data warehouse appliance in which both h/w and s/w are tightly coupled to deliver the best performance to the business.
- The appliance is like a black box. Forex: Washing machine.
Q2) List various data warehouse appliances available in the market? (or) who are all Netezza’s competitors?
- Oracle Exadata
- EMC Greenplum
- HP Vertica
- Microsoft Parallel machine.
Q3) Netezza (vs) Hadoop:
- Hadoop: For Unstructured data. Forex: Someone wishing you on your birthday
- Netezza: Structured data. Registering for a Facebook account.
Q4) List various appliance components?
- SMP Hosts
- Snippet Blades
- Subcomponents: Disk Enclosures, SPA, SPU, FPGA
S-Blade = SPU
Storage Array 1, Storage Array 2.
Each Storage Array has 4 disk Enclosures. Each Disk Enclosure contains 12 disks of 1TB size.
Each S-Blade owns several disks, which reside in a storage array within the same rack.
Storage Array = Storage Disk = N disk enclosures
Storage Array 1 + Chassis 1 => Snippet Processing Array (SPA) 1
Sorage Array2 + Chassis 2 => SPA 2
Q5) List a few ETL tools?
Ans: Informatica, Datastage, Ab intio
Q6) List a few BI tools?
Ans: Cognos, OBIEE, SSIS
Q7) Where system catalog information is stored? In Netezza host or Netezza disks?
Ans: Netezza hosts.
For example: when we create a table, the table definition is stored in host disks (df -k) and rows/records of this table are stored in Netezza disks
Q8) What are default user-id's and passwords in Netezza?
Ans: root, nz, admin.
Q9) What is clustering in Netezza?
Ans: Clustering is nothing but a grouping of similar things. In Netezza, we have 2 SMP hosts. These 2 hosts are part of a cluster. In this cluster, one host is active and another host is passive.
Q10) List a few backslash commands in Netezza?
Ans: Backslash commands: To avoid basic day to day SQL
For example, du is to display users. l is to list the database users.
Q11) What is the difference between “nzsql” and “nzsql -E”
Ans: E ==> To see the underlying query generated by the backslash command.
Q12) List 2 default databases in Netezza?
Ans: SYSTEM, MASTER_DB
MASTER_DB: This is a template database; helps us to create new databases faster.
Q13) What is the extent and page size in Netezza?
- Extent size: 3MB
- Page size: 128 KB
Note: The above are fixed. we can’t change them.
Q14) What are various maximums in Netezza?
- Columns: 1600 per table or view
- Maximum columns per distribution: 4
- Max Length of database and column names: 128 bytes
- Max number of characters in a char/varchar field: 64,000
- Max connections to the server: 2000. Default: 500
- Row size: 65,535 bytes
Q15) List a few environmental variables in Netezza?
Q16) List constraints in Netezza
- Primary Key (Not enforced)
- Unique key (Not enforced)
- Referential Integrity (Not enforced)
- Not Null (Enforced)
- Data type verification (Enforced)
Q17) Techniques to improve Netezza appliance performance?
Ans: As a developer:
At design time, have the best distribution key. To avoid data skew
Use appropriate data types. For example: If INT2 is good, then don’t use INT4. Forage, Byteint (Int1) is good enough
Q18) What is a Snippet?
Ans: Snippet is a part of the given query. When we submit a query to Netezza, it is divided into multiple snippets. These snippets are executed by Snippet(s)-Blades.
Q19) How do you change the distribution key of already creating a table?
Ans: CTAS: Create a table as …