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
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
66. What data types are most suited for Zone maps?
Integer Datatypes (Byteint, Smallint, Int, Bigint)
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?
69. Can we stop and start Netezza using NZSYSTEM?
70. Why integer data types are preferred in Netezza?
They give best performance in Joins.
71. Where do you find Netezza system parameters?
72. In Netezza, can we do cross database inserts/updates/deletes?
Nope. Only selects
73. In Netezza, can we do cross database selects?
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>]
-h or -? or –help display this help
-[rR]ev or -V print the software revision of this program
-hc display help for <subcmd>
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
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?
80. Alter table
81. Alter table change database of a column