ibm actualtests 000-540 exam questions & answers...the netezza high availability (ha) solution...

IBM Actualtests 000-540 Exam Questions & Answers

Number: 000-540Passing Score: 800Time Limit: 120 minFile Version: 24.7

http://www.gratisexam.com/

IBM 000-540 Exam Questions & Answers

Exam Name: IBM PureData System for Analytics v7.0

For Full Set of Questions please visit: http://www. actualtests.com/exam-000-540.htm

Actualtests

QUESTION 1A SELECT statement spends all its time returning 1 billion rows. What can be done to make this faster?

A. Use a CURSORB. Use an EXTERNAL TABLEC. Use the COPY commandD. Increase PreFetch Count

Correct Answer: BSection: (none)Explanation

Explanation/Reference:Use an EXTERNAL TABLECREATE EXTERNAL TABLE <table_name >[SAMEAS table_name ]|([column_name [,...]]) USING (DATAOBJECT (<file_path >)[MODE truncate |append ][DELIMITER<delimiter >]);

Example:CREATE EXTERNAL TABLE customer_ext SAMEAS customerUSING (DATAOBJECT ('/tmp/customer.unl ') DELIMITER '|');--load data to Ext tableINSERT INTO customer_ext SELECT * FROM customer ;

QUESTION 2What 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.

Correct Answer: DSection: (none)Explanation

Explanation/Reference:Note: Answer C is incorrect : to back up host data use - nzhostbackup command

QUESTION 3When 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.

Correct Answer: BSection: (none)

Explanation

Explanation/Reference:

QUESTION 4Which is NOT a function that can be performed by nzbackup ?

A. Backup dataB. Backup schemasC. Backup users/groupsD. Backup the /nz/data directory



QUESTION 5Which 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.

Correct Answer: CSection: (none)Explanation

Explanation/Reference:control file is optionalThe nzload command is an ODBC client application that loads data either: • Remotely on all the supported client platforms.• Locally. on the Netezza host.The nzload program performs all inserts into the target table within a single transaction . A commit is issued atthe end of the nzload program. If a load is unable to complete (due to fatal errors) and the transaction can notbe committed all records are logically deleted.

QUESTION 6You have access to two databases, DEV and PROD. Currently you are connected to the DEV database. Whichoperation is permitted?

A. Delete from PROD..TableAB. Select * from PROD..TableA

C. Truncate table PROD..TableAD. Insert into PROD..TableA select * from DEV..TableB


Explanation/Reference:NPS supports cross database access to referenced objects, tables and views on the same (within a single)Netezza system. There is no impact on performance whether the objects are in the same or differentdatabases. Server to server cross database access is not supported as cross database join performance is notoptimal.Cross database access support is limited to SELECT (queries) only and does not support INSERT, UPDATEand DELETE operations of referenced objects.However cross database access does allow SELECT ... FROM clauses to include INSERT, DELETE,UPDATE, CTAS statements, joins, set operations, aggregations, sub-selects, and view definitions on thecurrent database. The current database is the database that nzsql is connected to.

QUESTION 7Which statement is true?

A. MATERIALIZED VIEWS can be run against EXTERNAL tablesB. 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.


Explanation/Reference:An EXTERNAL TABLE is a data file on disk that supports a schema definition, unload data to a external diskfile and can be further processed using nzsql SQL. External tables provide e functionality to unload databasetable data to an external disk file or load data from an external disk file into a database table.

BYou cannot specify an external, temporary, system, or a clustered base table (CBT) as abase table for the view.

QUESTION 8Given 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.

Correct Answer: CESection: (none)Explanation

Explanation/Reference:_V_SESSION provides the following information:ID, PID, UserName, Database, ConnectTime, ConnStatus, and LastCommandand it DOES NOT provide query execution plan

QUESTION 9When 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).



QUESTION 10The query history data collected DOES NOT provide insight to which of the type of performance and behaviorquestions?

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.



QUESTION 11When 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.



QUESTION 12Which High-Availability (HA) mechanism is used by the PureData System for Analytics Appliance?

