hp0 a25 q&a demo troytec

Edition: 2.0

© 2013 - 2014 TroyTec, LTD All Rights Reserved

HP NonStop SQL/MXExam: HP0-A25

HP0-A25

1 http://www.troytec.com

QUESTION: 1

Who is allowed to add columns to a table? (Select two.)

A. the SUPER.SUPER

B. the owner of the table

C. the owner’s group super

D. the owner of the schema

E. users that have been granted access

Answer: A, D

QUESTION: 2

Using the following DDL command: CREATE TABLE new_table LIKE old_table

What can be specified for new_table? (Select two.)

A. Format 1 or Format 2

B. indexes from old_table

C. headings from old_table

D. organization and primary key

E. extent sizes and maximum extents

Answer: C, E

QUESTION: 3

A column definition includes DOB DATE HEADING ‘Birth Date’. What is the heading

output for SELECT MAX(DOB)?

A. MAX(dob)

B. Birth Date

C. EXPR(dob)

D. EXPR

Answer: D

QUESTION: 4

HP0-A25


Which statements are true about AUDITCOMPRESS? (Select two.)

A. The audit contains only altered columns.

B. It has no effect on transaction throughput.

C. It is part of the system’s TMF configuration.

D. It contributes to higher transaction throughput.

E. Compression is performed by the audit disk process.

Answer: A, D

QUESTION: 5

When a table has the primary key defined as DROPPABLE, which statements are true?

(Select two.)

A. A SYSKEY is generated for the table.

B. The STORE BY clause must be used.

C. A DROPPABLE key can only be ascending.

D. The primary key is used as the clustering key.

E. A seperate unique index is build for the primary key.

Answer: A, E

QUESTION: 6

Which command reports the average free space in data blocks?

A. FUP INFO =table, slack

B. FUP INFO =table, stat, analyze

C. FUP INFO table mucatalog.myschema.mytable stat

D. FUP INFO table mucatalog.myschema.mytable detail

Answer: C

QUESTION: 7

In SQLƒMX syntax, for which database element is an explicit assigned character set

supported?

HP0-A25


A. row

B. table

C. column

D. constraint

Answer: C

QUESTION: 8

How do you access the current data on the SQLƒMP view $data.mycat.myview from

mxci? (Select two.)

A. SELECT * FROM $data.mycat.myview;

B. CREATE VIEW cat.sch.myview AS SELECT ordernum, order_qty FROM

$data.mycat.myview; SELECT*FROM cat.sch.myview;

C. CREATE SQLMP ALIAS cat.sch.myview $data.mycat.myview; SELECT * FROM

cat.sch.myview

D. CREATE TABLE cat.sch.myview LIKE &data.mycat.myview; INSERT INTO

cat.sch.myview SELECT * FROM $data.mycat.myview; SELECT * FROM

cat.sch.myview;

Answer: A, C

QUESTION: 9

Which statements are true about the location of the system catalog tables of the system

\MYSYS? (Select two.)

A. The subvolume of the catalog tables is MYSYS.

B. The subvolume of the catalog tables is always ZSDO.

C. The volume of the catalog tables is always $SYSTEM.

D. The subvolume of the catalog tables is always ZSQLMX.

E. The volume of the catalog tables can be found in

$SYSTEM.ZSQLMX.MXANCHOR.

Answer: B, E

QUESTION: 10

What is the correct definition of a column?

HP0-A25


A. C1 VARCHAR (1OO) DEFAULT ‘HAMBURG’

B. C1 VARCHAR (1OO) NULL DEFAULT NULL

C. C1 VARCHAR (1OO) DEFAULT “SANTA CLARA”

D. C1 VARCHAR (1OO) NULL DEFAULT ‘HAMBURG’

Answer: A

QUESTION: 11

Your system has 3O audited data disks. You want to create a partitioned table with 6O

partitions. How can you accomplish this? (Select two.)

A. You define two partitions on each disk in different subvolumes with the same

filename.

B. You install SMF and configure 6O logical disks.

C. You install 3O additional disks.

D. You define two partitions on each disk in the same subvolume with different

