db2 V9 730 认证练习三(数据库及其对象)

Practice Questions

1. While attempting to connect to a database stored on an iSeries server from a Windows

client, the following message was displayed:



SQL1013N The database alias name or database name "TEST_DB" could not be found.

Which of the following actions can be used to help determine why this message was

displayed?

A. Execute the LIST REMOTE DATABASES command on the server; look for an entry

for the TEST_DB database



B. Execute the LIST DCS DIRECTORY command on the server; look for an entry

for the TEST_DB database



C. Execute the LIST REMOTE DATABASES command on the client; look for an entry

for the TEST_DB database



D. Execute the LIST DCS DIRECTORY command on the client; look for an entry

for the TEST_DB database



2. A database named TEST_DB resides on a z/OS system and listens on port 446. The TCP/IP

address for this system is 192.168.10.20 and the TCP/IP host name is MYHOST. Which

of the following commands is required to make this database accessible to a Linux

client?



A. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20;

CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr;

CATALOG DCS DATABASE zos_db AS test_db;



B. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20;

CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr;



C. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446;

CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr;



D. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446;

CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr;

CATALOG DCS DATABASE zos_db AS test_db;

3. In which of the following scenarios would a stored procedure be beneficial?

A. An application running on a remote client needs to be able to convert degrees

Celsius to degrees Fahrenheit and vice versa



B. An application running on a remote client needs to collect three input

values, perform a calculation using the values provided, and store the input

data, along with the results of the calculation in two different base tables



C. An application running on a remote client needs to track every modification

made to a table that contains sensitive data



D. An application running on a remote client needs to ensure that every new

employee that joins the company is assigned a unique, sequential employee

number



4. If the following SQL statements are executed in the order shown:

CREATE TABLE orders

(order_num INTEGER NOT NULL,

Buyer_name VARCHAR(35),

Amount NUMERIC(5,2));

CREATE UNIQUE INDEX idx_orderno ON orders(order_num);

Which of the following describes the resulting behavior?

A. Every ORDER_NUM value entered must be unique; whenever the ORDERS table

is queried rows should be displayed in order of increasing ORDER_NUM values



B. Every ORDER_NUM value entered must be unique; whenever the ORDERS table

is queried rows will be displayed in no particular order



C. Duplicate ORDER_NUM values are allowed; no other index can be created for

the ORDERS table that reference the ORDER_NUM column



D. Every ORDER_NUM value entered must be unique; no other index can be created

for the ORDERS table that reference the ORDER_NUM column



5. An alias can be an alternate name for which two of the following DB2 objects?











A. Sequence

B. Trigger

C. View

D. Schema

E. Table

6. Which of the following DB2 objects can be referenced by an INSERT statement to

generate values for a column?

A. Sequence



B. Identity column



C. Trigger



D. Table function



7. Which of the following is NOT an attribute of Declared Global Temporary Tables

(DGTTs)?

A. Each application that defines a DGTT has its own instance of the DGTT



B. Two different applications cannot create DGTTs that have the same name



C. DGTTs can only be used by the application that creates them, and only for

the life of the application





D. Data stored in a DGTT can exist across transaction boundaries

8. Which of the following is an accurate statement about packages?

A. Packages provide a logical grouping of database objects.



B. Packages contain control structures that are considered the bound form for

SQL statements



C. Packages describe the objects in a DB2 database and their relationship to

each other



D. Packages may be used during query optimization to improve the performance

for a subset of SELECT queries



9. Which of the following events will NOT cause a trigger to be activated?

A. A select operation



B. An insert operation



C. An update operation



D. A delete operation



10. Which of the following DB2 objects is NOT considered executable using SQL?

A. Routine



B. Function



C. Procedure



D. Trigger



11. Which of the following is NOT an accurate statement about views?

A. Views are publicly referenced names and no special authority or privilege

is needed to use them.



B. Views can be used to restrict access to columns in a base table that contain

sensitive data



C. Views can be used to store queries that multiple applications execute on

a regular basis in a database



D. Views support INSTEAD OF triggers



12. Which of the following SQL statements can be used to create a DB2 object to store

numerical data as EURO data?

A. CREATE NICKNAME euro FOR DECIMAL (9,3)



B. CREATE ALIAS euro FOR DECIMAL (9,3)



C. CREATE DISTINCT TYPE euro AS DECIMAL (9,3)



D. CREATE DATA TYPE euro AS DECIMAL (9,3)



13. A sequence was created with the DDL statement shown below:

CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10

User USER1 successfully executes the following statements in the order shown:

VALUES NEXT VALUE FOR my_seq INTO :hvar;

VALUES NEXT VALUE FOR my_seq INTO :hvar;

User USER2 successfully executes the following statements in the order shown:

ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5;

VALUES NEXT VALUE FOR my_seq INTO :hvar;

After users USER1 and USER2 are finished, user USER3 executes the following query:

SELECT NEXT VALUE FOR my_seq FROM my1

What value will be returned by the query?

A. 5



B. 10



C. 20



D. 30



14. Given the following statements:

CREATE TABLE tab1 (c1 INTEGER, c2 CHAR(5));

CREATE VIEW view1 AS SELECT c1, c2 FROM tab1 WHERE c1 < 100;

CREATE VIEW view2 AS SELECT c1, c2 FROM view1

WITH CASCADED CHECK OPTION;

Which of the following INSERT statements will fail to execute?

A. INSERT INTO view2 VALUES(50, 'abc')



B. INSERT INTO view1 VALUES (100, 'abc')



C. INSERT INTO view2 VALUES(150, 'abc')



D. INSERT INTO view1 VALUES(100, 'abc')



15. Given the following statements:

CREATE TABLE t1 (c1 INTEGER, c2 CHAR(5));

CREATE TABLE t1audit (user VARCHAR(20), date DATE,

action VARCHAR(20));

CREATE TRIGGER trig1 AFTER INSERT ON t1

FOR EACH ROW

MODE DB2SQL

INSERT INTO t1audit VALUES (CURRENT USER, CURRENT DATE,

'Insert');

If user USER1 executes the following statements:

INSERT INTO t1 VALUES (1, 'abc');

INSERT INTO t1 (c1) VALUES (2);

UPDATE t1 SET c2 = 'ghi' WHERE c1 = 1;

How many new records will be written to the database?

A. 0



B. 2



C. 3



D. 4



Answers

1. The correct answer is D. In order to access a remote database from a client workstation,

the database must be cataloged in the system database directory of both the client and

the server

and the server workstation must be cataloged in the client's node directory.

(The entry in the node directory tells the DB2 Database Manager how to connect to the server

to get access to the database stored there.) Because the information needed to connect

to DRDA host databases is different from the information used to connect to LAN-based

databases, information about remote host or iSeries databases is kept in a special

directory known as the Database Connection Services (DCS) directory. If an entry in the

DCS directory has a database name that corresponds to the name of a database stored in

the system database directory, the specified Application Requester (which in most cases

is DB2 Connect) can forward SQL requests to the database that resides on a remote DRDA

server. The contents of the DCS directory file can be viewed by executing the LIST DCS

DIRECTORY command. If there is no record for a zSeries or iSeries database in the DCS

directory, no database connection can be established.

2. The correct answer is D. In order to access a remote database on a z/OS server, the database

must be cataloged in the system database directory of the client, the server must be

cataloged in the client's node directory, and an entry for the database must exist in the

DCS directory. Answer D illustrates the proper way to catalog the server, the DCS database,

and create a corresponding entry for the DCS database in the system database directory.

3. The correct answer is B. A scalar user-defined function would be the best option for the

requirements outlined in answer A; an UPDATE trigger and a DELETE trigger that inserts

records into an activity table every time update and delete operations are performed on

a table containing sensitive data would be the best way to accomplish the requirements

outlined in answer C; and an identity column or sequence could be used to address the

requirements shown in answer D.

4. The correct answer is A. If the UNIQUE clause is specified when the CREATE INDEX statement

is executed, rows in the table associated with the index will not have two or more

occurrences of the same values in the set of columns that make up the index key. Furthermore,

the creation of an index provides a logical ordering of the rows of a table so in this

example, rows inserted into the ORDERS table will be ordered ORDER_NUM values, in ascending

order.

5. The correct answers are C and E. An alias is simply an alternate name for a table or view.

(Aliases can also be created for nicknames that refer to tables or views found on federated

systems.) Once created, an alias can be referenced the same way the table or view the alias

refers to can be referenced.

6. The correct answer is A. Sequences, identity columns, and triggers can be used to

automatically generate values for columns. However, only sequences can be referenced in

an INSERT statement.

7. The correct answer is B. Unlike base tables, whose descriptions and constraints are stored

in the system catalog tables of the database to which they belong, declared temporary tables

are not persistent and can only be used by the application that creates them-and only for

the life of the application. When the application that creates a declared temporary table

terminates, the rows of the table are deleted, and the description of the table is dropped.

(However, data stored in a temporary table can exist across transaction boundaries.)

Another significant difference focuses on naming conventions: base table names must be

unique within a schema, but because each application that defines a declared temporary

table has its own instance of that table, it is possible for many applications to create

declared temporary tables that have the same name.

8. The correct answer is B. A package is an object that contains the information needed to

process SQL statements associated with a source code file of an application program. When

an Embedded SQL source code file is precompiled, a corresponding package that contains,

among other things, the access plans that will be used to process each SQL statement

embedded in the source code file is produced. (Access plans contain optimized information

that the DB2 Database Manager uses to execute SQL statements.) This package must reside

in a DB2 database that contains the data objects referenced by the package before the

corresponding application can be executed against that database. The process of creating

and storing a package in a DB2 database is known as "binding," and by default, packages

are automatically bound to a database during the precompile process.

Schemas provide a logical grouping of database objects; the system catalog describes the

objects in a DB2 database and their relationship to each other; and Multi-dimensional

Clustering Tables (MDCs) may be used during query optimization to improve the performance

for a subset of SELECT queries.

9. The correct answer is A. A trigger can be activated whenever an insert, update, or delete

operation is performed against the subject table that is associated with the trigger.

10. The correct answer is D. Routines are a type of database object that you can use to

encapsulate logic that can be invoked like a programming sub-routine. There are many

different types of routines available; routines can be grouped in different ways, but are

primarily grouped by their system or user definitions, by their functionality, and by their

implementation. The supported routine definitions are:

System-defined routines



User-defined routines



The supported functional types of routines are:

Procedures (also called stored procedures)



Functions



Methods



The supported routine implementations are:

Built-in routines



Sourced routines



SQL routines



External routines



11. The correct answer is A. Views can be used to restrict access to columns in a base table

that contain sensitive data, views can be used to store queries that multiple applications

execute on a regular basis in a database, and views support INSTEAD OF triggers. Aliases

are publicly referenced names that require no special authority or privilege to use.

12. The correct answer is C. A distinct data type is a user-defined data type that is derived

from one of the built-in data types available with DB2. Although a distinct data type shares

a common internal representation with a built-in data type, it is considered a separate

data type that is distinct from any other data type (hence, the "distinct" in the name).

Distinct user-defined data types can be created by executing the CREATE DISTINCT TYPE SQL

statement. The basic syntax for this statement is:

CREATE DISTINCT TYPE [TypeName]

AS [SourceDataType]

where:

TypeName Identifies the name that is to be assigned to the distinct data type to

be created.

is to be based on.

SourceDataType Identifies the built-in data type that the distinct data type to be created

Thus, if you wanted to create a distinct data type to store EURO data, you could do so

by executing an SQL statement like that in answer C.

13. The correct answer is C. The first VALUES statement executed by user USER1 will return

the value 10; the second will return the value 20. The ALTER statement changes the behavior

of the sequence and the VALUES statement executed by user USER2 will return the value 5.

When user USER3 executes queries the database to obtain the next sequence number, the value

30 is returned. Why? Because when user USER2 obtained a value from the sequence, four more

values were generated and cached since a cache value of 5 was specified for the sequence.

Since an increment value of 5 was also used, the numbers cached were: 10, 15, 20, and 25.

But none of the cached values were used - they were discarded when user USER2 terminated

their database connection. Then, when user USER3 queries the sequence for the next number

available, it received the number 30 because that was, in fact, the next number in the

sequence.

14. The correct answer is C. The statement "INSERT INTO view2 VALUES(150, 'abc')" will fail

because the value 150 is greater than 100; because view VIEW2 was created with the WITH

CASCADED CHECK OPTION specified, the "WHERE c1 < 100" clause used to create view VIEW1

became a constraint that is used to validate insert and update operations that are performed

against view VIEW2 to ensure that all rows inserted into or updated in the base table the

view refers to conform to the view's definition.

15. The correct answer is D. Each time a record is inserted into table T1, trigger TRIG1 is

fired and a record is written to the table T1AUDIT. If both tables were queried after the

update operation completes, the results would look something like this:

SELECT * FROM t1

C1 C2

--------------

1 ghi

2 -

2 record(s) selected.

SELECT * FROM t1audit

USER DATE ACTION

--------------------------------------

RSANDERS 01/20/2007 Insert

RSANDERS 01/20/2007 Insert

2 record(s) selected.

In order to track update and delete operations performed against table T1, similar UPDATE

and DELETE triggers would need to be created.