31. How do you remove logically deleted records?
Using Groom Table/Nzreclaim. Before we apply groom, the delete record must be part of at least one backup set id.
Groom (vs) Nzreclaim:
Nzreclaim: CLI. This can be only run from NZSQL interface.
Groom: We can run this from SQL interface. For ex: we can run this from Aginity.
32. How do you restore deleted records?
set show_deleted_records = TRUE
33. What are various ways to load tables in Netezza?
- External Table
- NZLOAD (Wrapper to External Table)
34. What are various ways to unload a table or create a table level backup?
- External Table
35. What are external tables in Netezza?
- In Netezza we have 3 types of tables:
- System Tables: These are stored on Netezza host disks
- User tables: These are stored on Netezza data slices
- External Tables: These are stored outside Netezza system. If we have file on Windows box, then we have to use REMOTESOURCE ‘odbc’ option
36. How do you update a row/record in Netezza table?
- If we are trying to update distribution column, it fails
- In Netezza update = Delete current + Insert new
37. Can you explain what happens in Netezza when you try to update a row/record in Netezza?
Same as (6) above
38. What are the fundamental principles on which Netezza is built?
- AMPP (Asynchronous Massive Parallel Processing): Gives massive performance
- Because of its distribution capabilities
- FPGA (Secret sauce of Netezza’s massive performance)
- Easy maintenance. For example: No indexes, Limited space management. High Availability
- Scalability: We can easily plug in another Netezza boxes to current Netezza appliance.
39. What is FPGA?
Field programmable Gate Array. It takes care of de-compression, Restrictions, & Projections and sends the filtered data back to CPU.
40. What are transactions in Netezza?
Transaction: All or None. It follows ACID properties.
In Oracle, we have to explicitly commit a transaction. But In Netezza, Transactions are auto commit unless we explicitly use BEGIN.
41. What are various datatypes in Netezza?
SELECT * FROM _V_DATATYPE;
42. List 4 hidden datatypes in Netezza
ROWID, CREATEXID, DELETEXID, Datasliceid
Rowid, Createxid, Deletexid are stored in Netezza Data slices.
But, Datasliceid is stored on Netezza host disks
43. How do you create a new user?
Using CREATE USER
\h create user ==> Gives us the help
44. How do you create a new group?
Using CREATE GROUP
\h create group ==> Gives us the help
45. How do you add existing users to an already create group?
Alter user (or) Alter Group
46. What are resource sharing groups (RSG)?
Normal Group (vs) Resource Sharing Group:
By default every group belongs to Normal Group/Public.
RSG’s are nothing but we specify resource usage limits. For example: We add CEO, CIO, Senior Leadership userid’s into HIGHPRIORITY groups. So that, when ever they login to check any dashboards, more resources will be allocated to them.
47. List 2 types of privileges in IBM Netezza
- Object Level Privileges: Select, insert
- System Level Privileges: Create database, create user
48. What is a primary key? Does Netezza have Primary Key constraints?
Primary key = Unique key + No nulls
In Netezza we can create primary key, but Netezza doesn’t enforce primary keys.
49. What is a foreign key? Does Netezza has foreign Key constraints?
Foreign Key: Parent-child relation
In Netezza we can create foreign key, but Netezza doesn’t enforce foreign key constraints
50. What is a NOT NULL constraint? Why NOT NULL is better for Netezza’s performance
Every row in Netezza has a NULL VECTOR which says about the columns null/not null details.
If row header says a particular column is NOT NULL, we don’t have to do some extra checks for that column at the run time.
51. What is the difference between SPU and S-Blade?
Both are same. Where “S” stands for Snippet.
In old Netezza versions – such as Mustang – we use SPU terminology.
52. What is a dataslice and disk?
Disk: Physical drive on which data resides.
Host disks: hold Netezza software, host operating system, database metadata
SPU disks: hold user databases and tables
Data slice: Logical representation of the data that is saved on a disk
Data partition: Logical representation of a data slice that is managed by a specific SPU
53. How may disks, FPGA’s, CPU Processors are available in Twinfin 12?
54. How may disks, FPGA’s, CPU Processors are available in Twinfin 6?
96/4 = 24 each
55. How may disks, FPGA’s, CPU Processors are available in Twinfin 24?
96 *2 ==> 192 each
56. What are clustered base tables and how they are different from normal tables?
Clustering is nothing but grouping of similar things.
In clustered base tables, we group a tables records using a column/columns.
These columns are called as organizing key.
For a table, we can specify a maximum of 4 columns as an Organizing Key.
57. List the log file name that captured day-to-day Netezza’s diagnostic messages
58. What are various things collected as part of statistics?
- Number of rows
- Number of columns & it’s diversity
- How data is deviant from standard deviation
- About nulls/not nulls
59. What are views? List few system views?
Logical representation of a table. _v_datatype, _v_table. Anything starts with “_v”
60. What is a Join? List various Joins in Netezza?
Join: Combining result from multiple tables.
Ex: Inner join, self join, Outer join, Left Outer Join, Right Outer Join, Cross Join