filenames.

Answer: C, D

QUESTION: 12

A table mytable has been defined on the development system. As several changes have

been made the original create−script is no longer valid. Which combination of mxci

commands can be used to produce a valid create−script?

A. LOG and SHOWDDL

B. OUT and SHOWDDL

C. OUT and INVOKE

D. LOG and INFO TABLE, OBEYFORM

Answer: A

QUESTION: 13

What are the benefits of using rowsets in SQLƒMX? (Select two.)

A. to reduce the throughput

HP0-A25


B. to reduce the number of calls to servernet

C. to reduce the disc space for the database

D. to reduce simultaneous access to several rows

E. to reduce the number of calls to the database system

Answer: B, E

QUESTION: 14

The following query is a typical example of which potential problem? UPDATE

inventory SET retail_price =retail_price * 1.1;

A. the Halloween update

B. the query will take too long to execute

C. the retail_price column cannot be CAST to a float

D. the executor would typically start a parallel query even on small tables

Answer: A

QUESTION: 15

What takes place during a POPULATE INDEX? (Select two.)

A. DDL lock

B. user−defined transaction

C. automatic adjustment of MAXEXTENTS

D. the data movement in a single TMF transaction

E. automatic reload to organize the index structure more efficiently and to reduce index

levels

Answer: A, C

QUESTION: 16

How will a subquery be transformed by the normalizer?

A. into a groupby

B. into a join

C. into an index scan

D. into an MDAM range

HP0-A25


Answer: B

QUESTION: 17

Which command is used to recreate a lost stored procedure?

A. BRCOM Restore with SQLDATA ON

B. BRCOM Restore with OSS option

C. TMFCOM RECOVERY

D. OBEY SHOWDDL1_Stored_Procedures file from mxci

Answer: D

QUESTION: 18

Which commands are used to manage access privileges for SQLƒMX tables, views and

indexes? (Select two.)

A. DML

B. DDL

C. GRANT

D. REVOKE

E. SAFECOM ADD DISKFILE

Answer: C, D

QUESTION: 19

Which rules apply to physical user schema names? (Select two.)

A. The fourth character must be a letter.

B. The subvolume name always starts with ZSD.

C. The subvolume name length must be between 4 and 8.

D. Vowels and the digit zero are not allowed in user−defined names.

E. The subvolume name is always system−generated; the user can not define it.

Answer: A, B

HP0-A25


QUESTION: 20

Which execution methods are physical operators? (Select two.)

A. join

B. scan

C. exchange

D. index_scan

E. hybrid_hash_join

Answer: D, E

QUESTION: 21

Which command is used to monitor query plan caching?

A. ENV

B. HISTORY

C. DISPLAY_QC

D. DISPLAY STATISTICS

Answer: C

QUESTION: 22

Which statement is used to increase the maximum number of extents of index xind1to

76O?

A. mxtool

INDEX maxextents −a 76O

B. mxtool

ALTER INDEX, maxextent 76O

C. mxci

>>MODIFY INDEX xind1 MAXEXTENTS 76O;

D. mxci

>> ALTER INDEX xind1 ATTRIBUTE MAXEXTENTS 76O;

Answer: D

HP0-A25


QUESTION: 23

What is the underlying physical file of an SQLƒMX table?

A. OSS file

B. Format 2 relative Guardian file

C. Format 1 sequential Guardian file

D. Format 2 key−sequenced Guardian file

Answer: D

QUESTION: 24

An SQLƒMX table is approaching capacity in terms of the maximum number of extents.

Which utility can be used to make more space available within the table.

A. DCOM

B. mxci load

C. mxtool compress

D. FUP RELOAD

Answer: D

QUESTION: 25

Which statement is true about dropping an SQLƒMP alias?

A. The underlying SQLƒMP object is dropped.

B. Dropping the SQLƒMP alias invalidates the underlying physical table.

C. The mapping from an SQLƒMX ANSI name to a physical SQLƒMP file is removed.

D. Dropping the underlying SQLƒMP object causes the related SQLƒMP aliases to be

dropped.

Answer: C

QUESTION: 26

Which SQLƒMX objects will be deleted by RESTORE with the KEEP OFF option?

(Select two.)

HP0-A25


A. catalog

B. schema

C. table with indexes

D. table with constraints

E. table with triggers

F. table with partitions

Answer: C, F

QUESTION: 27

What information from NSMƒWeb can be used for MXCS client data source

configurations.

$MXOAS AVL 1,821 $ZTCO 1865O Mar 12 11:29 AS

$XOOB1 AVL 1,487 $ZTCPO 18651 Dec 11 19:58 CFG

A. $MXOAS

B. 1,487

C. 1865O

D. $ZTCO

Answer: C

QUESTION: 28

Which statement is true about dropping hash partitions?

A. You can drop any partition of the table.

B. The MODIFY TABLE command is used.

C. Online partition operations are supported.

D. Dropping the first partition drops the indexes first.

Answer: B

QUESTION: 29

Which statement are true about the DROP INDEX statement options? (Select two.)

A. The RESTRICT option, by default, will not allow an index to be dropped if it is being

used to validate a constraint.

HP0-A25


B. The RESTRICT option, if specified, will not allow an index to be dropped if it has

dependent programs.

C. The CASCADE option, if specified, will drop both the index and any constraints

using the index for validation.

D. The CASCADE option, by default, will invalidate the dependent programs.

E. The CASCADE option, if specified, will drop the underlying table before dropping

the index.

Answer: A, C

QUESTION: 30

Which requirements must you meet to drop a schema? (Select two.)

A. Your userid needs DROP authority granted.

B. It must be empty unless you use the CASCADE option.

C. You must be the owner of the catalog containing the schema.

D. You must have remote passwords for any nodes to which the catalog of the schema

has been registered.

Answer: B, D

QUESTION: 31

The SAVE_DROPPED_TABLE_DDL attribute of the system defaults is useful for

which purpose?

A. saving a table label

B. recovering a dropped table

C. the DROP SCHEMA CASCADE option

D. recovering dropped tables, indexes and views

Answer: B

QUESTION: 32

What is ZCLIDLL?

A. a DLL containing the executor in DP2

B. a DLL containing privileged SQLƒMX code

HP0-A25


C. a DLL dynamically bound to an MXCI process

D. a DLL dynamically bound to an MXESP process

Answer: C

QUESTION: 33

What does the mxci SHOWLABEL command display? (Select two.)

A. the partition hash key

B. the owner of the table

C. the number of allocated extents

D. the resource fork’s redefinition timestamp

E. how many DDL LOCKS are currently active on the table

Answer: B, C

QUESTION: 34

A long running application transaction has failed and has to be backed out. Which

TMFCOM command allows you to find out if the backout has completed?

A. STATUS OPERATIONS

B. INFO TRANSACTION

C. STATUS BACKOUT

D. SSTATUS TRANSACTION

Answer: D

QUESTION: 35

An INSERT statement is backed out due to a disk full error. Which utility helps identify

the disk in error?

A. FUP

B. DSAP

C. SHOWDDL

D. MAXEXTENTS

HP0-A25


Answer: B

QUESTION: 36

Which command do you use to clear the following error:

*** ERROR[21O5] This query could not be compiled because of incompatible Control

Query Shape (CQS) specifications. Inspect the CQS in effect.

A. showcontrol * cancel;

B. cancel query shapes;

C. control query shape cut;

D. control query shape any;

Answer: C

QUESTION: 37

What is the function of the MXGNAMES utility?

A. It allows Guardian names to be used from MXCI.

B. It obtains a list of SQLƒMX table names from a specific schema.

C. It converts Guardian file names into their corresponding ANSI names.

D. It provides BackupƒRestore2 and TMF with appropriately formatted Guardian

names.

Answer: D

QUESTION: 38

Which feature do you use to identify a relationship with another table?

A. clustering key

B. foreign key

C. alternate key

D. SYSKEY

Answer: B

QUESTION: 39

HP0-A25


Which NonStop server feature supports enhanced application availability?

A. multithreading

B. mirrored disks

C. call−level interface (CLI)

D. shared run−time library (SRL)

Answer: B

QUESTION: 40

Click the Task button.

Using drag and drop, match each clustering factor with its corresponding table attribute.

A.

HP0-A25


Answer: A

QUESTION: 41

What can be used to execute dynamic queries? (Select two.)

A. NSMƒweb

B. Visual Query Planner (VQP)

C. XML (Extensible Markup Language)

D. MXCS (SQLƒMX Connectivity Service)

E. mxci (SQLƒMX Conversational Interface)

Answer: D, E

QUESTION: 42

Which statement is true about the ZMXSTMPL file?

A. It contains the error messages for SQLƒMX.

HP0-A25


B. It is the SQLƒMX error message generator program file.

C. It contains the complied event messages for NonStop SQLƒMX

D. It contains SQLƒMX error messages for the TSƒMP environment.

Answer: C

QUESTION: 43

Which object integrates the SQLƒMX compiler and the catalog manager?

A. MXESP

B. MXUDR

C. MXUTP

D. MXCMP

Answer: D

QUESTION: 44

Which queries will display all rows of data from the emp table? (Select two.)

A. selectename, dname

fromemp natural left outer join dept;

B. selectename, dname

fromemp natural right outer join dept;

C. select ename, dname

fromemp left outer join emp using (deptno);

D. selectename, dname

from emp left join dept on emp.deptno = dept.deptno union

selectename, dname

form emp right join dept on emp.deptno = dept.deptno;

Answer: A, D

QUESTION: 45

What is the name of the SQLƒMX command line utility in the OSS environment?

A. mxtool

B. mxci

C. mxutil

D. mxsqlc

HP0-A25


Answer: A

QUESTION: 46

What is not a reason for normalizing data?

A. to reduce data maintenance

B. to manage space more efficiently

C. to reduce the number of table joins

D. to decrease query response times in DSS environments

Answer: D

QUESTION: 47

A large table EMPLOYEES (EmployeeNo (primaryKey), FirstName, Salary, Dapt,

Address) has a composite index placed on columns (FirstName, LastName). Which

statement will most likely use index−only access?

A. SELECT FirstName from EMPLOYEES where LastName = ‘Hogan’

B. SELECT FirstName, LastName from EMPLOYEES where salary > 8OOOO;

C. SELECT LastName, EmployeeNo from EMPLOYEES where EmployeeNo > 1OO;

D. SELECT LastName, FirstName from EMPLOYEES where EmployeeNo = 1OO;

Answer: A

QUESTION: 48

Which object acts as an external bridge between NonStop SQLƒMX and NonStop

SQLƒMP?

A. MXAUDSRV

B. MXRDTSRV

C. MXGNAMES

D. MXCI

Answer: B

HP0-A25


QUESTION: 49

A Database Administrator (DBA) may choose to denormalize tables that are frequently

joined. Which additional condition should exist?

A. Data storage waste is a major concern

B. Join columns are supported with unique indexes

C. Tables commonly joined in queries have a 1:1 relationship

D. Many simple, short−running queries are issued against fact tables

Answer: C

QUESTION: 50

Which interface description file is used by SQLƒMX for ECOBOL?

A. sqlcli.o

B. sqlcli.h

C. esqlcli.o

D. esqlcli.h

Answer: C

QUESTION: 51

Click the Exhibit button.

HP0-A25


1OO,OOO new rows have been added to a fact table called ORDERS that currently

contains one million rows. The ORDERS table contains a primary key constraint on the

order_id column and a secondary index on column store_id.

It has been determined that the ORDERS table and its corresponding indexes should be

reorganized. Which action should be taken?

A. Issue a mxci reload command on the orders table.

B. Issue fup reload commands on all physical files that implement the orders table.

C. Issue a mxci reorganize command on the orders table and also on the table two

corresponding indexes. Issue a mxci reorganize command on the orders table and also

on the table? two corresponding indexes.

D. Issue fup reload commands on all physical files that implement the orders table and

corresponding indexes.

Answer: D

QUESTION: 52

Which condition must be met so that the optimizer can choose to completely bypass a

table and cover the query through index−only access?

