IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 201:

  Given the following DDL and INSERT statements:

  CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 > 10; CREATE VIEW v2 AS SELECT col1 FROM

  v1 WITH CASCADED CHECK OPTION; CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 < 100;

  INSERT INTO v1 VALUES(5);

  INSERT INTO v2 VALUES(5);

  INSERT INTO v3 VALUES(20);

  INSERT INTO v3 VALUES(100);

  How many of these INSERT statements will be successful?

  A. 0

  B. 1

  C. 2

  D. 3

  Correct Answer: C

• Question 202:

  Which of the following describes the objects of a DB2 database and their relationships?

  A. Instance

  B. Table space

  C. System catalog

  D. Schema repository

  Correct Answer: C

• Question 203:

  Which of the following DB2 objects are publicly referenced names that require no special authority or privilege to use them?

  A. View

  B. Alias

  C. Table

  D. Package

  Correct Answer: B

• Question 204:

  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

  Correct Answer: A

• Question 205:

  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)

  Correct Answer: C

• Question 206:

  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

  Correct Answer: B

• Question 207:

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

  A. Routine

  B. Function

  C. Procedure

  D. Trigger

  Correct Answer: D

• Question 208:

  Within the application MY_APP the following PREVIOUS VALUE expression references the sequence MY_SEQ: PREVIOUS VALUE FOR my_seq After which of the following events will the most recently generated value of MY_SEQ persist so that it can be returned to the active MY_APP application?

  A. After a ROLLBACK is issued

  B. After the sequence is altered

  C. After the sequence is dropped

  D. After the current session ends

  Correct Answer: A

• Question 209:

  A DRDA host database resides on a z/OS or an i5/OS system and listens on port 446. The TCP/IP address for this system is 192.168.10.1 and the TCP/IP host name is myhost. Which of the following commands is required to update the local node directory so that a DB2 client can access this DRDA database?

  A. CATALOG TCPIP NODEmyhost REMOTE db2srv SERVER 446

  B. CATALOG TCPIP NODE mydb2srv REMOTEmyhost SERVER 446

  C. CATALOG TCPIP NODEmyhost REMOTE db2srv SERVER 192.168.10.1

  D. CATALOG TCPIP NODE mydb2srv REMOTEmyhost SERVER 192.168.10.1

  Correct Answer: B

• Question 210:

  Given the following ALTER SEQUENCE statement:

  ALTER SEQUENCE myseq RESTART WITH 0 INCREMENT BY 1 NO MAXVALUE CACHE 5 ORDER

  Assuming that the sequence had reached a value of 100 prior to the RESTART, which of the following is true?

  A. The next value will be 0 and the sequence will never use the values 101 to 105.

  B. The next value will be 101 to ensure uniqueness between existing and newly generated sequence values.

  C. Previously cached values are retained by DB2, and after the restart, will be used for values 101 to 105.

  D. The next value will be 0 and DB2 will not ensure uniqueness between existing and newly generated values.

  Correct Answer: D