Working with Databases and Database Objects | DB2 9 Fundamentals: Certification Study Guide

44.	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
45.	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;
46.	Which of the following statements will catalog the database MYDB on the node MYNODE and assign it the alias MYNEWDB? A. CATALOG DATABASE mynewdb AT NODE mynode B. CATALOG DATABASE mynewdb AS mydb AT NODE mynode C. CATALOG DATABASE mydb AT NODE mynode D. CATALOG DATABASE mydb AS mynewdb AT NODE mynode
47.	Which of the following are NOT stored in the system catalog tables? A. SQL statements used to create views B. SQL statements used to create triggers C. SQL statements used to create constraints D. Table names
48.	Which of the following tools can NOT be used to catalog a database? A. Control Center B. SQL Assist C. Configuration Assistant D. Command Line Processor
49.	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
50.	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
51.	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
52.	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
53.	If a view named V1 is created in such a way that it references every column in a table named EMPLOYEE except a column named SALARY, which of the following is NOT an accurate statement? A. View V1 can be used in the same context as the EMPLOYEE table for all data retrieval operations that do not acquire SALARY information B. View V1 can be used as a data source for other views C. View V1 does not have to reside in the same schema as the EMPLOYEE table D. All data, except SALARY data that is stored in the EMPLOYEE table is copied to the physical location associated with view V1
54.	Which of the following would NOT provide access to data stored in table TABLE1 using the name T1? A. An alias named T1 that references table TABLE1 B. A view named T1 that references table TABLE1 C. A schema named T1 that references table TABLE1 D. An alias named T1 that references a view named V1 that references table TABLE1
55.	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
56.	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
57.	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')
58.	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
59.	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
60.	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
61.	Given the following information: Protocol: TCP/IP Port Number: 5000 Host Name: DB_SERVER Database Name: TEST_DB Database Server Platform: Linux Which of the following will allow a client to access the database stored on the server? A. CATALOG DATABASE test_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000 OSTYPE LINUX; B. CATALOG TCPIP NODE 5000 REMOTE SERVER db_server OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER; C. CATALOG TCPIP NODE db_server REMOTE db_server SERVER 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER; D. CATALOG TCPIP NODE db_server REMOTE db_server PORT 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;
62.	A declared temporary table is used for which of the following purposes? A. Backup purposes B. Storing intermediate results C. Staging area for load operations D. Sharing result data sets between applications
63.	Which of the following DB2 objects is NOT considered executable using SQL? A. Routine B. Function C. Procedure D. Trigger
64.	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
65.	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)

Answers

44.

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.

45.

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.

46.

The correct answer is D. The correct syntax for the CATALOG DATABASE command is:

 CATALOG [DATABASE | DB] [DatabaseName] <AS [Alias]> <ON [Path] | AT NODE [NodeName]> <AUTHENTICATION [AuthenticationType]> <WITH "[Description]">

where:

DatabaseName	Identifies the name that has been assigned to the database to be cataloged.
Alias	Identifies the alias that is to be assigned to the database when it is cataloged.
Path	Identifies the location (drive and/or directory) where the directory hierarchy and files associated with the database to be cataloged are physically stored.
NodeName	Identifies the node where the database to be cataloged resides. The node name specified should match an entry in the node directory file (i.e., should correspond to a node that has already been cataloged).
AuthenticationType	Identifies where and how authentication is to take place when a user attempts to access the database. The following values are valid for this parameter: SERVER, CLIENT, SERVER_ENCRYPT, KERBEROS TARGET PRINCIPAL [PrincipalName] (where PrincipalName is the fully qualified Kerberos principal name for the target server), DATA_ENCRYPT, and GSSPLUGIN.
Description	A comment used to describe the database entry that will be made in the database directory for the database to be cataloged. The description must be enclosed by double quotation marks.

47.

The correct answer is A. The system catalog tables are used to keep track of information like database object definitions, database object dependencies, database object privileges, column data types, and table constraints. In most cases, the complete characteristics of a database object are stored in one or more system catalog tables when the object is created. However in some cases, such as when triggers and constraints are defined, the actual SQL used to create the object is stored instead. Characteristics of views, not the SQL used to create them, are stored in the system catalog tables.

48.

The correct answer is B. The Control Center and the Configuration Assistant provide visual tools for cataloging databases while the CATALOG DATABASE command can be executed from the Command Line Processor. Since the CATALOG DATABASE command is a command and not an SQL statement, it cannot be issued from the SQL Assist utility.

49.

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.

50.

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.

51.

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.

52.

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.

53.

The correct answer is D. Although a view looks like a base table, it does not exist as a table in physical storage; therefore, it does not contain data. Instead, a view refers to data that is stored in other base tables so data stored in the EMPLOYEE table would not be copied to view V1.

54.

The correct answer is C. An alias is an alternate name for a table or view; therefore, it is possible to create an alias named T1 for the table TABLE1, and it is possible to create an alias named T1 for a view named V1. Views are used to provide a different way of looking at the data stored in one or more base tables. Schemas, on the other hand, are used to logically group data; therefore, a schema named T1 could be used to group aliases and views that reference table TABLE1, but it could not be used to provide access to the data stored in table TABLE1.

55.

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.

56.

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 his database connection. Then, when user USER3 queried the sequence for the next number available, he received the number 30 because that was, in fact, the next number in the sequence.

57.

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.

58.

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.

59.

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.

60.

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 (MCDs) may be used during query optimization to improve the performance for a subset of SELECT queries.

61.

The correct answer is C. In this case, both the node and the database must be cataloged by executing the CATALOG … NODE and CATALOG DATABASE commands. Answer C is the only answer that uses the correct syntax for these two commands.

62.

The correct answer is B. Declared temporary tables are used to hold temporary data on behalf of a single application and are automatically destroyed when the application that declared them disconnects from the database. Declared temporary tables are not used for backup purposes, to save data for load operations, or to share result data sets between applications.

63.

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

64.

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.

65.

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.