IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 211:

  Which object may be used during query optimization to improve the performance for a subset of SELECT queries?

  A. MQT

  B. Trigger

  C. Read Only Cursor

  D. Stored Procedure

  Correct Answer: A

• Question 212:

  Given the following information:

  Protocol: TCP/IP Port Number: 5000 Host Name: DB_SERVER Database Name: TEST_DB Database Server Platform: Linux

  A. CATALOG DATABASEtest_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000 OSTYPE LINUX;

  B. CATALOG TCPIP NODE 5000 REMOTE SERVERdb_server OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

  C. CATALOG TCPIP NODEdb_server REMOTE db_server SERVER 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

  D. CATALOG TCPIP NODEdb_server REMOTE db_server PORT 5000 OSTYPE LINUX; CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

  Correct Answer: C

• Question 213:

  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

  Correct Answer: B

• Question 214:

  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

  Correct Answer: B

• Question 215:

  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

  Correct Answer: D

• Question 216:

  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

  Correct Answer: A

• Question 217:

  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

  Correct Answer: D

• Question 218:

  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 view2VALUES(50, 'abc')

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

  C. INSERT INTO view2VALUES(150, 'abc')

  D. INSERT INTO view1VALUES(100, 'abc')

  Correct Answer: C

• Question 219:

  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

  Correct Answer: C

• Question 220:

  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

  Correct Answer: A