A. AIX-HAB. Linux-HAC. SuSE-HAD. Solaris-HA


Explanation/Reference:The Netezza high availability (HA) solution uses Linux-HA and Distributed Replicated Block Device (DRBD) asthe foundation for cluster management and data mirroring. The host server (often referred to as host within the documentation) is a Linux server that runs the Netezzasoftware and utilities

QUESTION 13Which 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.

Correct Answer: ASection: (none)Explanation


QUESTION 14Given the following query: Select count(*) from TableA; How can you view the query plan without executing thequery?

A. Use explain.B. Use statistics.C. Use pg.log file.D. Set show_plan = true;


Explanation/Reference:Use the EXPLAIN command to show a statement execution plan.

QUESTION 15TableA is currently being accessed in a number of long running queries. Which of the following operations isNOT blocked, thus allowing the operation to immediately proceed?

A. DROP TABLE TableAB. ALTER TABLE TableAC. TRUNCATE TABLE TableAD. GRANT SELECT ON TableA TO UserA



If the table is in use by an active query, the ALTER command will wait until that query completes.

QUESTION 16If 'admin' user is running a query, what percentage of resources are available when Guaranteed ResourceAllocation (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


Explanation/Reference:The Netezza admin user account has a unique and powerful impact on the system and the GRA allocations. Bydefault, the admin user is allocated 100 resource sharing points, which means that the admin user typicallyreceives half of the available system resources when other RSGs are active, and 100% of the systemresources when no other RSGs are busy.

QUESTION 17When 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.



QUESTION 18What 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.



QUESTION 19Why 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.



QUESTION 20The PureData System for Analytics implements which level of transaction isolation?

A. SerializableB. Committed readC. Repeatable readD. Uncommitted read



QUESTION 21Which 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.



QUESTION 22Where are the database log files located?

A. /var/logB. /usr/localC. /nz/kit/logD. The SYSTEM database


Explanation/Reference:The postgres log file is the main database log file. It contains information about database activities.Log file/nz/kit/log/postgres/pg.log — Current log /nz/kit/log/postgres/pg.log.n — Archived log

QUESTION 23OLE-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.



QUESTION 24In 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.



QUESTION 25Which command is used to display operational statistics about system capacity, faults, and performance?

A. nzstatsB. nzstateC. nzshowD. nzsystem


Explanation/Reference:CORRECT- AThe nzstats command displays operational statistics about system capacity, faults, and performance.Operational statistics provide you with the following information:� A high-level overview of how your system is running in a context of recent system activity� Details so that you can diagnose problems, understand performance characteristics, and interface to system

management software

Note: The terms group and table are based on Simple Network Management Protocol (SNMP) concepts andare not associated with Netezza database groups or tables.INCORRECT

Use the nzstate command to display the current system state or to wait for a particular system state to occur.

Use the nzsystem command to change the system state, and show and set configuration information.

QUESTION 26Which command allows you to show information about the database hardware as well as take actions such asactivate or deactivate components, locate components, or delete them from the system?

A. sarB. nzhwC. nzsystemD. nzinventory


Explanation/Reference:CORRECTUse the nzhw command to manage the hardware of the Netezza system. The command allows you to showinformation about the system hardware as well as take actions such as activate odeactivate components, locate components, or delete them from the system.nzhw [-h|-rev] [-hc] subcmd [subcmd options]

INCORRECTUse the nzsystem command to change the system state, and show and set configuration information.

nzinventoryThis command is obsolete in Release 5.0. User nzhw instead

QUESTION 27What is the only predefined database user, which is able to access all objects and perform all tasks against thedatabase?

A. rootB. publicC. adminD. system



QUESTION 28What operating system runs on a PureData System for Analytics host?

A. AIX

B. SUSE LinuxC. Nucleus OSD. Redhat Linux



QUESTION 29What 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)



QUESTION 30Why 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.



QUESTION 31What 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. f there are any active updates running against the table TableA.



QUESTION 32What two characteristics can be changed when using the CREATE TABLE AS (CTAS) statement to copyTableA 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.



QUESTION 33What is the host Linux file that must be modified to adjust optimizer settings permanently?

