1. | While attempting to connect to a database stored on an iSeries server from a Windows client, the following message was displayed:
Which of the following actions can be used to help determine why this message was displayed?
|
|
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?
|
|
3. | In which of the following scenarios would a stored procedure be beneficial?
|
|
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?
|
|
5. | An alias can be an alternate name for which two of the following DB2 objects?
|
|
6. | Which of the following DB2 objects can be referenced by an INSERT statement to generate values for a column?
|
|
7. | Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)?
|
|
8. | Which of the following is an accurate statement about packages?
|
|
9. | Which of the following events will NOT cause a trigger to be activated?
|
|
10. | Which of the following DB2 objects is NOT considered executable using SQL?
|
|
11. | Which of the following is NOT an accurate statement about views?
|
|
12. | Which of the following SQL statements can be used to create a DB2 object to store numerical data as EURO data?
|
|
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?
|
|
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?
|
|
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?
|
|
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:
The supported functional types of routines are:
The supported routine implementations are:
| ||||
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:
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. |