Netezza Database Administration Quiz

Materialized views are pre-computed and stored physically on disk, unlike regular views which are only stored queries. The materialized view occupies actual storage space to hold the computed result set, which is why they need to be refreshed when base data changes.

Accessing a materialized view requires explicitly querying the view by name in your SQL statement. You must write queries against the materialized view directly - the database does not automatically rewrite queries to use the materialized view instead of base tables. Query rewrite is an optional feature that must be explicitly enabled and configured.

In Netezza, the materialized view refresh threshold is set using SET SYSTEM DEFAULT MATERIALIZED REFRESH THRESHOLD. This system parameter controls what percentage of base table changes will trigger an automatic refresh of the materialized view. The SET MV DEFAULT syntax is incorrect.

Database row IDs are guaranteed to be unique but are not sequential. When records are deleted, gaps are created in the sequence. Additionally, concurrent inserts can allocate IDs that don't appear in strictly increasing order. This is true for most database systems including Netezza which uses a unique row identifier system.

DeleteXid defaults to 0, which is a special value indicating the record has not been deleted. When a record is deleted, its DeleteXid is set to the transaction ID that performed the deletion. This is part of Netezza's transaction tracking system where each record carries both InsertXid and DeleteXid.

Transaction IDs (XIDs) in Netezza are generated sequentially for committed transactions. The system maintains a monotonically increasing sequence of transaction identifiers, which is fundamental to how the database tracks changes and provides transactional isolation and rollback capability.

Netezza manages locking automatically and does not provide user-accessible explicit table locking commands. The system uses its own locking mechanisms internally to maintain consistency and isolation, but users cannot directly issue LOCK TABLE commands or manually control table-level locks.

Netezzza uses a zone map architecture and append-only storage model. Updated records are marked as deleted and new versions are appended rather than being modified in-place. This design supports features like time travel queries and efficient rollback capability.

When an insert or load operation is aborted, any records that were inserted during that transaction are not visible to other users. The transaction is rolled back completely, maintaining atomicity - either all records are visible or none are, preserving data consistency.

Users can generate statistics on specific columns using commands like GENERATE STATISTICS. This is a common database administration task for optimizing query performance. Column-level statistics help the query optimizer make better decisions about query execution plans.

Generate statistics and Generate Express statistics are valid Netezza commands for collecting table statistics. Generate JIT statistics is not a valid command syntax in Netezza - JIT (Just-In-Time) statistics are automatically collected by the system during query execution, not through a manual generation command.

JIT statistics are automatically generated by Netezza during query execution on regular database tables to help the optimizer make better decisions. External tables, system tables, and virtual tables do not have JIT statistics generated for them.

JIT statistics are limited and collected incrementally during query execution. Running Generate statistics provides comprehensive table statistics that cover all columns and give the optimizer complete information. Both types serve different purposes - JIT for immediate optimization and Generate for complete analysis.

The nzsession command is used to manage and control user sessions in Netezza. It can be used to view active sessions and abort or terminate specific transactions, making it the correct choice for aborting transactions.

The nzsystem command is a versatile utility in Netezza that can start the server (nzsystem start), stop the server (nzsystem stop), and show server status (nzsystem status or nzsystem show). Therefore all three operations are valid uses of this command.

After loading data with nzload, generating statistics is essential for optimal query performance. The optimizer needs current table statistics to create efficient execution plans. Without updated statistics, queries may perform poorly after data loads.

The nzsql -t option displays only the data rows (tuples) without column headers or other formatting elements. This is useful for extracting pure data for scripting or further processing. The -r option is for row output mode, -c is for running a single command.

The ! meta-command in nzsql is used to pause the current query and escape to the shell to execute operating system commands. This allows users to run shell commands without exiting nzsql. To exit nzsql, use \q instead.

The \ds meta-command in nzsql lists all sequences in the current database. Sequences are database objects used to generate unique numeric values, typically for auto-incrementing primary keys. This is different from \dt for tables or \dn for synonyms.