A. /nz/data/postgresql.confB. /nz/data/config/system.cfgC. /nz/kit/sys/initTopology.cfg

D. /nz/kit/sys/initTopology.cfg



QUESTION 34Which 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.


Explanation/Reference:Starting in Release 6.0, you use the GROOM TABLE command to maintain the user tables by reclaiming diskspace for deleted or outdated rows, as well as to reorganize the tables by their organizing keys. The GROOM TABLE command processes and reorganizes the table records in each data slice in a series of“steps.” Users can perform tasks such as SELECT, UPDATE, DELETE, and INSERT operations while theonline data grooming is taking place. The SELECT operations run in parallel with the groom operations; anyINSERT, UPDATE, and DELETE operations run serially between the groom steps.

QUESTION 35Why 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.


Explanation/Reference:For the system to create the best execution plan for a query, it must make some decisions based on what itknows about the database tables it is accessing. Without up-to-date statistics the system uses internal, defaultvalues that are independent of the actual table and which result in suboptimal queries with long run times.Per table Number of recordsPer column • Minimum value• Maximum value• Null counts (per column)• Number of distinct values (also called dispersion)

The admin user can assign other users this privilege. For example, to give user1 privilege to run GENERATESTATISTICS on one or all tables in the DEV database, the admin user must grant user1 LIST privilege ontables in the system database, and GENSTATS from the dev database, as in these sample SQL commands:SYSTEM(ADMIN)=> GRANT LIST ON TABLE TO user1;DEV(ADMIN)=> GRANT GENSTATS ON TABLE TO user1;

QUESTION 36Using a date column as a distribution key may distribute rows evenly across dataslices, why is this a poorchoice 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.



QUESTION 37You 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.



QUESTION 38Which 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.



QUESTION 39Which 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.



QUESTION 40When creating a table, you can specify a column constraint for each individual column of either "NULL" or "NOTNULL". 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.



QUESTION 41What 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.



QUESTION 42When are zonemaps initially created for a table?

A. nzbackupB. nzreclaimC. CREATE TABLED. ALTER TABLE ADD ZONEMAPS


Explanation/Reference:Note: nzreclaim command has been replaced by GROOM command

QUESTION 43Which of the following is a valid datatype definition?

A. TEXTB. CLOBC. CHAR(64000)D. NUMERIC(1,38)


Explanation/Reference:CHAR(n)n <=64000

INCORRECT:

numeric(p, s) p>s Numeric with precision p and scale s. Precision can range from 1 to 38, scale from 0 to theprecision, e.g. numeric(18, 5)

Netezza supports only 2 binary data types: VARBINARY(n), ST_GEOMETRY(n)

QUESTION 44Columns 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.



QUESTION 45When doing a CREATE SEQUENCE, the largest "maximum value" is based on what?

A. BIGINTB. FLOAT8C. NUMERIC(38,0)D. VARCHAR(256)


Explanation/Reference:A sequence value is an integer that you can use wherever you would use numeric values. Netezza supports user sequences for the four integer types: byteint, smallint, integer, and bigint.

CREATE SEQUENCE <sequence name> [as <data type> <options>]Where the options are the following: START WITH <start value> INCREMENT BY <increment> no minvalue | minvalue <minimum value> no maxvalue | maxvalue <maximum value> cycle | no cycle


QUESTION 46Which procedural logic is NOT supported by NZPLSQL?

A. Scrollable CursorsB. Looping (while, for)C. Conditionals (if/else)D. Returning a scalar result or a result set



QUESTION 47Which attribute CANNOT be defined for both users and groups?

A. Row set limit.B. Default priority.C. Query time out.D. Password expiration.



QUESTION 48What is the maximum number of databases that can be created?

A. 1B. 12C. 31D. >96



QUESTION 49When 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.


Explanation/Reference:A sequence value is an integer that you can use wherever you would use numeric values. Netezza supportsuser sequences for the four integer types: byteint, smallint, integer, and bigint.Note: Sequences always provide a unique sequence number; however, you are not guaranteed that sequencenumbers will be predictable, monotonically increasing values. Gaps and unexpected sequence numbers occuras a result of the Netezza topology and how it processes sequences for query performance

