IBM Netezza Interview questions (3/3)

61. What is a Sequence? How do you create a new sequence?

These are used to generate surrogate key.

62. How do you delete all rows in a Netezza table at once?

Truncate table :  We can’t rollback

Delete from table: We can rollback using DELETEXID column

63. Do we have private synonyms in Netezza?

No. All synonyms are public.

64. How do you find out table size?

select objname, sum(used_bytes)/1024/1024 size_in_MB from _v_obj_relation_xdb

join _v_sys_object_dslice_info on (objid = tblid) where objname like ‘T1%’;

How to run?

nzsql -db SYSTEM -f c.sql  (or) nzsql -db RAVI -f c.sql

(or) run the above SQL from GUI

[nz@netezza ravi]$ nzsql -db ravi -f c.sql

OBJNAME | SIZE_IN_MB

———+————

T1      |   0.750000

(1 row)

 

65. How do you find out a database size?

select orx.database::nvarchar(64) as “databasename” ,

case when sum(sod.used_bytes) is null then 0 else sum(sod.used_bytes)/1073741824 end as “usedspace_gb”,

case when sum(sod.allocated_bytes) is null then 0 else sum(sod.allocated_bytes)/1073741824 end as “allocatedspace_gb”

from _v_sys_object_dslice_info sod inner join _v_obj_relation_xdb orx on orx.objid = sod.tblid

group by “databasename” order by “databasename”;

Output will be like below:

**************************

[nz@netezza ravi]$ nzsql -f b.sql

databasename | usedspace_gb | allocatedspace_gb

————–+————–+——————-

RAVI         |     0.000122 |          0.002930

TESTDB       |     0.000244 |          0.005859

UAT          |     0.000244 |          0.005859

(3 rows)

66. What data types are most suited for Zone maps?

Integer Datatypes (Byteint, Smallint, Int, Bigint)

Date

67. List which options are prioritized when join operation is required.

Netezza evaluates joins in this order of preference:

1) Colocated joins: All data for joins are located on the same SPU.

2) Redistribute: All required data is not located in the same SPU, send data to corresponding SPU where driving table data is located.

3) Broadcast: Mentioned as replication above; Send all data from SPU to host which collates all that data, sends it to all SPUs. Each SPU has entire table data. That is, if Netezza machine has 32 SPU, there will be 32 physical tables, one each SPU.

 

Coming to joins, typically Netezza prefers in this order:

A) Hash join in memory

B) Hash join on disk

C) Sort Merge join

D) Nested loops

E) Cross join

Oracle preference closely resembles the same or similar order.

68. What are CLI commands to stop and start Netezza?

nzstop

nzstart

nzsystem -restart

69. Can we stop and start Netezza using NZSYSTEM?

nzsystem -restart

70. Why integer data types are preferred in Netezza?

They give best performance in Joins.

 

71. Where do you find Netezza system parameters?

/nz/data/postgresql.conf

 

72. In Netezza, can we do cross database inserts/updates/deletes?

Nope. Only selects

 

73. In Netezza, can we do cross database selects?

Yes

 

74. What is the difference between active session and active transaction?

A session can have multiple transactions.

We can see active sessions using “nzsession”

[nz@netezza kit]$ nzsession -h

Usage: nzsession [-h|-rev] [-hc] <subcmd> [<subcmd options>]

Options:

-h or -? or –help   display this help

-[rR]ev or -V        print the software revision of this program

-hc            display help for <subcmd>

 

Subcommands:

show              show the list of current sessions (default)

abort             abort a running user session

abortTxn          abort the transaction of a current SQL session

priority          change the priority of an SQL session

listSessionTypes  list the session types supported by show

 

75. Get Netezza version running

select version();

76. How do find out running port number in Netezza?

netstat | grep port

 

77. What is the use of NZPASSWORD cli command?

To cache passwords; Avoids the need of typing the password every time.

 

78. How do you find locks in Netezza?

Netezza follows Higher isolation level called serializable.

nz_show_locks DB_NAME  TABLE_NAME

 

79. What are version tables?

https://www-304.ibm.com/connections/forums/html/topic?id=c9f19ff4-c267-4e9f-bcf3-2c4dd9619a39

 

80. Alter table

https://www-304.ibm.com/support/knowledgecenter/SSULQD_7.2.0/com.ibm.nz.dbu.doc/r_dbuser_alter_table.html

 

81. Alter table change database of a column

https://www-304.ibm.com/connections/forums/html/topic?id=49f96c09-fd83-4e72-a118-aaadbc74c4a0&ps=25

 

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: