DB2考试自测题8

fengming

（1/55）Which two of the following types of storage management method is supp
orted by DB2 OLAP Server ?  
(Select all that apply)
A.        Object
B.        Network
C.        Relational
D.        Hierachical
E.        Multi-dimensional

（2/55）Which of the following DB2 data types is used to store 50 MB of binar
y data as a single value?  
(Select the correct response)
A.        BLOB
B.        CLOB
C.        DBCLOB
D.        FOR BIT DATA
E.        VARCHAR FOR BIT DATA

（3/55）Which of the following must be set up to allow the Control Center to
view database objects?  
(Select the correct response)
A.        ODBC
B.        JAVA
C.        DB2 Administration Server
D.        Client Configuration Assistant

（4/55）Which of the following DB2 CLP options specify the file that contains
the statements to be executed?  
(Select the correct response)
A.        –f
B.        –b
C.        -0
D.        –w

（5/55）Which of the following DELETE RULES on CREATE TABLE will delete a dep
endent table row if the parent table row is deleted?  
(Select the correct response)
A.        ON DELETE REMOVE
B.        ON DELETE CASCADE
C.        ON DELETE RESTRICT
D.        ON DELETE SET NULL
E.        ON DELETE PROPAGATE

（6/55）With DBADM authority on the database and given the statements:
CREATE TABLE t1 (c1 CHAR(1))
INSERT INTO t1 VALUES ('b')
CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1='a' WITH CHECK OPTION
INSERT INTO v1 VALUES ('a')
INSERT INTO v1 VALUES ('b')
How many rows would be returned from the statement, SELECT c1 FROM t1?  
(Select the correct response)
A.        0
B.        1
C.        2
D.        3

（7/55）For which of the following can locks be obtained?  
(Select the correct response)
A.        A trigger
B.        A table view
C.        A table column
D.        A database buffer
E.        A row referenced by an index key

（8/55）Given the statement:
CREATE TABLE t1 (c1 CHAR(1))
Data has been inserted into the table with rows of a,b,c,d,e,f. Given the fol
lowing command is issued:
ALTER TABLE t1 ADD CONSTRAINT con1 CHECK (c1 ='a')
Which of the following occurs?  
(Select the correct response)
A.        Rows with c1 values of b,c,d,e,f are deleted
B.        Rows with c1 values of b,c,d,e,f have c1 set to NULL
C.        The ALTER command will fail as rows violate the constraint
D.        The ALTER command will move the violating rows to the exception table

（9/55）How many DB2 Administration Server (DAS) Instances can be set up per
physical machine?  
(Select the correct response)
A.        0
B.        1
C.        One for each instance on the physical machine
D.        One for each database on the physical machine

（10/55）Which of the following is the result of a successful ROLLBACK statem
ent?  
(Select the correct response)
A.        Held locks are released
B.        Release-pending conditions are undone
C.        Tables in LOAD PENDING are released
D.        Constraint checking conditions are undone
E.        Existing database connections are released

（11/55）Which of the following is the most appropriate reason to consider re
voking the SELECT privilege on the catalog tables from PUBLIC after creating
a database?  
(Select the correct response)
A.        To prevent users from creating tables without proper authority.
B.        Some system catalogs record user data in some columns, and this data may b
e confidential.
C.        To prevent users from viewing passwords for other DB2 userids that DB2 sto
res in the catalog tables.
D.        Some catalog tables are large, so preventing users from viewing them is a
good way to keep users from submitting long-running queries against the catal
ogs.

（12/55）When manually establishing communications from a Windows NT client t
hrough a DB2 Connect gateway to DB2 UDB for OS/390, which of the following is
NOT required to catalog?  
(Select the correct response)
A.        The client.
B.        The database on the DRDA server.
C.        The Database Connection Service database.
D.        The node where the DB2 Connect Gateway is.

（13/55）Which of the following will rebuild a package in the database from t
he existing catalog information?  
(Select the correct response)
A.        bind
B.        rebind
C.        update
D.        rebuild

（14/55）A user has a numeric data column with a maximum value of 100,000. Wh
ich of the following data types will use the minimum amount of storage for th
e column?  
(Select the correct response)
A.        IDENTITY
B.        BIGINT
C.        INTEGER
D.        SMALLINT

（15/55）Which of the following products can be used to perform a dictionary-
based search?  
(Select the correct response)
A.        Net.Data
B.        XML Extender
C.        AVI Extender
D.        Text Extender

（16/55）Given the following transaction:
CREATE TABLE dwaine.mytab (col1 INT, col2 INT)
INSERT INTO dwaine.mytab VALUES (1,2)
INSERT INTO dwaine.mytab VALUES (4,3)
ROLLBACK
Which of the following would be returned from the statement:
SELECT * FROM dwaine.mytab?
  
(Select the correct response)
A.      COL1     COL2
     --------------    -------------
0        Records  selected
B.     COL1      COL2
------     -------
  1          2
1  records selected
C.    SQLCODE -204 indicating that "DWAINE.MYTAB" is an undefined name.
D.    COL1      COL2
-------   --------
   1        2
   4          3
2  records  selected

（17/55）Which of the following privileges is necessary to populate the table
with large amounts of data?  
(Select the correct response)
A.        LOAD
B.        ALTER
C.        UPDATE
D.        IMPORT

（18/55）Given the following table with a primary key on empid:
Emp:
Empid Name
11        Joe Smith
23        Melanie Jones
30   Robert Bruce
49   Janice Baker
66    Mario Diaz
68    Maria Diaton
Give the following statement in an embedded SQL program bound with Repeatable
Read:
Select * from Emp where empid < 55
How many rows in the table will be locked after the statement is run?  
(Select the correct response)
A.        0
B.        1
C.        4
D.        5
E.        6

（19/55）Given the following UPDATE statement:
UPDATE address2 SET housenumber_buildingname=
(SELECT buildingname FROM address1
WHERE address2.id = address1.id)
WHERE HOUSENUMBER_BUILDINGNAME IS NULL
Which of the following describes the result of the statement?
  
(Select the correct response)
A.        The statement will succeed.
B.        The statement will fail because a subquery cannot exist in an UPDATE state
ment.
C.        The statement will succeed only if ADDRESS1.ID and ADDRESS2.ID are defined
as primary keys.
D.        The statement will succeed if the data retrieved from the subquery does no
t have duplicate values for ADDRESS1.ID.


（20/55）Given two embedded SQL programs and the following actions:
       Pgm1                          Pgm2
INSERT INTO mytab VALUES (...)     DELETE FROM mytab
COMMIT                             ROLLBACK
DELETE FROM mytab                  INSERT INTO mytab VALUES (...)
ROLLBACK                           COMMIT
If there exists one (1) row in table mytab before the programs are executed c
oncurrently, how many records will be in the table once the programs complete
?  
(Select the correct response)
A.        0
B.        1
C.        2
D.        3
E.        4

（21/55）Given the following embedded SQL programs:
Program 1:
CREATE TABLE mytab (col1 INT, col2 CHAR(24))
COMMIT
Program 2:
INSERT INTO mytab VALUES ( 20989,'Joe Smith')
INSERT INTO mytab VALUES ( 21334,'Amy Johnson')
COMMIT
DELETE FROM mytab
ROLLBACK
INSERT INTO mytab VALUES ( 23430,'Jason French')
ROLLBACK
INSERT INTO mytab VALUES ( 20993,'Samantha Jones')
COMMIT
DELETE FROM mytab WHERE col1=20993
ROLLBACK
Which of the following indicates the number of records that will be returned
by the statement:
SELECT * FROM mytab?  
(Select the correct response)
A.        0
B.        1
C.        2
D.        3
E.        4

（22/55）Which of the following DB2 components can limit the resource consump
tion of queries?  
(Select the correct response)
A.        DB2 Connect
B.        DB2 Query Patroller
C.        DB2 Performance Monitor
D.        DB2 Net Search Extender

（23/55）Which of the following products must be installed to provide a singl
e point of control for local and remote DB2 databases?  
(Select the correct response)
A.        DB2 Runtime Client
B.        DB2 Administration Client
C.        DB2 Connect Enterprise Edition
D.        DB2 Enterprise-Extended Edition

（24/55）Which of the following is possible once a user has been given mainte
nance authority?  
(Select the correct response)
A.        DB2 userids can be created.
B.        Views can be created on the catalogs.
C.        Statistics can be collected for database objects.
D.        A table can be populated by using the LOAD command.

（25/55）Given the table T1, created by:
CREATE TABLE t1
(
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
c1 CHAR(3)
)
The following SQL statements are issued:
INSERT INTO t1 VALUES (1, 'ABC')
INSERT INTO t1 VALUES (5, 'DEF')
Which of the following values are inserted into the ID column by the followin
g statement?
INSERT INTO t1(c1) VALUES ('XYZ')  
(Select the correct response)
A.        0
B.        1
C.        2
D.        5
E.        6

（26/55）Which of the following Control Center features can be used to update
information for the optimizer to choose the best path to data?  
(Select the correct response)
A.        Show Related
B.        Generate DDL
C.        Run Statistics
D.        Reorganize Table

（27/55）Which of the following tools allows the DBA to set limits, and be al
erted if these limits are exceeded?  
(Select the correct response)
A.        DB2 Index Wizard
B.        DB2 Script Center
C.        DB2 Command Center
D.        DB2 Performance Monitor

（28/55）Which of the following can be accomplished with a single UPDATE stat
ement?  
(Select the correct response)
A.        Updating multiple tables
B.        Updating a view consisting of joined tables
C.        Updating multiple tables based on a WHERE clause
D.        Updating a table based on a sub-select using joined tables

（29/55）Given the statement:
CREATE TABLE t1
(
c1 CHAR(3)
CONSTRAINT c1
CHECK (c1 IN ('A01','B01','C01'))
)
DB2 verifies that the table check constraint is met during which of the follo
wing actions?
(Select the correct response)
A.        Adding data using load
B.        The reorg of the table
C.        The insert of each row in t1]
D.        The creation of the index for the table


（30/55）Given the following embedded SQL programs:
Program 1:
Create table mytab (col1 int, col2 char(24))
Commit
Program 2:
Insert into mytab values ( 20989,'Joe Smith')
Commit
Insert into mytab values ( 21334,'Amy Johnson')
Delete from mytab
Commit
Insert into mytab values ( 23430,'Jason French')
Rollback
Insert into mytab values ( 20993,'Samantha Jones')
Commit
Delete from mytab where col1=20993
Rollback
Which of the following records will be returned by the statement
SELECT * FROM mytab?  
(Select the correct response)
A.        20989, Joe Smith
B.        21334, Amy Johnson
C.        23430, Jason French
D.        20993, Samantha Jones
E.        No records are returned

（31/55）In which of the following locations are the referential constraints
stored?  
(Select the correct response)
A.        The user tables.
B.        The explain tables.
C.        SYSIBM.SYSTRIGGERS.
D.        The system catalog tables.

（32/55）Which of the following SQL statements can remove all rows from a tab
le named COUNTRY?  
(Select the correct response)
A.        DELETE country
B.        DELETE FROM country
C.        DELETE * FROM country
D.        DELETE ALL FROM country

（33/55）Given two embedded SQL program executions with the following actions
:
Pgm1
INSERT INTO mytab VALUES (...)
COMMIT
INSERT INTO mytab VALUES (...)
ROLLBACK

Pgm2
INSERT INTO mytab VALUES (...)
ROLLBACK
INSERT INTO mytab VALUES (...)
COMMIT
How many records will be successfully inserted and retained in the table myta
b?  
(Select the correct response)
A.        1
B.        2
C.        3
D.        4

（34/55）With DBADM authority on the database and given the statements:
CREATE TABLE t1 (c1 CHAR(1))
INSERT INTO t1 VALUES ('b')
CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1 ='a'
INSERT INTO v1 VALUES ('a')
INSERT INTO v1 VALUES ('b')
How many rows would be returned from the statement, SELECT c1 FROM t1?
(Select the correct response)
A.        0
B.        1
C.        2
D.        3

（35/55）Given the table T1 created by:
CREATE TABLE t1
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
c1 CHAR(10) NOT NULL,
c2 CHAR(10)
)
Which of the following INSERT statements will succeed?  
(Select the correct response)
A.        INSERT INTO t1 VALUES (1, 'abc', NULL)
B.        INSERT INTO t1 VALUES (1, NULL, 'def')
C.        INSERT INTO t1 (c1, c2) VALUES ('abc', NULL)
D.        INSERT INTO t1 (c1, c2) VALUES (NULL, 'def')

（36/55）Which of the following products can be used to generate Extensible M
arkup Language documents from DB2 tables?  
(Select the correct response)
A.        Net Search
B.        XML Extender
C.        AVI Extender
D.        Text Extender

（37/55）Which of the following describes why savepoints are NOT allowed insi
de an atomic unit of work?  
(Select the correct response)
A.        Atomic units of work span multiple databases, but savepoints are limited t
o units of work which operate on a single database.
B.        A savepoint implies that a subset of the work may be allowed to succeed, w
hile atomic operations must succeed or fail as a unit.
C.        A savepoint requires an explicit commit to be released, and commit stateme
nts are not allowed in atomic operations such as compound SQL.
D.        A savepoint cannot be created without an active connection to a database,
but atomic operations can contain a CONNECT as a sub-statement.

（38/55）Which of the following DB2 UDB isolation levels will NOT lock any ro
ws during read processing?  
(Select the correct response)
A.        Read Stability
B.        Repeatable Read
C.        Uncommited Read
D.        Cursor Stability

（39/55）For which of the following database objects can locks be obtained?  
(Select the correct response)
A.        View
B.        Table
C.        Trigger
D.        Buffer Pool

（40/55）Which of the following utilities can examine a table and its indexes
and update the system catalogs with the table's statistical information?  
(Select the correct response)
A.        runstats
B.        getstats
C.        check index
D.        chkstats

（41/55）Given the statement:
CREATE TABLE t1
(
c1 INTEGER NOT NULL,
c2 INTEGER,
PRIMARY KEY(c1),
FOREIGN KEY(c2) REFERENCES t2
)
How many non-unique indexes are defined for table t1?  
(Select the correct response)
A.        0
B.        1
C.        2
D.        3

（42/55）Given the following scenario: An application uses a 15 digit value t
o uniquely identify customer transactions. This number is also used for arith
metic operations. Which of the following is the most efficient DB2 data type
for the column definition for this purpose?  
(Select the correct response)
A.        CHAR
B.        CLOB
C.        INTEGER
D.        NUMERIC(15,2)
E.        DECIMAL(15,0)

（43/55）Which of the following is NOT a valid data type on CREATE TABLE?  
(Select the correct response)
A.        CLOB
B.        DOUBLE
C.        NUMERIC
D.        DATETIME

（44/55）The user USER1 is executing the statement
CREATE TABLE app1.table1 (col1 INT, col2 INT)
Which of the following privileges is required by USER1 for the statement to b
e successful?  
(Select the correct response)
A.        CREATEIN for the database
B.        CREATEIN for the schema app1
C.        CREATEIN for the schema user1
D.        CREATEIN for the table table1

（45/55）Which of the following Control Center options shows the dependencies
between a specific view and its tables?  
(Select the correct response)
A.        Show SQL
B.        Show Related
C.        Sample Contents
D.        Customize Columns

（46/55）Which of the following occurs if an application ends abnormally duri
ng an active unit of work?  
(Select the correct response)
A.        Current unit of work is committed
B.        Current unit of work is rolled back
C.        Current unit of work remains active
D.        Current unit of work moves to pending state

（47/55）Which of the following can occur once connected to a database or DRD
A server with an explicit authorization name?  
(Select the correct response)
A.        Omit a user's password.
B.        Change a user's password if the server supports this function.
C.        Omit the name of the database or DRDA server if it is local.
D.        Use the commit option on the connect statement to commit in-doubt units of
work from a previous connection that was terminated.

（48/55）Which of the following tasks can be performed using the ALTER TABLES
PACE statement?  
(Select the correct response)
A.        Assign a bufferpool.
B.        Change the table space name.
C.        Change the type of the table space.
D.        Change the page size of the table space.

（49/55）For a clustering index to be effective in keeping the data in order,
which of the following parameters must be set correctly for the index?  
(Select the correct response)
A.        FREE ROWS
B.        PERCENT FREE
C.        CLUSTERRATIO
D.        CLUSTER FACTOR

（50/55）Which of the following CANNOT be used to restrict specific values fr
om being inserted into a column in a particular table?  
(Select the correct response)
A.        view
B.        index
C.        check constraint
D.        referential constraint

（51/55）Given an embedded SQL program with a single connection, two threads
and the following actions:
Thread 1: INSERT INTO mytab VALUES (...)
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: ROLLBACK
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: COMMIT
How many records will be successfully inserted into the table mytab?  
(Select the correct response)
A.        0
B.        1
C.        2
D.        3

（52/55）Given a table T1, with a column C1 char(3), that contains strings in
upper and lower case letters, which of the following queries will find all r
ows where C1 is the string 'ABC' in any case?  
(Select the correct response)
A.        SELECT * FROM t1 WHERE c1 = 'ABC'
B.        SELECT * FROM t1 WHERE UCASE(c1) = 'ABC'
C.        SELECT * FROM t1 WHERE IGNORE_CASE(c1 = 'ABC')
D.        SELECT * FROM t1 WHERE c1 = 'ABC' WITH OPTION CASE INSENSITIVE

（53/55）Given an embedded SQL program with a single connection, two threads
and the following actions:
Thread 1: INSERT INTO mytab VALUES (...)
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: ROLLBACK
Thread 2: INSERT INTO mytab VALUES (...)
Thread 1: COMMIT
How many records will be successfully inserted into the table mytab?  
(Select the correct response)
A.        0
B.        1
C.        2
D.        3

（54/55）If a DB2 Warehouse Manager toolkit is selected during the installati
on of DB2 UDB Version 7.1, which of the following databases must be defined?

(Select the correct response)
A.        None
B.        Target Database
C.        Source Database
D.        Control Database

（55/55）User2 has DBADM authority on database DB1. This allows the user to d
o which of the following?  
(Select the correct response)
A.        Drop database DB1
B.        Backup database DB1
C.        Create tables in any database
D.        Create tables in database DB1