QUESTION 50Which 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;


Explanation/Reference:The system creates zone maps for all columns in SPM (sorted , projected, materialized) views that have datatypes integer, date, and timestamp. The system also creates zone maps for all ORDER BY columns in the SPMview, except for columns of numeric types that are larger than 8 bytes (19 decimal digits or more).

QUESTION 51What is the maximum number of columns you can choose as organizing keys for clustered base table?

A. 1B. 2C. 4D. 8


Explanation/Reference:A clustered base table (CBT) is a user table that contains data which is organized using one to four organizingkeys. An organizing key is a column of the table that you specify for clustering the table records; Netezza usesthe organizing keys to group records within the table and save them in the same or nearby extents. Netezzaalso creates zone maps for the organizing columns to accelerate the performance of queries on that table thatrestrict using the organizing keys.

QUESTION 52When doing a CREATE TABLE, constraints can be specified at the column level. Which two constraints areNOT automatically enforced via the database? (Choose two.)

A. NULLB. UNIQUEC. NOT NULLD. PRIMARY KEYE. DEFAULT VALUE

Correct Answer: BDSection: (none)Explanation

Explanation/Reference:The system permits and maintains primary key, default, foreign key, unique, and references. The Netezza doesnot support constraint checks and referential integrity. The user must ensure constraint checks and referential integrity.

QUESTION 53Which 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.



QUESTION 54What is the function of the Distributed Replicated Block Device (DRBD)?

A. High AvailabilityB. Load BalancingC. High ConcurrencyD. Work Load Management


Explanation/Reference:Distributed Replicated Block Device (DRBD) is a block device driver that mirrors the content of block devices(hard disks, partitions, logical volumes, and so on) between the hosts. Netezza uses the DRBD replication onlyon the /nz and /export/home partitions.As new data is written to the /nz partition and the /export/home partition on the primary host, the DRBDsoftware automatically makes the same changes to the /nz and /export/home partition of the standby host.

QUESTION 55What is the state of the two hosts on a properly functional PureData System for Analytics Appliance?

A. Active - ActiveB. Active - Stand byC. Stand by - BackupD. Stand by - Passive



QUESTION 56nzload is invoked with the "allowReplay" option enabled. While the PureData System for Analytics Appliancechanges state from online to pausing then to online, which statement is true about the nzload process on theAppliance?

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.

Correct Answer: B

Section: (none)Explanation


QUESTION 57What is the naming convention for system views?

A. _v_...B. nz_v_...C. _$v_...D. sys_v_...



QUESTION 58What is the maximum number of columns that can participate in a distribution key?

A. OneB. FourC. UnlimitedD. Depends on the number of columns of integer datatype.



QUESTION 59What 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.

Correct Answer: DESection: (none)Explanation


QUESTION 60Which 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.



QUESTION 61The PureData System for Analytics implements which level of transaction isolation?

A. SerializableB. Committed readC. Repeatable readD. Uncommitted read



QUESTION 62The simplest query execution plan file must have the following minimum nodes?

A. ScanNode, RestrictNode, LimitNode, ReturnNodeB. ScanNode, RestrictNode, HashNode, ReturnNodeC. ScanNode, RestrictNode, ProjectNode, ReturnNodeD. ScanNode, RestrictNode, AggregateNode, ReturnNode



QUESTION 63What 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.



QUESTION 64What 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.

Correct Answer: CDSection: (none)Explanation

Explanation/Reference:When an event occurs, you can have the system send an e-mail message or execute an external command

QUESTION 65Where are the database log files located?

A. . /var/log

B. . /usr/local

C. /nz/kit/logD. The SYSTEM database



QUESTION 66If user A has a minimum resource allocation of 20% and a maximum of 80%, user A can receive whatpercentage 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.



QUESTION 67Which 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.



QUESTION 68What is the fastest join utilized by PureData System for Analytics?

A. Hash join.B. Merge join.C. Expression join.D. Cross product join.



