IBM Netezza Interview questions (2/3)

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?

Using DELETEXID.

set show_deleted_records = TRUE

33. What are various ways to load tables in Netezza?

  1. External Table
  2. NZLOAD (Wrapper to External Table)
  3. CTAS

 

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.

A: Atomicity

C: Consistency

I: Isolation

D: Durability

 

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?

96 each

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

sysmgr.log

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

 

Thanks,
Ravi

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: