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

image from book

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;

image from book

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

image from book

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

image from book

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

image from book

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

image from book

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

image from book

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

image from book

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

image from book

10. 

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

  • A. Routine

  • B. Function

  • C. Procedure

  • D. Trigger

image from book

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

image from book

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)

image from book

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 sysibm.sysdummy1 

What value will be returned by the query?

  • A. 5

  • B. 10

  • C. 20

  • D. 30

image from book

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')

image from book

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

image from book

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] <WITH COMPARISONS> 

where:

TypeName

Identifies the name that is to be assigned to the distinct data type to be created.

SourceDataType

Identifies the built-in data type that the distinct data type to be created is to be based on.

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.




DB2 9 Fundamentals Certification Study Guide
DB2 9 Fundamentals: Certification Study Guide
ISBN: 1583470727
EAN: 2147483647
Year: 2007
Pages: 93

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net