QUESTION 69In a UNIX 64bit environment, a 64bit application requires which software to execute SQL statements viaODBC? (Choose two.)

A. nzsqlB. 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.

Correct Answer: BDSection: (none)Explanation


QUESTION 70Which 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.



The nzload command is an ODBC client application that loads data remotely or locally. You can use the nzloadcommand on the Netezza host and on all the supported client platforms.

QUESTION 71When 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.



QUESTION 72Which 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.



QUESTION 73Which 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.


Explanation/Reference:Use the nzbackup command to backup a database, including all schema objects and all table data within thedatabase.While a backup is running on a database, users cannot drop or truncate a table in that database until thebackup completes. The DROP TABLE and TRUNCATE TABLE commands will wait (and appear to hang)until the backup finishes. Operations such as an insert or load, or creating or dropping other object types suchas view, will run without wait- ing while the backup is in progress. If a GROOM TABLE VERSIONS commandruns on a table that is being backed up, the backup process will exit with an error. You have to restart thebackup process.

QUESTION 74

You have access to two databases, DEV and PROD. Currently you are connected to the DEV database. Whichoperation is permitted?

A. Delete from PROD..TableAB. Select * from PROD..TableAC. Truncate table PROD..TableAD. Insert into PROD..TableA select * from DEV..TableB


Explanation/Reference:Only SELECT allowed for cross database queris

QUESTION 75What 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.



QUESTION 76When enabled, query history always collects information about login failures, session creation, session termination, and query history process startup. Which area does query history NOTcollect data?

A. PlansB. DisksC. TablesD. Queries



QUESTION 77With 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.



QUESTION 78A 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.

Correct Answer: ADSection: (none)Explanation


QUESTION 79Which 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.



QUESTION 80Which is NOT a function of the Field Programmable Gate Arrays (FPGA) residing on the Netezza DatabaseAccelerator card?

A. ProjectsB. RestrictsC. AggregateD. Decompress



QUESTION 81

Which is NOT a way to check if the database is online?

A. nzstateB. cat /proc/databaseC. nzsystem showStateD. nzsql -c "select * from _v_system_info;"



QUESTION 82Which two commands can be used to shut the database system down? (Choose two.)

A. nzstopB. nzdownC. nzhw stopD. nzsystem stopE. service halt nps

Correct Answer: ADSection: (none)Explanation

Explanation/Reference:CORRECT:Use either the nzsystem stop or the nzstop command to stop system operation. The nzstop command is ascript that initiates a system stop by halting all processing.Note: You must run nzstop while logged in as a valid Linux user such as nz on the host. You cannot run thecommand remotely.

INCORRECT:Use the nzhw command to manage the hardware of the Netezza system. The command allows you to showinformation about the system hardware as well as take actions such as activate or deactivate components,locate components, or delete them from the system.

QUESTION 83What 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.


Explanation/Reference:Use the nzconvert command to convert between any two encodings, between these encodings and UTF-8,and from UTF-32, -16, or -8 to NFC, for loading with the nzload command or external tables.

QUESTION 84Given 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.



QUESTION 85What 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.



QUESTION 86When 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).



QUESTION 87Which piece of information is NOT gathered as part of the statistical information when generate statistics is runon 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.



QUESTION 88How 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.



QUESTION 89What should be considered when you are asked to select the distribution key columns for a very large facttable?

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.



QUESTION 90Which 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.



QUESTION 91When 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.



QUESTION 92Which 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.


Explanation/Reference:Users can perform tasks such as SELECT, UPDATE, DELETE, and INSERT operations while the online datagrooming is taking place. The SELECT operations run in parallel with the groom operations; any INSERT,UPDATE, and DELETE operations run serially between the groom steps.

QUESTION 93What are two considerations for usage of materialized views? (Choose two)

A. Materialized views are not supported.B. Materialized views are logical entities.C. Materialized views may improve query performance.D. Materialized views are the same as database views.E. Materialized views are considered for usage by the Optimizer.




ibm actualtests 000-540 exam questions & answers...the netezza high availability (ha) solution...

Documents