A. The exclusive lock mode is selected.

B. OR optimization is performed.

C. 5O% percent of the rows are updated.

D. All columns represented in the query are available in an index.

HP0-A25


Answer: D

QUESTION: 53

What does the MXANCHOR file contain on the local node?

A. SYSTEM_DEFAULS

B. pointer to the system defaults

C. SYSTEM_DEFAULT_SAHEMA

D. pointer to the location of the system catalog

Answer: D

QUESTION: 54

Which is not a valid component of a CREATE TRIGGER statement?

A. a trigger type specification

B. AFTER or BEFORE specifications

C. a statement that causes the trigger to fire

D. a limit specifying the number of times the trigger should fire

Answer: D

QUESTION: 55

In which type of tables or files can referential integrity be enforced?

A. SQLƒMX tables only

B. SQLƒMX constraint tables

C. SQLƒMX and SQLƒMP tables

D. All SQLƒMX tables and Enscribe files on the system

Answer: A

QUESTION: 56

Which parameter of the FUP RELOAD command controls the resources used by the

HP0-A25


load?

A. SLACK

B. SHARE

C. RATE

D. INTERVA;

Answer: C

QUESTION: 57

Which CONTROL statement forces an access plan?

A. CONTROL QUERY DEFAULT

B. CONTROL TABLE

C. CONTROL QUERY SHAPE

D. CONTROL EXECUTION

Answer: C

QUESTION: 58

Which object can MEASCOM output not be directed to?

A. spooler

B. terminal

C. SQL table

D. enscribe file

Answer: C

QUESTION: 59

Which CONTROL QUERY DEFAULT INSERT_VSBB parameter is used for simple

inserts?

A. OFF

B. USER

C. SYSTEM

HP0-A25


D. LOADNODUP

Answer: A

QUESTION: 60

During an online reorganization of a table, when are other processes prevented from

reading and writing to the table being reorganized?

A. when file labels and metadata are updated

B. when the old table location is being deleted

C. when the table in the new location is being created

D. while data are being copied to the new table location

Answer: A

QUESTION: 61

Which CONTROL statement can be used as an embedded SQL complier directive?

A. CONTROL TABLE

B. CONTROL EXECUTION

C. CONTROL QUERY SHAPE

D. CONTROL QUERY DEFAULT

Answer: A

QUESTION: 62

For which objects does mxtool INFO display information? (Select three.)

A. file locks

B. open file

C. ANSI names

D. NSK file names

E. ANSI namespace

F. active transactions

Answer: C, D, E

HP0-A25


QUESTION: 63

You create table and omit the STORE BY and PRIMARY KEY clauses. Which

statement is true about the data storage in this situation?

A. The data are stored in no particular order.

B. The data are stored in the order of the first field.

C. The data are stored in the order they are inserted.

D. Data are stored by a SYSKEY automatically generated.

Answer: D

QUESTION: 64

Which tool is used to adjust the SQLMX Buffer?

A. mxci

B. SCF

C. mxtool

D. DCOM

Answer: B

QUESTION: 65

For what does the DISPLAY STATISTICS command provide statistics?

A. your current MXCI session

B. tables and indices in current schema

C. the last executed DML or PREPARE statement

D. the number of statements that have been executed

Answer: C

QUESTION: 66

Which operation does not create scratch files?

HP0-A25


A. SORT

B. HASH_JOIN

C. HASH_GROUPBY

D. ORDERED_HASH_JOIN

Answer: D

QUESTION: 67

Which query will display only those employees who have been assigned phone code?

A. Select *

From employees

Where phone_code in (null)

B. Select *

From employees

Where phone_code <> null;

C. Select *

From employees

Where phone_code is not null;

D. Select *

From employees

Where phone_code != null;

Answer: C

QUESTION: 68

Which SQLƒMX complier component expands views that are listed in the query, looks

up metadata for table information, and then produces a semantically correct query tree?

A. Binder

B. Parser

C. Optimizer

D. Codegen

Answer: A

QUESTION: 69

Which statement are true about aggregate functions? (Select two.)

HP0-A25


