Transcript 000-540 IBM PureData System for Analytics v7.0 Version

000-540
IBM PureData System for Analytics v7.0
Version 4.0
Topic 1, Volume A
QUESTION NO: 1
A SELECT statement spends all its time returning 1 billion rows. What can be done to make this
faster?
A. Use a CURSOR.
B. Use an EXTERNAL table.
C. Use the COPY command.
D. Increase the PreFetch Count.
Answer: B
QUESTION NO: 2
What function can nzbackup perform?
A. Back up pg.log files.
B. Back up TEMP tables.
C. Back up host data (catalog metadata).
D. Back up the data using the specified number of streams.
Answer: D
QUESTION NO: 3
nzbackup/nzrestore with the -user option has been replaced with which option to allow multi-level
security (MLS) information, such as categories, cohorts, and levels to be backed up/restored?
A. -all
B. -mls
C. -users
D. -globals
Answer: D
QUESTION NO: 4
When loading a local ASCII file into table via an external table, where can the SQL be submitted?
A. From a User Defined Function.
B. From a JDBC application networked to the system.
C. From nzsql on an AIX machine networked to the system.
D. From nzsql on Microsoft Windows networked to the system.
Answer: B
QUESTION NO: 5
Which is NOT a function that can be performed by nzbackup?
A. Backup data
B. Backup schemas
C. Backup users/groups
D. Backup the /nz/data directory
Answer: D
QUESTION NO: 6
Which nzload operation statement is correct?
A. nzload must have a control file.
B. nzload performs singleton inserts.
C. nzload user must have CREATE EXTERNAL TABLE privilege.
D. nzload must be run from the PureData System for Analytics host.
Answer: C
QUESTION NO: 7
Name the CLI application that allows you to load data from ASCII files to database tables?
A. nzload
B. loadmgr
C. nzimport
D. nzrestore
Answer: A
QUESTION NO: 8
You have access to two databases, DEV and PROD. Currently you are connected to the DEV
database. Which operation is permitted?
A. Delete from PROD..TableA
B. Select * from PROD..TableA
C. Truncate table PROD..TableA
D. Insert into PROD..TableA select * from DEV..TableB
Answer: B
QUESTION NO: 9
Which statement is true?
A. MATERIALIZED VIEWS can be run against EXTERNAL tables.
B. GENERATE STATISTICS can be run against EXTERNAL tables.
C. EXTERNAL table data is deleted using the SQL DELETE command.
D. EXTERNAL tables can be used to unload data to a Microsoft Windows directory.
Answer: D
QUESTION NO: 10
What is required to troubleshoot a query?
A. nzevents and the pg.log.
B. nzsql and the ODBC config.
C. nzinventory and the dbos.log.
D. nzdumpschema and the execution plan.
Answer: D
QUESTION NO: 11
Given a plan ID, how would you obtain the query execution plan? (Choose two.)
A. Turn ODBC tracing ON.
B. Issue "SELECT * FROM _V_SESSION;"
C. Go to the query history within the Windows NzAdmin GUI tool.
D. Run EXECUTION PLAN before the query has been submitted.
E. Issue "SHOW PLANFILE <nn>;" after the query has been submitted.
Answer: C,E
QUESTION NO: 12
When is the ToplogyImbalance event triggered?
A. When a SAS switch has failed.
B. When the path the path between an S-Blade and its disks has failed.
C. When a disk enclosure's voltage sensor is reading outside the nominal range.
D. When an S-Blade has ownership of more dataslices than is optimal (usually following a disk
replacement).
Answer: D
QUESTION NO: 13
Which component failure does NOT trigger a HardwareServiceRequested event?
A. Fan
B. Disk
C. Host
D. S-Blade
Answer: C
QUESTION NO: 14
Which statement describes administrative privileges?
A. Provides audit functionality for the database.
B. Manages resource allocations for concurrent processing.
C. Gives permission to execute global operations and to create objects.
D. Gives additional storage to select members of the administrator group.
Answer: C
QUESTION NO: 15
The query history data collected DOES NOT provide insight to which of the type of performance
and behavior questions?
A. The typical or most common types of queries.
B. The longest and shortest running queries on the system.
C. The queries submitted by specific users or groups of users.
D. The rotational speed of the disk drives during query execution.
Answer: D
QUESTION NO: 16
When setting up a crontab entry, what must be done on a PureData System for Analytics server?
A. Create an nzcron event.
B. nzstop/nzstart the database.
C. Stop and start the appliance.
D. Add entry to active host and standby host.
Answer: D
QUESTION NO: 17
Which is a function of the Linux-HA heartbeat?
A. Performs regeneration of failed disks on the hosts.
B. Manages communication and processes on the S-Blades.
C. Performs disk partitioning and status of jobs on the S-Blades.
D. Manages communication and status of services on the hosts.
Answer: D
QUESTION NO: 18
Which High-Availability (HA) mechanism is used by the PureData System for Analytics
Appliance?
A. AIX-HA
B. Linux-HA
C. SuSE-HA
D. Solaris-HA
Answer: B
QUESTION NO: 19
Which condition will NOT fail or restart a running query on a PureData System for Analytics
Appliance?
A. Disk resets or fails.
B. Host resets or fails.
C. S-Blade resets or fails.
D. Snippet Processing Unit (SPU) resets or fails.
Answer: A
QUESTION NO: 20
Which condition will interrupt a query running on a fully operational PureData System for
Analytics?
A. One fan fails.
B. One disk fails.
C. Active host fails.
D. One power supply fails.
Answer: C
QUESTION NO: 21
A group has a resource allocation maximum of 50% and the job maximum is set to 1. Two users
within this resource group each execute 1 job at the same time, which statement is correct?
A. Each job gets 25% and both run at the same time.
B. Each job gets 50% but only one job runs at a time.
C. Each job gets 50% and both run at the same time.
D. You cannot set the job maximum per resource group.
Answer: B
QUESTION NO: 22
Given the following query: Select count(*) from TableA; How can you view the query plan without
executing the query?
A. Use explain.
B. Use statistics.
C. Use pg.log file.
D. Set show_plan = true;
Answer: A
QUESTION NO: 23
TableA is currently being accessed in a number of long running queries. Which of the following
operations is NOT blocked, thus allowing the operation to immediately proceed?
A. DROP TABLE TableA
B. ALTER TABLE TableA
C. TRUNCATE TABLE TableA
D. GRANT SELECT ON TableA TO UserA
Answer: D
QUESTION NO: 24
If 'admin' user is running a query, what percentage of resources are available when Guaranteed
Resource Allocation (GRA) is Enabled?
A. The maximum GRA assigned to the ADMIN group.
B. The maximum GRA assigned to the PUBLIC group.
C. 100% of the resources while other resource groups are active.
D. Typically half of the resources while other resource groups are active.
Answer: D
QUESTION NO: 25
What CANNOT be measured and managed by system views?
A. Temperature values on S-blade.
B. Status of the host attached storage.
C. Power supply status for each frame.
D. Resource group utilization and history.
Answer: B
QUESTION NO: 26
What will occur when two tables are joined together and the data is NOT collocated?
A. No data movement is required.
B. A merge join operation will be performed.
C. A cluster based join method is performed.
D. Data is dynamically redistributed or broadcasted.
Answer: D
QUESTION NO: 27
When a temporary table is created, where will the data be stored?
A. On the host under the /nz/tmp directory.
B. On the S-Blades under the data partition.
C. Held in physical memory on the S-Blades.
D. On the S-Blades under the swap partition.
Answer: B
QUESTION NO: 28
What is the limitation on join types with floating-point data types?
A. The system does not have a limitation on floating-point data types.
B. The system cannot perform any type of join on floating-point data types.
C. The system cannot perform a fast sort merge join on a floating point data type, but instead must
perform a slower hash join.
D. The system cannot perform a fast hash join on a floating point data type, but instead must
perform a slower sort merge join.
Answer: D
QUESTION NO: 29
Which statement is true about data flowing in/out of the PureData System for Analytics Appliance?
A. Bypasses the host.
B. Flows through the host.
C. Compressed on the host.
D. Uncompressed on the host.
Answer: B
QUESTION NO: 30
Why is table skew important to manage?
A. Low table skew negatively impacts performance.
B. High table skew negatively impacts performance.
C. Low table skew negatively impacts data compression.
D. Deleted rows consume disk space until they are freed.
Answer: B
QUESTION NO: 31
Which statement is true regarding the compress engine?
A. Data automatically gets compressed.
B. Columns to be compressed must be defined.
C. The nzcompress utility must be run to compress the table.
D. The nzcompress utility must be run to compress the database.
Answer: A
QUESTION NO: 32
The PureData System for Analytics implements which level of transaction isolation?
A. Serializable
B. Committed read
C. Repeatable read
D. Uncommitted read
Answer: A
QUESTION NO: 33
Which two statements are true about queries on the PureData System for Analytics Appliance?
(Choose two.)
A. Queries are executed in parallel by the host.
B. Queries must be compressed before they can be executed.
C. Queries are compiled into snippets and executed in parallel by the S-Blades.
D. Queries must be uncompressed by the user and executed in parallel by the host.
E. A query is compiled into a snippet which is executed across all available S-Blades.
Answer: C,E
QUESTION NO: 34
In order to set a runaway query event to trigger when a query runs longer than two minutes, which
two items must be configured? (Choose two.)
A. The runaway query event was enabled.
B. The pg.log file was configured for two minutes.
C. The runaway query event was configured for two minutes.
D. The nzsystem command was used to set the QUERYTIMEOUT to two minutes.
E. The CREATE USER command was used to set the QUERYTIMEOUT to two minutes.
Answer: A,E
QUESTION NO: 35
Where are the database log files located?
A. /var/log
B. /usr/local
C. /nz/kit/log
D. The SYSTEM database
Answer: C
QUESTION NO: 36
OLE-DB based applications on UNIX requires which PureData System for Analytics software?
A. Install the JDBC driver for UNIX.
B. Install the ODBC driver for UNIX.
C. Install the OLE-DB driver for UNIX.
D. OLE-DB is not supported on UNIX.
Answer: D
QUESTION NO: 37
In order for an ODBC application running on Microsoft Windows to connect to PureData System
for Analytics, which two steps must be performed? (Choose two.)
A. Install nzsql for Windows.
B. Install nzAdmin for Windows.
C. Install an ODBC driver for Windows.
D. Install an ODBC Driver Manager for Windows.
E. Create or modify a Data Source Name (DSN) for a connection to a database.
Answer: C,E
QUESTION NO: 38
Which command is used to display operational statistics about system capacity, faults, and
performance?
A. nzstats
B. nzstate
C. nzshow
D. nzsystem
Answer: A
QUESTION NO: 39
Which command is used to abort a user's SQL transaction?
A. kill
B. abort
C. nzstop
D. nzsession
Answer: D
QUESTION NO: 40
Which command allows you to show information about the database hardware as well as take
actions such as activate or deactivate components, locate components, or delete them from the
system?
A. sar
B. nzhw
C. nzsystem
D. nzinventory
Answer: B
QUESTION NO: 41
What is the only predefined database user, which is able to access all objects and perform all tasks
against the database?
A. root
B. public
C. admin
D. system
Answer: C
QUESTION NO: 42
What operating system runs on a PureData System for Analytics host?
A. AIX
B. SUSE Linux
C. Nucleus OS
D. Redhat Linux
Answer: D
QUESTION NO: 43
What PureData System for Analytics component is responsible for controlling table storage?
A. S-Blade (Snippet-Blade)
B. SFI (Switching Fabric Interface)
C. FPGA (Field Programmable Gate Array)
D. DRBD (Distributed Replicated Block Device)
Answer: A
QUESTION NO: 44
Click on the exhibit.
Given the following query: SELECT * FROM TableA, TableB WHERE TableA.c_custkey =
TableB.o_custkey; What type of join processing will occur when the query is executed?
A. Collocation hash join processing
B. Distributed hash join processing
C. Broadcast sort merge join processing
D. Replication sort merge join processing
Answer: A
QUESTION NO: 45
Why should you avoid using Boolean data types as a distribution key for a table ?
A. Table will not join.
B. Table will not broadcast.
C. Table is likely to be skewed.
D. Table is likely to be evenly spread across data slices.
Answer: C
QUESTION NO: 46
What will prevent a GROOM TABLE command from running on a table named TableA?
A. If there are any materialized views on the table TableA.
B. If there are any active selects running against the table TableA.
C. If there are any active nzloads running against the table TableA.
D. If there are any active updates running against the table TableA.
Answer: A
QUESTION NO: 47
What two characteristics can be changed when using the CREATE TABLE AS (CTAS) statement to
copy TableA to TableB? (Choose two.)
A. The views on TableA.
B. The privileges on TableA.
C. The distribution key of TableB.
D. The materialized views on TableB.
E. The order of the data stored in TableB.
Answer: C,E
QUESTION NO: 48
When a CREATE TABLE AS (CTAS) is performed on a table creating more than 1,000,000,000
rows, which statement is true?
A. Groom will be automatically invoked.
B. Primary key constraints will be checked.
C. Statistics will automatically be generated.
D. Materialized views will be automatically generated.
Answer: C
QUESTION NO: 49
What is the host Linux file that must be modified to adjust optimizer settings permanently?
A. /nz/data/postgresql.conf
B. /nz/data/config/system.cfg
C. /nz/kit/sys/initTopology.cfg
D. /nz/kit/log/sysmgr/sysmgr.log
Answer: A
QUESTION NO: 50
Which statement is true about the groom feature?
A. Groom allows the user to add row level security.
B. Groom validates primary key constraints on the table.
C. Groom reorders rows based on the DISTRIBUTE ON clause.
D. Groom allows the user to reorganize tables based on the ORGANIZE ON clause.
Answer: D
QUESTION NO: 51
Why is generating statistics on a table important to query performance?
A. The table statistics are used to facilitate disk space reclamation of deleted rows.
B. The optimizer uses statistics to determine the optimal execution plan for queries.
C. The Zone Map information cross references the table statistics for each column to avoid
scanning.
D. The Field Programmable Gate Array (FPGA) utilizes the statistics to identify which
row/columns to filter.
Answer: B
QUESTION NO: 52
Using a date column as a distribution key may distribute rows evenly across dataslices, why is this a
poor choice of a distribution key?
A. Queries on the table will invoke compression.
B. Queries on date range may involve processing skew.
C. Queries on date range will utilize only host processing.
D. Queries on the table will invoke compression on the date columns.
Answer: B
QUESTION NO: 53
Which statement is true?
A. All user space is available for database expansion.
B. Each database is assigned it's own section of disk space.
C. When creating the database you can specify which disks to use.
D. When you create a database you can set the maximum space it is allowed to use.
Answer: A
QUESTION NO: 54
You have created a group GroupA with JOB MAXIMUM of 4, which statement is true?
A. No more than 4 users can be assigned to GroupA.
B. If users in GroupA submit more than 4 queries they will fail.
C. If users in GroupA submit more than 4 queries, then they will queue in order.
D. The job maximum cannot be less than the default concurrent query limit of 48.
Answer: C
QUESTION NO: 55
Which CREATE DATABASE attributes are required?
A. The database name.
B. The database name and the redo log file name.
C. The database name and the table space name.
D. The database name and the temporary table space name.
Answer: A
QUESTION NO: 56
Which statement is true for database users and groups?
A. All users must belong to admin group.
B. Creation of users and groups is not allowed.
C. Users and groups are local and tied to a particular database.
D. Users and groups are global and not tied to a particular database.
Answer: D
QUESTION NO: 57
When creating a table, you can specify a column constraint for each individual column of either
"NULL" or "NOT NULL". When column values are later evaluated within a query statement,
which of these is true?
A. 0 is equal to null, the empty string ( '' ) is equal to null.
B. 0 is equal to null, the empty string ( '' ) is not equal to null.
C. 0 is not equal to null, the empty string ( '' ) is equal to null.
D. 0 is not equal to null, the empty string ( '' ) is not equal to null.
Answer: D
QUESTION NO: 58
To create a database table, at a minimum, what must be specified?
A. column name and data type.
B. column name, data type and primary key.
C. column name, data type and default constraint.
D. column name, data type and distribute on clause.
Answer: A
QUESTION NO: 59
Which two characteristics describe materialized views. (Choose two.)
A. Materialized views can contain aggregates.
B. Materialized views can contain a HAVING clause.
C. Materialized views can contain a WHERE clause.
D. Materialized views can contain an ORDER BY clause.
E. Materialized views can reference only one base table in the FROM clause.
Answer: D,E
QUESTION NO: 60
What is a benefit of Clustered Base Tables?
A. Replicates base table data.
B. An alternate way to order a table's data.
C. Materializes on disk joins between tables.
D. Increases the performance of load operations.
Answer: B
QUESTION NO: 61
When are zonemaps initially created for a table?
A. nzbackup
B. nzreclaim
C. CREATE TABLE
D. ALTER TABLE ADD ZONEMAPS
Answer: C
QUESTION NO: 62
Which of the following is a valid datatype definition?
A. TEXT
B. CLOB
C. CHAR(64000)
D. NUMERIC(1,38)
Answer: C
QUESTION NO: 63
Columns in a table may be zonemapped. How are those columns chosen?
A. They are automatically selected by the system.
B. They are based on the same columns as the table's distribution key.
C. They are added via the ALTER TABLE ADD INDEX(column_name) statement.
D. They are added via the ALTER TABLE ADD ZONEMAP(column_name) statement.
Answer: A
QUESTION NO: 64
When doing a CREATE SEQUENCE, the largest "maximum value" is based on what?
A. BIGINT
B. FLOAT8
C. NUMERIC(38,0)
D. VARCHAR(256)
Answer: A
QUESTION NO: 65
On which component(s) does the UDX run?
A. The host only.
B. The host and the S-blades only.
C. The S-blades and the FPGAs only.
D. The host, the S-blades and the FPGAs.
Answer: B
QUESTION NO: 66
Which procedural logic is NOT supported by NZPLSQL?
A. Scrollable Cursors
B. Looping (while, for)
C. Conditionals (if/else)
D. Returning a scalar result or a result set
Answer: A
Topic 2, Volume B
QUESTION NO: 1
Which attribute CANNOT be defined for both users and groups?
A. Row set limit.
B. Default priority.
C. Query time out.
D. Password expiration.
Answer: D
QUESTION NO: 2
Zone Maps provide what information about a table?
A. The list of unique column values found within an extent.
B. An index pointer to each row's exact location in the base table.
C. The minimum and maximum column values found within an extent.
D. The connection between the map and reduce stages when using MapReduce.
Answer: C
QUESTION NO: 3
What is the maximum number of databases that can be created?
A. 1
B. 12
C. 31
D. > 96
Answer: D
QUESTION NO: 4
When working with sequences, which statement will always be true?
A. One-up numbers are generated.
B. Integers (whole numbers) are generated.
C. The first value generated is the number 1.
D. The sequence number assigned will be unique across all tables and databases.
Answer: B
QUESTION NO: 5
What is a characteristic of a column that is based on the TIMESTAMP datatype?
A. It includes a date portion.
B. It includes a timezone offset.
C. The value is reevaluated each time the row is selected.
D. The value is initially assigned when the row is first created.
Answer: A
QUESTION NO: 6
Which will result in support for zonemaps on the LastName column?
A. CREATE TABLE TableA (LastName VARCHAR(20));
B. CREATE TABLE TableA (LastName VARCHAR(20));
ALTER TABLE TableA ZONEMAP ON (LastName);
C. CREATE TABLE TableA (LastName VARCHAR(20)) DISTRIBUTE ON (LastName);
D. CREATE TABLE TableA (LastName VARCHAR(20));
CREATE MATERIALIZED VIEW ViewA AS SELECT * FROM TableA ORDER BY LastName;
Answer: D
QUESTION NO: 7
What is the maximum number of columns you can choose as organizing keys for clustered base
table?
A. 1
B. 2
C. 4
D. 8
Answer: C
QUESTION NO: 8
Which clause CANNOT be used with a CREATE VIEW statement?
A. SUM ()
B. GROUP BY
C. ORDER BY
D. OUTER JOIN
Answer: C
QUESTION NO: 9
When doing a CREATE TABLE, constraints can be specified at the column level. Which two
constraints are NOT automatically enforced via the database? (Choose two.)
A. NULL
B. UNIQUE
C. NOT NULL
D. PRIMARY KEY
E. DEFAULT value
Answer: B,D
QUESTION NO: 10
Which statement is true regarding any database user?
A. The user has a table space defined.
B. The user can be assigned to more than one group.
C. The user must have a corresponding host account.
D. The user can be removed from the default PUBLIC group.
Answer: B
QUESTION NO: 11
What is the function of the Distributed Replicated Block Device (DRBD)?
A. High Availability
B. Load Balancing
C. High Concurrency
D. Work Load Management
Answer: A
QUESTION NO: 12
Which statement is true about a simple load using external tables?
A. The load can be configured to automatically continue to load if the S-Blade resets or fails.
B. The load can be configured to automatically create a backup file of the table being loaded.
C. The load can be configured to automatically create a duplicate copy of the table being loaded.
D. The load can be configured to automatically remove duplicate rows from the table being loaded.
Answer: A
QUESTION NO: 13
What is the state of the two hosts on a properly functional PureData System for Analytics
Appliance?
A. Active - Active
B. Active - Stand by
C. Stand by - Backup
D. Stand by - Passive
Answer: B
QUESTION NO: 14
nzload is invoked with the "allowReplay" option enabled. While the PureData System for Analytics
Appliance changes state from online to pausing then to online, which statement is true about the
nzload process on the Appliance?
A. nzload will fail.
B. nzload automatically continues without user intervention.
C. nzload must be stopped and resumed by the user from the last check point.
D. nzload automatically restarts but the user must remove the allowReplay option.
Answer: B
QUESTION NO: 15
What is the naming convention for system views?
A. _v_...
B. nz_v_...
C. _$v_...
D. sys_v_...
Answer: A
QUESTION NO: 16
What is the maximum number of columns that can participate in a distribution key?
A. One
B. Four
C. Unlimited
D. Depends on the number of columns of integer datatype.
Answer: B
QUESTION NO: 17
If data is being nzload'ed into TableA, which of the following operations will be blocked until the
load has completed?
A. A DELETE from TableA
B. A SELECT from TableA
C. A TRUNCATE of TableA
D. An nzbackup of the database in which TableA resides
Answer: C
QUESTION NO: 18
What two conditions must be met to achieve a collocated join between two tables? (Choose two.)
A. Both tables have primary keys defined.
B. Both join columns have the same name.
C. Both tables must be distributed on random.
D. Both join columns have identical data types.
E. Both tables must be distributed on their join column.
Answer: D,E
QUESTION NO: 19
Which statement is true regarding the compress engine?
A. Compression ratio is data dependent.
B. Compression is performed by the host.
C. Compression degrades data scan speeds.
D. Compression is performed by the SAS switches.
Answer: A
QUESTION NO: 20
The PureData System for Analytics implements which level of transaction isolation?
A. Serializable
B. Committed read
C. Repeatable read
D. Uncommitted read
Answer: A
QUESTION NO: 21
The simplest query execution plan file must have the following minimum nodes?
A. ScanNode, RestrictNode, LimitNode, ReturnNode
B. ScanNode, RestrictNode, HashNode, ReturnNode
C. ScanNode, RestrictNode, ProjectNode, ReturnNode
D. ScanNode, RestrictNode, AggregateNode, ReturnNode
Answer: C
QUESTION NO: 22
Short Query Bias (SQB) allows for reserved resources to be used for which type of query?
A. Queries with no joins.
B. Queries returning only one row in the result set.
C. SQL statements less than 255 characters in length.
D. Queries with a runtime estimation of less than 2 seconds.
Answer: D
QUESTION NO: 23
What is true about queries submitted on the PureData System for Analytics Appliance?
A. Queries are uncompressed by the host and sent to the user.
B. Queries are consolidated by the host and executed together in one block.
C. Queries are compiled by the host and executed in parallel by the S-Blades.
D. Queries are compressed by the host and optimized for parallel processing.
Answer: C
QUESTION NO: 24
What two types of notification can be configured for an event? (Choose two.)
A. Insert a row into TableA.
B. Run a stored procedure.
C. Run a command on the active Host.
D. Send an email to a list of addresses.
E. Send a Simple Network Message Protocol (SNMP) event.
Answer: C,D
QUESTION NO: 25
Where are the database log files located?
A. /var/log
B. /usr/local
C. /nz/kit/log
D. The SYSTEM database
Answer: C
QUESTION NO: 26
If user A has a minimum resource allocation of 20% and a maximum of 80%, user A can receive
what percentage of available resources?
A. A maximum of 20% of resources when other users are on the system.
B. A maximum of 50% of resources when other users are on the system.
C. A maximum of 80% of resources when no other users are on the system.
D. A maximum of 100% of resources when no other users are on the system.
Answer: C
QUESTION NO: 27
Given the following query: SELECT * FROM TableA; how does data flow through the PureData
System for Analytics components?
A. Disk to FPGA to Host to User.
B. Disk to FPGA to S-Blade to User.
C. Disk to Host to S-Blade to FPGA to User.
D. Disk to FPGA to S-Blade to Host to User.
Answer: D
QUESTION NO: 28
Which statement regarding temporary tables is true?
A. They are limited to 1 terabyte in size.
B. They may include a DISTRIBUTE ON clause.
C. They are limited to 16 per user per database.
D. They will be included in any nzbackup of the database.
Answer: B
QUESTION NO: 29
What is the fastest join utilized by PureData System for Analytics?
A. Hash join.
B. Merge join.
C. Expression join.
D. Cross product join.
Answer: A
QUESTION NO: 30
In a UNIX 64bit environment, a 64bit application requires which software to execute SQL
statements via ODBC? (Choose two.)
A. nzsql
B. An ODBC driver manager.
C. The system does not support 64 bit ODBC.
D. A 64-bit ODBC driver for the same UNIX environment.
E. A 32-bit ODBC driver for the same UNIX environment.
Answer: B,D
QUESTION NO: 31
OLE-DB based applications requires which PureData System for Analytics software?
A. Install the JDBC driver for windows.
B. Install the ODBC driver for windows.
C. Install the OLE-DB Driver for windows.
D. Install the nzload bulk load for windows.
Answer: C
QUESTION NO: 32
Which statement is NOT true regarding the nzload utility?
A. nzload runs on windows.
B. nzload runs on SuSE Linux.
C. nzload blocks update/delete against the target table.
D. nzload runs on the PureData System for Analytics host.
Answer: C
QUESTION NO: 33
When unloading data via an external table, where can the SQL be submitted?
A. From a User Defined Function.
B. From nzsql on an AIX machine networked to the system.
C. From nzsql on Microsoft Windows networked to the system.
D. From an ODBC application on Windows networked to the system.
Answer: D
QUESTION NO: 34
Which file format is NOT supported by the nzload utility?
A. ASCII delimited format.
B. Fixed length record format.
C. gzip compressed file format.
D. PureData System for Analytics compressed file format.
Answer: C
QUESTION NO: 35
Which statement is NOT true?
A. nzrestore can restore selected tables.
B. nzrestore can restore from an nzbackup UNIX pipe.
C. nzrestore can restore to a different database from the backup database.
D. nzrestore can restore to a different PureData System for Analytics server from the backup server.
Answer: B
QUESTION NO: 36
Which statement is NOT true?
A. nzbackup backs up selected tables.
B. nzbackup backs up to a file system destination.
C. nzbackup backs up to a Veritas NetBackup destination.
D. nzbackup backs up to a IBM Tivoli Storage Manager destination.
Answer: A
QUESTION NO: 37
You have access to two databases, DEV and PROD. Currently you are connected to the DEV
database. Which operation is permitted?
A. Delete from PROD..TableA
B. Select * from PROD..TableA
C. Truncate table PROD..TableA
D. Insert into PROD..TableA select * from DEV..TableB
Answer: B
QUESTION NO: 38
Within a single PureData System for Analytics, what is the fastest way to copy a 10TB TableA from
DatabaseA to another DatabaseB?
A. Use the COPY command.
B. Use external tables and UNIX pipes.
C. Use nzbackup and nzrestore commands.
D. Use a CTAS (Create Table as Select) SQL statement.
Answer: D
QUESTION NO: 39
What function can nzrestore perform?
A. Restore TEMP tables.
B. Restore database triggers.
C. Rename tables during the restoration.
D. Return materialized views to their original state.
Answer: D
QUESTION NO: 40
An ETL/ODBC application is inserting 1 million records in a single transaction against the
PureData System for Analytics. 1 million individual INSERT SQL statements are generated. What
can make this faster?
A. Use a COPY command.
B. Use an EXTERNAL TABLE.
C. Increase the Socket Buffer Size.
D. Set the COMMIT interval to 10,000.
Answer: B
QUESTION NO: 41
When enabled, query history always collects information about login failures, session creation,
session termination, and query history process startup. Which area does query history NOT collect
data?
A. Plans
B. Disks
C. Tables
D. Queries
Answer: B
QUESTION NO: 42
With LDAP enabled, which of the following is verified against an LDAP server?
A. Database user ADMIN privileges.
B. Database user SELECT privileges.
C. Database user GROUP authentication.
D. Database user password authentication.
Answer: D
QUESTION NO: 43
What must the Linux administrator ensure when creating a Linux user on the host?
A. The user account is provided super-user privileges.
B. The user account exists in the LDAP authentication server.
C. The user account password is at least 8 characters in length.
D. The user account is created on the active and standby server.
Answer: D
QUESTION NO: 44
A stored procedure returning one value is created, which two methods can be used to execute the
procedure? (Choose two.)
A. Using the EXECUTE command.
B. Using the SELECT statement within a FROM clause.
C. Using the SELECT statement within a HAVING clause.
D. Using the SELECT statement without a FROM clause.
E. Using the SELECT statement with tables in the FROM clause.
Answer: A,D
QUESTION NO: 45
Which User Defined Function (UDF) statement is true?
A. It accepts zero or more input values but produces one output value.
B. It accepts exactly one input value and produces exactly one output value.
C. it accepts exactly one input value and produces one or more output values.
D. It accepts zero or more input values and produces one or more output values.
Answer: A
QUESTION NO: 46
The primary copy of a dataslice is spread across how many disks?
A. 1.
B. 2.
C. 4.
D. 12.
Answer: A
QUESTION NO: 47
Which is NOT a function of the Field Programmable Gate Arrays (FPGA) residing on the Netezza
Database Accelerator card?
A. Projects
B. Restricts
C. Aggregates
D. Decompresses
Answer: C
QUESTION NO: 48
Which is NOT a way to check if the database is online?
A. nzstate
B. cat /proc/database
C. nzsystem showState
D. nzsql -c "select * from _v_system_info;"
Answer: B
QUESTION NO: 49
All database users are members of what predefined database group, which governs the basic access
permissions that are granted to the system?
A. GUEST
B. USERS
C. PUBLIC
D. GLOBAL
Answer: C
QUESTION NO: 50
Which two commands can be used to shut the database system down? (Choose two.)
A. nzstop
B. nzdown
C. nzhw stop
D. nzsystem stop
E. service halt nps
Answer: A,D
QUESTION NO: 51
What is nzconvert used for?
A. To convert compressed external tables into ASCII text.
B. To convert between character encodings before loading data.
C. To translate stored procedures into NZPLSQL compatible syntax.
D. To upgrade/downgrade the database to a different software version.
Answer: B
QUESTION NO: 52
Given a plan ID, how would you obtain the query execution plan? (Choose two.)
A. Turn ODBC tracing ON.
B. Issue "SELECT * FROM _V_SESSION;"
C. Go to the query history within the Windows NzAdmin GUI tool.
D. Run EXECUTION PLAN before the query has been submitted.
E. Issue "SHOW PLANFILE <nn>;" after the query has been submitted.
Answer: C,E
QUESTION NO: 53
Which option does NOT trigger a HardwareNeedsAttention event?
A. A power supply has failed.
B. A switch has been rebooted.
C. A SAS cable fails or is disconnected.
D. A chassis or fabric switch port is in a down state for longer than the defined threshold.
Answer: A
QUESTION NO: 54
What is required to troubleshoot a query?
A. nzevents and the pg.log.
B. nzsql and the ODBC config.
C. nzinventory and the dbos.log.
D. nzdumpschema and the execution plan.
Answer: D
QUESTION NO: 55
When is the ToplogyImbalance event triggered?
A. When a SAS switch has failed.
B. When the path the path between an S-Blade and its disks has failed.
C. When a disk enclosure's voltage sensor is reading outside the nominal range.
D. When an S-Blade has ownership of more dataslices than is optimal (usually following a disk
replacement).
Answer: D
QUESTION NO: 56
Which piece of information is NOT gathered as part of the statistical information when generate
statistics is run on a table?
A. The number of rows in the table.
B. The number of updated rows in the table.
C. The minimum and maximum value in each column.
D. The number of unique or distinct values in each column.
Answer: B
QUESTION NO: 57
Which two statistical data points are automatically updated when performing an INSERT on a
table? (Choose two.)
A. The number of rows in the table.
B. The column histogram statistics.
C. The number of NULLs in each column.
D. The number of unique values in each column.
E. The minimum and maximum column value information (excluding text columns).
Answer: A,E
QUESTION NO: 58
How does the PureData System for Analytics utilize the distribution key to store records on disk?
A. System assigns records to a FPGA based on their distribution key value.
B. System assigns records to the host based on their distribution key value.
C. System assigns records to a logical data slice based on their distribution key value.
D. System assigns records to a Netezza Database Accelerator based on their distribution key value.
Answer: C
QUESTION NO: 59
What should be considered when you are asked to select the distribution key columns for a very
large fact table?
A. As many columns as possible.
B. Columns that contain many nulls.
C. Columns that contain few unique values.
D. Columns used to join to other large tables.
Answer: D
QUESTION NO: 60
A table, TableA has one column, ColumnA. The following query can be used to determine the skew
of Table A.
A. Select distinct (ColumnA) from TableA;
B. Select distinct(datasliceid) from TableA;
C. Select datasliceid, count(*) from TableA group by 1 order by 2;
D. Select ColumnA, count(*) from TableA group by ColumnA order by 2;
Answer: C
QUESTION NO: 61
Which statement is true?
A. All user space is available for database expansion.
B. Each database is assigned it's own section of disk space.
C. When creating the database you can specify which disks to use.
D. When you create a database you can set the maximum space it is allowed to use.
Answer: A
QUESTION NO: 62
When a poor choice of distribution key is made, what is the impact to storage allocation?
A. Data is likely to negatively affect views.
B. Data is likely to negatively affect compression.
C. Data is likely to generate duplicate sequence values.
D. Data is likely to be unevenly spread across the system.
Answer: D
QUESTION NO: 63
Which statement is true about optimizer settings?
A. They can be set system wide only.
B. They can be set at the database level only.
C. They can be set at the session and at the group level.
D. They can be set system wide and at the session level.
Answer: D
QUESTION NO: 64
Which statement about the GROOM TABLE feature is true?
A. GROOM TABLE requires space to make a complete copy of the table.
B. GROOM TABLE only operates on tables that include an ORGANIZE ON clause.
C. GROOM TABLE prevents nzload from adding data while groom is in progress.
D. GROOM TABLE allows UPDATE, DELETE, and INSERT operations to occur while groom is
in progress.
Answer: D
QUESTION NO: 65
After performing a groom on a clustered base table, which statement is true?
A. The table is redistributed on the ORGANIZE ON clause.
B. The table is reorganized on the ORGANIZE ON clause.
C. The table is reorganized on the DISTRIBUTE ON clause.
D. The table is redistributed on the DISTRIBUTE ON clause.
Answer: B
QUESTION NO: 66
What are two considerations for usage of materialized views? (Choose two)
A.
B.
C.
D.
E.
Materialized views are not supported.
Materialized views are logical entities.
Materialized views may improve query performance.
Materialized views are the same as database views.
Materialized views are considered for usage by the Optimizer.
Answer: CE