A. You can use aggregate functions in any clause of a SELECT statement.

B. You can use aggregate functions on a table, only by grouping the whole table as one

single group.

C. You can pass column names, expressions, constants, or functions as parameters to an

aggregate function.

D. You can use aggregate functions only in the column list of the SELECT clause and in

the WHERE clause of a SELECT statement.

E. You can mix single row columns with aggregate functions in the column list of a

SELECT statement by grouping on the single row columns.

Answer: C, E

QUESTION: 70

What is the purpose of the DESCRIBE INPUT statement?

A. to show static parameters declared in a FETCH statement

B. to show the tables used by the program in presentation order

C. to describe the input parameters of a dynamic SQL statement

D. to describe the last SELECT statement in the SQLCA structure

Answer: C

QUESTION: 71

What is the effect of the AFTER LAST ROW clause in a stream statement?

A. The clause prevents deleting or updating from a stream.

B. The statement returns an error if there are no rows in the table.

C. The statement returns all existing rows then ends execution after the last row.

D. The statement only returns a row if it was inserted or updated after the statement

began executing.

Answer: D

QUESTION: 72

When will the language complier return both a warning and an error message?

HP0-A25


A. when you declare SQLCODE (anywhere in your program) but you do not declare

SQLSTAE within a Declare Section

B. when you do not declare SQLCODE anywhere in your program and you do not

declare SQLSTAE within a Declare Section

C. when you declare SQLODE outside of a Declare Section and SQLSTATE within a

Declare Section

D. If you declare both SQLCODE and SQLSTATE with a Declare Section

Answer: B

QUESTION: 73

The current value of the CHAR(13) column jobdesc is SQLƒMX Expert. What will be

the new value of jobdesc after the following UPDATE STATEMENT is executed?

UPDATE persnl.job SET jobesc = RPAD (jobdesc, 1, ‘X’);

A. X

B. xQLƒMX Expert

C. Sxxxxxxxxxxxx

D. SQLƒMX Experx

Answer: D

QUESTION: 74

What will happen when the following query is executed? SELECT * FROM

STREAM(quotes);

A. A syntax error will be returned.

B. Other applications trying to insert rows into the QUOTES table will block until the

query ends.

C. There will be as many rows returned as there are in the QUOTES table and then

query will end immediately.

D. There will be as many rows returned as there are in the QUOTES table and then

query will block and wait for more rows to be inserted.

Answer: D

QUESTION: 75

HP0-A25


Which statement is true about the following mxic command? mxci>> control query

default optimization_level ‘O’;

A. The compiler performs full optimization of the query using exhaustive search

algorithms.

B. This optimization level is best when accessing large tables resulting in complex query

plans.

C. The compiler uses heuristic information about data objects to perform one−pass

optimization.

D. This optimization level is often specified in decision support system (DSS)

environments.

Answer: C

QUESTION: 76

Which OSS command is used to compile the C program “sqlprog.sql”?

A. mxcomp sqlprog.sql −o sqlprog.exe

B. nmcƒin sqlprog.sqlƒsqlprog.exe;mxcomp

C. c89 −Wsqlmx −Wmxcmp sqlprog.sql −o sqlprog.exe

D. ƒGƒSYSTEMƒSYSTEMƒmxcomp −in sqlprog.sql

Answer: C

QUESTION: 77

When must the declaration of a cursor include the FOR UPDATE OF clause?

A. when the cursor is restarted using OPEN CURSOR with the WHERE CURRENT OF

clause

B. when a DELETE is performed against a position of the cursor using the WHERE

CURRENT OF clause

C. when an UPDATE is performed against a position of the cursor using the WHERE

CURRENT OF clause

D. when any operation is performed against a position of the cursor using the WHERE

CURRENT OF clause

Answer: C

HP0-A25


QUESTION: 78

Reload Analyzer (TRA) can be used to determine if files or tables would benefit from

being reloaded. For which file or table organization can TRA be used?

A. relative

B. key−sequenced

C. entry−sequenced

D. any structured type.

Answer: B

HP0-A25


hp0 a25 q&a demo troytec

Documents